Friday, 24 April 2015

postgis - How to Select all polygons or lines that lie at least partially within a circle with a radius?


I am storing OS Open Local data, as downloaded here: https://www.ordnancesurvey.co.uk/opendatadownload/products.html (top result).


In particular my geometry column takes one of two possible shapes:


SELECT ST_AsGeoJSON(geometry) FROM roads LIMIT 1;

Resulting in:


{"type":"LineString","coordinates":[[464223.46,1213396.46],[464257.63,1213398.4],[464294.12,1213438.78],[464330.93,1213444.89]]}


And


 SELECT ST_AsGeoJSON(geometry) FROM buildings LIMIT 1;

Resulting in:


   {"type":"Polygon","coordinates":[[[454003.53,1203047.09],[453984.86,1203083.28],[453997.27,1203089.71],[454015.96,1203053.49],[454003.53,1203047.09]]]}

Given a lat, a long, and a radius in meters, how would I SELECT all roads or buildings which are either contained by, or intersect the selected circle?



Answer



Because the use of ST_Buffer/St_Intersects is really undesired here, I post another option with the use of only ST_DWithin. The advantage is a much faster and more correct than using ST_Buffer. Also see Paul Ramsey`s post.



SELECT * 
FROM buildings AS b
WHERE ST_DWithin(b.geom,
ST_Transform(
ST_SetSrid(
ST_MakePoint({longitude},{latitude})
,4326)
,27700)
,{radius})

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...