A year ago I had modified code from here to make a set of about thirty million hex cells across the continental United States, using the projection from the National Map, EPSG:2163.
Today, topology wizard strk was building a set of hex cells to test conversion to topology in PostGIS 2.0. The question arose, would the hex cells I had built with another method convert cleanly to a topology, and without specifying a tolerance distance?
So I tried converting a subset of the US cells into a topology.
First step, pick a few thousand contiguous hex cells:
-- create one Alameda County multipolygon
DROP TABLE if exists alameda_tracts CASCADE;
CREATE TABLE alameda_tracts as
SELECT
1 as ogc_fid,
st_union(wkb_geometry) as wkb_geometry
FROM
census_2010_tracts
WHERE
census_2010_tracts.countyfp10 = '001';
ALTER TABLE alameda_tracts add PRIMARY KEY ( ogc_fid );
--
-- transform and compare county to hex cells
DROP TABLE if exists some_hex_cells CASCADE;
CREATE TABLE some_hex_cells as
SELECT
distinct on (hex_grid_cells.id, hex_grid_cells.wkb_geometry)
hex_grid_cells.id, hex_grid_cells.wkb_geometry
FROM
public.alameda_tracts,
public.hex_grid_cells
WHERE
ST_Intersects(
hex_grid_cells.wkb_geometry,
ST_Transform(alameda_tracts.wkb_geometry,2163) );
--
alter table some_hex_cells add primary key (id);
create index shc_geom_idx on some_hex_cells USING GIST (wkb_geometry);
select count(*) from some_hex_cells;
count
-------
5272
(1 row)
In an EXPLAIN ANALYZE of the collection of the hex cells query above, the postgres scheduler showed a linear scan of the table being transformed, which is one record long, and an index scan on the 30 million hex cells – perfect! Next step, create a PostGIS 2.0 topology in EPSG:2163 :
SELECT CreateTopology('hexagon_topo', 2163 );
-- use DropTopology('hexagon_topo') to start over..
Now, do the conversion to topology, time it, and validate :
\timing
SELECT TopoGeo_AddPolygon( 'hexagon_topo', h.wkb_geometry )
FROM some_hex_cells h;
thuban1 Time: 636187 ms
i7-960+ Time: 239265 ms
select TopologySummary('hexagon_topo');
topologysummary
---------------------------------------------------------------
Topology hexagon_topo (2), SRID 2163, precision 0 +
10608 nodes, 15879 edges, 5272 faces, 0 topogeoms in 0 layers+
It strikes me as odd that the summary would list “0 topogeoms” but if I recall that means that there are no *heirarchical* topologies. Since TopologySummary() returned ok, perhaps my one topology is valid. But, ValidateTopology() returns zero rows ?? I guess there is more to topology to learn, still…
SELECT ValidateTopology('hexagon_topo');
validatetopology
------------------
(0 rows)
PS- just noticed this post