拓展mysqlspatial函数_MySQL

拓展mysqlspatial函数

1) 空间中的点是否相等

[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) 查找区域的中心点

[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) 计算两点间的空间距离

[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. 项目前台、后台的实现

1) 项目后台实现

l 数据库设计

[sql]

--创建表

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;

--往表中插入数据

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 后台代码使用到的查询点的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