Skip to content

Instantly share code, notes, and snippets.

@soiqualang
Created April 13, 2024 05:09
Show Gist options
  • Save soiqualang/6782b6acdf61317b7f4b179264beba8a to your computer and use it in GitHub Desktop.
Save soiqualang/6782b6acdf61317b7f4b179264beba8a to your computer and use it in GitHub Desktop.
-- Tim kiem cac cua hang TGDĐ cach 1000m
SELECT * FROM hcm_tgdd WHERE ST_DWithin(
    ST_Transform(geom, 32648),
    ST_Transform(ST_SetSRID(ST_Point(106.69093,10.77082), 4326), 32648),
    1000
);

-- Tao diem tu toa do
SELECT ST_SetSRID(ST_Point(106.69093, 10.77082), 4326);


-- Tao buffer 1000m
SELECT ST_Buffer(ST_SetSRID(ST_Point(106.69093, 10.77082), 4326), 0.01) AS buffer;

-- Chuyen tu do sang m
SELECT ST_Buffer(ST_Transform(ST_SetSRID(ST_Point(106.69093, 10.77082), 4326), 32648), 1000) AS buffer;


-- Tim cua hang trong quan 1
SELECT * FROM points
WHERE ST_Contains(polygon_geom, points_geom);

SELECT * FROM hcm_tgdd
WHERE ST_Contains((SELECT geom FROM "hcm_quanhuyen" WHERE "name_2" = 'Quận 1'), geom);

image

@soiqualang
Copy link
Author

POLYGON((104.91514205932 19.444427490235, 104.51963424682 19.224700927735, 104.53062057495 18.73031616211, 104.93711471557 18.367767333985, 105.77207565307 18.499603271485, 105.58530807495 19.070892333985, 104.91514205932 19.444427490235))

POLYGON((105.22275924682 19.74105834961, 105.06895065307 19.070892333985, 105.59629440307 18.301849365235, 106.29941940307 18.477630615235, 106.38731002807 18.90609741211, 105.57432174682 19.76303100586, 105.22275924682 19.74105834961))

LINESTRING(104.23398971557 18.86215209961, 106.49717330932 19.356536865235)

POINT(105.42051315307 18.873138427735)


--st_intersection
select st_intersection(GeomFromEWKT('SRID=4326;POLYGON((104.91514205932 19.444427490235, 104.51963424682 19.224700927735, 104.53062057495 18.73031616211, 104.93711471557 18.367767333985, 105.77207565307 18.499603271485, 105.58530807495 19.070892333985, 104.91514205932 19.444427490235))'),GeomFromEWKT('SRID=4326;POLYGON((105.22275924682 19.74105834961, 105.06895065307 19.070892333985, 105.59629440307 18.301849365235, 106.29941940307 18.477630615235, 106.38731002807 18.90609741211, 105.57432174682 19.76303100586, 105.22275924682 19.74105834961))')) as the_geom, 1 as id

--st_difference
select st_difference(GeomFromEWKT('SRID=4326;POLYGON((104.91514205932 19.444427490235, 104.51963424682 19.224700927735, 104.53062057495 18.73031616211, 104.93711471557 18.367767333985, 105.77207565307 18.499603271485, 105.58530807495 19.070892333985, 104.91514205932 19.444427490235))'),GeomFromEWKT('SRID=4326;POLYGON((105.22275924682 19.74105834961, 105.06895065307 19.070892333985, 105.59629440307 18.301849365235, 106.29941940307 18.477630615235, 106.38731002807 18.90609741211, 105.57432174682 19.76303100586, 105.22275924682 19.74105834961))')) as the_geom, 1 as id

--st_union
select st_union(GeomFromEWKT('SRID=4326;POLYGON((104.91514205932 19.444427490235, 104.51963424682 19.224700927735, 104.53062057495 18.73031616211, 104.93711471557 18.367767333985, 105.77207565307 18.499603271485, 105.58530807495 19.070892333985, 104.91514205932 19.444427490235))'),GeomFromEWKT('SRID=4326;POLYGON((105.22275924682 19.74105834961, 105.06895065307 19.070892333985, 105.59629440307 18.301849365235, 106.29941940307 18.477630615235, 106.38731002807 18.90609741211, 105.57432174682 19.76303100586, 105.22275924682 19.74105834961))')) as the_geom, 1 as id

--st_symdifference
select st_symdifference(GeomFromEWKT('SRID=4326;POLYGON((104.91514205932 19.444427490235, 104.51963424682 19.224700927735, 104.53062057495 18.73031616211, 104.93711471557 18.367767333985, 105.77207565307 18.499603271485, 105.58530807495 19.070892333985, 104.91514205932 19.444427490235))'),GeomFromEWKT('SRID=4326;POLYGON((105.22275924682 19.74105834961, 105.06895065307 19.070892333985, 105.59629440307 18.301849365235, 106.29941940307 18.477630615235, 106.38731002807 18.90609741211, 105.57432174682 19.76303100586, 105.22275924682 19.74105834961))')) as the_geom, 1 as id

image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment