[postgis-users] Querying geometries with different srid

[Ralf Suhr]

Hi Farrukh,

your setup produce this error. 

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
     );

or

SELECT *
FROM GeometryValueType gvt
WHERE gvt.id IN (
    SELECT gvt1.id
    FROM GeometryValueType gvt1
    WHERE ST_SRID(gvt.geometry) = 4326
     ) AND Within(gvt1.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0, 0
100, -100 100, -100 0))', 4326)) = true;

become the same in postgresql planner. I've testet all querys with Postgis 
1.4.0 without any error. Except 
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 );

But you can create a partial index and the last query will work:

CREATE INDEX GeometryValueType_geom_wgs_idx  ON GeometryValueType using gist 
(geometry) where ST_Srid(geometry) = 4326;

Gr
Ralf

Am Freitag 14 Januar 2011, 16:33:33 schrieb Farrukh Najmi:
> Hi Ralph,
> 
> Thanks so very much for your help.
> 
> In my PG 8.3 PostGIS 1.3.3 setup when I try to run explain on the nested
> select version of my query I get:
> 
> ---------------------------------------------------------------------------
> ---------------------------------------------------------------------------
> ---------------------------------------------------------------------------
> ---------------------- Nested Loop IN Join  (cost=0.00..2.69 rows=1
> width=358)
>     Join Filter: (gvt.id = gvt1.id)
>     ->  Seq Scan on geometryvaluetype gvt  (cost=0.00..1.33 rows=1
> width=358)
>           Filter: (st_srid(geometry) = 4326)
>     ->  Seq Scan on geometryvaluetype gvt1  (cost=0.00..1.27 rows=7
> width=8) Filter: within(gvt1.geometry,
> '0103000020E6100000010000000500000000000000000059C0000000000000000000000000
> 0000000000000000000000000000000000000000000000000000594000000000000059C0000
> 000000000594000000000000059C00000000000000000'::geometry) (6 rows)
> 
> Based on above it seems I am first filtering on SRID - No?
> 
> What am I missing?
> 
> Can you tell me what my query should look like to make it work?
> 
> On 01/14/2011 10:12 AM, Ralf Suhr wrote:
> > Hi Farrukh,
> > 
> > you have to first check for the right srid before you can compare the
> > bbox. If you use the SQL command EXPLAIN for your query yyou can see
> > what will be done.
> > 
> > SELECT *
> > FROM GeometryValueType gvt
> > WHERE gvt.id IN (
> > 
> >      SELECT gvt1.id
> >      FROM GeometryValueType gvt1
> >      WHERE ST_SRID(gvt.geometry) = 4326
> >      
> >       ) AND Within(gvt1.geometry, ST_GeomFromText('POLYGON((-100 0, 0 0,
> >       0
> > 
> > 100, -100 100, -100 0))', 4326)) = true;
> > 
> > Gr
> > Ralf
> > 
> > Am Freitag 14 Januar 2011, 15:55:31 schrieb Farrukh Najmi:
> >> BTW, using nested SELECT did not work either as shown in follow query:
> >> 
> >> 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
> >> 
> >>       )
> >> 
> >> It again gives the "Operation on mixed SRID geometries" error :-(
> >> 
> >> It seems to me that surely this should be doable. If it is not doable,
> >> do folks think that is an issue that should be tracked?
> >> 
> >> On 01/14/2011 09:49 AM, Farrukh Najmi wrote:
> >>> Hi Ture,
> >>> 
> >>> Thanks for the advice. Unfortunately WITH is not supported in postgres
> >>> 8.3. So I could not try it out.
> >>> 
> >>> Any other ideas?
> >>> 
> >>> On 01/14/2011 12:38 AM, Ture Pålsson wrote:
> >>>> 2011/1/14 Farrukh Najmi<farrukh at wellfleetsoftware.com>:
> >>>>>> --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 )
> >>>> 
> >>>> Does SQL guarantee left-to-right evaluation and short-circuiting of
> >>>> AND checks the way that C does? I must confess I have no clue.
> >>>> However, if it doesn't, that might be what's causing your headaches.
> >>>> I'd try moving the SRID filter into a WITH construct or a sub-select
> >>>> just to see what happens:
> >>>> 
> >>>> WITH filteredgeom AS (
> >>>> 
> >>>>     SELECT * FROM gvt WHERE ST_SRID(geometry) = 4326)
> >>>> 
> >>>> SELECT * FROM filteredgeom WHERE within(geometry, ...) ;
> >>>> 
> >>>> Probably lots of syntax errors (haven't had breakfast yet) but you get
> >>>> the idea...
> >>>> 
> >>>>     -- Ture
> >>>> 
> >>>> _______________________________________________
> >>>> postgis-users mailing list
> >>>> postgis-users at postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users