Postgis 中 2 个点之间的距离（srid 4326）（以米为单位）

发布于 2024-12-20 13:08:54 字数 927 浏览 1 评论 0原文

这可能是一个简单的问题，但我不太擅长 PostGIS，也没有完全理解所有这些。

基本上我有一个带有 POINT 列（point）的表（nodes）。我已在此列上创建了索引

create index nodes__points on nodes using gist (point)

该列

select addgeometrycolumn('nodes', 'points', 'POINT', 4326, 2)

是使用 srid 4326 创建的，因为我正在添加表单（纬度、经度）中的数据。（即爱尔兰都柏林位置为 lat=53.353 lon=-6.264 的坐标系（我用 GeomFromText('POINT(-6.264 53.535)') 添加））。

对于每个点，我想找到大约在以该点为中心的 1 公里框中的所有点（因此从节点为 a、节点为 b 中选择 a.id、count(*)，其中 SOME_DISTANCE_FUNCTION_HERE(a.point, b.point, 1000) group by a.id; 不必很精确，一个 1km 的 bbox 就可以了。 1km 的圆圈就可以了。

ST_Distance/ST_DWithin/等都使用 SRID 的单位。对于 4326/WGS64 来说是度（所以 1 = 1 纬度/经度），但我想使用米

。 st_dwithin 可以使用米，但如果我这样做，explain 显示索引没有被使用，

并使用地理索引？

更新：这是在 PostgreSQL 9.1 和 PostGIS 2.0 svn 上构建的。

原文

This is probably a simple question, but I'm not very good at PostGIS and don't fully grok all of this.

Basically I have a table (nodes) with a POINT column (point). I have created an index on this column

create index nodes__points on nodes using gist (point)

The column was created with

select addgeometrycolumn('nodes', 'points', 'POINT', 4326, 2)

I am using srid 4326 because I'm adding data that's in the form (latitude, longitude). (i.e. the co-ordinate system where the position of Dublin, Ireland is lat=53.353 lon=-6.264 (which I've added with GeomFromText('POINT(-6.264 53.535)'))).

For each point, I want to find all points that are roughly within a 1km box centred on that point (so selcet a.id, count(*) from nodes as a, nodes as b where SOME_DISTANCE_FUNCTION_HERE(a.point, b.point, 1000) group by a.id;. It doesn't have to be exact, just a rough hueristic figure. a 1km bbox is fine, a 1km circle is fine. It doesn't have to be exactly 1km, just that order of magnitude.

The ST_Distance/ST_DWithin/etc. all use the units of the SRID, which for 4326/WGS64 is degrees (so 1 = 1 degree of lattitude/longitude). But I want to use metres.

I tried ST_distance_sphere and st_dwithin which can use metres, but if I do that, the explain shows that the index isn't being used.

How can I get roughly what I want, and use the geographical index?

UPDATE: This is on PostgreSQL 9.1 and PostGIS 2.0 svn build.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

落在眉间の轻吻 2024-12-27 13:08:54

您可以使用 ST_Transform 来使用米，还请记住，并非所有函数都适用于地理类型，但如果您确实需要速度，则使用 ST_DWithin 是最快的方法。以下是度和米之间换算的近似值：

| places | degrees    | distance |
| ------ | ---------- | -------- |
| 0      | 1.0        | 111 km   |
| 1      | 0.1        | 11.1 km  |
| 2      | 0.01       | 1.11 km  |
| 3      | 0.001      | 111 m    |
| 4      | 0.0001     | 11.1 m   |
| 5      | 0.00001    | 1.11 m   |
| 6      | 0.000001   | 0.111 m  |
| 7      | 0.0000001  | 1.11 cm  |
| 8      | 0.00000001 | 1.11 mm  |

You could use ST_Transform to use meters, also remeber that not all functions are available with geography types but if you really need speed use ST_DWithin, is the fastest way. Here's an aproximation of conversions between degrees and meters:

| places | degrees    | distance |
| ------ | ---------- | -------- |
| 0      | 1.0        | 111 km   |
| 1      | 0.1        | 11.1 km  |
| 2      | 0.01       | 1.11 km  |
| 3      | 0.001      | 111 m    |
| 4      | 0.0001     | 11.1 m   |
| 5      | 0.00001    | 1.11 m   |
| 6      | 0.000001   | 0.111 m  |
| 7      | 0.0000001  | 1.11 cm  |
| 8      | 0.00000001 | 1.11 mm  |

回复收藏 0 原文