[postgis-users] Building Heirarchical Topology

[Paul Ramsey]

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