[OSM-dev] projection for calculating distances in PostGIS

Emilie Laffray emilie.laffray at gmail.com
Wed May 19 13:53:56 BST 2010


On 19 May 2010 13:26, Marcus Wolschon <Marcus at wolschon.biz> wrote:

> > On 19 May 2010 13:06, Marcus Wolschon <Marcus at wolschon.biz> wrote:
>
> It is postgis 1.4 and the data-columns are geometries in WGS84 with
> the bounding-boxes
> stored pre-computed and projected in their own column, for faster access.
>
> The page you mentioned has a ST_DWithin_Sphere but that only works
> between 60 and -60° and it does not help with the distance-calculation
> required for
> sorting the results by distance (I forgot to mention that. This is
> about finding the closest
> element to a search-point where the elements may be points, lines and
> areas.).
> This code needs to work on all of the continental planet except north
> and south polar
> regions. (60,60 is still somewhere in russia).
>
> So the question is just to find a better projection to use.
> Due to locality it would even be possible to use different projections
> based on WGS84-
> coordinate-regions.
>
>
The page is interesting due to the utmzone function. This will allow to know
what projection to use if you want a good approximation. It return a SRID
that you can use to do a ST_Transform.
But what you want to do is going to be very painfully slow especially if you
are going to work with geometries other than points, since it means that you
have to reproject the data for each geometries you are going to work with
potentially. The following page will work in any units that you want.
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic

ST_Distance_Sphere on 1.4 works only on points. One other possibility
instead of using a nearest neighbour functionality is to do a search with
ST_DWithin applying a ratio depending on the latitude. Once you have this,
you can always restrict with ST_Distance by using an approximation of what
your value should be in meters through an approximation. Needless to say
that this kind of logic belongs into a function.

Emilie Laffray

Emilie Laffray
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20100519/f447171a/attachment.html>


More information about the dev mailing list