﻿ 拓展mysqlspatial函数-Mysql_数据库-脚本宝典

# 拓展mysqlspatial函数

## 拓展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

Tags：

﻿
<