[postgis-users] optimize query

[Obe, Regina]

I think using expand instead of buffer would be much more efficient since expand is a much simpler process than buffer and the && would then reduce back to a bounding box compare anyway, but in this case it probably doesn't matter too much since it is a static point you are buffering.

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Pedro Doria Meunier
Sent: Fri 3/9/2007 12:18 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] optimize query



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.

 

 




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070309/275048cb/attachment.html>