1

I have a query that returns the closest distance between a set of points and multiline:

SELECT 
   DISTINCT ON (h.field_1) h.field_1, b.id, ST_Distance(h.wkb_geometry, b.wkb_geometry)  as dist
FROM points As h, lines As b   

However, given that the projection is EPSG 4326, the distance returned is in degrees which doesn't make much sense. How could one rewrite the query and cast to ::geography so that the distances returned are in meters ?

  • I think that your title may need some editing to mention cast to ::geography and perhaps something about measuring distances in meters. – user30184 Jan 9 '17 at 21:49
3

You just cast your geometry to geography when you pass it into ST_Distance like this:

SELECT 
   DISTINCT ON (h.field_1) h.field_1, b.id, ST_Distance(h.wkb_geometry::geography, b.wkb_geometry::geography)  as dist
FROM points As h, lines As b 

ST_Distance also allows another parameter for use_spheriod (Pass in false to return answer in sphere instead of spheroid):

SELECT 
   DISTINCT ON (h.field_1) h.field_1, b.id, ST_Distance(h.wkb_geometry::geography, b.wkb_geometry::geography, false)  as dist
FROM points As h, lines As b 
  • general question here, does calculating the distance in geography make the result less accurate than geometry? and is it faster for the function to compute in geography rather than geometry? – ziggy Jan 9 '17 at 21:46
  • 1
    @ziggy That's a good question. Here is a post that is similar: gis.stackexchange.com/questions/89396/… – kttii Jan 10 '17 at 14:11
1

If you want to get distances in meters, you could also project the geometries to a projection with meter units.

SELECT 
   DISTINCT ON (h.field_1) h.field_1, b.id, ST_Distance(ST_Transform(h.wkb_geometry, [EPSG]), ST_Transform(b.wkb_geometry, [EPSG]))  as dist
FROM points As h, lines As b   

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.