拓展mysqlspatial函數(shù)_MySQL

拓展mysqlspatial函數(shù)

1) 空間中的點(diǎn)是否相等

[delphi]

DELIMITER $$

CREATE DEFINER = 'smart'@'localhost'

FUNCTION ArePointsEqual(p1 POINT,

p2POINT

)

RETURNS TINYINT(1)

DETERMINISTIC

NO SQL

BEGIN

RETURN IsZero(x(p1) -x(p2)) AND IsZero(y(p1) - y(p2));

END

$$

DELIMITER ;

2) 查找區(qū)域的中心點(diǎn)

[sql]

USE smartu;

DELIMITER $$

CREATE DEFINER = 'smart'@'localhost'

FUNCTION GetCenterPoint(g GEOMETRY)

RETURNS POINT

DETERMINISTIC

NO SQL

BEGIN

DECLARE envelope POLYGON;

DECLARE sw, ne POINT; #South-West and North-East points

DECLARE lat, lng DOUBLE;

SET envelope =exteriorring(Envelope(g));

SET sw =pointn(envelope, 1);

SET ne =pointn(envelope, 3);

SET lat = x(sw) + (x(ne)- x(sw)) / 2;

SET lng = y(sw) + (y(ne)- y(sw)) / 2;

RETURN POINT(lat, lng);

END

$$

DELIMITER ;

3) LineN

[sql]

USE smartu;

DELIMITER $$

CREATE DEFINER = 'smart'@'localhost'

FUNCTION LineN(ls LINESTRING,

n INT

)

RETURNS LINESTRING

DETERMINISTIC

NO SQL

BEGIN

IF n >= numpoints(ls)THEN

RETURN NULL;

END IF;

RETURNLineString(pointn(ls, n), pointn(ls, n + 1));

END

$$

DELIMITER ;

4) 計(jì)算兩點(diǎn)間的空間距離

[sql]

USE smartu;

DELIMITER $$

CREATE DEFINER = 'smart'@'localhost'

FUNCTION DISTANCE(lat1 DOUBLE,

lon1DOUBLE,

lat2DOUBLE,

lon2DOUBLE

)

RETURNS DOUBLE

DETERMINISTIC

NO SQL

COMMENT 'counts distance (km) between 2 points on Earth surface'

BEGIN

DECLARE dtor DOUBLEDEFAULT 57.295800;

RETURN (6371 *acos(sin(lat1 / dtor) * sin(lat2 / dtor) +

cos(lat1 / dtor) *cos(lat2 / dtor) *

cos(lon2 / dtor - lon1 /dtor)));

END

$$

DELIMITER ;

5) 是否為0

[sql]

USE smartu;

DELIMITER $$

CREATE DEFINER = 'smart'@'localhost'

FUNCTION IsZero(n DOUBLE)

RETURNS TINYINT(1)

DETERMINISTIC

NO SQL

BEGIN

DECLARE epsilon DOUBLEDEFAULT 0.00000000001;

RETURN (abs(n) <=epsilon);

END

$$

DELIMITER ;

2. 項(xiàng)目前臺、后臺的實(shí)現(xiàn)

1) 項(xiàng)目后臺實(shí)現(xiàn)

l 數(shù)據(jù)庫設(shè)計(jì)

[sql]

--創(chuàng)建表

createtable smart_u_convenience_item_spatial(

item_spatial_id varchar(36) not null,

location point not null,

latitude varchar(20),

longitude varchar(20),

convenience_item_code varchar(500),

convenience_item_name varchar(500),

primary key (`item_spatial_id`),

spatial key `sp_index`(location)

)ENGINE=MyISAM;

--往表中插入數(shù)據(jù)

INSERTINTO smart_u_convenience_item_spatial

SELECTt.convenience_item_id

, PointFromText(concat('POINT(',t.item_latitude, ' ', t.item_longitude, ')'))

, t.item_latitude

, t.item_longitude

, t.convenience_item_code

, t.convenience_item_name

from smart_u_convenience_item t;

l 后臺代碼使用到的查詢點(diǎn)的sql

[sql]

SELECT *

FROM

(SELECT *

FROM

smart_u_convenience_item t

WHERE

t.convenience_item_idIN

(SELECTs.item_spatial_id

FROM

smart_u_convenience_item_spatial s

WHERE

intersects(location,

geomfromtext(concat('POLYGON((', 3.9921123E7, ' ', 1.16365462E8,

',', 3.9921123E7, ' ', 1.16441881E8,

',', 3.9879484E7, ' ', 1.16441881E8,

',', 3.9879484E7, ' ', 1.16365462E8,

',', 3.9921123E7, ' ', 1.16365462E8,'))'))))) t

ORDER BY

item_longitude ASC

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