PostGIS ST_Intersects on geometry column with mixed SRID

Question

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.

1. 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?

2. 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')?

Answer 1

If you're certain that all the geometries returned by the where clause are srid 26919, the following query will work:

SELECT ST_AsBinary(geom)
FROM mygeoms
WHERE foreign_key = 2 AND st_intersects(st_setsrid(geom,26919),ST_MakeEnvelope(130304.897221584,4770134.6,903134.322778416,5287134.93,26919));

This specifies the srid of both geometries going into the intersect, so they match. The problem with a mixed srid table is that the srid for the table will most likely be set to 0 in the postgis geometry_columns table. This is what postgis looks at if you do not specify the srid of the geometry. 0 obviously does not match 26919, hence the error.

If you want to make sure that you're only intersecting geometries that are srid 26919, just add it to your where clause (you will still need to use st_setsrid so st_intersect uses the correct srid):

SELECT ST_AsBinary(geom)
FROM mygeoms
WHERE foreign_key = 2 AND st_srid(geom) = 26919 AND st_intersects(st_setsrid(geom,26919),ST_MakeEnvelope(130304.897221584,4770134.6,903134.322778416,5287134.93,26919));