0

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: Layers match

However when imported into QGIS, the result is wrong: Layers offset

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:

After transformation

How can I get my PostGIS PostgreSQL statements to recognise the spatial position of the data in the same way that ArcGIS does?

  • One way maybe to see that transformation ArcGIS is using, and then try to use the same in QGIS. – enolan Dec 5 '18 at 17:33
  • EPSG:4285 is surely wrong for data in the UK. BTW the OS adress dataset also contains X and Y coordinates in EPSG:27700. – AndreJ Dec 5 '18 at 18:04
  • Let me play GIS detective: - you imported (or set, as in 'not transformed') the adress geometries with the wrong CRS (coordinates of both are, in fact, EPSG:27700); - in ArcGIS, you loaded the builings first; - ArcGIS sets EPSG:27700 as project CRS; - you load the adresses second and ArcGIS enforces the project CRS --> the adresses coordinates are accidentally correctly interpreted (I always hated that 'feature', but this time it (sort of) did the right thing...). QGIS tries to transform each layer into the project CRS, and (rightly) fails to interprete the coordinates correctly. – ThingumaBob Dec 5 '18 at 20:26
  • PostGIS fails for the same reason to find containment. it actually would have also failed, in your first try, even if the adresses were correctly set; pretty much all functions require all geometries to be in the same CRS! the second try (where you transformed) fails due to the wrongly assigned CRS. not sure if the ArcGIS thing holds, AFAIK it always nags when a layer is not in the project CRS. in either way, ArcGIS doesn't simply magic the point in the correct place if they were stored with a wrong projection in PostGIS, so you need to find the issue in the data stored in PostgreSQL/PostGIS. – ThingumaBob Dec 5 '18 at 20:30
  • 1
    AddressBase is supplied in both. LAT/LON and British National Grid. X_Coordinate and Y_Coordinate fields are the coordinates in EPSG:27700 – HeikkiVesanto Dec 6 '18 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.