CODEWITHSUNDEEP
postgis
Allan Miller
Allan Miller

Reputation: 31

PostGIS ST_DWithin distance_of_srid Using Degrees, When Should Be Meters

I have run into a problem using PostGIS ST_DWithin using geometry points that has me completely stumped. Even though I'm using geometry points with SRID 3857 (see below), ST_DWithin interprets seems to interpret the third argument ( double precision distance_of_srid ) as DEGREES. Here's an example.

Using this table test_person_avg_lngs_lats:

       Column        |         Type         | Modifiers 
---------------------+----------------------+-----------
 avg_lng             | double precision     | 
 avg_lat             | double precision     | 
 person_avg_location | geometry(Point,3857) | 
 store_lng           | double precision     | 
 store_lat           | double precision     | 
 store_location      | geometry(Point,3857) |

and the following query:

SELECT avg_lat, avg_lng, store_lng, store_lat,
    ST_Distance_Spheroid(person_avg_location, store_location, CAST('SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG","7030\"]]' AS spheroid))/1000 AS distance,
    ST_DWithin(person_avg_location, store_location, 1) AS dwithin
FROM test_person_avg_lngs_lats
WHERE ST_DWithin(person_avg_location, store_location, 1)

The query returns results that interpret the third argument to ST_DWithin as 1 DEGREE versus 1 METER, even though I'm using geometry points with SRID 3857 which I have confirmed, uses meters units. Whatever N I pass as the third argument to ST_DWithin, the results consistently return distances around N * 100 km ( ~ 66 miles ). That's why I'm assuming ST_DWithin is interpreting it as 1 DEGREE.

Here is a sample result that should be interpreted as one meter (distance is in miles):

avg_lat         avg_lng        store_lng  store_lat  distance       dwithin
43.3275959623, -71.1169553872, -71.0626,  42.3291,   68.9794023576, true

This is the closest I've come to something on the subject: ST_DWithin takes parameter as degree , not meters , why?

Any ideas as to what might be causing this, or what I might look for to move forward in analyzing the problem?

I'm using:

postgis_full_version                                                                     
------------------------------------------------------------------
 POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER
(1 row)

version                                                   
-------------------------------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

Upvotes: 3

Views: 3739

Answers (1)

Mike T
Mike T

Reputation: 43692

Obviously, distance_of_srid uses the same distance units of the SRID, which is typically either degrees or meters. With the geometry type, distances are calculated on a flat Cartesian plane using maths familiar to most high school students. The units are not interpreted for the geometry type.

However, this assumes that the data are actually projected with the correct SRID. If you mix a projected SRID like 3857 with lat/long coordinates expressed as degrees than you will get unexplainable garbage. Review how you populated the person_avg_location and store_location columns, because I'm 99.9% certain that there was an error there.

Upvotes: 1

Related Questions