drop function add_polygon(); create or replace function add_polygon() returns void as $$ declare x numeric(10,0);y numeric(10,0); x1 numeric(10,0);y1 numeric(10,0);x2 numeric(10,0);y2 numeric(10,0);x3 numeric(10,0);y3 numeric(10,0); x4 numeric(10,0);y4 numeric(10,0);x5 numeric(10,0);y5 numeric(10,0);x6 numeric(10,0);y6 numeric(10,0); x7 numeric(10,0);y7 numeric(10,0);x8 numeric(10,0);y8 numeric(10,0);x9 numeric(10,0);y9 numeric(10,0); x10 numeric(10,0);y10 numeric(10,0);x11 numeric(10,0);y11 numeric(10,0);x12 numeric(10,0);y12 numeric(10,0); count int;i int:=1; max int:=0;j int;k int;setcode int; --create or replace type x_list IS TABLE of shp_mayshuru_coordinates.x%type INDEX BY numeric; begin select count(*) into count from tmpco; LOOP --select count(*) into max from shp_mayshuru_coordinates where code=i; select count(s.code) into max from "isims-final".wlke_mesno_coordinates s,tmpco where s.code=tmpco.code and tmpco.id=i; select distinct s.code into setcode from "isims-final".wlke_mesno_coordinates s,tmpco where s.code=tmpco.code and tmpco.id=i; -- if length of the coordinates is 4 if (max=4) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if length of the coordinates is 3 if (max=3) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if length of coordinates is 5 if (max=5) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the length of the coordinates is 6 if (max=6) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the length of the coordinates is 7 if (max=7) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the length of the coordinates is 8 if (max=8) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; if (k=8) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x8:=x;y8:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the length of coordinates is 9 if (max=9) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; if (k=8) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x8:=x;y8:=y;k:=k+1; End IF; if (k=9) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x9:=x;y9:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the frequency of coordinates is 10 if (max=10) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; if (k=8) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x8:=x;y8:=y;k:=k+1; End IF; if (k=9) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x9:=x;y9:=y;k:=k+1; End IF; if (k=10) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x10:=x;y10:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the frequency of coordinates is 11 if (max=11) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; if (k=8) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x8:=x;y8:=y;k:=k+1; End IF; if (k=9) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x9:=x;y9:=y;k:=k+1; End IF; if (k=10) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x10:=x;y10:=y;k:=k+1; End IF; if (k=11) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x11:=x;y11:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; -- if the frequency of coordinates is 12 if (max=12) then j=1;k=1; drop table test; create table test(sn serial,code numeric, x numeric(10,0), y numeric(10,0)); insert into test(code,x,y) select c.code,c.x,c.y from "isims-final".wlke_mesno_coordinates c,tmpco where c.code=tmpco.code and tmpco.id=i; if (k=1) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x1:=x;y1:=y;k:=k+1; End IF; if (k=2) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x2:=x;y2:=y;k:=k+1; End IF; if (k=3) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x3:=x;y3:=y;k:=k+1; End IF; if (k=4) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x4:=x;y4:=y;k:=k+1; End IF; if (k=5) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x5:=x;y5:=y;k:=k+1; End IF; if (k=6) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x6:=x;y6:=y;k:=k+1; End IF; if (k=7) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x7:=x;y7:=y;k:=k+1; End IF; if (k=8) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x8:=x;y8:=y;k:=k+1; End IF; if (k=9) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x9:=x;y9:=y;k:=k+1; End IF; if (k=10) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x10:=x;y10:=y;k:=k+1; End IF; if (k=11) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x11:=x;y11:=y;k:=k+1; End IF; if (k=12) Then select t.x into x from test t where t.sn=k; select t.y into y from test t where t.sn=k; x12:=x;y12:=y;k:=k+1; End IF; insert into "isims-final".wlkemesno_area values( 'SRID=20137;POLYGON(('|| x1 || ' ' || y1 || ','|| x2 || ' ' || y2 || ','|| x3 || ' ' || y3 || ','|| x4 || ' ' || y4 || ','|| x1 || ' ' || y1 || '))'); update "isims-final".wlkemesno_area set code=setcode where code is null; insert into area select st_area(geom),setcode from "isims-final".wlkemesno_area where code=setcode; End IF; Exit when count=i; i:=i+1; end loop; end; $$language plpgsql; select add_polygon( );
أكثر...
أكثر...