java高效地理围栏算法代码地理围栏服务能关闭么

转载

mob6454cc716fb0 2023-11-09 20:48:19

文章标签 java高效地理围栏算法代码 git PostgreSQL 插入数据 文章分类 Java 后端开发

文章目录

背景情况
PostGIS
一、安装与配置
二、创建GIS 数据库
三、几何对象

3.1 几何对象的输入
3.2 几何对象的存储
3.3 几何对象的输出
3.4 几何对象的运算

四、应用场景

小结

参考

背景情况

在工作中(数据库：pg)遇到求附近终端的问题，一开始通过在服务端计算，将负责的所有终端放到服务器内存中，计算距离排序，做内存分页，返回手机端，这样做，服务器压力很大。后来将计算排序的问题放到手机端去实现，将负责的终端全部下发到手机本地，通过手机本地进行计算排序，但后来发现，手机性能差一点的、数据量大的情况手机直接奔溃了。最后放在SQL中去处理，但是都是通过计算来得到结果，我觉得不够完美，因为他性能也很低，更谈不上使用索引，数据量根本撑不起。于是关注了PostGIS，想让他解决我的性能问题

PostGIS

空间数据是一类重要的数据，地图导航、打车软件、餐厅推荐、外卖快递。空间数据通常结构复杂，数据量大，对于空间数据的分析查询，其模式也跟一般DBMS不同

参考文档：http://www.postgis.net/docs/manual-2.4/ PostGIS：http://www.postgis.net/

一、安装与配置

环境信息：

PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit(提前安装完成)

接下来安装GIS

查看目前yum中可用版本

yum search postgis

安装一个PostgreSQL10对应版本的PostGIS

yum install -y postgis24_10

安装失败，出现下面：

解决依赖关系完成
错误：软件包：gdal-libs-1.11.4-12.rhel7.x86_64 (pgdg10)
需要：libgta.so.0()(64bit)
错误：软件包：postgis24_10-2.4.7-1.rhel7.x86_64 (pgdg10)
需要：hdf5
……

则需要安装epel-release包：

yum install epel-release

再从新执行安装命令

yum install -y postgis24_10

检查文件是否存在

ls /usr/pgsql-10/share/extension |grep postgis.control

java高效地理围栏算法代码地理围栏服务能关闭么_git

二、创建GIS 数据库

使用客户端连接工具创建数据库(例如：Navicat)

创建数据库，并创建postgis扩展

create database gis;
create extension postgis;

查询安装

SELECT * FROM pg_available_extensions where name ~'gis';
-- 查看版本信息
SELECT * FROM postgis_full_version();

java高效地理围栏算法代码地理围栏服务能关闭么_PostgreSQL_02

三、几何对象

PostGIS 支持很多几何类型: 点、线、多边形、复合集合体等，并提供了大量使用的相关函数。所有几何对象都有一个公共父类 Geometry 所有对象都是以ST 开头（空间类型 Spatial Type）

3.1 几何对象的输入

PostGIS 支持多种空间对象创建方式，大体上可以分为几类：

WKT Well-Know-text
WKB Well-Know-Binary
GeoJSON等编码
返回几何类型的函数

select 
'Point(1 2)'::GEOMETRY AS wkt,
'0101000000000000000000F03F0000000000000040'::GEOMETRY AS wkb,
ST_GeomFromGeoJSON('{"type":"Point","coordinates":[1,2]}') AS geo_json,
ST_Point(1,2)

3.2 几何对象的存储

PostGIS的几何类型与PostgreSQL内建的几何类型使用了不同的存储方式。

select Point(1,2),ST_Point(1,2);

java高效地理围栏算法代码地理围栏服务能关闭么_PostgreSQL_03

PostgreSQL 中的Point 只是一个包含两个Double的结构图。PostGIS 的点类型ST_Point 采用了不同的存储方式（21字节），除了两个坐标分量，还包括了一些额外的元数据：例如几何对象的实际类型，参考系的ID等。

使用 ST_AsTest 输出WKT

select ST_AsText(ST_point(1,2))

java高效地理围栏算法代码地理围栏服务能关闭么_PostgreSQL_04

在实际使用中，通常PostGIS 的空间数据类型使用统一的Geometry类型，无论点、线、还是多边形，都可以放入Geometry类型字段中。

CREATE TABLE geo (
	geom geometry
);

