PostgreSQL 空间位置(geometry 经纬、点、线、面...)、行政地址(门牌、商圈、行政区...) 相互转换方法

digoal 2018-07-25

1156

作者

digoal

日期

2018-07-25

背景

空间位置(geometry 经纬、点、线、面...)、行政地址(门牌、商圈、行政区...) 相互转换需求，如果你有这方面的素材库，在PostgreSQL里面转换，性能是非常高效的。

例如输入任意一个空间对象，扫描出附近的空间对象，或者包含它的对象，或者它包含的对象。（构图）

输入任意一个空间对象，搜索离他最近的空间对象。

输入任意一个空间对象，以及其他的非空间过滤条件(使用btree_gist插件)，搜索出附近的空间对象，或者包含它的对象，或者它包含的对象。或离他最近的空间对象。

DEMO

1、建表，存储空间、实体映射信息。

create table tbl_loc ( id int8 primary key, level int2, -- 空间对象的级别（国、省、市、区、县、街道、建筑、街道） pos geometry, -- 空间对象的位置(多边形、线段、点) loc_name text -- 空间对象的描述 );

2、创建空间+级别复合索引

```
create extension btree_gist;

create index idx_tbl_loc_1 on tbl_loc using gist(level, pos);
```

3、创建UDF接口函数

create or replace function get_loc_name( int2, -- 级别（国、省、市、区、县、街道、建筑、街道） geometry -- 输入一个空间对象（点、线段、多边形），找在某个级别内，离他最近的空间对象，并找出它的描述 ) returns tbl_loc as $$ select tbl_loc from tbl_loc where level=$1 order by pos <-> $2 limit 1; $$ language sql strict;

4、查看UDF接口函数执行计划

```
load 'auto_explain';
set auto_explain.log_analyze =on;
set auto_explain.log_buffers =on;
set auto_explain.log_min_duration =0;
set auto_explain.log_nested_statements =on;
set auto_explain.log_timing =on;
set auto_explain.log_verbose =on;
set client_min_messages =log;

postgres=# select * from get_loc_name(1::int2,st_setsrid(st_makepoint(110+random()10,30+random()10),4326));
LOG: duration: 0.171 ms plan:
Query Text:
select tbl_loc from tbl_loc where level=$1 order by pos <-> $2 limit 1;

Limit (cost=0.42..0.96 rows=1 width=107) (actual time=0.168..0.168 rows=1 loops=1)
Output: tbl_loc., ((pos <-> $2))
Buffers: shared hit=6
-> Index Scan using idx_tbl_loc_1 on public.tbl_loc (cost=0.42..2455327.73 rows=4586107 width=107) (actual time=0.167..0.167 rows=1 loops=1)
Output: tbl_loc., (pos <-> $2)
Index Cond: (tbl_loc.level = $1)
Order By: (tbl_loc.pos <-> $2)
Buffers: shared hit=6
LOG: duration: 0.466 ms plan:
Query Text: select * from get_loc_name(1::int2,st_setsrid(st_makepoint(110+random()10,30+random()10),4326));
Function Scan on public.get_loc_name (cost=0.27..0.28 rows=1 width=74) (actual time=0.451..0.451 rows=1 loops=1)
Output: id, level, pos, loc_name
Function Call: get_loc_name('1'::smallint, st_setsrid(st_makepoint(('110'::double precision + (random() * '10'::double precision)), ('30'::double precision + (random() * '10'::double precision))), 4326))
Buffers: shared hit=6
id | level | pos | loc_name
-----------+-------+----------------------------------------------------+----------------------------------
333848315 | 1 | 0101000020E610000000001C7BA1735C400000F03DF1984040 | 8cbcf713c4210ca4bbc09cd4c039c230
(1 row)
```

5、空间位置(geometry 经纬、点、线、面...)、行政地址(门牌、商圈、行政区...) 相互转换压测

首先写入一批随机空间对象数据（以点为例，写入约1亿个点 122536913）

```
vi test.sql

\set id random(1,2000000000)
\set level random(1,10)
insert into tbl_loc values (:id, :level, st_setsrid(st_makepoint(110+random()10, 30+random()10), 4326), md5(random()::text)) on conflict(id) do nothing;

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 600
```

压测

```
vi test.sql

select * from get_loc_name(1::int2,st_setsrid(st_makepoint(110+random()10, 30+random()10),4326));

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120
```

性能，TPS： 10.3万。

transaction type: ./test.sql scaling factor: 1 query mode: prepared number of clients: 56 number of threads: 56 duration: 120 s number of transactions actually processed: 12377402 latency average = 0.543 ms latency stddev = 0.171 ms tps = 103143.196980 (including connections establishing) tps = 103153.166730 (excluding connections establishing) script statistics: - statement latencies in milliseconds: 0.543 select * from get_loc_name(1::int2,st_setsrid(st_makepoint(110+random()*10, 30+random()*10),4326));

使用PG的流复制，可以很容易把请求吞吐做上去，哪怕是对外提供高并发的转换类接口服务也是很轻松的。

如何得到空间位置(geometry 经纬、点、线、面...)、行政地址(门牌、商圈、行政区...) 对应关系

一种方法是通过openstreetmap得到，openstreetmap是开放的，自由的，社会人都可以维护、获取的一个开源空间素材库。

1、导出映射关系

https://www.openstreetmap.org/

https://www.openstreetmap.org/export#map=15/30.2601/120.1653

2、将空间位置(geometry 经纬、点、线、面...)、行政地址(门牌、商圈、行政区...) 对应关系导入到PostgreSQL

https://github.com/openstreetmap/osm2pgsql/releases

```
sudo yum install -y cmake3 make gcc-c++ boost-devel expat-devel zlib-devel \
bzip2-devel postgresql10-devel proj-devel proj-epsg lua-devel

wget wget https://github.com/openstreetmap/osm2pgsql/archive/0.96.0.tar.gz
tar -zxvf 0.96.0.tar.gz
cd osm2pgsql-0.96.0/

alias cmake=cmake3

mkdir build && cd build
cmake .. -G "Unix Makefiles" -DCMAKE_BUILD_TYPE=Debug -DBUILD_TESTS=ON
make
make install

Install the project...
-- Install configuration: "Debug"
-- Installing: /usr/local/bin/osm2pgsql
-- Set runtime path of "/usr/local/bin/osm2pgsql" to ""
-- Installing: /usr/local/share/man/man1/osm2pgsql.1
-- Installing: /usr/local/share/osm2pgsql/default.style
-- Installing: /usr/local/share/osm2pgsql/empty.style
```

3、导入到数据

osm2pgsql -d postgres -E 4326 ./map.osm

4、查询导入的一些对象

postgres=# select name,way,st_astext(way) from planet_osm_point limit 5; name | way | st_astext --------+----------------------------------------------------+------------------------------- 湘湖 | 0101000020E6100000924149DCBE0E5E40F67CCD72D92A3E40 | POINT(120.2303992 30.1673805) 江虹路 | 0101000020E6100000741200D26D0C5E4030B6B52A2E2F3E40 | POINT(120.1942029 30.1842982) 江晖路 | 0101000020E6100000C0C469D2370D5E409862B3C81B2F3E40 | POINT(120.2065321 30.1840177) 西兴 | 0101000020E6100000EB1F9FEBD60D5E40433866D993303E40 | POINT(120.2162427 30.189756) 滨康路 | 0101000020E6100000E1F725D1810E5E400E034CCFAA2F3E40 | POINT(120.2266734 30.1862001) (5 rows)