锋利的PostGIS--如何用图形关系去关联数据

一 前言

       用SQL去实现表与表的join可以说是每个程序员必备技能，一般我们用来实现联表查询，假设a表与b表有个字段如id可以关联，查询写起来就是下面这样：

select a.name1,b.name2 from test_t a join test_t2 b on a.id=b.id;

当然在PostgreSQL里也可以跨表更新，语法写起来是这样的：

update test_t1 a set name = b.name from test_t2 b where a.id=b.id;

    对于普通的关系表而言，这种join关联字段字段类型可能是int？text?numeric?date？基本上都是典型常见数据类型，但在PostGIS空间数据库中，图形类型（geometry）才是它的核心，本文主要介绍下空间数据表如何使用图形去查询统计分析数据，如何抽象图形关系完成数据处理和分析。

二 空间关系简介

      关系表的关联是通常是通过字段与字段是否存在相等关系判定的，空间表的关联基本上是通过空间关系是否存在确定的，先简单介绍主要的空间关系如下图：

如图：图形的空间关系主要分：相交和相离两大类，而相交根据实际图形关系可以细分更多的空间关系。在空间数据处理中，ST_Disjoint（相离）关系是原则上不使用的，该函数不会走空间索引；同时，相交关系足够覆盖90%的业务场景，细分关系只有在某些特殊的强图形关系业务场景下才会使用。

三 基础案例

案例一：经典点面数据集的统计分析

已知：全国地级市的经纬度及其GDP产值，全国省级行政区图形边界，汇总统计每个省级行政区的GDP：

create table city_pt(
  gid serial primary key,
  gdp numeric,
  geom geometry(Point,4326)
);
create index city_pt_geom on city_pt using gist(geom);


create table province(
 gid serial primary key,
 name text,
 geom geometry(MultiPolygon,4326)
);


--关联统计查询
select a.name,sum(b.gdp) as gdp from province a,city_pt b where 
ST_Intersects(a.geom,b.geom);

这个案例非常简单，可以说是PostGIS业务应用经典的入门语句，通过该案例基本可以掌握如何根据业务需求设计空间数据库和业务查询sql，where是条件，select里是统计语句，也就是掌握该语句，100分就已经掌握60分，学习成本非常低，业务价值非常大！

案例二：经典的空间查询统计分析

已知一份自然保护区的面状数据，每个保护区都有自己的类型描述，这时候有个自然灾害区域形成，要求按照保护区类型统计自然灾害区域受灾面积。

--已知灾害区域图形为 zaihai_py_geom;
--自然保护区数据集
create table nature_py(
   gid serial primary key,
   nature_type text, --保护区类型
   geom geometry(Polygon,4326)
);
create index nature_py_geom on nature_py using gist(geom);
--业务查询语句
select nature_type,sum(
  ST_Area(ST_Intersection(geom,zaihai_py_geom),true)
) from nature_py where ST_Intersects(geom,zaihai_py_geom) 
group by nature_type;

ST_Intersects：判定保护区是否受灾害区域影响。

ST_Intersection：求交集，这里的业务含义就是保护区区域与灾害区域相交的区域，这个区域代表受灾害影响。

ST_Area：计算面状图形面积，这里的业务含义就是灾害区域面积计算。

该案例也是业务场景中出镜率非常高的，需要读者消化吸收并实际灵活应用。

四 扩展延申

       前文案例都是非常明确的图形数据与图形数据的直接关系，但有时候图形关系可能是间接的。

旧数据

新数据

案例：业务部门有一份旧行政区划数据，该数据有很多重要的业务字段，同时存在一份更精确更新的行政区数据，但没有业务字段。业务要求，图形使用新数据，并把旧数据的业务字段重新绑定到新数据上。

分析：

    1 由于两份数据来源不同，不能通过id和 区划名称 去匹配。

    2 新旧边界大体位置相同，但是不能简单通过图形相等和图形相交去匹配数据，相交肯定会与相邻省份有交集，达不到新旧数据一一对应的目的。

解决方案：旧数据面生成位于面内的点，判断点与新数据面是否相交，从而进行数据更新。

执行步骤：

1 给新数据添加需要更新的业务字段，应该和旧数据的业务字段数量，名称，类型都保持一致即可。

alter table new_city add clolum filed1 text;
alter table new_city add clolum filed2 text;

2 旧数据面转点

alter table old_city add column pt_geom geometry(Point,4326);
update old_city set pt_geom = ST_PointOnSurface(geom);

update new_city t1 set field1=t2.field1,field2=t2.field2 
from old_city t2 where ST_Intersects(t1.geom,t2.geom);