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