{"id":484,"date":"2012-04-25T17:49:41","date_gmt":"2012-04-26T00:49:41","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=484"},"modified":"2012-08-24T19:09:13","modified_gmt":"2012-08-25T02:09:13","slug":"topology-of-hex-cells","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=484","title":{"rendered":"Topology of Hex Cells"},"content":{"rendered":"<p><script type=\"text\/javascript\">jQuery(document).ready(function($) { $(\"#gallery a\").lightBox({fixedNavigation:true});});<\/script><\/p>\n<div id='gallery'>\n<a href=\"wp-content\/uploads\/2012\/04\/hex_topo_alameda-1024x547.png\"><img decoding=\"async\" src=\"wp-content\/uploads\/2012\/04\/hex_topo_alameda-300x160.png\" alt=\"alameda topo\" align=\"right\" \/><\/a>\n<\/div>\n<p> A year ago I had modified code from <a href=\"http:\/\/www.godsmonsters.com\/Notes\/hex-square-grids-mapping\/\" title=\"grid cells source code\" target=\"_blank\"><em>here<\/em><\/a> to make a set of about thirty million hex cells across the continental United States, using the projection from the <a href=\"http:\/\/nationalmap.gov\/\" title=\"USGS National Map\" target=\"_blank\"><strong>National Map<\/strong><\/a>, <a href=\"http:\/\/spatialreference.org\/ref\/epsg\/2163\/\" title=\"epsg 2163\" target=\"_blank\"><strong>EPSG:2163<\/strong><\/a>.<\/p>\n<p> Today, topology wizard <strong>strk<\/strong> was building a set of hex cells to test conversion to topology in <strong>PostGIS 2.0<\/strong>. The question arose, would the hex cells I had built with another method convert cleanly to a topology, and without specifying a tolerance distance? <\/p>\n<p>So I tried converting a subset of the US cells into a topology.<br \/>\nFirst step, pick a few thousand contiguous hex cells:<\/p>\n<blockquote>\n<pre>\r\n-- create one Alameda County multipolygon\r\nDROP TABLE if exists alameda_tracts CASCADE;\r\nCREATE TABLE alameda_tracts as\r\nSELECT \r\n  1 as ogc_fid, \r\n  st_union(wkb_geometry) as wkb_geometry\r\nFROM \r\n  census_2010_tracts\r\nWHERE\r\n  census_2010_tracts.countyfp10 = '001';\r\nALTER TABLE alameda_tracts add PRIMARY KEY ( ogc_fid );\r\n--\r\n-- transform and compare county to hex cells\r\nDROP TABLE if exists some_hex_cells CASCADE;\r\nCREATE TABLE some_hex_cells as\r\nSELECT \r\n  distinct on (hex_grid_cells.id, hex_grid_cells.wkb_geometry)\r\n  hex_grid_cells.id, hex_grid_cells.wkb_geometry\r\n  \r\nFROM \r\n  public.alameda_tracts, \r\n  public.hex_grid_cells\r\n\r\nWHERE \r\n  ST_Intersects( \r\n    hex_grid_cells.wkb_geometry,\r\n    ST_Transform(alameda_tracts.wkb_geometry,2163) );\r\n    \r\n--\r\nalter table some_hex_cells add primary key (id);\r\ncreate index shc_geom_idx on some_hex_cells USING GIST (wkb_geometry);\r\n\r\nselect count(*) from some_hex_cells;\r\n count \r\n-------\r\n  5272\r\n(1 row)\r\n<\/pre>\n<\/blockquote>\n<p>&nbsp;<br \/>\nIn an <code>EXPLAIN ANALYZE<\/code> 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 &#8211; perfect! Next step, create a <strong>PostGIS 2.0 <\/strong>topology in EPSG:2163 :<\/p>\n<blockquote>\n<pre>\r\nSELECT CreateTopology('hexagon_topo', 2163 );\r\n\r\n-- use DropTopology('hexagon_topo') to start over..\r\n<\/pre>\n<\/blockquote>\n<p>&nbsp;<br \/>\nNow, do the conversion to topology, time it, and validate :<\/p>\n<blockquote>\n<pre>\r\n\\timing\r\nSELECT TopoGeo_AddPolygon( 'hexagon_topo', h.wkb_geometry )\r\n  FROM some_hex_cells h;\r\n\r\nthuban1 Time: 636187 ms\r\ni7-960+ Time: 239265 ms\r\n\r\n select TopologySummary('hexagon_topo');\r\n                        topologysummary                        \r\n---------------------------------------------------------------\r\n Topology hexagon_topo (2), SRID 2163, precision 0            +\r\n 10608 nodes, 15879 edges, 5272 faces, 0 topogeoms in 0 layers+\r\n<\/pre>\n<\/blockquote>\n<p> It strikes me as odd that the summary would list &#8220;0 topogeoms&#8221; but if I recall that means that there are no *heirarchical* topologies. Since <code>TopologySummary()<\/code> returned ok, perhaps my one topology is valid.  But, <code>ValidateTopology()<\/code> returns zero rows ??  I guess there is more to topology to learn, still&#8230;<\/p>\n<blockquote>\n<pre>\r\nSELECT ValidateTopology('hexagon_topo');\r\n validatetopology \r\n------------------\r\n(0 rows)\r\n<\/pre>\n<\/blockquote>\n<p>PS- just noticed <a href=\"http:\/\/www.dimensionaledge.com\/main\/postgis\/how-to-create-hexagonal-grids-in-postgis\/\" title=\"make hex grid\">this post<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/484"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=484"}],"version-history":[{"count":31,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/484\/revisions"}],"predecessor-version":[{"id":731,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/484\/revisions\/731"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=484"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}