[postgis-users] PostGIS spatial query performance

[Paul Ramsey]

Yes, this is sadly true. In order to figure out the answer, there is
choice by to interogate every feature in at least one of the tables. 

In the distance-from-a-fixed-point case, it is possible to leverage the
indexing to make the process very fast. Construct a query rectagle which
is slightly larger than your desired filter radius, and do an overlap
query (&&) as well as the distance() query. The && query will use the
index and drastically subset the amount of features which need the
distance() test applied to them.

Ideally, distance() would do this automatically in some way, so that
even the full join distance(geomcolumn,geomcolumn case would at least
finish in closer to a*n time rather than n*m time.

P.

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