begin;-- 從一個(gè)點(diǎn)表數(shù)據(jù)中生成初始三角網(wǎng)CREATE OR REPLACE FUNCTION create_tin_from_table(varchar,varchar) returns booleanas $$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';		-- 創(chuàng)建邊數(shù)據(jù)表	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;	-- 創(chuàng)建三角形表	sqlstr := 'create table "'|| tin_rel ||'"('||	'_geoid serial primary key,' ||	'line1id integer,'||		--邊的編號(hào)	'line2id integer,'||	'line3id integer,'||	'point1id integer,'||	        --頂點(diǎn)編號(hào)	'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;			--為三角形表創(chuàng)建索引	sqlstr := 'create index "'|| tin_rel ||'_idx" on "'|| tin_rel ||'" using gist(_geo)';	execute sqlstr;	-- 創(chuàng)建三角網(wǎng)的最小凸多邊形表，僅僅有一條記錄	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;	--創(chuàng)建臨時(shí)表空間，存放所有的點(diǎn)，對表的所有操作都是在臨時(shí)表中進(jìn)行	sqlstr := 'create table "'|| pt_tmp ||'" as select * from "'|| pt_rel ||'"';  --從原來的電表中創(chuàng)建點(diǎn)臨時(shí)表表	execute sqlstr;		sqlstr := 'alter table "'|| pt_tmp ||'" add _geoid serial; ';   --修改表使表中的id遞增	execute sqlstr;		sqlstr := 'alter table "'|| pt_tmp ||'" add isused boolean default false';  --修改表增加已用在凸包上的點(diǎn)	execute sqlstr;		sqlstr := 'update "'|| pt_tmp ||'" set _geo=setsrid(_geo,-1)';   --更新表使表中id統(tǒng)一	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);	--創(chuàng)建最初的三角網(wǎng)	--取第一點(diǎn)	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個(gè)三角形入庫		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;	-- 將已經(jīng)使用過的點(diǎn)在臨時(shí)點(diǎn)表中做好標(biāo)記	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; 	--將臨時(shí)表刪掉	sqlstr := 'drop table "'|| pt_tmp ||'"';	execute sqlstr;	return ret;end;$$ language 'plpgsql';create or replace function get_geometry_id(geometry,varchar) returns integeras $$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 booleanas $$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;