mysql 地区表 数据 mysql地理数据库

06年10月16日

需求:公司现在gis系统有大量的经纬度坐标，都是车辆行驶几个月采集过来的坐标。现在在做区域搜索功能（就是在上个月某个时间内车辆在哪里行驶）。因为现在数据库单个车辆的经纬度坐标太多，所以搜索起来非常慢（让人忍受不了的速度），随着时间的推移，可能搜索起来会越来越慢，所以公司决定考虑使用mysql的空间数据库特性。把经纬度坐标都转移到成空间数据库坐标. 

公司使用mysql4.1.2,

 

 

说明:这是本人学习mysql的空间数据库特性时候解决的一些问题和和对一些问题的看法，肯定有错误的地方，希望看到这篇文章的朋友能够多多指教，给我留言。

 

大致需要解决的问题如下: 1 空间数据库怎么样存储经纬度坐标? 2 现有的经纬度坐标如何转换成空间数据库的经纬度坐标? 3 如何查询空间数据库里面的数据？ 先来解决第一个问题！ This section describes the standard spatial data formats that are used to represent geometry objects in queries. They are: Well-Known Text (WKT) format Well-Known Binary (WKB) format Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. （通常用来表示几何物体的空间数据格式有2种一种是WTK格式,一种是WKB格式。）   WTK格式存储几何物体的一些例子 Examples of WKT representations of geometry objects: A Point: ·         POINT(15 20) Note that point coordinates are specified with no separating comma. A LineString with four points: ·         LINESTRING(0 0, 10 10, 20 25, 50 60) Note that point coordinate pairs are separated by commas. A Polygon with one exterior ring and one interior ring: ·         POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5)) A MultiPoint with three Point values: ·         MULTIPOINT(0 0, 20 20, 60 60) A MultiLineString with two LineString values: ·         MULTILINESTRING((10 10, 20 20), (15 15, 30 15)) A MultiPolygon with two Polygon values: ·         MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5))) A GeometryCollection consisting of two Point values and one LineString: ·         GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))     WKB格式存储的一些例子 The Well-Known Binary (WKB) representation for geometric values is defined by the OpenGIS specification. It is also defined in the ISO SQL/MM Part 3: Spatial standard. WKB is used to exchange geometry data as binary streams represented by BLOB values containing geometric WKB information. WKB uses one-byte unsigned integers, four-byte unsigned integers, and eight-byte double-precision numbers (IEEE 754 format). A byte is eight bits. For example, a WKB value that corresponds to POINT(1 1) consists of this sequence of 21 bytes (each represented here by two hex digits): 0101000000000000000000F03F000000000000F03F The sequence may be broken down into these components: Byte order : 01 WKB type   : 01000000 X          : 000000000000F03F Y          : 000000000000F03F Component representation is as follows: The byte order may be either 0 or 1 to indicate little-endian or big-endian storage. The little-endian and big-endian byte orders are also known as Network Data Representation (NDR) and External Data Representation (XDR), respectively. The WKB type is a code that indicates the geometry type. Values from 1 through 7 indicate Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. A Point value has X and Y coordinates, each represented as a double-precision value. WKB values for more complex geometry values are represented by more complex data structures, as detailed in the OpenGIS specification.   MySQL Spatial Data Types　（mysql的空间数据格式） MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values: GEOMETRY POINT LINESTRING POLYGON GEOMETRY can store geometry values of any type. The other single-value types (POINT, LINESTRING, and POLYGON) restrict their values to a particular geometry type. The other data types hold collections of values: MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION GEOMETRYCOLLECTION can store a collection of objects of any type. The other collection types (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION) restrict collection members to those having a particular geometry type.     现在两种空间数据的存储格式大致已经知道了，mysql的空间数据类型也清楚了。 现在猜想第二个问题解决的大致过程是！ 15，20  转换成  标准空间格式(wtk) poing(15,20)    然后再用mysql函数转换到  mysql的空间数据列中。 现在实现看看 1 先在test数据库里添加一个支持空间数据列的表geom. CREATE TABLE geom (g GEOMETRY); 但这里出现一个问题:The storage engine for the table doesn’t support GEOMETRY 大致意思:就是这个表的存储引擎不支持GEOMETRY列，查了下文档，‘spatial columns are supported only for MyISAM tables.’看到如上一句话。但是mysql默认表存储引擎应该是MyISAM，照理说应该直接支持。先不管它，直接指明表的engine = MYISAM.这样就创建成功了   2 在geom表里添加可以存储point类型数据 ALTER TABLE geom ADD pt POINT;   3 向point列添加标准的空间数据   4 数据已经存进去了，只要把它去出来，第三个问题也就解决啦！   这是从mysql的空间数据列中取出标准的wtk格式空间数据的函数AsText().   存储读取三个问题解决了！接下来自由发挥了！

 

