上下级行政区范围不一致问题解决

问题:

pg数据库中的省市县行政区数据库的数据有问题, 省级范围和市县合并后的范围接边不一致, 导致查下出的结果错误

解决方法:

通过合并小行政区得到大行政区
省市县的表结构如下

CREATE TABLE "public"."shengj" (
  "gw_objectcid" int4 NOT NULL DEFAULT nextval('shengj_gid_seq'::regclass),
  "code" varchar(10) COLLATE "pg_catalog"."default",
  "name" varchar(50) COLLATE "pg_catalog"."default",
  "type" varchar(50) COLLATE "pg_catalog"."default",
  "gw_shape" "public"."geometry"
)
;

CREATE TABLE "public"."xianj" (
  "gw_objectcid" int4 NOT NULL DEFAULT nextval('xianj_gid_seq'::regclass),
  "code" varchar(10) COLLATE "pg_catalog"."default",
  "name" varchar(60) COLLATE "pg_catalog"."default",
  "prov_code" varchar(10) COLLATE "pg_catalog"."default",
  "prov_name" varchar(50) COLLATE "pg_catalog"."default",
  "pref_code" varchar(10) COLLATE "pg_catalog"."default",
  "pref_name" varchar(50) COLLATE "pg_catalog"."default",
  "type" varchar(50) COLLATE "pg_catalog"."default",
  "gw_shape" "public"."geometry"
)
;
CREATE TABLE "public"."shij" (
  "gw_objectcid" int4 NOT NULL DEFAULT nextval('shij_gid_seq'::regclass),
  "prov_code" varchar(10) COLLATE "pg_catalog"."default",
  "prov_name" varchar(50) COLLATE "pg_catalog"."default",
  "code" varchar(10) COLLATE "pg_catalog"."default",
  "name" varchar(50) COLLATE "pg_catalog"."default",
  "type" varchar(50) COLLATE "pg_catalog"."default",
  "gw_shape" "public"."geometry"
)
;

县合并成市

UPDATE shij sj SET gw_shape = ( SELECT st_union ( xj.gw_shape ) FROM xianj xj WHERE xj.pref_code = sj.code )

市合并成省

UPDATE shengj sheng  SET gw_shape = ( SELECT st_union ( shi.gw_shape ) FROM shij shi WHERE shi.prov_code = sheng.code )

重复点去除

update shengj set gw_shape = st_removerepeatedpoints(gw_shape);
update shij set gw_shape = st_removerepeatedpoints(gw_shape);
update xianj set gw_shape = st_removerepeatedpoints(gw_shape);

如果查询效率慢在不影响业务精度的前提下可以做抽稀处理, 先对最小行政区抽稀, 然后再逐级合并, 这样效果会稍微好一点, 但是也会导致接边问题

附pg抽稀方法