Use ST_DWithin to query pairs of geometry points within 400 miles in a PostgreSQL table

user_11698640 0
Viewed 188

I have a table called h2combines in Postgres that has two points-geometry fields: geom1 and geom2, and some other fields. I want to select all records that geom1 and gemo2 are with 400 miles. I tried this:

SELECT * from h2combines WHERE ST_DWithin(geom1, geom2, 643738);

However, it returned all rows. Seems I misunderstand something here. Thanks!

cursor postgis gis postgresql geometry
1 Answers

Welcome to SO.

To get the distance in meters/miles you have to cast your geometries to geography, e.g.

SELECT * FROM h2combines 
WHERE ST_DWithin(geom1::geography, geom2::geography, 643737.6);

Keep in mind that calculations using GEOMETRY and GEOGRAPHY are made differently, and so are their results. GEOGRAPHY calculates the coordinates over an spherical surface (which can be much slower than GEOMETRY) and uses meters as unit of measurement, while GEOMETRY uses a planar projection and uses the SRS unit.

user_22753880
Related