3

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

3
  • What do you get when running select distinct st_srid(geom) from mygeoms WHERE foreign_key = 2;
    – JGH
    Oct 4, 2021 at 17:34
  • I get 26919 as the only result when running that query.
    – warpjedi
    Oct 4, 2021 at 17:59
  • 1
    The planner uses the index on 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.
    – geozelot
    Oct 4, 2021 at 20:00

1 Answer 1

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));
7
  • Not sure what you mean about unknown SRIDs. The SRID for the envelope is specified in the last parameter of ST_MakeEnvelope: ST_MakeEnvelope(130304.897221584,4770134.6,903134.322778416,5287134.93,26919). The SRID of the geometry is set in the geometry column. I don't want to interesect ALL of the geometries, just the ones with srid 26919 already set. I think your solution would give unintended results with geometries from other projections.
    – warpjedi
    Oct 4, 2021 at 18:44
  • I think I get what you mean, the foreign_key will filter out the ones in other projections. I tried your suggestion, the query was taking over a minute before I stopped it. For some reason it really wants to do the intersect before the foreign key filter. I tried 'AND st_srid(seam_geom) = 26919' instead and the query only took a few seconds. There are over 1.5 million rows in this table.
    – warpjedi
    Oct 4, 2021 at 18:54
  • My mistake about the envelope. It's been a while since I've used it and forgot the srid was a parameter. You can filter by srid - see my edit
    – jbalk
    Oct 4, 2021 at 19:01
  • I think that is what I will end up doing, I was hoping there was an option on the geometry operation itself to skip geometries with different SRID's, or a way for the query to do the foreign key filter first.
    – warpjedi
    Oct 4, 2021 at 19:25
  • 1
    This does seem strange. Based on your comments about the srid matching for fk 2, foreign_key = 2 should be equivalent to st_srid(geom) = 26919. The query planner should filter on fk before intersect, since it's faster, but I don't have an answer for that. It's hard to guess without knowing more about your database and table (indices, geometry validity, etc.)
    – jbalk
    Oct 4, 2021 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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