[GIS] How to Select an SRID? And how is it different from the SRID passed into ST_Transform

coordinate systemgeofencingpostgispostgresqlsrid

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: https://postgis.net/docs/ST_Transform.html

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

And why is the query not producing results?

Best Answer

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.

Related Question