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

Question

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

score 0 · Answer 1 · answered Jul 04 '22 at 04:31

0

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.

answered Jul 04 '22 at 04:31

jian

4,119
1
17
32

Your understanding of `ST_GeomFromText` is wrong. It does **not** transform a geometry, but it simply creates a geometry from a WKT literal and sets a SRS if provided. Check it yourself: `SELECT ST_AsEWKT(ST_GeomFromText('SRID=4326;POINT(1 1)',26918))` <- according to your logic it should transform the geometry, but in the end all you have is **exactly the same coordinate** with a different (wrong!) SRS. – Jim Jones Jul 04 '22 at 11:22
1

@JimJones You are right. `SELECT ST_AsEWKT(ST_GeomFromText('SRID=4326;POINT(1 1)',26918))` srid will be 26918. – jian Jul 04 '22 at 12:16
Yes, and it's a big issue. The coordinates that are originally `WGS84` will be invalid if seen as `EPSG:26918`. – Jim Jones Jul 04 '22 at 12:35

Jim Jones · Answer 2 · 2022-07-05T07:28:44.227

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, -- casting the 'geometry' to 'geography'
                 2000); -- 2km radius

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.

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

2 Answers2