[postgis-devel] [PostGIS] #771: Unable to query if geometry column has mixed srid

[PostGIS]

#771: Unable to query if geometry column has mixed srid
---------------------------+------------------------------------------------
  Reporter:  farrukhnajmi  |       Owner:  pramsey      
      Type:  defect        |      Status:  closed       
  Priority:  medium        |   Milestone:  PostGIS 1.4.3
 Component:  postgis       |     Version:  1.3.X        
Resolution:  wontfix       |    Keywords:               
---------------------------+------------------------------------------------
Changes (by robe):

  * status:  new => closed
  * resolution:  => wontfix


Comment:

 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.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/771#comment:2>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.