postgresql ST_DWithin 将参数作为 degree ，而不是 meter ，为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8444753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ST_DWithin takes parameter as degree , not meters , why?
提问by smallufo
The ST_DWithindocument says , the third parameter(distance) is in meters. But when I execute some query , it seems it takes the 3rd parameter as 'degree' ?
该ST_DWithin文件说，第三个参数（距离）以米为单位。但是当我执行一些查询时，它似乎将第三个参数作为 '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.
所有点都以 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.
它将第三个参数 (100) 作为 'degree' ，因此它返回所有数据，我必须缩小到 0.001 才能找到附近的点。
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 ?
但是如何直接将米作为第三个参数传递（我不想进行米/度转换）？我的查询有什么问题？为什么 postgreSQL 不像文档所说的那样把它当作米？
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.
如果是SRID导致了这个问题，那么什么SRID直接以'meter'为单位？（我尝试转换为 SRID=2163 ，但仍处于程度）谢谢。
采纳答案by radek
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.
如果您的数据在 SRID=4326 中，则您指定的距离以度为单位。
You either have to use ST_Transformand meter based coordinate system, or one of the two functions: ST_Distance_Sphere(faster, less accurate) or ST_Distance_Spheroid.
您要么必须使用ST_Transform和基于米的坐标系，要么使用以下两个函数之一：ST_Distance_Sphere（更快，更不准确）或ST_Distance_Spheroid。
回答by sal
If your geometryis in WGS84, i.e. srid 4326, you can cast the geometries to geography
如果您geometry使用 WGS84，即 srid 4326，您可以将几何图形转换为geography
SELECT *
FROM theuser
WHERE ST_DWithin(
point::geography,
ST_GeomFromText('POINT(120.9982 24.788)',4326)::geography,
100 -- DISTANCE IN METERS
);
回答by Francisco Valdez
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).
我不建议你每次想使用 DWithin 时都转换为米，顺便说一下，如果你想要米，你需要像Albers 投影这样的等面积投影（有很多），你为什么不试试 ST_Buffer(point, degree) ，看看它在谷歌地球上的作用，进行测量并找到一个你喜欢的数字，通常你需要预定义的范围，比如非常接近 = 0.00008，接近 = 0.0005，远 = 0.001，非常远 = 0.01，非常非常远= 0.1 等（均以度为单位）。
In a previous question you asked for the fastest way, you are in the right direction.
在之前的问题中，您要求最快的方式，您的方向是正确的。
回答by Utku
As it referenced here, https://postgis.net/docs/ST_DWithin.html, there are two different signatures for ST_DWithin function.
正如此处引用的https://postgis.net/docs/ST_DWithin.html， ST_DWithin 函数有两种不同的签名。
If anyone wants to use meters directly, the second signature which has a boolean flag will work.
如果有人想直接使用仪表，则具有布尔标志的第二个签名将起作用。
As a JPA example, I'm posting my query below.
作为 JPA 示例，我将在下面发布我的查询。
@Query("select f from Facility as f where dwithin(f.address.coordinates, :center, 10000, true) = TRUE")
List<Facility> findAllByDistance(@Param("center") Point point);
回答by Mohammed shafeek
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.
这里，0.1 = 100 米。
