Unable to query if geometry column has mixed srid

[farrukhnajmi]

Details are described in this thread:

​http://postgis.refractions.net/pipermail/postgis-users/2011-January/028613.html

If a table has a geometry column and rows where teh geometry column value has different srid then there does not seem to be any way to do spatial filters in a query on the table for rows where the geometry value has a specified srid. The following queries all give the error "Operation on mixed SRID geometries".

—Gives Error: Operation on mixed geometries SELECT * FROM GeometryValueType gvt WHERE ( ST_SRID(gvt.geometry) = 4326 AND

within(gvt.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0 100, -100 100, -100 0))', 4326)) = true )

—Gives Error: Operation on mixed geometries SELECT * FROM GeometryValueType gvt WHERE

ST_SRID(gvt.geometry) = 4326 AND gvt.id IN (

SELECT gvt1.id FROM GeometryValueType gvt1 WHERE

within(gvt1.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0 100, -100 100, -100 0))', 4326)) = true

)

My POSTGIS version is:

"POSTGIS="1.3.3" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS"

My PG version is 8.3

[robe]

Farrukh,

I usually don't ask this, but why do you have mixed SRIDs in your table? This is just in general bad and not something I think we care to support. If you most have a network of tables queryable from a single table, then I would suggest putting them in an inheritance structure such that each table has its own SRID and then using a constraint exclusion check where ST_SRID(geometry) = 4326. That way when you query those other tables will be skipped anyway.

There are other issues with your query. within is not an indexed function and has been removed in PostGIS 2.0, so please remove it and replace with ST_Within. That will make your code both upward compatible and faster.

If you really must use a table with mixed srids, then you might be able to trick the planner into processing your data in your desired order by doing:

SELECT gvt.* FROM 
(SELECT * FROM GeometryValueType WHERE ST_SRID(geometry) = 4326) AS gvt
  WHERE 
   ST_Within(gvt.geometry, 
 ST_GeomFromText('POLYGON((-100 0, 0 0, 0 100, -100 100, -100 0))', 4326) 

Also as I think someone mentioned in the thread — you can put in an index on your GeometryValueType table, but putting a spatial one actually doesn't help much with your within since that doesn't use a spatial index (though if you switched to ST_Within then it could take advantage of the index). I would instead just put a functional index on SRID and also make sure you have a spatial index on your geometry column

CREATE INDEX GeometryValueType_geom_srid_idx  ON GeometryValueType using btree
 ST_SRID(geometry) ;

CREATE INDEX GeometryValueType_geom_geometry_gist_idx  ON GeometryValueType using gist(geometry) ;

Since index operations are generally faster, the planner would most likely favor running an indexed operation first over a non-indexed one. Also btree indexes are generally considered less costly so the planner would favor checking that first over the spatial one. But again this all goes into the category of tricking the planner which i try to avoid as it can come and bite you later. Just keep your tables having separate srids is a better solution.

[robe]

—slight correction - forgot a paren

SELECT gvt.* FROM 
(SELECT * FROM GeometryValueType WHERE ST_SRID(geometry) = 4326) AS gvt
  WHERE 
   ST_Within(gvt.geometry, 
 ST_GeomFromText('POLYGON((-100 0, 0 0, 0 100, -100 100, -100 0))', 4326) )