背景情况
在工作中(数据库: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
二、创建GIS 数据库
使用客户端连接工具创建数据库(例如:Navicat)
创建数据库,并创建postgis扩展
create database gis;
create extension postgis;
查询安装
SELECT * FROM pg_available_extensions where name ~'gis';
-- 查看版本信息
SELECT * FROM postgis_full_version();
三、几何对象
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);
PostgreSQL 中的Point 只是一个包含两个Double的结构图。PostGIS 的点类型ST_Point 采用了不同的存储方式(21字节),除了两个坐标分量,还包括了一些额外的元数据:例如几何对象的实际类型,参考系的ID等。
使用 ST_AsTest
输出WKT
select ST_AsText(ST_point(1,2))
在实际使用中,通常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;
经纬度的输出几何点
select st_aslatlontext(st_point(111.321367,39.966956));
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;
案例三: 想知道某条路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);
- 优化查询,简化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;
- 创建索引,并执行
-- 创建空间索引
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
扩展:加大数据量,进行性能测试
使用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
删除索引后,速度变慢许多
小结
使用PostGIS
在一些特定的场景带来十足的便利性。更多的功能,可以查看官网API。
可以看看德哥的一些关于gis的文章:
参考
《PostgreSQL实战》