楼栋与学区数据处理存储过程

CREATE OR REPLACE FUNCTION buildingareaprocess()
RETURNS integer AS 
$BODY$

DECLARE 
r RECORD;
num int4 := 0;
sql varchar;
create_sql varchar;

BEGIN
	--给区设置坐标系
    select UpdateGeometrySRID('public', 'qu_area', 'the_geom', 4326) ;
	--给楼栋添加小学、中学、行政区字段schema和数据库后面可以修改
	ALTER TABLE
    geo_data.public.buildingarea ADD COLUMN xiaoxue VARCHAR(250);
	ALTER TABLE
    geo_data.public.buildingarea ADD COLUMN zhongxue VARCHAR(250);
	ALTER TABLE
    geo_data.public.buildingarea ADD COLUMN qu VARCHAR(40);
	
	--添加索引
	--IF to_regclass('geo_data.public.index_fid') IS NULL THEN
		--CREATE INDEX index_fid ON geo_data.public.buildingarea (fid);
	--END IF;
	CREATE INDEX IF NOT EXISTS index_buildingfid ON geo_data.public.buildingarea (fid);
	
	--如果有表就删除
	drop table if exists primary_tmp;
	drop table if exists middle_tmp;
	drop table if exists district_tmp;

	
	create table primary_tmp as
	select b.fid,array_to_string(group_concat(xx."NAME"),',') xiaoxue from buildingarea b,xx_area xx  where ST_Contains(xx.the_geom,b.the_geom) group by b.fid ;
	
	create table middle_tmp as
	select b.fid,array_to_string(group_concat(zx."NAME"),',') zhongxue from buildingarea b,cz_area zx where ST_Contains(zx.the_geom,b.the_geom) group by b.fid ;

	create table district_tmp as
	select b.fid,q."name" qu from buildingarea b,qu_area q where ST_Contains(q.the_geom,b.the_geom) ;

	--给临时表也建上索引
	CREATE INDEX IF NOT EXISTS index_primary_tmp ON primary_tmp (fid);
	CREATE INDEX IF NOT EXISTS index_middle_tmp ON middle_tmp (fid);
	CREATE INDEX IF NOT EXISTS index_district_tmp ON district_tmp (fid);

	--赋值
	update buildingarea b set xiaoxue = (select t.xiaoxue from primary_tmp t where b.fid=t.fid);
	update buildingarea b set zhongxue = (select t.zhongxue from middle_tmp t where b.fid=t.fid);
	update buildingarea b set qu = (select t.qu from district_tmp t where b.fid=t.fid);

	--给区加索引
	CREATE INDEX IF NOT EXISTS index_buildingqu ON geo_data.public.buildingarea (qu);

	--最后将buildingarea的数据按区来划分一遍
	sql := 'select distinct "name" qu from qu_area;';
	FOR r IN EXECUTE sql LOOP
		create_sql := 'create table geo_data.public.qu_'||num||' as select * from buildingarea where qu = '''||r.qu||'''';
		EXECUTE create_sql;
		num := num + 1;
	END LOOP;
	return num;

END;
$BODY$
LANGUAGE plpgsql;



备注:
postgresql里面没有聚合函数,这个要在运行前加上

CREATE AGGREGATE group_concat(anyelement) ( 
sfunc = array_append, -- 每行的操作函数,将本行append到数组里 
stype = anyarray, -- 聚集后返回数组类型 
initcond = '{}' -- 初始化空数组 
);

发布了144 篇原创文章 · 获赞 28 · 访问量 10万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览