Fringe Division

Appearance

PostGIS ​

Init ​

sql
CREATE EXTENSION postgis;

SQL ​

Add GEOM column ​

sql
-- use `ST_GeomFromText` for `WKT`
ALTER TABLE province ADD COLUMN geom geometry(Point, 4326);

UPDATE province
SET geom = ST_SetSRID(
    ST_MakePoint(
        cast(longitude AS DOUBLE PRECISION),
        cast(latitude AS DOUBLE PRECISION)
        ),
    4326)
WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

Cast projection unit to meter ​

sql
ST_Distance(
    geom::geography,
    ST_MakePoint(longitude,latitude)::geography) <= 3000

Find polygon from point ​

sql
-- single point
ST_DWithin(ST_SetSRID(ST_POINT(longitude,latitude),4326)::geography, geom,0)

 against another table
SELECT *
FROM a JOIN b
ON ST_WITHIN(points.geom, boundary.geom)
LIMIT 10;

Find distance betwee x & y ​

sql
-- unit depends on projection
ST_Distance(
    the_geom::geography,
    ST_MakePoint(longitude,latitude)::geography)
AS distance_from_holy_land

Count points in polygon ​

sql
SELECT boundary.gid, count(points.geom) AS totale
FROM boundary LEFT JOIN points
ON ST_CONTAINS(boundary.geom,points.geom)
GROUP BY boundary.gid;