[postgis-users] Issue with spatial query (PostGIS, ST_DWithin, SRID, transform)

[Paul Ramsey]

You're using OSM and you're worried about precision? :) (couldn't resist)

you can use st_distance_spheroid() or st_distance_sphere() to skip the
transform step. That won't give you an indexed result, so things will
be slow on large databases for spatially restricted queries.

P.

On Fri, Jul 24, 2009 at 10:12 AM, Mulone<andrea.ballatore at ucd.ie> wrote:
>
> I just realised that... Thanks.
>
> But then again, what SRID am I supposed to use to get meters?
> Sorry but I couldn't find any proper documentation on this apart from blog
> posts.
>
> For example, the query:
>
> SELECT distance(
> transform( GeomFromEWKT('SRID=4326;POINT( -6.195  53.283 0)'), 32661 ),
> transform( way, 32661 )
> ),*
> FROM planet_osm_point
> WHERE osm_id = 47204752
>
> seems to return meters, but how can I figure out the query precision and
> reliability?
>
> Thanks again,
> Mulone
>
>
> Paul Ramsey-3 wrote:
>>
>> It means you should be using POINT(-6.195 53.283 0), you are reversing
>> your coordinates for the lonlat point.
>>
>> P
>>
>> On Fri, Jul 24, 2009 at 9:48 AM, Mulone<andrea.ballatore at ucd.ie> wrote:
>>>
>>> If I execute:
>>>
>>> SELECT distance(GeomFromEWKT('SRID=4326;POINT(53.283 -6.195 0)'), way),*
>>> FROM planet_osm_point WHERE osm_id = 47204752
>>>
>>> the expected distance is about 100-150 meters, and I get
>>> 84.1155775105245, which is in decimal degrees, but what does that mean?
>>> Degrees from what?
>>>
>>> If I re-project everything in another SRID, I get the following values,
>>> which aren't less mysterious to me:
>>> SRID: 32662 ===> distance: 9363703.25625372
>>> SRID: 26986 ====> distance: 9366636.40006539
>>>
>>> Any ideas?
>>>
>>> Mulone
>>>
>>>
>>>
>>> Mulone wrote:
>>>>
>>>> Hi Pedro,
>>>> Thanks for your reply.
>>>>
>>>> I've tried to re-project everything with
>>>>
>>>> SELECT osm_id FROM planet_osm_polygon WHERE
>>>> ST_DWithin(ST_transform(way,2163),ST_transform(GeomFromText('POINT(53.30124
>>>> -6.21804 0)', 4326),2163), 100);
>>>>
>>>>
>>>> But it doesn't return anything.
>>>>
>>>> That's my scenario: there is just one polygon within 100m, and I'm
>>>> actually able to get that object if I enter 84.16 as a distance. Is
>>>> there
>>>> a logical explanation to this? With such a huge distance I would expect
>>>> postigs to return everything.
>>>>
>>>>
>>>> Pedro Doria Meunier wrote:
>>>>>
>>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>>> Hash: SHA1
>>>>>
>>>>> Mulone,
>>>>>
>>>>> Openstreetmap uses a Mercator projection.
>>>>> When you specify '100' you're actually saying 'give me everything
>>>>> within 100 *degrees*'
>>>>>
>>>>> You should reproject your data to the desired/applicable projection by
>>>>> means of transform();
>>>>>
>>>>> HTH,
>>>>>
>>>>> Pedro Doria Meunier
>>>>> GSM: +351 96 17 20 188
>>>>> Skype: pdoriam
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Mulone wrote:
>>>>>> [apologies for cross posting]
>>>>>>
>>>>>> 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
>>>>> -----BEGIN PGP SIGNATURE-----
>>>>> Version: GnuPG v1.4.7 (GNU/Linux)
>>>>> Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
>>>>>
>>>>> iD8DBQFKaK5G2FH5GXCfxAsRAiNvAJwND0pmRWNaODnJ0foW9uraaS1YnACfbdIX
>>>>> +de0ZbKmALUpjlFbzbO/rnk=
>>>>> =eWY7
>>>>> -----END PGP SIGNATURE-----
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Issue-with-spatial-query-%28PostGIS%2C-ST_DWithin%2C-SRID%2C-transform%29-tp24631779p24647972.html
>>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>>
>>> _______________________________________________
>>> 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
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Issue-with-spatial-query-%28PostGIS%2C-ST_DWithin%2C-SRID%2C-transform%29-tp24631779p24648322.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>