danfran
8/1/2017 - 3:42 PM

Postgres - Adding geo index and query within distance

Postgres - Adding geo index and query within distance

Add geometry column first

ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

Update geometry with points on WGS84/RDIS-4326 column with latitude and longitude from other columns

UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

Create indexes

CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Find nearest points within 5 miles for a specific point

SELECT latitude, longitude,geom FROM my_table WHERE ST_DWithin(geom, ST_MakePoint(1.292846,52.63066)::geography, 1609 * 5);