INSERT INTO geo VALUES
(st_point(1.0,2.0)),
('LineString(0 0, 1 1,2 1, 2 3)'),
('Polygon((0 0, 1 0, 1 1,0 1,0 0))'),
('MultiPoint(1 2,3 4)');

3.3 几何对象的输出

常用的格式：WKT 与 GeoJSON

SELECT st_astext(geom) AS wkt,st_asgeojson(geom) AS json
FROM geo;

java高效地理围栏算法代码地理围栏服务能关闭么_java高效地理围栏算法代码_05

经纬度的输出几何点

select st_aslatlontext(st_point(111.321367,39.966956));

java高效地理围栏算法代码地理围栏服务能关闭么_PostgreSQL_06

3.4 几何对象的运算

PostGIS 提供多种多样的关系判断与几何运算函数，功能非常强大。

案例一： 计算两个坐标点之间的距离，比如（1,1）和（2,2）两点之间的距离

select st_point(1,1)<->st_point(2,2);

案例二： 计算地理坐标之间的距离。比如 A(1116.321367,39.966956),B(116.315346,39.997398);

通过引入地理坐标系，（4326号坐标系，指WGS84国际GPS坐标系），就可以计算出这两点间的地理距离（3.4km）

select ST_AsText(st_geomfromtext('point(116.321367 39.966956)',4326))::geography <-> 
			 ST_AsText(st_geomfromtext('point(116.315346 39.997398)',4326))::geography;

java高效地理围栏算法代码地理围栏服务能关闭么_java高效地理围栏算法代码_07

案例三： 想知道某条路R的总长度，可以使用ST_length 统计 WGS84 坐标系下折线的总长度

SELECT
	st_length (
		st_geomfromtext (
			'multilinestring((116.351494 39.976407,116.253159 39.976395, 116.353365 39.976479))',
			4326
		) :: geography
	);

案例四： 某一景点的面积，可以通过ST_Area对ST_Polygon计算得出

SELECT
	st_length (
		st_geomfromtext (
			'polygon(( 坐标点))',
			4326
		) :: geography
	);

四、应用场景

圈入与地理围栏
圈人是LBS服务中常见的需求：给出一个中心点，找出该点周围一定距离范围内所有符合条件的对象。例如：找出以用户为中心，周围1公里所有的公交站，并按距离远近排序。

空间索引
让我们的表不再执行暴力扫描了。在千万条上亿的情况，性能更高。

上实验

创建表。使用 geometry类型创建一个字段

DROP TABLE IF EXISTS store;
CREATE TABLE store (
	id INT PRIMARY KEY,
	name VARCHAR(200),
	address VARCHAR(200),
	longitude DECIMAL,
	latitude  DECIMAL,
	storeposition geometry
);

插入数据

-- truncate table store;
INSERT INTO store(
	id,name,address,longitude,latitude,storeposition
)VALUES
(1,'华师店','华师','113.345493','23.13944',st_point('113.345493','23.13944')),
(2,'华景路','华景路','113.357938','23.134783',st_point('113.357938','23.134783')),
(3,'石牌桥','石牌桥','113.332103','23.133126',st_point('113.332103','23.133126')),
(4,'体育西路','体育西路','113.321117','23.130916',st_point('113.321117','23.130916')),
(5,'车陂','车陂','113.3953613','23.124297',st_point('113.3953613','23.124297')), 
(6,'员村','员村','113.363689','23.115535',st_point('113.363689','23.115535')),
(7,'怡景花园','怡景花园','113.354505','23.116403',st_point('113.354505','23.116403')),
(8,'珠江新城','珠江新城','113.321203','23.119087',st_point('113.321203','23.119087')), 
(9,'天河公园','天河公园','113.362316','23.123981',st_point('113.362316','23.123981')),
(10,'骏景花园','骏景花园','113.385233','23.122245',st_point('113.385233','23.122245')),
(11,'棠东','棠东','113.389782','23.130927',st_point('113.389782','23.130927')),
(12,'未知地点','未知地点',null,'23.130927',st_point(null,'23.130927'));

求里车陂点距离小于 4km的点。（使用两个点的方式，传入st_point）

select name AS "地点",
st_point(longitude,latitude)::geography <-> st_point(113.3953613,23.124297) AS "到车陂的距离"
FROM store 
WHERE st_point(longitude,latitude)::geography <-> st_point(113.3953613,23.124297) < 4000
ORDER BY st_point(longitude,latitude)::geography <-> st_point(113.3953613,23.124297);

