2

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
Improve this question
1

1 Answer 1

Reset to default
7

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
Improve this answer
7
  • 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
    Commented Dec 1, 2014 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
    Commented Dec 1, 2014 at 17:54
  • 1
    I was wrong! Casting to geography works just fine. See updated Question.
    – Benjamin
    Commented Dec 1, 2014 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
    Commented Dec 1, 2014 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
    Commented Dec 1, 2014 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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