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<farr...@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@postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
