how to find ou lat long with 2KM radius using Postgres-CodePudding

I have a postgres table with lat-long values stored as point (Geometric data type). I am interested to query all rows with 2km radius for the given lat-long values. Also, I am expecting for a suitable datatype for this, currently I stored these values as POINT. But on some investigation, I found to use POLYGON here. But even though I couldn't able to achieve the results what expected.

Can any one point me the exact query with suitable GTS functions to achieve this

CodePudding user response：

https://postgis.net/workshops/postgis-intro/spatial_relationships.html
example explanation:

SELECT name
FROM nyc_streets
WHERE ST_DWithin(
        geom,
        ST_GeomFromText('POINT(583571 4506714)',26918),
        10
      );

The first "geom" refer to the column name in nyc_streets.
ST_GeomFromText: transform text to geom. 26918 is srid.
10 : 10 meters.

CodePudding user response：

To query geometries within a certain radius you might wanna use ST_DWithin. In order to use it with metres you have to either use a SRS that has metre as unit, such as EPSG:26918, or use geography instead of geometry:

SELECT *
FROM mytable
WHERE ST_DWithin(ST_MakePoint(1,2)::geography, -- 1=long / 2=lat
                 geom::geography,2000);

In case you're dealing with different geometry types, such as polygon or linestring, you might wanna use ST_GeogFromText instead of ST_MakePoint:

SELECT *
FROM mytable
WHERE ST_DWithin(ST_GeogFromText('POINT(1 2)'),
                 geom::geography,2000); 

SELECT *
FROM mytable
WHERE  ST_DWithin(ST_GeogFromText('POLYGON((1 1,2 2,3 3,1 1))'),
                  geom::geography,2000);

Keep in mind that transforming a geometry is much more than just change its SRID - check ST_Transform.