Windows 10, ArcGIS Pro, QGIS 2.18, pgAdmin 4-postgreSQL
I have two (sample area) PostGIS tables in a PostgreSQL database:
building_polys (SRID: 27700) -OS MastermapTopo
address_points (SRID: 4258) -OS AddressBase
I have a project set up in British National Grid projection (27700) in ArcGIS and QGIS too. When my postgis layers are imported into ArcGIS I get the expected result where the point lies within the building polygon:
I believe this is due to the way QGIS re-projects layers from 4258 to 27700...
This in itself is not an issue, HOWEVER my problem is that using ST_WITHIN statements in PostgreSQL (using pgAdmin) seem to use the QGIS approach...
If I run:
SELECT t.*, s.uprn FROM public.building_polys t, public.address_points s WHERE ST_WITHIN(s.wkb_geometry,t.wkb_geometry)
It returns nothing. For comparison, if I add a 5 metre search distance on that (using ST_DWITHIN) I do get my expected results:
SELECT t.*, s.uprn FROM public.building_polys t, public.address_points s WHERE ST_DWITHIN(s.wkb_geometry,t.wkb_geometry,5)
This confirms that postgis recognises an offset just like QGIS.
I decided to apply a transformation of the point data, from 4258 to 27700, to see if that would shift the points into their correct position:
ALTER TABLE public.address_points ALTER COLUMN wkb_geometry TYPE geometry(Point, 27700) USING ST_Transform(ST_SetSRID(wkb_geometry, 4258), 27700);
This doesn't help though. It does transform the points to 27700 but they remain in the same position and more importantly, SQL statements still recognise the point as lying outside of the polygon:
How can I get my PostGIS PostgreSQL statements to recognise the spatial position of the data in the same way that ArcGIS does?