[postgis-users] Newcomer-performance issue

[Carl Anderson]

Pedro,

Looking at the spatial operator && (which utilized indexes)

I see that neither the Left or the Right side of the operator is a field 
in a database tuple/record.
Both sides are the output of functions.

First, I don't believe that functions are indexable in PostgreSQL
Even if they are, I doubt you built such an index.

Both sides of the && operator are the results of a transform so a 
regular GIST index is useless as a basis for an index scan.
Would it be possible to rewrite your test as

select name, city
from rede_estradas
where

geometry && 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;

presuming that the geometry in rede_estradas is in SRID 32628 to start with.

If I was concerned about speed I would add an extra geometry column in 
the projection 32628 to rede_estradas, and index it.

C.

Carl Anderson
GIS Manager
Fulton County Georgia, USA

Pedro Doria Meunier wrote:
>
> Hi list,
>
> This is my first post so, Hello from Madeira Is. !! J
>
> 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… L 
> 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 - pdoriam at hotmail.com <mailto:pdoriam at hotmail.com>
>
> ICQ - 308-182-126
>
> Skype: pdoriam
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>