PostgreSQL:如何获取特定半径内的所有点 [英] PostgreSQL: How to get all points in certain radius
本文介绍了PostgreSQL:如何获取特定半径内的所有点的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用Postgresql 9.3,并且已安装了多维数据集和地球距离扩展。
I'm using Postgresql 9.3 and iv'e installed cube and earthdistance extensions.
我正在尝试遵循本教程,所以我有一个简单的事件表,其中包含4个字段:id,名称,纬度,经度。
I'm trying to follow this tutorial, so i have a simple events table with 4 fields: id, name, lat, lng.
现在,我正在尝试运行此查询,以获取1公里半径内的所有事件:
Now i'm trying to run this query, to get all events within 1 km radius:
SELECT events.id, events.name FROM events WHERE earth_box(31.789225, 34.789612, 1000) @> ll_to_earth(events.lat, events.lng);
但我一直收到此错误:
20:59:34 Kernel error: ERROR: function earth_box(numeric, numeric, integer) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
所以我在铸造时也运行了同样的东西:
So i ran the same thing with casting:
SELECT events.id, events.name FROM events WHERE earth_box(CAST(31.789225 AS float8), CAST(34.789612 AS float8), 1000) @> ll_to_earth(events.lat, events.lng);
我得到:
21:16:17 Kernel error: ERROR: function earth_box(double precision, double precision, integer) does not exist
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
推荐答案
好,终于几个小时后,我明白了: )
O.K, finally after couple of hours i got it :)
显然我正在使用的教程已经过时,正确的语法是:
Apparently the tutorial i was working with was outdated, the correct syntax is this:
SELECT events.id, events.name FROM events WHERE earth_box(ll_to_earth(31.789225,34.789612), 1000) @> ll_to_earth(events.lat, events.lng);
这篇关于PostgreSQL:如何获取特定半径内的所有点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文