1

Say I wanted to find geometries that intersect a circle at lat/long as outlined here How to Select all polygons or lines that lie at least partially within a circle with a radius?.

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

Now my data was the OS Open Local data: https://www.ordnancesurvey.co.uk/opendatadownload/products.html (top result).

I've imported the .shp files.

Running the .prj through here: http://prj2epsg.org/search produces:

    PROJCS["British_National_Grid",GEOGCS["GCS_OSGB_1936",DATUM["D_OSGB_1936",
SPHEROID["Airy_1830",6377563.396,299.3249646]],PRIMEM["Greenwich",0.0],
UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],
PARAMETER["False_Easting",400000.0],PARAMETER["False_Northing",-100000.0],
PARAMETER["Central_Meridian",-2.0],PARAMETER["Scale_Factor",0.9996012717],
PARAMETER["Latitude_Of_Origin",49.0],UNIT["Meter",1.0],AUTHORITY["EPSG",27700]]

So in my postgis db I run:

SELECT srid FROM spatial_ref_sys WHERE srtext LIKE '%British_National_Grid%';

Which produces two results: 27700 and 7405

By inspection the former corresponds exactly to my .prj

The query above, however, finds no results for any buildings in London (51.5236, -0.1047) irrespectively of radius size (500, 5000, 500000).

Additionally, dropping the ST_SetSrid produces:

ERROR:  Input geometry has unknown (0) SRID

Why doesn't the ST_Transform set the SRID as documented here: http://postgis.org/docs/ST_Transform.html

Would I ever want the two SRID's to be distinct?

And why is the query not producing results?

3

This part of your query:

ST_SetSrid(
  ST_MakePoint({longitude},{latitude})
  ,27700)

makes a point in OSGB (EPSG:27700) coordinates so 51.5,-0.1 is somewhere off the Scilly Isles (Britain's very own Null Island). So you either need to create the point using the OS grid reference for London (554000,170000), or make the point in lat/lon using the SRID 4326 instead and then transform it to 27700 to match your data.

  • setting the srid to 4326 still returns no results.. What is SRID 4326? – Abraham P Jun 16 '17 at 9:58
  • ah yes, you will also need to use latitude, longitude as that is what 4326 is – Ian Turton Jun 16 '17 at 9:59
  • still no results :/ Thanks for the explanation re 4326 though! – Abraham P Jun 16 '17 at 10:03
  • 1
    ST_Transform(ST_SetSRID(ST_MakePoint(-0.1047, 51.5236), 4326), 27700) produces a point in clerkenwell in SRID 27700...if that doesn´t work, well, then your buildings are somewhere else (either in reality or because of another SRID) edit: ah you accepted, so it did work? – ThingumaBob Jun 16 '17 at 10:25

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.