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
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
