1 | CREATE OR REPLACE FUNCTION grid(text, geometry, integer) |
---|
2 | RETURNS integer AS |
---|
3 | $BODY$ |
---|
4 | |
---|
5 | |
---|
6 | DECLARE |
---|
7 | bextent box3d_extent := ST_Extent($2); |
---|
8 | maxx real := ST_XMax( bextent ); |
---|
9 | maxy real := ST_YMax( bextent ); |
---|
10 | minx real := ST_XMin( bextent ); |
---|
11 | miny real := ST_YMin( bextent ); |
---|
12 | |
---|
13 | cell geometry := ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))',25832); |
---|
14 | |
---|
15 | counter integer := 0; |
---|
16 | query text; |
---|
17 | --i integer; |
---|
18 | --j integer; |
---|
19 | |
---|
20 | stepx integer := ceil((maxx-minx)/$3); |
---|
21 | stepy integer := ceil((maxy-miny)/$3); |
---|
22 | |
---|
23 | BEGIN |
---|
24 | |
---|
25 | query := 'DROP TABLE IF EXISTS '||$1||';'; |
---|
26 | EXECUTE query; |
---|
27 | query := 'CREATE TABLE '||$1||' ( gid integer );'; |
---|
28 | EXECUTE query; |
---|
29 | query := 'SELECT AddGeometryColumn (\'public\',\''||$1||'\',\'wkb_geometry\',25832,\'POLYGON\',2);'; |
---|
30 | --RAISE NOTICE 'Info: %', query; |
---|
31 | EXECUTE query; |
---|
32 | FOR 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; |
---|
46 | END LOOP; |
---|
47 | query := 'DROP SEQUENCE IF EXISTS '||$1||'_gid_seq;'; |
---|
48 | EXECUTE query; |
---|
49 | |
---|
50 | query := 'CREATE INDEX '||$1||'_gidx ON '||$1||' USING GIST ( wkb_geometry ); |
---|
51 | CREATE SEQUENCE '||$1||'_gid_seq;'; |
---|
52 | EXECUTE query; |
---|
53 | query := 'UPDATE '||$1||' SET gid = nextval(\''||$1||'_gid_seq\'); |
---|
54 | ALTER TABLE '||$1||' ALTER COLUMN gid SET DEFAULT nextval(\''||$1||'_gid_seq\');'; |
---|
55 | EXECUTE query; |
---|
56 | |
---|
57 | return counter; |
---|
58 | |
---|
59 | END |
---|
60 | $BODY$ |
---|
61 | LANGUAGE plpgsql VOLATILE |
---|
62 | COST 100; |
---|
63 | -- |
---|