source: trunk/data/konvert/postnas_0.6/atkis_rp_rendering/processing/functions/function_grid.sql @ 114

Revision 114, 1.7 KB checked in by tobias.dick, 10 years ago (diff)

Initiale Dateien um per PostNAS importierte ATKIS Daten für ein Online Rendering aufzubereiten. Die Scripte erzeugen pro darzustellender Objektart bis zu 6 Generalisierungsstufen. Die Parameter können in den Shellscripten angepasst werden. Die Visualisierung kann z.B. über den Mapserver erfolgen. Der Bildaufbau sollte in jeder Stufe unter einer Sekunde benötigen. Die Laufzeit der Scripte beträgt auf einem single core 2.0GHz Prozessor ca 2.5 Stunden.

Line 
1CREATE OR REPLACE FUNCTION grid(text, geometry, integer)
2  RETURNS integer AS
3$BODY$
4
5
6DECLARE
7bextent box3d_extent := ST_Extent($2);
8maxx real := ST_XMax( bextent );
9maxy real := ST_YMax( bextent );
10minx real := ST_XMin( bextent );
11miny real := ST_YMin( bextent );
12
13cell geometry := ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))',25832);
14
15counter integer := 0;
16query text;
17--i integer;
18--j integer;
19
20stepx integer := ceil((maxx-minx)/$3);
21stepy integer := ceil((maxy-miny)/$3);
22
23BEGIN
24
25query := 'DROP TABLE IF EXISTS '||$1||';';
26EXECUTE query;
27query := 'CREATE TABLE '||$1||' (  gid integer );';
28EXECUTE query;
29query := 'SELECT AddGeometryColumn (\'public\',\''||$1||'\',\'wkb_geometry\',25832,\'POLYGON\',2);';
30--RAISE NOTICE 'Info: %', query;
31EXECUTE query;
32FOR i IN  0..(stepx-1) LOOP
33        FOR j IN  0..(stepy-1) LOOP
34                 
35                query := 'INSERT INTO '|| $1 ||' (wkb_geometry) VALUES (st_geometryfromtext(\'POLYGON(('
36                        || minx+i*$3 || ' ' || miny+j*$3 || ','
37                        || minx+i*$3 || ' ' || miny+(j+1)*$3 || ','
38                        || minx+(i+1)*$3 || ' ' || miny+(j+1)*$3 || ','
39                        || minx+(i+1)*$3 || ' ' || miny+j*$3 || ','
40                        || minx+(i+1)*$3 || ' ' || miny+j*$3 || ','
41                        || minx+i*$3 || ' ' || miny+j*$3 ||
42                      '))\', 25832));';
43                EXECUTE query;
44        counter := counter + 1;
45        END LOOP;
46END LOOP;
47query := 'DROP SEQUENCE IF EXISTS '||$1||'_gid_seq;';
48EXECUTE query;
49
50query := 'CREATE INDEX '||$1||'_gidx ON '||$1||' USING GIST ( wkb_geometry );
51          CREATE SEQUENCE '||$1||'_gid_seq;';
52EXECUTE query;
53query := 'UPDATE '||$1||' SET gid = nextval(\''||$1||'_gid_seq\');
54          ALTER TABLE '||$1||' ALTER COLUMN gid SET DEFAULT nextval(\''||$1||'_gid_seq\');';
55EXECUTE query;
56
57return counter;
58
59END
60$BODY$
61  LANGUAGE plpgsql VOLATILE
62  COST 100;
63--
Note: See TracBrowser for help on using the repository browser.