0

there is one thing I do not understand when using the following Query in geometries which are all SRID 4326.

WITH s AS (SELECT * FROM de_sim_points_start WHERE id = 6545)
SELECT e.*, ST_Distance(e.geom, s.geom, true) as distance FROM de_sim_points_end AS e, s
WHERE ST_DWithin(e.geom, s.geom, 10000) 
AND e.parent_geometry = s.parent_geometry
ORDER BY distance

Result looks like

ID;parent_geometry;geom;distance
14033;"092730137137";"0101000020E6100000FE6FACF6AA8027409C2C8D9A0C744840";0.245261803286329
14031;"092730137137";"0101000020E6100000CCDBF35375812740825574F5F8734840";0.243900901309088
14029;"092730137137";"0101000020E6100000DCEE415F6A812740F7F2A95305744840";0.243893460499341
14026;"092730137137";"0101000020E61000003510CD8FB781274033F5CA0231744840";0.243011470475221
13535;"092730137137";"0101000020E610000039E0FBE271802740C6906BE8D6754840";0.242827846195593
14008;"092730137137";"0101000020E610000020C337BB18822740C5250EA2F3734840";0.242728103270343
13999;"092730137137";"0101000020E61000002A9807A3668227409825450CE4734840";0.242264477969627
13533;"092730137137";"0101000020E6100000C8AB5D1715812740968D666FCD754840";0.241653901891845

As far as I understand, distance results in SRID 4326 are in meters (I'm totally fine with that). So how can a point be just centimetres away from each other, when in reality they are serval meters away from each other? Image below uses point with id 14033 (orange) Plot

EDIT: New Query with accepted answer looks like this and performs as expected

WITH s AS (SELECT * FROM de_sim_points_start WHERE id = 6545)
SELECT e.*, ST_Distance(e.geom::geography, s.geom::geography) as distance FROM de_sim_points_end AS e, s
WHERE ST_DWithin(e.geom::geography, s.geom::geography, 10000)
  AND e.parent_geometry = s.parent_geometry
  AND ST_Distance(e.geom::geography, s.geom::geography) > 2000
ORDER BY RANDOM()
LIMIT 1
6

All the measurements are always returned in the units of chosen CRS. WGS84 is defined in degrees of latitude or longitutde, thus it returns results in degrees.

If you want to obtain results in meters, you have two options:

  1. Start using geography type
  2. Reproject your data into national coordinate system defined in meters
  • Number 1 is not an option. Number 2 might be possible, but comes at a cost. Also I need these Points later for routing (start and destination) – Benjamin Dec 1 '14 at 17:43
  • Isn't there a library or anything, that can convert between meters and degrees? BTW Backend is python based, so anything that can do the calcs in python is fine – Benjamin Dec 1 '14 at 17:54
  • 1
    I was wrong! Casting to geography works just fine. See updated Question. – Benjamin Dec 1 '14 at 18:14
  • By the way if you are just measuring distance between points, I strongly recommend using geometry operators instead of ST_Distance as they are much, much faster (with spatial indexes). – Michal Zimmermann Dec 1 '14 at 18:24
  • my goal is to find a random point which meets the condition more than 2000m and less than 10000m away from the start point. Also ST_DWithin already uses indexes and I do not know how to use ST_DWithin for the 2000m condition, thats why I use ST_Distance with an additional WHERE statement (updated Question) – Benjamin Dec 1 '14 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.