[postgis-users] Building Heirarchical Topology

[Regina Obe]

Your topo should be a collection of topo elements, not the collection of
primitives that make up the topo.

Try:

WITH elms AS (
  SELECT left(geoid20,14) AS blkid, 
	ARRAY[(topo).id,(topo).layer_id]  AS elm
  FROM block20_topo
  WHERE geoid20 LIKE '53001950100101%'
)
INSERT INTO blockgroup20_topo (bgid20, topo) SELECT blkid AS bgid20,
  CreateTopoGeom(
    'census',
    3, -- polygon
    2, -- blockgroup20_topo layer_id
    TopoElementArray_Agg(elm)
    ) AS topo
FROM elms
GROUP BY 1

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
Behalf
> Of Paul Ramsey
> Sent: Wednesday, February 8, 2023 2:24 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Building Heirarchical Topology
> 
> Hello users, a question! But first all the setup.
> 
> Starting from a base set of data, census blocks, which come with a nice
> heirarchical key, so it's easy to create contiguous collections just by
struncating
> the key and grouping.
> 
> Here's an example key (the column is called geoid20): 530019501001
> 
> OK, so let's start just building a topology and associated topogeoms for
the
> blocks. Original simple features geometry table is 'block20', new topogeom
> table is 'block20_topo'.
> 
> SELECT CreateTopology('census', 6599);
> CREATE TABLE block20_topo (geoid20 text); SELECT
> AddTopoGeometryColumn(
>      'census',
>      'public',
>      'block20_topo',
>      'topo',
>      'POLYGON'
>  );
> 
> All good! Now we load the simple features data into the topology and get
> topogeoms at the same time (magic).
> 
> -- Load 463 blocks in one tract, ~15seconds INSERT INTO block20_topo
> (geoid20, topo)  SELECT
>      geoid20, toTopoGeom(geom, 'census', 1)
>   FROM block20
>   WHERE geoid20 LIKE '53001950100%';
> 
> Still, good, keeping things small, just one tract of 463 blocks.
> 
> OK, now hierarchy. Blocks can be formed up into groups. PostGIS topology
> supports heirarchy by letting one build up "parent" topogeoms where each
> parent item is made up of "child" topogeoms. In this case the block groups
will
> be made up of blocks.
> 
> CREATE TABLE blockgroup20_topo (bgid20 text); SELECT
> AddTopoGeometryColumn(
>      'census',
>      'public',
>      'blockgroup20_topo',
>      'topo',
>      'POLYGON',
>      1 -- layer_id of the blocks
>  );
> 
> So far so good: make table, add topogeom for the parent groups, and
specify in
> the function the layer_id of the children.
> 
> Now, how to build and insert a parent topogeom into the blockgroup20_topo
> table?
> 
> WITH elms AS (
>   SELECT left(geoid20,14) AS blkid,
>     GetTopoGeomElements(topo) AS elm
>   FROM block20_topo
>   WHERE geoid20 LIKE '53001950100101%'
> )
> INSERT INTO blockgroup20_topo (bgid20, topo) SELECT blkid AS bgid20,
>   CreateTopoGeom(
>     'census',
>     3, -- polygon
>     2, -- blockgroup20_topo layer_id
>     TopoElementArray_Agg(elm)
>     ) AS topo
> FROM elms
> GROUP BY 1
> 
> This would seem the most likely way, getting all the elements from the
> relevant children using GetTopoGeomElements(), group them into one array
> with TopoElementArray_Agg() and finally create the topogeom with
> CreateTopoGeom(). But it fails with an error.
> 
> ERROR:  TopoGeom element layer do not match TopoGeom child layer
> 
> Just changing the layer_id in the CreateTopoGeom() to 1 doesn't fix it,
because
> though it successfully creates a topogeom, the restrictions in the topo
column
> blockgroup20_topo correctly stop it from inserting a topogeom that
references
> layer_id 1 into a column that is built for layer_id 2.
> 
> Thoughst?
> 
> P
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users