[postgis-users] SQL Statement for Multiple Tables- operatordoesn't exist: boolean && geometry

[Robert Burgholzer]

For my money, I like to use the intersection() function, even though it
has been considered inefficient, it manages to weed out all
non-overlapping geometries, and I have not noticed a significant
performance hit:

Select a.id, b.blahblah 
Fromtable1 as a, table2 as b
Where a.the_geom && b.the_geom 
And area2d(intersection(a.the_geom, b.the_geom)) > 0.0;


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Emily Gouge
Sent: Thursday, January 12, 2006 11:33 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] SQL Statement for Multiple Tables-
operatordoesn't exist: boolean && geometry

Adam,

The '&&' operator compares the bounding boxes of the geometries, not the
actual geometries.  Table 
joins done using the '&&' operator will join any two rows where the
bounding boxes of the geometries 
overlap.  I'm not sure what the data looks like, however in addition to
the && operator you might 
need to add an intersects (or other geometry function) constraint to the
join as well (so the 
actually geometries are compared).  Something like "...zipcodes.the_geom
&& states.the_geom AND 
intersects(zipcodes.the_geom, states.the_geom) ..."

Emily





Adam wrote:

> I changed my query to this:
> 
> SELECT zipcodes.name, urban_areas.name, states.state, 
> time_zones.gmt_offset, counties.county
> FROM zipcodes, urban_areas, states, time_zones, counties
> WHERE zipcodes.the_geom && urban_areas.the_geom AND
>              zipcodes.the_geom && states.the_geom AND
>              zipcodes.the_geom && time_zones.the_geom AND
>              zipcodes.the_geom && counties.the_geom
> AND distance( zipcodes.the_geom, 'POINT(-88.050121 41.862380)') = 0
> 
> When it runs I get multiple rows with states and counties way off
point.  \
> The output should look like:  60137, Chicago Metro Area, Illinois, -6,

> DuPage County
> 
> But here's my output:
> 
> 60137, Chicago Metro Area, Michigan, -6,
> 60137, Chicago Metro Area, Michigan, -6, Cook County
> 60137, Chicago Metro Area, Michigan, -6, DuPage County
> 60137, Chicago Metro Area, Illinois, -6,
> 60137, Chicago Metro Area, Illinois, -6, Cook County
> 60137, Chicago Metro Area, Illinois, -6, DuPage County
> 60137, Chicago Metro Area, Michigan, -5,
> 60137, Chicago Metro Area, Michigan, -5, Cook County
> 60137, Chicago Metro Area, Michigan, -5, DuPage County
> 60137, Chicago Metro Area, Illinois, -5,
> 60137, Chicago Metro Area, Illinois, -5, Cook County
> 60137, Chicago Metro Area, Illinois, -5, DuPage County
> 60137, Chicago Metro Area, Michigan, -6,
> 60137, Chicago Metro Area, Michigan, -6, Cook County
> 60137, Chicago Metro Area, Michigan, -6, DuPage County
> 60137, Chicago Metro Area, Illinois, -6,
> 60137, Chicago Metro Area, Illinois, -6, Cook County
> 60137, Chicago Metro Area, Illinois, -6, DuPage County
> 
> What am I doing wrong with this query?
> 
> ----- Original Message ----- From: "Paul Ramsey"
<pramsey at refractions.net>
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Sent: Thursday, January 12, 2006 12:50 AM
> Subject: Re: [postgis-users] SQL Statement for Multiple Tables - 
> operatordoesn't exist: boolean && geometry
> 
> 
>> Just add an "and":
>>
>> SELECT zipcodes.name, urban_areas.name, states.state
>> FROM zipcodes, urban_areas, states
>> WHERE distance( zipcodes.the_geom, 'POINT(-88.0501 41.8623)') = 0 AND

>> zipcodes.the_geom && urban_areas.the_geom
>> AND zipcodes.the_geom && states.the_geom
>>
>> On Jan 11, 2006, at 10:39 PM, Adam wrote:
>>
>>> I have three tables:  zip codes, metro areas, and states.
>>>
>>> My sql statement is:
>>>
>>> SELECT zipcodes.name, urban_areas.name, states.state FROM zipcodes, 
>>> urban_areas, states WHERE distance( zipcodes.the_geom, 'POINT 
>>> (-88.0501 41.8623)') = 0 AND zipcodes.the_geom &&  
>>> urban_areas.the_geom && states.the_geom
>>>
>>> When I try to query them all in one statement I get this error:
>>> operator does not exist, boolean && geometry
>>> Hint:  No operator matches the given name and argument type(s).
You 
>>> may need to add explicit type casts.
>>>
>>> If I run this statement with only two tables instead of three this 
>>> statement will work.
>>>
>>> How should I join three plus tables and query for one locaton?
>>> _______________________________________________
>>> 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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users