[postgis-users] PostGIS spatial query performance

[Alexander Pucher]

Jan Hartmann wrote:

>It is possible to do this much faster. As Paul said, you can speed up a
>distance query (which doesn't use indices) by preceding it with an overlap
>query, like:
>
>select park.*
>from park,roads
>where park.the_geom && roads.the_geom
>and distance(park.the_geom,mypoints.the_geom) = 0
>
>  
>
wow, speeds up from about 6 minutes to 2 seconds with this query:

select park.the_geom
from park, road
where park.the_geom && road.the_geom
and distance(park.the_geom, road.the_geom) = 0;

Thanks for that. Also works fine to include this query string into a UMN 
Mapserver mapfile.

One last question: How do I get the inverse result of the above query, 
i.e all parks, that do *not* have a road running through them?

regards
alex

-- 
________________________________________________________

Institut fuer Geographie und Regionalforschung
Universitaet Wien
Kartografie und Geoinformation

Departement of Geography and Regional Research
University of Vienna
Cartography and GIS

Universitaetstr. 7, A-1010 Wien, AUSTRIA

Tel: (+43 1) 4277 48644
Fax: (+43 1) 4277 48649
E-mail: alexander.pucher at univie.ac.at

FTP: ftp://ftp.gis.univie.ac.at
WWW: http://www.gis.univie.ac.at/karto
--------------------------------------------------------
Atlas of Eastern and Southeastern Europe: http://www.aos.ac.at
--------------------------------------------------------

M$ is not the answer. M$ is the question!
No is the answer -- Eric Naggum