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)
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
Answer
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:
- Start using geography type
- Reproject your data into national coordinate system defined in meters
No comments:
Post a Comment