AW: [postgis-users] 'Within' slows down query performance

[Düster Horst]

Alex,

Instead of 

select distinct(gid),* from airport_them
where (airport_them.gid = airport.gid)
and (within(airport.the_geom,buff_123.buffer))

try to take the advantage of spatial index and the faster distance function:

select distinct(gid),* from airport_them
where airport_them.gid = airport.gid
  and airport.the_geom && buff_123.buffer
  and distance(airport.the_geom,buff_123.buffer) = 0;

For this solution it is necessary to build spatioal indexes on airport_them
and buffer_123.
  

best regards 
 
Dr. Horst Düster 
GIS-Koordinator
 
Amt für Geoinformation
Abteilung SO!GIS Koordination
Werkhofstr. 65
4509 Solothurn 

Tel.: 032 627 25 32 
Fax: 032 627 22 14

horst.duester at bd.so.ch <mailto:horst.duester at bd.so.ch> 
www.sogis.so.ch <http://www.sogis.so.ch/> 



-----Ursprüngliche Nachricht-----
Von: Alexander Pucher [mailto:pucher at atlas.gis.univie.ac.at]
Gesendet am: Freitag, 26. März 2004 16:46
An: PostGIS Users Discussion
Betreff: [postgis-users] 'Within' slows down query performance

Hi all,

need once again some help to speed up my PostGIS query.

As a first step, I buffer a multipoint table (settlements) and store the 
buffer result in a temporary table (buffer_123)
As a next step, I want to select all airports within this buffer with 
this query:

select distinct(gid),* from airport_them
where
    (airport_them.gid = airport.gid)
and
    (within(airport.the_geom,buff_123.buffer))

is this the correct way to do such a thing? It takes about a minute to 
get the result.

Any help is very appreciated,
regards,
alex.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users