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

[TECHER David]

Hi Adam

That's not really the best solution but you can try this for this moment:

Add "distinct" after "select" and test with "and counties.county is not
null"

SELECT disticnt 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
and counties.county is not null

Adam a écrit :

> 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
>


	

	
		
___________________________________________________________________________ 
Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et l'international.
Téléchargez sur http://fr.messenger.yahoo.com