2

I have a CartoDB map here:

https://dms2203.cartodb.com/viz/78db6c02-3148-11e5-a42f-0e018d66dc29/public_map Link: https://dms2203.cartodb.com/viz/78db6c02-3148-11e5-a42f-0e018d66dc29/public_map

I'm trying to find the distance of the points to the polygon. If the points are inside, they should have a value of 0. Basically, I'm looking for Near (Analysis) from ArcGIS equiv. in PostGIS http://resources.arcgis.com/en/help/main/10.1/index.html#//00080000001q000000

Could someone help me craft out the SQL? I'm trying to learn to do it in PostGIS but figure it's easier to test out the SQL statement in CartoDB.

I've referred to this: http://postgis.net/docs/ST_Distance.html but have had trouble with the syntax on my end.

The points are in a layer called: points_near

The polygon is in a layer called: polygon_near

1

@jmsmcb answered this on Twitter. https://twitter.com/jmsmcb/status/624231773107564545

SELECT *, ST_Distance(the_geom, (SELECT the_geom FROM polygon_near)) as distance_to_polygon from points_near

I think this returned degrees, so asking how to get meters or km.

And here it is:

SELECT *, ST_Distance(the_geom::geography, (SELECT the_geom FROM polygon_near)) as distance_to_polygon from points_near

Many thanks to answered this on Twitter. https://twitter.com/jmsmcb/status/624231773107564545

Many thanks to @jmsmcb!


Question from comments

So for the 2nd geography to be projected, use this?

SELECT *, ST_Distance(the_geom::geography, (SELECT the_geom::geography FROM polygon_near)) as distance_to_polygon from points_near
  • If it is returning degrees then your projection is in degrees, most likely wgs84. You'll either need to reproject to a different projection that uses meters or feet in its coordinate system, or cast the points as geography. The geography based system returns meters, but the calculations are slower. – HeyOverThere Jul 23 '15 at 15:20
  • I think you might want to cast the second geometry to geography too. ST_Distance returns degrees for geometry parameters and meters for geography parameters. You can learn more about it here: postgis.refractions.net/docs/ST_Distance.html – iriberri Jul 24 '15 at 8:07
  • Like this (also above)? --- SELECT *, ST_Distance(the_geom::geography, (SELECT the_geom::geography FROM polygon_near)) as distance_to_polygon from points_near – GIS Danny Jul 27 '15 at 15:02

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.