1

I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093)

DO $$
DECLARE
  _curs   CURSOR FOR SELECT geom3857 FROM nrw;
  _table  TEXT     := 'nrw_hx_10k';
  _srid   INTEGER  := 3857;
  _height NUMERIC  := 10000;
  _width  NUMERIC  := _height * 0.866;
  _geom   GEOMETRY;
  _hx     GEOMETRY := ST_GeomFromText(
                        FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
                          (_width *  0.5), (_height * 0.25),
                          (_width *  0.5), (_height * 0.75),
                                       0 ,  _height,
                          (_width * -0.5), (_height * 0.75),
                          (_width * -0.5), (_height * 0.25)
                        ), _srid);

BEGIN
  CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));

  OPEN _curs;
  LOOP
    FETCH
    _curs INTO _geom;
    EXIT WHEN NOT FOUND;

    INSERT INTO hx_tmp
      SELECT
        ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
      FROM
        generate_series(
          (st_xmin(_geom) / _width)::INTEGER * _width - _width,
          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
          _width) x_series,
        generate_series(
          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
          _height * 1.5) y_series
      WHERE
        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);

    INSERT INTO hx_tmp
      SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
      FROM
        generate_series(
          (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
          _width) x_series,
        generate_series(
          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
          _height * 1.5) y_series
      WHERE
        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);

  END LOOP;
  CLOSE _curs;

  CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
  EXECUTE 'DROP TABLE IF EXISTS '|| _table;
  EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
  EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
  EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
  DROP TABLE IF EXISTS hx_tmp;
END $$;

When I try to use the function, I get the following error:

ERROR:  function generate_series(numeric, numeric, numeric) does not exist
LINE 5:         generate_series(
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  INSERT INTO hx_tmp
      SELECT
        ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
      FROM
        generate_series(
          (st_xmin(_geom) / _width)::INTEGER * _width - _width,
          (st_xmax(_geom) / _width)::INTEGER * _width + _width,
          _width) x_series,
        generate_series(
          (st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
          (st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
          _height * 1.5) y_series
      WHERE
        ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
CONTEXT:  PL/pgSQL function inline_code_block line 27 at SQL statement
********** Error **********

ERROR: function generate_series(numeric, numeric, numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function inline_code_block line 27 at SQL statement
  • and have you tried explicit type casts? PG function calls usually don't like type ambiguity much at runtime. – ThingumaBob Apr 11 at 7:59
  • What version of Postgres are you using, the generate_series(numeric,numeric,numeric) function was only added in Postgres 9.5. So, if you have lower, do as @ThingumaBob suggests, and cast to int or bigint. – John Powell Apr 11 at 8:34
  • So, because _width is of type numeric, a line like (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5) will end up being of type numeric. So, you need to cast the whole expression, eg, (st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5))::bigint – John Powell Apr 11 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.