不规则三角网中初始三角网的建立.sql

在开源数据库Postgre中使用GSQL语言实现不规则三角网中的初始三角网的建立

begin;

-- 从一个点表数据中生成初始三角网
CREATE OR REPLACE FUNCTION create_tin_from_table(varchar,varchar) returns boolean
as $$
declare
	ret boolean;
	pt_rel alias for $1;
	tin_pre alias for $2;
	tin_rel varchar;

	pt_tmp varchar;
	tin_bound varchar;
	myrec record;
	sqlstr text;	
	ptnum integer;
	i integer;
	minbound geometry;
	minbound_ls geometry;
	edge_rel varchar;
	triangle geometry;	

	pt_1 geometry;
	pt_2 geometry;
	pt_3 geometry;
	pt_1_id integer;
	pt_2_id integer;
	pt_3_id integer;
	
	line_1 geometry;
	line_2 geometry;
	line_3 geometry;
	line_1_id integer;
	line_2_id integer;
	line_3_id integer;

begin
	--变量初始化
	ret := true;
	pt_tmp := tin_pre || '_tmp';
	edge_rel := tin_pre || '_edge';
	tin_rel := tin_pre || '_tin';
	
	-- 创建边数据表
	sqlstr := 'create table "'||  edge_rel ||'"('||
		'_geoid serial primary key , '||
		'startid integer,'||
		'endid integer,'||
		'isbound boolean default false'||
		');';
		execute sqlstr;
		
		sqlstr := 'select AddGeometryColumn('|| quote_literal('') ||','|| quote_literal(edge_rel) ||','|| quote_literal('_geo') ||',-1,'|| quote_literal('LINESTRING') ||',2)';
		execute sqlstr;

	-- 创建三角形表
	sqlstr := 'create table "'|| tin_rel ||'"('||
	'_geoid serial primary key,' ||
	'line1id integer,'||		--边的编号
	'line2id integer,'||
	'line3id integer,'||
	'point1id integer,'||	        --顶点编号
	'point2id integer,'||
	'point3id integer'||
	');';
	execute sqlstr;
	
	sqlstr := 'select AddGeometryColumn('|| quote_literal('') ||','|| quote_literal(tin_rel) ||','|| quote_literal('_geo') ||',-1,'|| quote_literal('POLYGON') ||',2)';
	execute sqlstr;	
	
	--为三角形表创建索引
	sqlstr := 'create index "'|| tin_rel ||'_idx" on "'|| tin_rel ||'" using gist(_geo)';
	execute sqlstr;

	-- 创建三角网的最小凸多边形表，仅仅有一条记录
	tin_bound := tin_pre || '_bound';
	sqlstr := 'create table "' || tin_bound || '"('||
		'_geoid serial primary key'||
		');';
	execute sqlstr;

	sqlstr := 'SELECT AddGeometryColumn('|| quote_literal('') ||','|| quote_literal(tin_bound) ||','|| quote_literal('_geo') ||',-1,'|| quote_literal('POLYGON') ||',2)';
	execute sqlstr;

	--创建临时表空间，存放所有的点，对表的所有操作都是在临时表中进行
	sqlstr := 'create table "'|| pt_tmp ||'" as select * from "'|| pt_rel ||'"';  --从原来的电表中创建点临时表表
	execute sqlstr;
	
	sqlstr := 'alter table "'|| pt_tmp ||'" add _geoid serial; ';   --修改表使表中的id递增
	execute sqlstr;
	
	sqlstr := 'alter table "'|| pt_tmp ||'" add isused boolean default false';  --修改表增加已用在凸包上的点
	execute sqlstr;
	
	sqlstr := 'update "'|| pt_tmp ||'" set _geo=setsrid(_geo,-1)';   --更新表使表中id统一
	execute sqlstr;

	-- 求出最小凸包
	sqlstr := 'insert into "'|| tin_bound ||'"(_geo) select setsrid(st_convexhull(st_collect(_geo)),-1) from "'|| pt_tmp ||'"';
	execute sqlstr;
	
	sqlstr := 'select _geo from "'|| tin_bound ||'" limit 1';
	for myrec in execute sqlstr loop
		minbound := (myrec._geo);
		minbound_ls := st_exteriorring(minbound);
	end loop;
	ptnum := npoints(minbound_ls);

	--创建最初的三角网
	--取第一点
	i := 1;
	pt_1 := st_pointn(minbound_ls,i);
	pt_1_id := get_geometry_id(pt_1,pt_tmp);
	i := i + 1;

	pt_2 := st_pointn(minbound_ls,i);
	pt_2_id := get_geometry_id(pt_2,pt_tmp);
	i := i + 1;
	
	--第一条边入库
	line_1 := makeline(pt_1,pt_2);
	sqlstr := 'insert into "'|| edge_rel ||'"(_geo,startid,endid,isbound) values('|| quote_literal(astext(line_1)) ||'::geometry,'|| pt_1_id ||','|| pt_2_id ||',true)';
	execute sqlstr;
	line_1_id := currval('"' || edge_rel || '__geoid_seq"');
	
	while (i < ptnum) loop
		pt_3 := st_pointn(minbound_ls,i);
		pt_3_id := get_geometry_id(pt_3,pt_tmp);
			
		-- 三角形的第二条边
		line_2 := makeline(pt_2,pt_3);
		sqlstr := 'insert into "'|| edge_rel ||'"(_geo,startid,endid,isbound) values('|| quote_literal(astext(line_2)) ||'::geometry,'|| pt_2_id ||','|| pt_3_id ||',true)';
		execute sqlstr;
		line_2_id := currval('"'|| edge_rel ||'__geoid_seq"');
		
		-- 三角形的第三条边
		line_3 := makeline(pt_3,pt_1);
		sqlstr := 'insert into "' || edge_rel || '"(_geo,startid,endid,isbound) values('|| quote_literal(astext(line_3)) ||'::geometry,'|| pt_3_id ||','|| pt_1_id ||',false)';
		execute sqlstr;
		line_3_id := currval('"' || edge_rel || '__geoid_seq"');

		-- 第i-2个三角形入库
		line_1 := st_addpoint(line_1,pt_3);
		line_1 := st_addpoint(line_1,pt_1);
		triangle := makepolygon(line_1);
		
		sqlstr := 'insert into "'|| tin_rel ||'"(_geo,line1id,line2id,line3id,point1id,point2id,point3id) values('|| quote_literal(astext(triangle)) ||'::geometry,' 
			|| line_1_id ||','|| line_2_id ||','|| line_3_id ||','||  pt_1_id ||','|| pt_2_id ||','|| pt_3_id ||')'; 
		execute sqlstr;

		pt_2_id := pt_3_id;
		pt_2 := pt_3;
		line_1 := st_reverse(line_3);
		line_1_id := line_3_id;		

		i := i + 1;
	end loop;
	sqlstr := 'update "' || edge_rel || '" set isbound=true where _geoid=' || line_3_id;
	execute sqlstr;

	-- 将已经使用过的点在临时点表中做好标记
	sqlstr := 'update "' || pt_tmp || '" set isused=true where _geoid in ( select startid from "' || edge_rel || '") or _geoid in (select endid from "' || edge_rel || '");'; 
	execute sqlstr; 

	--将临时表删掉
	sqlstr := 'drop table "'|| pt_tmp ||'"';
	execute sqlstr;

	return ret;
