MySQL空间查询（Geometry类）

性能测试：

一、创建支持查询的表

创建一个包含空间数据的名为points的表。

CREATE TABLE points(

longitude  DOUBLE,

latitude  DOUBLE,

point  POINT

);

这条命令创建一个名为points的表，包含一个longitude字段，一个latitude字段，一个point字段。

二、向数据库中导入数据

因笔者已有一个包含经纬度的文件log.csv。把log.csv导入表log。

LOAD DATA INFILE 'C:\\log.csv' 
INTO TABLE log 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' 
LINES TERMINATED BY  '\n'; 

把表log中的数据导入新表points中

INSERT INTO points(longitude, latitude)SELECT longitude, latitude FROM log;

三、根据表points中的longitude，latitude字段得到point字段

UPDATE points SET point = GeomFromText(CONCAT('POINT(',points.longitude,' ',points.latitude,')'));

若报错：Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

先执行SET SQL_SAFE_UPDATES = 0;语句，然后执行上面语句。

四、从表中读取数据

1、读取表中的point字段

SELECT  AsText(point)  FROM points;

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

2、返回一系列point的X()（经度）小于10，并且Y()（纬度）小于10的点的集合

SELECT AsText(point)  FROM points WHERE  X(point) < 10 AND Y(point) < 10;

3、

四、测试性能

SELECT  AsText(point) FROM points WHERE X(point) <13000000 and Y(point) > 60000000;

duration/fetch     :  11.606sec/0.047sec

SELECT *  FROM points WHERE longitude < 13000000 and  latitude > 60000000;

duration/fetch     :  11.2941sec/0.281sec

五、如果没有数据也不必担心，下面帮你制造数据

1、创建新表

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;

2、写函数

delimiter // -- 将SQL标识符设为 '//' 


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);
 


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

3、生成数据

CALL fill_points(1000000);

4、查找区域内的点

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;