[postgis-users] distance_spheroid and index files

[Paul Ramsey]

Index usage is important. As strk notes, 4326 is measured in degrees, so 
you'll have to convert your radius to degrees in order to create the 
index bounding box.

Also note that distance_spheroid is very accurate but VERY EXPENSIVE. If 
you don't mind getting "kind of right" answers, try the much faster 
distance_sphere, which is 10 times faster.

Paul

strk at refractions.net wrote:

> On Fri, Feb 25, 2005 at 05:02:22PM +0100, RIBOT, Nicolas wrote:
> 
>>>which gives me all the cities in a 10 km radius around Berlin. 
>>>Unfortunately, this query takes several minutes as the 
>>>distance needs to 
>>>be calculated for every single city, i.e. 60000 times. I read in the 
>>>PostGIS documentation that this query can be optimised to use the 
>>>spatial index, but I can't quite figure out how to do it. If somebody 
>>>could help me out, I'd appreciate that.
>>>
>>
>>I think you should use the "&&" operator to force index usage.
>>
>>Add a clause to your query to filter out geometries laying inside a 10 km
>>bounding box.
>>You are currently computing distance for each geometry even if this geometry
>>is far away from Berlin.
>>
>>Try to build a bounding box by expanding the berlin geometry to create a
>>10km large rectangle.
>>
>>Hope this helps, and pretty sure someone in the list will be able to show
>>you how to generate such a BBox for your query.
> 
> 
> Something like:
> 
> alter table <yourtable> add nearby geometry;
> update <yourtable> set nearby = expand(<yourgeom>, <degrees>);
> create index nearby_index on <yourtable> using gist (nearby gist_geometry_ops);
> 
> The problem is that 4326 is not an isometric projection, and that a box
> is not appropriate for distance queries (a circle would be better).
> You can find a similar discussion a week ago or so with nice workarounds
> for this.... (like projecting to isometric srs, using a buffer instead
> of a bbox and reprojecting back).
> 
> --strk;
> 
> 
>>Nicolas
> 
> 
>>---------------------------------------------------------
>>
>>CE COURRIER ELECTRONIQUE EST A USAGE STRICTEMENT INFORMATIF ET NE SAURAIT ENGAGER DE QUELQUE MANIERE QUE CE SOIT EADS ASTRIUM SAS, NI SES FILIALES.
>>
>>SI UNE ERREUR DE TRANSMISSION OU UNE ADRESSE ERRONEE A MAL DIRIGE CE COURRIER, MERCI D'EN INFORMER L'EXPEDITEUR EN LUI FAISANT UNE REPONSE PAR COURRIER ELECTRONIQUE DES RECEPTION. SI VOUS N'ETES PAS LE DESTINATAIRE DE CE COURRIER, VOUS NE DEVEZ PAS L'UTILISER, LE CONSERVER, EN FAIRE ETAT, LE DISTRIBUER, LE COPIER, L'IMPRIMER OU EN REVELER LE CONTENU A UNE TIERCE PARTIE.
>>
>>
>>
>>This email is for information only and will not bind EADS Astrium SAS in any contract or obligation, nor its subsidiaries.
>>
>>If you have received it in error, please notify the sender by return email. If you are not the addressee of this email, you must not use, keep, disseminate, copy, print or otherwise deal with it.
>>
>>---------------------------------------------------------
> 
> 
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users