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

[Adam]

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
>