[114] | 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 | -- |
---|