MySQL：简单的地理/空间查询

发布于 2025-01-01 13:27:16 字数 570 浏览 7 评论 0原文

我一直在研究如何最好地从 MySQL 数据库检索位置数据的子集。这个答案链接到许多引发希望的文章，但没有一篇提供任何具体、简单的示例：

假设我有一个包含表 PLACES 的数据库。 PLACES 中可能有数百万个条目。每行都有列类型 LAT、LON 和 NAME。

我想从以下半伪代码中形成一个功能语句：

Select * from PLACES where distance((LAT, LON), (givenLat, givenLon)) < 100 meters;
return *[NAME];

它相当简单，但尽管我广泛使用了 MySQL，但我以前从未使用过空间扩展。我愿意与他们合作，但我也愿意编写自定义函数（如果这样更快的话）。上面的查询实际上就是我需要生成的。文档之外的任何指示都会非常有帮助，谢谢。

原文

I have been researching how best to retrieve subsets of location data from a MySQL database. This answer had links to many hope-inducing articles, but none of which provided any concrete, simple examples:

Database: Best performance way to query geo location data?

Let's say I have a database with a table PLACES. PLACES could have millions of entries in it. Each row has column types LAT, LON, and NAME.

I would like to form a functional statement from the following semi-pseudocode:

Select * from PLACES where distance((LAT, LON), (givenLat, givenLon)) < 100 meters;
return *[NAME];

It's fairly simple, but though I've used MySQL extensively I have never used the spacial extensions before. I am willing to work with them but I am also willing to write custom functions if that would be faster. The above query is literally what I need to produce. Any pointers beyond the docs would be very helpful, thank you.

分享到QQ

分享到微博

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

发布评论

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

知你几分 2025-01-08 13:27:16

虽然我不掌握 mysql 地理空间扩展，但我建议您阅读此演示文稿 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL ，因为它包含我一直在使用的地理距离函数的有趣建议相当满意。

SELECT destination.*,
3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) *  pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))
asdistanceFROM users destination, users originWHERE origin.id=userid
and destination.longitudebetween lon1 and lon2 and destination.latitudebetween lat1 and lat2

Although I do not master mysql geospatial extentions, I would suggest you to read this presentation http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL , for it contains an interesting proposal of geo distance function that I have been using quite satisfactorily.

SELECT destination.*,
3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat - dest.lat) *  pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) * POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) ))
asdistanceFROM users destination, users originWHERE origin.id=userid
and destination.longitudebetween lon1 and lon2 and destination.latitudebetween lat1 and lat2

回复收藏 0 原文