[postgis-users] optimize query

[Pedro Doria Meunier]

Boy that was *fast*! J

 

Txs guys!

 

Anyway... you’re right Rob; but only if the point is inside the polygons
 if
not the query returns zilch


 

Think of the case when a vehicle is crossing a bridge
 ;-)

 

Pedro.

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Rob
Tester
Sent: sexta-feira, 9 de Março de 2007 16:10
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] optimize query

 

Add a where clause on the indexed column (presumably f.geometry).

 

WHERE f.geometry&& geomfromtext('POINT(-9.1533 38.69686)',4326) 

 

Rob.

 

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Pedro
Doria Meunier
Sent: Friday, March 09, 2007 9:04 AM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] optimize query
Importance: High

 

Hi List,

 

I’m once again in need of guru advice
 J

 

This is the query:

 

select freguesia, distance(geomfromtext('POINT(-9.1533
38.69686)',4326),f.geometry) as thedist 

from freguesias as f 

order by thedist 

limit 1;

 

as you can see it returns the closest polygon to a given point.

 

Problem: 4050 (**detailed**) polygons

exec time: 907 ms

 

The Gist index was created, btw


 

Is there any way to optimize this query and avoid the dreaded seq scan?

 

Txs in advance!

 

Pedro Doria Meunier.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070309/dcaab206/attachment.html>