YJPEpDa1

1DO $$
2DECLARE
3  _curs   CURSOR FOR SELECT geom3857 FROM nrw;
4  _table  TEXT     := 'nrw_hx_10k';
5  _srid   INTEGER  := 3857;
6  _height NUMERIC  := 10000;
7  _width  NUMERIC  := _height * 0.866;
8  _geom   GEOMETRY;
9  _hx     GEOMETRY := ST_GeomFromText(
10                        FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
11                          (_width *  0.5), (_height * 0.25),
12                          (_width *  0.5), (_height * 0.75),
13                                       0 ,  _height,
14                          (_width * -0.5), (_height * 0.75),
15                          (_width * -0.5), (_height * 0.25)
16                        ), _srid);
17
18BEGIN
19  CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));
20
21  OPEN _curs;
22  LOOP
23    FETCH
24    _curs INTO _geom;
25    EXIT WHEN NOT FOUND;
26
27    INSERT INTO hx_tmp
28      SELECT
29        ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
30      FROM
31        generate_series(
32          (st_xmin(_geom) / _width)::INTEGER * _width - _width,
33          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
34          _width) x_series,
35        generate_series(
36          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
37          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
38          _height * 1.5) y_series
39      WHERE
40        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
41
42    INSERT INTO hx_tmp
43      SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
44      FROM
45        generate_series(
46          (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
47          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
48          _width) x_series,
49        generate_series(
50          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
51          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
52          _height * 1.5) y_series
53      WHERE
54        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
55
56  END LOOP;
57  CLOSE _curs;
58
59  CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
60  EXECUTE 'DROP TABLE IF EXISTS '|| _table;
61  EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
62  EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
63  EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
64  DROP TABLE IF EXISTS hx_tmp;
65END $$; 
66     
67ERROR:  function generate_series(numeric, numeric, numeric) does not exist
68LINE 5:         generate_series(
69                ^
70HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
71QUERY:  INSERT INTO hx_tmp
72      SELECT
73        ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
74      FROM
75        generate_series(
76          (st_xmin(_geom) / _width)::INTEGER * _width - _width,
77          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
78          _width) x_series,
79        generate_series(
80          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
81          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
82          _height * 1.5) y_series
83      WHERE
84        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
85CONTEXT:  PL/pgSQL function inline_code_block line 27 at SQL statement
86********** Error **********
87
88ERROR: function generate_series(numeric, numeric, numeric) does not exist
89SQL state: 42883
90Hint: No function matches the given name and argument types. You might need to add explicit type casts.
91Context: PL/pgSQL function inline_code_block line 27 at SQL statement