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

[Paul Ramsey]

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