MYSQL的空间查询

http://blog.sina.com.cn/s/blog_a48af8c001018q1p.html

本文将向各位介绍如何使用MySql5.x中的空间数据库，并展示一下它高效的性能（前提是正确使用）。

本文适合于对SQL和MYSQL熟悉的人员。

 

步骤1：创建支持空间查询的表

        首先来说一下如何创建一个包含空间数据的名为Points的表。

CREATE TABLE `points` (

  `name` varchar(20) NOT NULL DEFAULT '',

  `location` point NOT NULL,

  `description` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`name`),

  SPATIAL KEY `sp_index` (`location`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

       这条DDL命令创建了一个名为Points的表，包含一个name字段和一个类型为point的字段location（所处位置）及descrption（描述）字段。

       正如你所看到的，空间类型字段的使用跟Mysql中其他类型一样，创建时选择相应的类型即可。

       空间数据类型的基类是Geometry。

可以在下面的文档中找到所有Mysql支持的空间数据类型：

http://dev.mysql.com/doc/refman/4.1/en/spatial-extensions.html

步骤2：向空间数据表中插入数据

       我们来看一看想Points表中的插入数据是多么的简单：

INSERT INTO Points (name, location) VALUES ( 'point1' , GeomFromText( ' POINT(31.5 42.2) ' ) )

       这是一个普通的SQL插入操作，只有函数GeomFromText()是我们以前未见过的。这个函数接受一个字符串，并且返回一个几何对象。有关该字符串的GIS标准格式详见：

http://dev.mysql.com/doc/refman/4.1/en/gis-wkt-format.html

步骤3：从空间数据表中读取数据

       从Points表中读取数据也是非常简单的：

SELECT name, AsText(location) FROM Points;

       以上语句的返回结果中location会被转换成跟第二步中一样的GIS标准字符串。实际上AsText函数仅仅是把数据库内部存储的几何对象格式化成一个字符串而已。

       下面一个函数也是非常有用的：

SELECT name, AsText(location) FROM Points WHERE X(location) < 10 and Y(location) > 12;

       该Select语句返回一系列location的X()（经度）小于10并且Y()（经度）大于12的点集合。

步骤4：空间表的高级查询

把指定的几何对象转变易读的文本：

SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));

返回指定几何对象的大小：

SELECT GeometryType(GeomFromText('POINT(1 1)'));

返回指定几何对象的类型：

SELECT GeometryType(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(10 10)';

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)

FROM Points

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 the center of the selection area:

 

[2]

SELECT name, AsText(location), SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) AS distance

FROM Points

WHERE Intersects( location, GeomFromText(@bbox) )

AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius

ORDER BY distance;

 

步骤6：测试性能

最后一步我们来试试在大数据量的情况下空间数据查询的性能。

首先我们新建一个存储过程，指定一个随机数值随机产生记录插入到Points表中。

CREATE PROCEDURE fill_points(

IN size INT(10)

)

BEGIN

DECLARE i DOUBLE(10,1) DEFAULT size;

 

DECLARE lon FLOAT(7,4);

DECLARE lat FLOAT(6,4);

DECLARE position VARCHAR(100);

 

-- Deleting all.

DELETE FROM Points;

 

WHILE i > 0 DO

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;

END WHILE;

END

 

然后调用该存储过程，参数指定一个较大的数字，例如我们想产生一百万条记录：

CALL fill_points(1000000);

然后我们执行查询[1]和[2]

 

在我机器上(Intel Core Duo 2.0 GHz Laptop)的测试结果是：

圆形区域选择（即周边搜索）结果不排序[1]

43862 rows in set ~1.10 sec with 1.000.000 records

圆形区域选择（即周边搜索）结果排序[2]

43862 rows in set ~1.72 sec with 1.000.000 records

 

原文地址：http://howto-use-mysql-spatial-ext.blogspot.com/