[postgis-users] Newcomer-performance issue

[Pedro Doria Meunier]

Hi list,

 

This is my first post so, Hello from Madeira Is. !! :-)

 

Now, I've been developing a fleet-tracking application and using (of course)
pgsql/postgis.

 

To be specific with my dilemma:

I want to search for the road closest to a vehicle's current coordinates,
and I want to give a radius in *meters*.

The road's layer is in WGS84 as well as the current vehicle's coordinates,
so I have to reproject on the fly to a datum that allows me to work in
metres; in this case WGS84 / UTM zone 28N.

 

So here's the query:

 

select name, city from rede_estradas where 

transform(geometry,32628) &&
setsrid(expand(transform(geomfromtext('POINT(-16.851888
32.646385)',4326),32628), 5), 32628) 

and distance(transform(geomfromtext('POINT(-16.851888
32.646385)',4326),32628), transform(geometry,32628)) <=5 limit 1;

 

Well. it 'werks'. *but*! It takes ~1600ms to retrieve the result. :-(
imagine this for a fleet of, say, 500 vehicles.

I'm using Gist on the geometry column and VACCUM ANALYZEd the table.

 

So, any of you Postgis gurus could point me to a way to speed up this query?

 

Any help/info would be greatly appreciated!

 

Best regards,

 

Pedro Doria Meunier

(351) 91 302 49 72 - (351) 96 247 99 12

MSN -  <mailto:pdoriam at hotmail.com> pdoriam at hotmail.com

ICQ - 308-182-126

Skype: pdoriam

 

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