標(biāo)簽

PostgreSQL , PostGIS , 多邊形 , 面 , 點 , 面點斷定 , 菜鳥

配景

菜鳥末端軌跡項目中涉及的一個關(guān)鍵需求,面面斷定.

在數(shù)據(jù)庫中存儲了一些多邊形記載,約幾百萬到千萬條記載,例如一個小區(qū),在地圖上是一個多邊形.

不同的快遞公司,會有各自不同的多邊形劃分辦法(每個網(wǎng)點負(fù)責(zé)的片區(qū)(多邊形),每個快遞員負(fù)責(zé)的片區(qū)(多邊形)).

用戶在寄件時,根據(jù)用戶的地位,查找對應(yīng)快遞公司負(fù)責(zé)這個片區(qū)的網(wǎng)點、或者負(fù)責(zé)該片區(qū)的快遞員.

一、需求

1、在數(shù)據(jù)庫中存儲了一些靜態(tài)的面信息,代表小區(qū)、園區(qū)、寫字樓等等.所有的面不相交.

2、為了支持分歧的業(yè)務(wù)類型,對一個地圖,可能劃分為分歧的多邊形組成.

例如不同的快遞公司,會有各自不同的多邊形劃分辦法(網(wǎng)點負(fù)責(zé)的片區(qū)(多邊形),某個快遞員負(fù)責(zé)的片區(qū)(多邊形)).

因此在一張地圖上,有多個圖層,每個圖層的多邊形劃分辦法可能不一樣.

3、快速的根據(jù)快遞公司、客戶的位置,求包括這個點的多邊形(即得到對應(yīng)快遞公司負(fù)責(zé)這個片區(qū)的網(wǎng)點、或者負(fù)責(zé)該片區(qū)的快遞員).

二、架構(gòu)設(shè)計

用到阿里云的RDS PostgreSQL,以及PG提供的PostGIS插件.

我們必要用到PostGIS的函數(shù)有兩個

http://postgis.net/docs/manual-2.3/ST_Within.html

1、ST_within

ST_Within — Returns true if the geometry A is completely inside geometry B

boolean ST_Within(geometry A, geometry B);

Returns TRUE if geometry A is completely inside geometry B. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. It is a given that if ST_Within(A,B) is true and ST_Within(B,A) is true, then the two geometries are considered spatially equal.

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Within.

-- a circle within a circle

2、ST_Contains

ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

boolean ST_Contains(geometry geomA, geometry geomB);

Returns TRUE if geometry B is completely inside geometry A. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. ST_Contains is the inverse of ST_Within. So ST_Contains(A,B) implies ST_Within(B,A) except in the case of invalid geometries where the result is always false regardless or not defined.

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Contains.

-- A circle within a circle

三、DEMO與性能

1 PG內(nèi)置幾何類型 面點搜索 壓測

為了簡化測試,采樣PG內(nèi)置的幾何類型進(jìn)行測試,用法與PostGIS是相似的.

1、創(chuàng)立測試表

postgres=# create table po(id int, typid int, po polygon);

2、創(chuàng)立分區(qū)表或分區(qū)索引

create extension btree_gist;

3、創(chuàng)立空間排他約束,可選

如果要求單個typid內(nèi)的po不重疊,可以創(chuàng)立空間排他約束

create table tbl_po(id int, typid int, po polygon)

4、寫入1000萬多邊形測試數(shù)據(jù)

insert into po select id, random()*20, polygon('(('||x1||','||y1||'),('||x2||','||y2||'),('||x3||','||y3||'))') from (select id, 180-random()*180 x1, 180-random()*180 x2, 180-random()*180 x3, 90-random()*90 y1, 90-random()*90 y2, 90-random()*90 y3 from generate_series(1,10000000) t(id)) t;

5、測試面點斷定性能

查詢包括point(1,1)的多邊形,響應(yīng)時間0.57毫秒.

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from po where typid=1 and po @> polygon('((1,1),(1,1),(1,1))') limit 1;

6、壓測

vi test.sql

驚不驚喜、意不不測

TPS：29萬 ,平均響應(yīng)光陰：0.2毫秒

2 PostGIS空間數(shù)據(jù)庫 面點搜索 壓測

阿里云 RDS PostgreSQL,HybridDB for PostgreSQL 已經(jīng)內(nèi)置了PostGIS空間數(shù)據(jù)庫插件,使用前創(chuàng)立插件即可.

create extension postgis;

1、建表

postgres=# create table po(id int, typid int, po geometry);

2、創(chuàng)立空間索引

postgres=# create extension btree_gist;

3、寫入1000萬多邊形測試數(shù)據(jù)

postgres=# insert into po

4、測試面點斷定性能

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from po where typid=1 and st_within(ST_PointFromText('POINT(1 1)'), po) limit 1;

5、壓測

vi test.sql

驚不驚喜、意不不測

TPS：19.8萬 ,平均響應(yīng)光陰：0.32毫秒

四、技術(shù)點

1、空間排他約束

這個約束可以用于強制記錄中的多邊形不相交.例如地圖這類嚴(yán)謹(jǐn)數(shù)據(jù),絕對弗成能出現(xiàn)兩個多邊形相交的,否則就有領(lǐng)土紛爭了.

PostgreSQL便是這么嚴(yán)謹(jǐn),意不意外.

2、分區(qū)表

本例中分歧的快遞公司,對應(yīng)分歧的圖層,每個快遞公司根據(jù)網(wǎng)點、快遞員負(fù)責(zé)的片區(qū)(多邊形)劃分為多個多邊形.

使用LIST分區(qū),每個分區(qū)對應(yīng)一家快遞公司.

3、空間索引

GiST空間索引,支持KNN、包括、相交、上下左右等空間搜索.

效率極高.

4、空間分區(qū)索引

《分區(qū)索引的利用和實踐 - 阿里云RDS PostgreSQL最佳實踐》

5、面面、點斷定

面面判斷或面點判斷是本例的主要需求,用戶在寄包裹時,根據(jù)用戶地位在數(shù)據(jù)庫的一千萬多邊形中找出覆蓋這個點的多邊形.

五、云端產(chǎn)物

阿里云 RDS PostgreSQL

六、相似場景、案例

《PostgreSQL 物流軌跡系統(tǒng)數(shù)據(jù)庫需求闡發(fā)與設(shè)計 - 包裹俠實時跟蹤與召回》

七、小結(jié)

菜鳥末端軌跡項目中涉及的一個關(guān)鍵需求,面面斷定.

在數(shù)據(jù)庫中存儲了一些多邊形記載,約幾百萬到千萬條記載,例如一個小區(qū),在地圖上是一個多邊形.

不同的快遞公司,會有各自不同的多邊形劃分辦法(網(wǎng)點負(fù)責(zé)的片區(qū)(多邊形),某個快遞員負(fù)責(zé)的片區(qū)(多邊形)).

用戶在寄件時,根據(jù)用戶的地位,查找對應(yīng)快遞公司負(fù)責(zé)這個片區(qū)的網(wǎng)點、或者負(fù)責(zé)該片區(qū)的快遞員.

使用阿里云RDS PostgreSQL,用戶存放約1千萬的多邊形數(shù)據(jù),單庫實現(xiàn)了每秒29萬的處理哀求,單次哀求平均響應(yīng)時間約0.2毫秒.

驚不驚喜、意不不測.

八、參考

http://postgis.net/docs/manual-2.3/ST_Within.html

《分區(qū)索引的利用和實踐 - 阿里云RDS PostgreSQL最佳實踐》