#771 closed defect (wontfix)
Unable to query if geometry column has mixed srid
Reported by: | farrukhnajmi | Owned by: | pramsey |
---|---|---|---|
Priority: | medium | Milestone: | PostGIS 1.4.3 |
Component: | postgis | Version: | 1.3.X |
Keywords: | Cc: | farrukhnajmi |
Description
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
Change History (3)
comment:1 by , 14 years ago
Cc: | added |
---|
comment:2 by , 14 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:3 by , 14 years ago
—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) )
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:
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
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.