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.

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.


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

SELECT *
FROM theuser
WHERE ST_DWithin(
  point::geography,
  ST_GeomFromText('POINT(120.9982 24.788)',4326)::geography,
  100 -- DISTANCE IN METERS
);

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.


Try below query, its working fine for me.

select * from theuser where ST_DWithin(point , ST_GeomFromText('POINT(120.9982 24.788)',4326) , 0.1/111.325 );

Here, 0.1 = 100 meters.


Need Your Help

Determining actual height of an Admob SmartBanner in Unity for Android

android unity3d admob

So I'm implementing admob banners into my Unity 4.6 Android game. I have the banner at the bottom and need to account for it in my UI. Specifically I need to know exactly how tall it is, but I'm ha...

Is there anyway to merge cobertura coverage xml reports together?

unit-testing code-coverage cobertura gcovr

I have c++/c application with a lots of unit tests. I would like to get overall coverage and also individual coverage of each test with condition that each test can be run only once.