本文将向各位介绍如何使用MySql5.x中的空间数据库,并展示一下它高效的性能(前提是正确使用)。
本文适合于对SQL和MYSQL熟悉的人员。
步骤1:创建支持空间查询的表
CREATE TABLE `points`(
) ENGINE=MyISAM DEFAULTCHARSET=gbk;
可以在下面的文档中找到所有Mysql支持的空间数据类型:
http://dev.mysql.com/doc/refman/4.1/en/spatial-extensions.html
步骤2:向空间数据表中插入数据
INSERT INTO Points (name,location) VALUES ( 'point1' , GeomFromText( ' POINT(31.5 42.2) ' ))
http://dev.mysql.com/doc/refman/4.1/en/gis-wkt-format.html
步骤3:从空间数据表中读取数据
SELECT name,AsText(location) FROM Points;
SELECT name,AsText(location) FROM Points WHERE X(location) < 10 andY(location) > 12;
步骤4:空间表的高级查询
把指定的几何对象转变易读的文本:
SELECTAsText(Envelope(GeomFromText('LineString(1 1,22)')));
返回指定几何对象的大小:
SELECTGeometryType(GeomFromText('POINT(1 1)'));
返回指定几何对象的类型:
SELECTGeometryType(GeomFromText('POINT(1 1)'));
查找指定矩形范围内的点:
SET @bbox = 'POLYGON((0 0,10 0, 10 10, 0 10, 0 0))';
SELECT name,AsText(location) FROM Points WHERE Intersects( location,GeomFromText(@bbox) );
步骤5:查找圆形区域内的点
这一步介绍如何查询圆形区域(通常用一个中心点和半径来表示)内的几何对象。
您首先想到的语句可能是:
SET @point = 'POINT(1010)';
SET @radius =20;
SELECT name,AsText(location) FROM Points WHERE Distance(location,GeomFromText(@point)) < @radius;
但是这条语句运行会出错,因为Distance函数还没有实现。MySql空间扩展文档说明中已经说明他们只实现了OpenGis标准的一部分。
一个替代的方式是使用intersect函数。
MySql空间扩展文档中已经指明各种几何对象可以使用intersect函数来判断几何对象是否和一个矩形相交。
这样在取得近似范围后我们可以再使用距离估算来过滤出正确的结果。
SET @center =GeomFromText('POINT(10 10)');
SET @radius =30;
SET @bbox =CONCAT('POLYGON((',
X(@center) - @radius, ' ',Y(@center) - @radius, ',',
X(@center) + @radius, ' ',Y(@center) - @radius, ',',
X(@center) + @radius, ' ',Y(@center) + @radius, ',',
X(@center) - @radius, ' ',Y(@center) + @radius, ',',
X(@center) - @radius, ' ',Y(@center) - @radius, '))'
);
[1]
SELECT name,AsText(location)
FROMPoints
WHERE Intersects( location,GeomFromText(@bbox) )
AND SQRT(POW( ABS(X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)),2 )) < @radius; To Obtain a result ordered by distance from thecenter of the selection area:
[2]
SELECT name,AsText(location), SQRT(POW( ABS( X(location) - X(@center)), 2) +POW( ABS(Y(location) - Y(@center)), 2 )) ASdistance
FROMPoints
WHERE Intersects( location,GeomFromText(@bbox) )
AND SQRT(POW( ABS(X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)),2 )) < @radius
ORDER BYdistance;
步骤6:测试性能
最后一步我们来试试在大数据量的情况下空间数据查询的性能。
首先我们新建一个存储过程,指定一个随机数值随机产生记录插入到Points表中。
CREATE PROCEDUREfill_points(
IN sizeINT(10)
)
BEGIN
DECLARE i DOUBLE(10,1)DEFAULT size;
DECLARE lonFLOAT(7,4);
DECLARE latFLOAT(6,4);
DECLARE positionVARCHAR(100);
-- Deletingall.
DELETE FROMPoints;
WHILE i > 0DO
SET lon = RAND() * 360 -180;
SET lat = RAND() * 180 -90;
SET position = CONCAT('POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name,location) VALUES ( CONCAT('name_', i), GeomFromText(position));
SET i = i -1;
ENDWHILE;
END
然后调用该存储过程,参数指定一个较大的数字,例如我们想产生一百万条记录:
CALLfill_points(1000000);
然后我们执行查询[1]和[2]
在我机器上(Intel Core Duo 2.0GHz Laptop)的测试结果是:
圆形区域选择(即周边搜索)结果不排序[1]
43862 rows in set ~1.10 secwith 1.000.000 records
圆形区域选择(即周边搜索)结果排序[2]
43862 rows in set ~1.72 secwith 1.000.000 records