[OSM-dev] Issue with spatial query (PostGIS, ST_DWithin, SRID, transform)

[Emilie Laffray]

Hello,

In short, ST_DWITHIN always works in the units of your SRID. In the case
of 4326, it will be in degrees decimal. You can get something in meters
by transforming into the proper UTM zone for
your area but the performance is dreadful (There is a function on
Postgis Wiki to find the proper UTM zone).
One of the thing that you can do is either perform an approximation of
your radius in degrees, but you have to be aware that the value will
change depending on your latitude and that it will never be a perfect
circle.
The other thing is to choose a radius in degrees large enough for what
you want considering average latitude usage of your program and add a
test with ST_DISTANCESPHERE that will always be in meters. Do no rely on
ST_DistanceSphere alone as it doesn't use the index.
If you want more information let me know.

Emilie Laffray

Mulone wrote:
> Hello,
> I'm running some queries on a PostGIS db containing OSM data loaded with
> osm2pgsql.
> I want to get the geometries within a certain distance from a point. I've
> written this query:
>
> SELECT osm_id FROM planet_osm_point WHERE
> ST_DWithin(way,geomfromtext('POINT(53.30124 -6.21804 0)', 4326), 100);
>
> where 100 is meant to be 100 meters. This query returns all the objects in
> the table, and running it with different distances I found a sort of
> threshold around 84.16, where I start getting less objects.
>
> I thought it was an issue related to the system reference, so I tried with
> this conversion:
>
> SELECT osm_id FROM planet_osm_point WHERE
> ST_DWithin(transform(way,2163),transform(geomfromtext('POINT(53.30124
> -6.21804 0)', 4326),2163), 5740000);
>
> The threshold in this case seems to be about 5740000, which is obviously not
> meters.
> How can I figure out the units DWithin is working in?
> Besides, I doubt this is the best way to do what I want, any better ideas?
>
> Thanks in advance!
> Mulone
>   


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 195 bytes
Desc: OpenPGP digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20090723/f42e282e/attachment.pgp>