MYSQL的空間查詢

http://blog.sina.com.cn/s/blog_a48af8c001018q1p.html 本文將向各位介紹如何使用MySql5.x中的空間數(shù)據(jù)庫，并展示一下它高效的性能（前提是正確使用）。 本文適合于對SQL和MYSQL熟悉的人員。 步驟1：創(chuàng)建支持空間查詢的表 首先來說一下如何創(chuàng)建一個(gè)包含空間

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

本文將向各位介紹如何使用MySql5.x中的空間數(shù)據(jù)庫，并展示一下它高效的性能（前提是正確使用）。

本文適合于對SQL和MYSQL熟悉的人員。

步驟1：創(chuàng)建支持空間查詢的表

首先來說一下如何創(chuàng)建一個(gè)包含空間數(shù)據(jù)的名為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命令創(chuàng)建了一個(gè)名為Points的表，包含一個(gè)name字段和一個(gè)類型為point的字段location（所處位置）及descrption（描述）字段。

正如你所看到的，空間類型字段的使用跟Mysql中其他類型一樣，創(chuàng)建時(shí)選擇相應(yīng)的類型即可。

空間數(shù)據(jù)類型的基類是Geometry。

可以在下面的文檔中找到所有Mysql支持的空間數(shù)據(jù)類型：

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

步驟2：向空間數(shù)據(jù)表中插入數(shù)據(jù)

我們來看一看想Points表中的插入數(shù)據(jù)是多么的簡單：

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

這是一個(gè)普通的SQL插入操作，只有函數(shù)GeomFromText()是我們以前未見過的。這個(gè)函數(shù)接受一個(gè)字符串，并且返回一個(gè)幾何對象。有關(guān)該字符串的GIS標(biāo)準(zhǔn)格式詳見：

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

步驟3：從空間數(shù)據(jù)表中讀取數(shù)據(jù)

從Points表中讀取數(shù)據(jù)也是非常簡單的：

SELECT name, AsText(location) FROM Points;

以上語句的返回結(jié)果中l(wèi)ocation會(huì)被轉(zhuǎn)換成跟第二步中一樣的GIS標(biāo)準(zhǔn)字符串。實(shí)際上AsText函數(shù)僅僅是把數(shù)據(jù)庫內(nèi)部存儲的幾何對象格式化成一個(gè)字符串而已。

下面一個(gè)函數(shù)也是非常有用的：

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

該Select語句返回一系列l(wèi)ocation的X()（經(jīng)度）小于10并且Y()（經(jīng)度）大于12的點(diǎn)集合。

步驟4：空間表的高級查詢

把指定的幾何對象轉(zhuǎn)變易讀的文本：

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

返回指定幾何對象的大小：

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

返回指定幾何對象的類型：

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

查找指定矩形范圍內(nèi)的點(diǎn)：

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：查找圓形區(qū)域內(nèi)的點(diǎn)

這一步介紹如何查詢圓形區(qū)域（通常用一個(gè)中心點(diǎn)和半徑來表示）內(nèi)的幾何對象。

您首先想到的語句可能是：

SET @point = 'POINT(10 10)';

SET @radius = 20;

SELECT name, AsText(location) FROM Points WHERE Distance(location, GeomFromText(@point)) < @radius;

但是這條語句運(yùn)行會(huì)出錯(cuò)，因?yàn)镈istance函數(shù)還沒有實(shí)現(xiàn)。MySql空間擴(kuò)展文檔說明中已經(jīng)說明他們只實(shí)現(xiàn)了OpenGis標(biāo)準(zhǔn)的一部分。

一個(gè)替代的方式是使用intersect函數(shù)。

MySql空間擴(kuò)展文檔中已經(jīng)指明各種幾何對象可以使用intersect函數(shù)來判斷幾何對象是否和一個(gè)矩形相交。

這樣在取得近似范圍后我們可以再使用距離估算來過濾出正確的結(jié)果。

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：測試性能

最后一步我們來試試在大數(shù)據(jù)量的情況下空間數(shù)據(jù)查詢的性能。

首先我們新建一個(gè)存儲過程，指定一個(gè)隨機(jī)數(shù)值隨機(jī)產(chǎn)生記錄插入到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

然后調(diào)用該存儲過程，參數(shù)指定一個(gè)較大的數(shù)字，例如我們想產(chǎn)生一百萬條記錄：

CALL fill_points(1000000);

然后我們執(zhí)行查詢[1]和[2]

在我機(jī)器上(Intel Core Duo 2.0 GHz Laptop)的測試結(jié)果是：

圓形區(qū)域選擇（即周邊搜索）結(jié)果不排序[1]

43862 rows in set ~1.10 sec with 1.000.000 records

圓形區(qū)域選擇（即周邊搜索）結(jié)果排序[2]

43862 rows in set ~1.72 sec with 1.000.000 records

http://howto-use-mysql-spatial-ext.blogspot.com/

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com