Geek asks and answers

Can PostGIS be used to create a mesh map of a country?

I am trying to divide a region map into square squares of 1 km x 1 km and, ultimately, count and extract how many points (given their latitude and longitude values) fall into each square grid of this map. Can this operation be performed in PostGIS, if so, how can I do this?

UPDATE: Mike Toews contains a detailed answer here:

https://gis.stackexchange.com/questions/16374/how-to-create-a-regular-polygon-grid-in-postgis

+4
source share
2 answers

As mentioned in my comment create a grid . To make a 1 km wide grid for an entire country, this can be challenging because the land is not flat and cannot be divided into ideal 1 km wide grids.

1 , . WGS84 (EPSG: 4326) , lat/long. , " ", (LAEA). , ETRS-LAEA (EPSG: 3035), . , Transverse Mercator 2000. .

PostGIS, ST_Transform(geom, 3035) (, ETRS-LAEA).

+1

PostGIS .

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     TEXT    := 'POLYGON((' || 0 || ' ' || 0 || ',' || (_width * 0.5) || ' ' || (_height * 0.25) || ',' ||
                 (_width * 0.5) || ' '
                 || (_height * 0.75) || ',' || 0 || ' ' || _height || ',' || (-1 * (_width * 0.5)) || ' ' ||
                 (_height * 0.75) || ',' ||
                 (-1 * (_width * 0.5)) || ' ' || (_height * 0.25) || ',' || 0 || ' ' || 0 || '))';
  _hx_g   GEOMETRY := ST_SetSRID(_hx::GEOMETRY, _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_g, 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_g, x_series, y_series)::GEOMETRY(POLYGON), _geom);

    INSERT INTO hx_tmp
      SELECT ST_Translate(_hx_g, 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_g, 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 $$;

: _curs: . _table: . _srid: ( ) . _height: .

declare, . x y . , . . , , .

.

enter image description here

0

More articles:


All Articles