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

[Adam]

Acutally I ended up doing this and it seemed to work okay, is this more 
efficient?

SELECT zipcodes.name, urban_areas.name, states.state, 
time_zones.gmt_offset, counties.county
FROM zipcodes, urban_areas, states, time_zones, counties
WHERE distance( urban_areas.the_geom, 'POINT(-88.050121 41.862380)') = 0 AND
              distance( states.the_geom, 'POINT(-88.050121 41.862380)') = 0 
AND
              distance( time_zones.the_geom, 'POINT(-88.050121 41.862380)') 
= 0 AND
              distance( counties.the_geom, 'POINT(-88.050121 41.862380)') = 
0 AND
              distance( zipcodes.the_geom, 'POINT(-88.050121 41.862380)') = 
0;


----- Original Message ----- 
From: "Paul Ramsey" <pramsey at refractions.net>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Thursday, January 12, 2006 5:46 PM
Subject: Re: [postgis-users] SQL Statement for Multiple 
Tables -operatordoesn't exist: boolean && geometry


> All in one massive SQL statement.  You might find that running each  layer 
> individually against your POINT is more efficient, however.   Doing it as 
> a join is probably not optimal, since you're getting no  particular 
> leverage from the different layers being overlaid.
>
>
> SELECT
>   zipcodes.name,
>   urban_areas.name,
>   states.state,
>   time_zones.gmt_offset,
>   counties.county
> FROM
>   (select 'POINT(-88.050121 41.862380)'::geometry as the_geom) as pt,
>   zipcodes,
>   urban_areas,
>   states,
>   time_zones,
>   counties
> WHERE
>   zipcodes.the_geom && pt.the_geom AND
>   urban_areas.the_geom && pt.the_geom AND
>   states.the_geom && pt.the_geom AND
>   time_zones.the_geom && pt.the_geom AND
>   counties.the_geom && pt.the_geom AND
>   Contains(zipcodes.the_geom,pt.the_geom) AND
>   Contains(urban_areas.the_geom,pt.the_geom) AND
>   Contains(states.the_geom,pt.the_geom) AND
>   Contains(time_zones.the_geom,pt.the_geom) AND
>   Contains(counties.the_geom,pt.the_geom)
>
>
> P.
>
> On Jan 11, 2006, at 11:19 PM, 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
>
>