end;
$$ language 'plpgsql';

create or replace function get_geometry_id(geometry,varchar) returns integer
as $$
declare
	id integer;
	sqlstr text;
	myrec record;
begin
	sqlstr := 'select _geoid from "'|| $2 ||'" where _geo = '|| quote_literal(astext($1)) || '::geometry';
	for myrec in execute sqlstr loop
		id := myrec._geoid;
	end loop;
	return id;
	
end;
$$ language 'plpgsql';

create or replace function drop_tin(varchar) returns boolean
as $$
declare
	tin_pre alias for $1;
	edge_rel varchar;
	tin_rel varchar;
	tin_bound varchar;
	sqlstr text;
begin
	edge_rel := tin_pre || '_edge';
	tin_rel := tin_pre || '_tin';
	tin_bound := tin_pre || '_bound';
       --删除空间列
	sqlstr := 'select dropgeometrycolumn ('|| quote_literal(edge_rel) ||','|| quote_literal('_geo') ||')'; 
	execute sqlstr;
	sqlstr := 'drop table "'|| edge_rel ||'"';
	execute sqlstr;

	sqlstr := 'select dropgeometrycolumn ('|| quote_literal(tin_rel) ||','|| quote_literal('_geo') ||')';
	execute sqlstr;
	sqlstr := 'drop table "'|| tin_rel ||'"';
	execute sqlstr;

	sqlstr := 'select dropgeometrycolumn ('|| quote_literal(tin_bound) ||','|| quote_literal('_geo') ||')';
	execute sqlstr;
	sqlstr := 'drop table "'|| tin_bound ||'"';
	execute sqlstr;

	
	return true;
end;
$$ language 'plpgsql';


end;