From 4e55f59114deebb3c22dacf7345b7dadc3c984cf Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 14 Aug 2011 12:32:23 +0000
Subject: [PATCH] #1071 - start work on tiger PostGIS topology loader

git-svn-id: http://svn.osgeo.org/postgis/trunk@7746 b70326c6-7e19-0410-871a-916f4a2858ee
---
 .../tiger_geocoder/tiger_2010/topology/README |  4 +
 .../topology/tiger_topology_loader.sql        | 79 +++++++++++++++++++
 2 files changed, 83 insertions(+)
 create mode 100644 extras/tiger_geocoder/tiger_2010/topology/README
 create mode 100644 extras/tiger_geocoder/tiger_2010/topology/tiger_topology_loader.sql

diff --git a/extras/tiger_geocoder/tiger_2010/topology/README b/extras/tiger_geocoder/tiger_2010/topology/README
new file mode 100644
index 000000000..f9f07214c
--- /dev/null
+++ b/extras/tiger_geocoder/tiger_2010/topology/README
@@ -0,0 +1,4 @@
+$Id$
+This folder is will contain functions for converting tiger data to 
+PostGIS topology format. It assumes tiger data has already been loaded using
+the tiger loader.
\ No newline at end of file
diff --git a/extras/tiger_geocoder/tiger_2010/topology/tiger_topology_loader.sql b/extras/tiger_geocoder/tiger_2010/topology/tiger_topology_loader.sql
new file mode 100644
index 000000000..63490d5a5
--- /dev/null
+++ b/extras/tiger_geocoder/tiger_2010/topology/tiger_topology_loader.sql
@@ -0,0 +1,79 @@
+/**********************************************************************
+ * $Id$
+ *
+ * PostGIS - Spatial Types for PostgreSQL
+ * Copyright 2011 Leo Hsu and Regina Obe <lr@pcorp.us> 
+ * Paragon Corporation
+ * This is free software; you can redistribute and/or modify it under
+ * the terms of the GNU General Public Licence. See the COPYING file.
+ *
+ * This file contains helper functions for loading tiger data
+ * into postgis topology structure
+ **********************************************************************/
+
+ /** topology_load_tiger: Will load all edges, faces, nodes into 
+ *  topology named toponame
+ *	that intersect the specified region
+ *  region_type: 'place', 'county'
+ *  region_id: the respective fully qualified geoid
+ *	 place - plcidfp
+ *	 county - cntyidfp 
+ * USE CASE: 
+ *	The following will create a topology called topo_boston and load Boston, MA
+ * SELECT topology.CreateTopology('topo_boston', 4269);
+ * SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000'); 
+ ****/
+CREATE OR REPLACE FUNCTION tiger.topology_load_tiger(IN toponame varchar,  
+	region_type varchar, region_id varchar)
+  RETURNS text AS
+$$
+DECLARE
+ 	var_sql text;
+ 	var_rgeom geometry;
+ 	var_statefp text;
+ 	var_rcnt bigint;
+ 	var_result text := '';
+BEGIN
+	--$Id$
+	CASE region_type
+		WHEN 'place' THEN
+			SELECT the_geom , statefp FROM place INTO var_rgeom, var_statefp WHERE plcidfp = region_id;
+		WHEN 'county' THEN
+			SELECT the_geom, statefp FROM county INTO var_rgeom, var_statefp WHERE cntyidfp = region_id;
+		ELSE
+			RAISE EXCEPTION 'Region type % IS NOT SUPPORTED', region_type;
+	END CASE;
+	-- start load in faces
+	var_sql := 'INSERT INTO ' || quote_ident(toponame) || '.face(face_id, mbr) 
+						SELECT tfid, ST_Envelope(the_geom) As mbr 
+							FROM tiger.faces WHERE statefp = $1 AND ST_Intersects(the_geom, $2) 
+							AND tfid NOT IN(SELECT face_id FROM ' || quote_ident(toponame) || '.face) ';
+	EXECUTE var_sql USING var_statefp, var_rgeom;
+	GET DIAGNOSTICS var_rcnt = ROW_COUNT;
+	var_result := var_rcnt::text || ' faces added. ';
+   -- end load in faces
+   
+   	-- start load in nodes
+	var_sql := 'INSERT INTO ' || quote_ident(toponame) || '.node(node_id, geom)
+					SELECT DISTINCT ON(tnid) tnid, geom
+						FROM 
+						( 
+							SELECT tnidf AS tnid, ST_StartPoint(ST_GeometryN(the_geom,1)) As geom 
+								FROM tiger.edges WHERE statefp = $1 AND ST_Intersects(the_geom, $2) 
+									AND tnidf NOT IN(SELECT node_id FROM ' || quote_ident(toponame) || '.node)
+						UNION ALL 
+							SELECT tnidt AS tnid, ST_EndPoint(ST_GeometryN(the_geom,1)) As geom 
+							FROM tiger.edges WHERE statefp = $1 AND ST_Intersects(the_geom, $2) 
+									AND tnidt NOT IN(SELECT node_id FROM ' || quote_ident(toponame) || '.node) 
+					) As n ';
+	EXECUTE var_sql USING var_statefp, var_rgeom;
+	GET DIAGNOSTICS var_rcnt = ROW_COUNT;
+	var_result := var_result || ' ' || var_rcnt::text || ' nodes added. ';
+   -- end load in nodes
+   
+   -- TODO: Load in edges --
+	RETURN var_result;
+END
+$$
+  LANGUAGE plpgsql VOLATILE
+  COST 1000;
-- 
2.50.0