使用mysql查看纬度/经度是否落在多边形内 [英] See if lat / long falls within a polygon using mysql

问题描述

我已经在下面创建了表格

I have the created the table below

CREATE TABLE geom (g GEOMETRY);

并插入了许多行，例如下面的示例:

and have inserted many rows, example below:

INSERT INTO geom (g)
VALUES(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))')
);

现在，我想搜索所有数据并返回我跌落的经度/纬度与任何多边形都没有的项.

Now I want to search all the data and return the entries where a lat / long I have falls withn any of the polygons.

如何使用mysql完成此操作?还是有人知道任何指向我正确方向的链接?

How can this be done using mysql? or is anyone aware of any links that will point me in the right direction?

推荐答案

MySQL自v5.1起仅支持在

MySQL as of v5.1 only supports operations on the minimum bounding rectangles (MBR). While there is a "Contains" function which would do what you need, it is not fully implemented and falls back to using MBRContains

来自

当前，MySQL未实现 这些功能根据 规格.那些是 实现的返回结果与 相应的基于MBR的功能. 这包括 除Distance()之外的以下列表 和Related().

Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions. This includes functions in the following list other than Distance() and Related().

这些功能可以在以下位置实现 未来版本，全面支持 空间分析，而不仅仅是基于MBR的 支持.

These functions may be implemented in future releases with full support for spatial analysis, not just MBR-based support.

您可以做的是让MySQL为您提供基于MBR的近似结果，然后对其进行后期处理以执行更准确的测试.或者，切换到 PostGIS ！

What you could do is let MySQL give you an approximate result based on MBR, and then post process it to perform a more accurate test. Alternatively, switch to PostGIS!

(2012年5月更新-感谢Mike Toews)

MySQL 5.6.1+提供了

MySQL 5.6.1+ offers functions which use object shapes rather than MBR

MySQL最初实现了这些功能，因此它们使用了 对象包围矩形，并返回与 相应的基于MBR的功能.从MySQL 5.6.1开始， 提供使用精确对象形状的版本.这些版本 以ST_前缀命名.例如，Contains()使用object 边界矩形，而ST_Contains()使用对象形状.

MySQL originally implemented these functions such that they used object bounding rectangles and returned the same result as the corresponding MBR-based functions. As of MySQL 5.6.1, corresponding versions are available that use precise object shapes. These versions are named with an ST_ prefix. For example, Contains() uses object bounding rectangles, whereas ST_Contains() uses object shapes.

这篇关于使用mysql查看纬度/经度是否落在多边形内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！