拓展mysqlspatial函数_MySQL
- WBOY原創
- 2016-06-01 13:32:381743瀏覽
bitsCN.com
拓展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)
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