Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ST_DWithin takes parameter as degree , not meters , why?

The ST_DWithin document says , the third parameter(distance) is in meters. But when I execute some query , it seems it takes the 3rd parameter as 'degree' ?

Here is my simplified table structure :

> \d+ theuser;
                         Table "public.theuser"
  Column  |          Type          | Modifiers | Storage  | Description 
----------+------------------------+-----------+----------+-------------
 id       | bigint                 | not null  | plain    | 
 point    | geometry               |           | main     | 
Indexes:
    "theuser_pkey" PRIMARY KEY, btree (id)
    "point_index" gist (point)
Referenced by:
    ...
Has OIDs: no

All points are stored with SRID=4326.

And this is the query :

> select * from theuser where ST_DWithin(point , ST_GeomFromText('POINT(120.9982 24.788)',4326) , 100 );

It takes the 3rd parameter (100) as 'degree' , so it returns all data , I have to narrow down to 0.001 to find nearby points.

But how do I directly pass meters as 3rd parameter (I don't want to do meter/degree transformation ) ? What's wrong with my query ? why postgreSQL doesn't take it as meters as document says ?

Environments :

> select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.9 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit

> SELECT postgis_lib_version();
 postgis_lib_version 
---------------------
 1.4.0

If it is the SRID that causes this problem , what SRID directly makes use of 'meter' as the unit ? (I tried transforming to SRID=2163 , but still in degree) Thanks.

like image 247
smallufo Avatar asked Dec 09 '11 11:12

smallufo


3 Answers

From the docs:

For Geometries: The distance is specified in units defined by the spatial reference system of the geometries.

If your data is in SRID=4326 the distance you are specifying is in degrees.

You either have to use ST_Transform and meter based coordinate system, or one of the two functions: ST_Distance_Sphere (faster, less accurate) or ST_Distance_Spheroid.

like image 185
radek Avatar answered Sep 27 '22 22:09

radek


If your geometry is in WGS84, i.e. srid 4326, you can cast the geometries to geography to have the units in meters

SELECT *
FROM theuser
WHERE ST_DWithin(
  point::geography,
  ST_GeomFromText('POINT(120.9982 24.788)',4326)::geography,
  100 -- DISTANCE IN METERS
);
like image 29
sal Avatar answered Sep 27 '22 22:09

sal


I would't recommend you to transform to meters every time you want to use DWithin, by the way if you want meters you need an equal area projection like Albers projection (there are many), why dont you try ST_Buffer(point, degrees) , and see what it does on google earth, make measures and find a number that you like, normally you need predefined ranges, something like very near = 0.00008, near = 0.0005, far = 0.001, really far = 0.01, really really far= 0.1, etc. (all in degrees).

In a previous question you asked for the fastest way, you are in the right direction.

like image 44
Francisco Valdez Avatar answered Sep 27 '22 23:09

Francisco Valdez