Thursday 27 April 2017

postgis 2.0 - Why is result of ST_Area or ST_Distance in SRID 4326 so small


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

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:




  1. Start using geography type

  2. Reproject your data into national coordinate system defined in meters


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...