[postgis-users] Newcomer-performance issue

[Pedro Doria Meunier]

Hi List (*especially* JEFF HOFFMAN ;-) )

First of all thank you very much to all who took the time to reply.

Special thanks to Jeff Hoffman that pointed me in the right direction.
Spatial indexes weren't being used. I should have suspected as much
beforehand...

And thus the solution presented itself quite simply:
Since I'll always use metres in radius-based searches it's only a matter of
creating indices for all WGS84/UTM zones! Bit tedious, but does the job! ;-)

Spatial indices (using that transform() ) *will* be used regardless of zone!
;-)

Again, thank you all.

Best regards,

Pedro Doria Meunier
(351) 91 302 49 72 - (351) 96 247 99 12
MSN - pdoriam at hotmail.com
ICQ - 308-182-126
Skype: pdoriam


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Jeff
Hoffmann
Sent: domingo, 1 de Outubro de 2006 23:18
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Newcomer-performance issue

Pedro Doria Meunier wrote:

> 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.

This is just a guess, but your query is probably not using the index you 
created.  You should check this using explain, but if you created the 
index on the original column and not the transformed version, it won't 
be able to use the index.  If it's not using the index, try creating an 
index like this:

create index rede_estradas_index_geometry on rede_estradas using 
gist(transform(geometry, 32628) gist_geometry_ops);


-- 
Jeff Hoffmann
Head Plate Spinner
PropertyKey.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users