06年10月18日 4　如何判断已有的经纬度点在某一区域内？ 在mysal4.1官方的文档找到下列函数，应该就是解决问题4的 Relations on Geometry Minimal Bounding Rectangles (MBRs) MySQL provides several functions that test relations between minimal bounding rectangles of two geometries g1 and g2. The return values 1 and 0 indicate true and false, respectively. • MBRContains(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 contains the Minimum Bounding Rectangle of g2. mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Point(1 1)'); mysql> SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1); ----------------------+----------------------+ | MBRContains(@g1,@g2) | MBRContains(@g2,@g1) | +----------------------+----------------------+ | 1 | 0 | +----------------------+----------------------+ • MBRDisjoint(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint (do not intersect). • MBREqual(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are the same. • MBRIntersects(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect. • MBROverlaps(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 overlap. • MBRTouches(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 touch. • MBRWithin(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2. mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'); mysql> SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1); +--------------------+--------------------+ | MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) | +--------------------+--------------------+ | 1 | 0 | +--------------------+--------------------+   现在我需要的功能是查找一辆车在某一段时间内是否在一段区域内经过，用点来说明的话，就是一个空间坐标点在一个特定时间段内是否包含在一个特定的矩形区域内。下面这个函数应该能达到这个功能: MBRWithin(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2.     下面是文档中解决问题4的方法。   在geom有６个点，现在构造一个Polygon((2.1 2.1,2.1 3.1,2.1 3.1,3.1 3.1))，判断是否有点包含在Polygon里，下面是实现过程。 按照判断应该有Point(3 3)应该包含在Polygon内。但是实际情况确实空集。出什么问题了呢？见下图。     查看Polygon的定义，查找原因。 YES!Polygon的外部边界（即第一参数）应该是封闭的。而我在上面构造的矩形只是我莫须有想出来的，不符合Polygon的规范 下面再实验下。 06年10月18日(晚) 我需要解决的问题中只需要矩形这个几何体，而在OpenGIS的Geometry Model的二维矩形表示用不带孔的Polygon就行了。Polygon是二维对象Surface的子类。 上图中的Polygon含有一个孔.如果要构造一个矩形，只要构造一个没有孔的封闭四边形的Polygon。带一个孔的Polygon的表示  看到上面的Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))’;（Polygon第一个参数是外部边界，第二个或者[第三个，第四个]都是Polygon里面的孔）0 0,0 3,3 0,0 0围成的图形是是一个三角形，中间有一个孔（相当于被挖空的一块），也是三角形(1 1,1 2,2 1,1 1))（在纸上画下就知道了）通过面积也可以知道孔是被挖掉的（3*3/2-1*1/2=4）；我现在要构造一个不带孔的矩形 那现在要构造一个矩形就简单的多了 呵呵算一下面积正好是矩形的面积。那跟预想的一样。 现在矩形也有了，点也有了，我们用mysql提供的函数来实际解决下问题4吧。（具体的函数见mysql4.1文档的空间数据库特性那节，现在mysql4.1只提供MySQL提供了一些可测试两个几何对象g1和g2最小边界矩形之间关系的函数。OpenGIS规范定义了下述函数。目前在MySQL尚未按照规范实施它们。对于那些已实施的函数，它们返回的结果与对应的基于MBR的函数返回的相同。包括下面列出的函数，但Distance()和Related()除外。在未来的版本中，可能会实施这些函数，为空间分析提供全部支持，而不仅仅是基于MBR的支持。） 我们用到的函数有2个： MBRContains(g1,g2) 返回1或0以指明g1的最小边界矩形是否包含g2的最小边界矩形。 MBRWithin(g1,g2) 返回1或0以指明g1的最小边界矩形是否位于g2的最小边界矩形内。   现在表中有6个点 我们来构造一个矩形。 现在来用MBRContains(g1,g2)测试下这个矩形包含了哪些点 返回了，和预想的一样:P再来测试下MBRWithin(g1,g2)函数 呵呵，OK!看上面的两张图也可以看出MBRContains和MBRWithin的区别了两个函数的区别在于参数，只要把（第一个参数 + 函数名 + 第二个参数）读出来读的通就行了，很好记忆。这不也正是SQL的优点吗~~~至此，第四个问题也解决了！