标簽
PostgreSQL , openstreetmap , 空間位置 , 點 , 線 , 面 , 行政位址 , 轉換 , osm2pgsql , poi , 門牌 , 商圈 , 行政區
https://github.com/digoal/blog/blob/master/201807/20180725_01.md#%E8%83%8C%E6%99%AF 背景
空間位置(geometry 經緯、點、線、面...)、行政位址(門牌、商圈、行政區...) 互相轉換需求，如果你有這方面的素材庫，在PostgreSQL裡面轉換，性能是非常高效的。
例如輸入任意一個空間對象，掃描出附近的空間對象，或者包含它的對象，或者它包含的對象。（構圖）
輸入任意一個空間對象，搜尋離他最近的空間對象。
輸入任意一個空間對象，以及其他的非空間過濾條件(使用btree_gist插件)，搜尋出附近的空間對象，或者包含它的對象，或者它包含的對象。或離他最近的空間對象。
https://github.com/digoal/blog/blob/master/201807/20180725_01.md#demo 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的流複制，可以很容易把請求吞吐做上去，哪怕是對外提供高并發的轉換類接口服務也是很輕松的。
https://github.com/digoal/blog/blob/master/201807/20180725_01.md#%E5%A6%82%E4%BD%95%E5%BE%97%E5%88%B0-%E7%A9%BA%E9%97%B4%E4%BD%8D%E7%BD%AEgeometry-%E7%BB%8F%E7%BA%AC%E7%82%B9%E7%BA%BF%E9%9D%A2%E8%A1%8C%E6%94%BF%E5%9C%B0%E5%9D%80%E9%97%A8%E7%89%8C%E5%95%86%E5%9C%88%E8%A1%8C%E6%94%BF%E5%8C%BA-%E5%AF%B9%E5%BA%94%E5%85%B3%E7%B3%BB 如何得到 空間位置(geometry 經緯、點、線、面...)、行政位址(門牌、商圈、行政區...) 對應關系
一種方法是通過openstreetmap得到，openstreetmap是開放的，自由的，社會人都可以維護、擷取的一個開源空間素材庫。
1、導出映射關系
https://www.openstreetmap.org/
2、将空間位置(geometry 經緯、點、線、面...)、行政位址(門牌、商圈、行政區...) 對應關系導入到PostgreSQL
https://github.com/openstreetmap/osm2pgsql/releasessudo 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
public | planet_osm_line | table | postgres
public | planet_osm_point | table | postgres
public | planet_osm_polygon | table | postgres
public | planet_osm_roads | table | postgres
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)
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)