I have a PostGIS geometry column populated with geometries with multiple SRID's. When I run this query I get the 'Operation on mixed SRID geometries' error.
SELECT ST_AsBinary(geom)
FROM mygeoms
WHERE foreign_key = 2 AND st_intersects(geom,ST_MakeEnvelope(130304.897221584,4770134.6,903134.322778416,5287134.93,26919));
All geometries in rows with foreign_key = 2 have an SRID = 26919.
I realize that you can't run geometry operations on mixed SRID's, but I have two questions.
Why is it even trying to run the intersect on any geometries where foreign_key != 2? Does it think intersecting is faster than an integer lookup?
Is there a way to tell the geometry operations to just ignore (not use) geometries with a different SRID? Or do I have to add an extra filter for the SRID to all of my queries (ex. 'AND ST_SRID(geom) = 26919')?
select distinct st_srid(geom) from mygeoms WHERE foreign_key = 2;
geom
over that on the FK because it estimates that the impact is larger, meaning that more rows can get filtered out. It doesn't care if the index or data type size is larger. However, the GIST index does not consider the CRS - bbox types don't carry that attribute, so it returns geometries independent of the CRS. Try that query with_ST_Intersects
(note the_
); a debug signature that is not utilizing the GIST index.