2

I have imported various shape files, all obtained from the geofabriks website, into QGIS using the 'PostGIS Shapefile Import/Export Manager'. A couple of them are throwing the error “Error: Operation on mixed SRID geometries”, on the below query.

I set the SRID during import, and have tried updating it using SET_SRID, st_transform, etc, but still get the error. Using ST_SRID to check the SRID returns 4326 (as expected).

Does anyone have any suggestions (I think I've tried everything on all the other questions about this)?

I also noticed that when adding these tables as a layer to QGIS, when zooming in a lot, the line covers the whole map (guessing this is related to the issue)

Files are from http://download.geofabrik.de/europe/great-britain/england.html (I'm having the issue only with Derbyshire, Nottinghamshire + Leicestershire, all others work as expected).

Could there be an issue with the shapefiles themselves?

Query - SELECT ST_CONTAINS(ST_ESTIMATEDEXTENT('tableName', 'geom'), ST_SetSRID(ST_MakePoint(lng,lat),4326)) as contains

Questions I've been looking at are:

I've tried various methods of updating the SRID, all seemed to work, but the error persisted. I've tried setting the Point with ::geography::geometry, to force it to use SRID 4326.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Sandwich
  • 131
  • 1
  • 5

1 Answers1

2

ST_EstimatedExtent returns a box2d, not a geometry so it will always have an SRID of 0 when autocast to geometry. Don't use ST_Contains for your containment checks against a box especially with points. Try doing instead

ST_EstimatedExtent(...) && ST_MakePoint(lng,lat)

If you do need containment (e.g. you have something other than point, you could just leave out your ST_SetSRID ) so your geometry also has no srid

Regina Obe
  • 10,503
  • 1
  • 23
  • 28
  • I removed the ST_SRID around the ST_MakePoint, and the query now runs as expected, presumably using an SRID of 0 for both, but works for my needs. Thanks – Sandwich Oct 24 '17 at 07:24