[postgis-users] Querying geometries with different srid
Ralf Suhr
Ralf.Suhr at itc-halle.de
Mon Jan 17 00:14:51 PST 2011
Hi Farrukh,
I have no idea what is wrong with your setup of postgis.
Explain 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 );
has to bve print about the usage of the partial index. I can not imagine if
there are only geometries with srid 4326, postgis is seeing another srid.
Gr
Ralf
Am Freitag 14 Januar 2011, 17:58:33 schrieb Farrukh Najmi:
> Hi Ralf,
>
> I tried the following steps:
>
> 1. creating the index: CREATE INDEX GeometryValueType_geom_wgs_idx ON
> GeometryValueType using gist (geometry) where ST_Srid(geometry) =
> 4326;
> 2. Then ReIndexing the GeometryValueType table
> 3. Then running your last suggested query but still get "Operation on
> mixed SRID geometries" error :-(
>
> 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 );
>
> Am I missing something in your instructions? Thanks again for your kind
> help.
>
> On 01/14/2011 11:29 AM, Ralf Suhr wrote:
> > 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,
> >> '0103000020E6100000010000000500000000000000000059C0000000000000000000000
> >> 000
> >> 0000000000000000000000000000000000000000000000000000594000000000000059C
> >> 0000 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
More information about the postgis-users
mailing list