[postgis-users] optimize query

[Pedro Doria Meunier]

Paul and Rob,

 

First of all thanks for your time!

 

This is the way I solved it in terms of speed


 

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

from warped_freguesias as f 

WHERE f.geometry && buffer(geomfromtext('POINT(-9.17266 38.68783)',4326),
0.016)

order by thedist 

limit 1;

 

I used roughly a nautical mile
 

 

Now my query returns in 15ms :P

 

Pedro Doria Meunier

 

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:56
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] optimize query

 

Yeah, it helps when you fully read the question before answering,  sorry
about that.

 

What I do in cases like you present is to create a buffer around the point
to create an area where that would find another object or return an error.
For instance I need to find the ‘nearest’ road to a point, if that point
falls within a city geometry, I create a buffer that is roughly 1 statute
mile, if it is rural then I use a 5 mile buffer. In these cases if I don’t
find a road close by, then it would be an error. Don’t know if that would
help you in your case or not. 

 

You could write a simple stored procedure that expanded the buffer to a max
limit if the query wasn’t returning any geometries. Of course that would
only help if your table is big and speed of executing multiple queries
against the index was faster than doing a seq scan. 

 

 

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:35 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] optimize query
Importance: High

 

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/0f4e2726/attachment.html>