[postgis-users] Whats wrong with my query?

[Jan Hartmann]

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