ST_DWithin takes parameter as degree , not meters

问题:

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.

回答1:

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.

回答2:

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
);

回答3:

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.

回答4:

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.