5

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?

5

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})
  • So this query runs. But it returns no results. How would I go about debugging this query? – Abraham P Jun 16 '17 at 8:46
  • So what does the 4326 SRID mean? When I looked up my original .prj files (I imported .shp files from the same directories here): prj2epsg.org/search I get PROJCS["British_National_Grid",GEOGCS["GCS_OS..... Running ` SELECT srid, proj4text FROM spatial_ref_sys WHERE srtext LIKE '%British_National_Grid%';` produces two results, 27700 and 7405. Neither one of them fixes the query. – Abraham P Jun 16 '17 at 9:15
  • this should almost certainly be latitude, longitude in the make point – Ian Turton Jun 16 '17 at 10:09

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.