[postgis-users] Querying geometries with different srid

[Ralf Suhr]

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