[postgis-users] SQL Statement for Multiple Tables

Paul Ramsey pramsey at refractions.net
Thu Jan 12 21:19:43 PST 2006 

No, because you aren't using any spatial indexes (&& operator).  Of  
course, if you haven't created any indexes, it will be exactly the  
same speed...  probably only counties and zipcodes are large enough  
tables that it matters.

If you want maximum speed, you can pre-calculate the state, timezone,  
county and urban area for every zipcode and add that to the zipcode  
table as extra columns, thus avoiding the multiple-table query  
altogether.

SELECT zipcodes.name, urban_areas.name, states.state,  
time_zones.gmt_offset, counties.county
FROM zipcodes, urban_areas, states, time_zones, counties
WHERE
       urban_areas.the_geom && 'POINT(-88.050121 41.862380)' AND
       states.the_geom && 'POINT(-88.050121 41.862380)' AND
       time_zones.the_geom && 'POINT(-88.050121 41.862380)' AND
       counties.the_geom && 'POINT(-88.050121 41.862380)' AND
       zipcodes.the_geom && 'POINT(-88.050121 41.862380)' AND
       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;


On Jan 12, 2006, at 9:06 PM, Adam wrote:

> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list