现在的位置: 首页 > 综合 > 正文

学习mysql4.1的空间数据库特性(1)

2013年08月28日 ⁄ 综合 ⁄ 共 6068字 ⁄ 字号 小 中 大 ⁄ 评论关闭

06年10月16日

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

公司使用mysql4.1.2,

 

参考资料:

官方中文资料: http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#gis-class-polygon

 

说明:这是本人学习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.

 

 

晕，突然发现官方有中文的文档，不用看洋文了！！！

http://dev.mysql.com/doc/refman/5.1/zh/spatial-extensions-in-mysql.html#gis-class-polygon

 

 

下面是文档中解决问题4的方法。

 

在geom有６个点，现在构造一个Polygon((2.1 2.1,2.1 3.1,2.1 3.1,3.1 3.1))

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）