[postgis-users] PostGIS spatial query performance

[David Garnier]

Hello,
Spatial indexing can't do anything for you since you're explicitly
asking for a full join between two tables. If you think about it, your
queries computes the distance between each possible pairs of shapes in
your tables. So if you have 10000 shapes in each table,this means that
distance will be called 100.000.000 times. Ouch.

You should try to find another way to get the data you're looking for.

Best Regards,
David Garnier

le jeu 08-08-2002 à 12:31, Alexander Pucher a écrit :
> Hi,
> 
> I have a question concerning the performance of a spatial query in PostGIS.
> 
> -) I put all GMAP demo layers into my PostgreSQL/PostGIS database.
> -) I created GIST indexes for all tables.
> 
> create index park_gist on park
> using GIST (the_geom GIST_GEOMETRY_OPS)
> .
> .
> .
> 
> -) Did vacuum analyze all tables.
> 
> OK, I want to query all parks in the GMAP demo that have a road running 
> through them.
> 
> My SQL query:
> 
> SELECT park.*
> FROM park, road
> WHERE DISTANCE(park.the_geom, road.the_geom) < 2
> 
> 
> 
> EXPLAIN SELECT park.*
> FROM park, road
> WHERE DISTANCE(park.the_geom, road.the_geom) < 2
> 
> gives me the following output:
> 
> Nested Loop  (cost=0.00..8917.66 rows=23736 width=163)
>   ->  Seq Scan on park  (cost=0.00..7.46 rows=46 width=131)
>   ->  Seq Scan on road  (cost=0.00..170.48 rows=1548 width=32)
> 
> 
> the query works fine and gives me the correct result after.....about 6 
> minutes ( on my 1Ghz Notebook)!!
> 
> I expected the result to be there within seconds?!?
> 
> Something wrong with the indexes??
> 
> Any help is welcome.
> 
> 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
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>