基于PostGis实现空间点抽稀

雨点打透心脏的1/2处 2022-09-07 12:28 32阅读 0赞

# 1. 数据准备 #

## 1.1 创建测试表格 ##

# 原始点表
    CREATE TABLE "public"."pointset" (
      "geom" "public"."geometry"
    );
    
    # 数据抽稀结果表
    CREATE TABLE "public"."thinout" (
      "geom" "public"."geometry"
    );

## 1.2 创建数据 ##

INSERT INTO pointset SELECT
    ( ST_Dump ( ST_GeneratePoints ( kl.geom, 400 ) ) ).geom AS geom 
    FROM
    	( SELECT ST_SetSRID ( ST_MakeBox2D ( ST_Point ( - 180, - 90 ), ST_Point ( 0, 0 ) ), 4326 ) geom ) kl;
    	
    INSERT INTO pointset SELECT
    ( ST_Dump ( ST_GeneratePoints ( kl.geom, 400 ) ) ).geom AS geom 
    FROM
    	( SELECT ST_SetSRID ( ST_MakeBox2D ( ST_Point ( 0, 0 ), ST_Point ( 180, 90 ) ), 4326 ) geom ) kl;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70]

# 2. 数据抽稀 #

## 2.1 sql语句 ##

INSERT INTO thinout SELECT geom from 
    (
    	SELECT 
    		WIDTH_BUCKET ( st_x ( geom ), -180, 180, 20 ) grid_x,
    		WIDTH_BUCKET ( st_y ( geom ), -90, 90, 20 ) grid_y,
    		st_centroid ( st_collect ( geom ) ) geom 
    	FROM pointset 
    	GROUP BY
    		grid_x,
    		grid_y 
    	) as t1;

## 2.2 抽稀效果 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 1]  
另外，我们也可以通过执行sql也发现抽稀效果很明显：

select 'pointset' as tablename,count(*) from pointset
    union
    select 'thinout' as tablename,count(*) from thinout;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 2]

## 2.3 Sql简析 ##

### 2.3.1 WIDTH\_BUCKET 函数 ###

`WIDTH_BUCKET`函数不是postgis特有的函数，它的主要作用是给你一个数值在哪个特定范围区间。比如下图，将分数0-100分分成5等分，求45分在第几段范围。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 3]  
根据上面的问题，可以转换为如下sql语句：

select WIDTH_BUCKET (45,0,100,5)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 4]

### 2.3.2 sql进一步简化 ###

SELECT 
    		WIDTH_BUCKET ( st_x ( geom ), -180, 180, 20 ) grid_x,
    		WIDTH_BUCKET ( st_y ( geom ), -90, 90, 20 ) grid_y,
    		geom
    FROM pointset

相当于给-180~180分成20个段，-90~90分成20个段，即20×20的网格，然后计算每个点对应网格的坐标。这样每个网格坐标(grid\_x,grid\_y)包含多个点。

### 2.3.3 聚合求中心点 ###

结合`group by`,并通过聚合函数`st_collect`将同一网格的点变为多点对象，最后通过`st_centroid`求多点对象的中心点坐标。所以，经过抽稀的点并不会与原始点重合。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70]: /images/20220829/02e80b986a3f4dde9a06bec6fcb71001.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 1]: /images/20220829/2463fbff2f484108b46bc15218496603.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 2]: /images/20220829/a192353747464e2a997fff66b14c9ef6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 3]: /images/20220829/c819942bd46545338ac73d3e20f5aaaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29ZaW5IZVpoaUd1YW5n_size_16_color_FFFFFF_t_70 4]: /images/20220829/6df60d10440d45d39460cbafea71ca95.png