[postgis-users] Whats wrong with my query?

[staub at gik.uni-karlsruhe.de]

I can't use any ID's, so I took your first suggestion to get inspired. I now
have my correct query:

select the_geom into test from Polygons
where (the_geom && GeometryFromText('POINT(-161.893119 55.560754)',4326))
union all
select the_geom from CompleteChain
where
(the_geom && GeometryFromText('POINT(-161.893119 55.560754)',4326));

Thanks for your proposal.

Guido Staub

Jan Hartmann wrote:

> Guido,
>
> Are these Polygons and CompleteChains independent unities or are they
> both parts of single geometrical objects? In the first case, you should
> select from the concatenation of both tables:
>
> select the_geom,'a' as tbl from Polygons
> union all
> select the_geom,'b' as tbl from CompleteChain
> where (tbl='a' and the_geom && GeometryFromText('POINT(...))
> or (tbl='b' and the_geom && GeometryFromText('POINT(...))
>
> In the second case, both tables should have a field identifying the
> object to which the geometries belong. The query would be:
>
> select pol.the_geom, pol.id, cc.the_geom, cc.id
> from Polygons pol CompleteChains cc
> where pol.id = cc.id
> and (    pol.the_geom && GeometryFromText('POINT(...))
>        or  cc.the_geom && GeometryFromText('POINT(...))
>       )
>
> The way you do it PostgreSQl will have to produce the complete cross
> product of both tables (every row of the first table combined with every
> row of the other). With AND conditions this can be optimized, but with
> only OR the planner cannot in advance reject rows before joining. For
> large tables this could easily produce a memory overflow.
>
> Moreover, even if it worked you would get back a cross product: every
> row of table one for which the first conditon holds combined with each
> row of table two, and every row of table two for which the second
> condition holds combined with each row of table one.
>
> HTH
>
> Jan Hartmann
>
> staub at gik.uni-karlsruhe.de wrote:
> > I've tried it, but there is still no success.
> > Now my query looks like:
> >
> > SELECT pol.the_geom as polgeo,cc.the_geom as ccgeo from Polygons
> > pol,CompleteChain cc where (pol.the_geom && GeometryFromText
> > ('POINT(-165.893119 55.560754)',4326))
> > or
> > (cc.the_geom && GeometryFromText('POINT(-161.893119 55.560754)',4326));
> >
> > Or did I misunderstand your suggestion? If so, any help is welcome!
> >
> > "Thomas, Cord" wrote:
> >
> >
> >>your or clause's should be parenthetically set apart.
> >>
> >>-----Original Message-----
> >>From: Guido Staub [mailto:guidostaub at lycos.de]
> >>Sent: Tuesday, December 17, 2002 11:37 AM
> >>To: postgis-users at postgis.refractions.net
> >>Subject: [postgis-users] Whats wrong with my query?
> >>
> >>  Hi all,
> >>
> >>can somebody tell me what's wrong with my query?
> >>SELECT pol.the_geom as pgeo,cc.the_geom as ccgeo
> >>from polygons pol,CompleteChain
> >>cc where pol.the_geom && GeometryFromText ('POINT(-161.893119
> >>55.560754)',4326) or cc.the_geom && GeometryFromText
> >>('POINT(-161.893119 55.560754)',4326);
> >>
> >>When I do them seperately the first one (Polygon && Point) returns one
> >>row and the other (CompleteChain && Point) no row. But if I do the query
> >>above I'm running out of memory. I only want to know which BBox of
> >>Polygons overlaps the specified point and which BBox of CompleteChain
> >>overlaps that point?
> >>
> >>Thanks
> >>Guido Staub
> >>
> >>_______________________________________________
> >>postgis-users mailing list
> >>postgis-users at postgis.refractions.net
> >>http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>_______________________________________________
> >>postgis-users mailing list
> >>postgis-users at postgis.refractions.net
> >>http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
> Jan Hartmann
> Department of Geography
> University of Amsterdam
> jhart at frw.uva.nl
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users