PostGIS query “Error: Operation on mixed SRID geometries” only for certain tables

Question

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:

• PostGIS Geometry Query returns "Error: Operation on mixed SRID geometries" only for certain values
• Operation on mixed SRID geometries

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.

Answer 1

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