1

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.

0

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

  • 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 7:24

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.