这种方式的效率算不上高，完全的表扫描。还加上类型转换。

使用 storeposition

-- 使用geometry 这种类型。
SELECT name AS "地点",
storeposition::geography <-> st_point(113.3953613,23.124297) AS "到车陂的距离"
FROM store 
WHERE storeposition::geography <-> st_point(113.3953613,23.124297) < 4000
ORDER BY storeposition::geography <-> st_point(113.3953613,23.124297);

java高效地理围栏算法代码地理围栏服务能关闭么_java高效地理围栏算法代码_08

优化查询，简化SQL

SELECT name AS "地点",
storeposition::geography <-> st_point(113.3953613,23.124297) AS "到车陂的距离"
FROM store 
ORDER BY storeposition <-> st_point(113.3953613,23.124297)
LIMIT 5;

java高效地理围栏算法代码地理围栏服务能关闭么_PostgreSQL_09

创建索引，并执行

-- 创建空间索引
CREATE INDEX  CONCURRENTLY idx_store_storeposition_gist ON store USING gist(storeposition);

-- SELECT * FROM  pg_indexes WHERE tablename='store';  
-- DROP INDEX idx_store_storeposition_gist

SELECT name AS "地点"
FROM store 
ORDER BY storeposition <-> st_point(113.3953613,23.124297)
LIMIT 5;

数据量很小，看不出，直接走seq scan

java高效地理围栏算法代码地理围栏服务能关闭么_git_10

扩展：加大数据量，进行性能测试

使用100w的数据，来测试性能。

SELECT  substr((random() * (117.62573-80.711667 ) + 80.711667 )::varchar ,0,9); -- 经度
SELECT substr((random() * (40.764399-22.492864) + 22.492864)::varchar ,0,9); -- 维度

序列表

-- 创建序列表
CREATE TABLE numserial (
	num INT 
);
-- 插入数据
INSERT INTO numserial(num) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- 生成0-9999999。
INSERT INTO numserial(num)
SELECT num FROM (
	SELECT (bw.num * 1000000 + sw.num * 100000 + w.num*10000 + q.num*1000 + b.num*100 + s.num*10 + g.num) AS num 
	FROM
		numserial g,numserial s,numserial b,numserial q,numserial w,numserial sw,numserial bw	
) tnum 
WHERE num >= 10
ORDER BY num ASC;

一时没有想起，在PostgreSQL中可以直接使用函数 generate_series()

SELECT id FROM  generate_series(1,10000000) t(id);

经纬度范围

– 经度纬度，取值范围。
[80.711667,117.62573]
[40.764399,22.492864]

插入数据

-- 插入经纬度，storeposition 为了保持与longitude,latitude 一致，采用更新操作
INSERT INTO store (
	id,name,address,longitude,latitude
)
SELECT num,  'name' || num, 'address' || num, substr((random() * (117.62573-80.711667 ) + 80.711667 )::varchar ,0,9)::NUMERIC,
substr((random() * (40.764399-22.492864) + 22.492864)::varchar ,0,9)::NUMERIC
FROM numserial 
WHERE num >= 13 and num <= 1000000;
-- 更新时存在锁，而且已经对该字段创建了索引，所以速度会很慢。可以先将索引删除插入数据后再创建。
UPDATE store SET storeposition = st_point(longitude,latitude) WHERE ID >= 13;

在实际中避免这种批量更新的情况发生，会一直占用锁。导致其他拿锁时出现等待时间过长。

[SQL]UPDATE store SET storeposition = st_point(longitude,latitude) WHERE ID >= 13;

时间: 1442.008s

受影响的行: 999988

再执行SQL,查看执行计划。

可以看出速度很快。0.051s

删除索引后，速度变慢许多

java高效地理围栏算法代码地理围栏服务能关闭么_java高效地理围栏算法代码_11

小结

使用PostGIS 在一些特定的场景带来十足的便利性。更多的功能，可以查看官网API。
可以看看德哥的一些关于gis的文章：

https://yq.aliyun.com/articles/157916/?spm=a2c4e.11154792.blogalbumarticle.47.56834b1fI8QxcC

参考

《PostgreSQL实战》

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：一球从100米高度落下python 一球从100米高度自由落下python

下一篇：spring 配置mysql驱动 spring数据库配置

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