protected Dictionary<string, DataRow> GetShapeRowsByLOGRECNO(DbConnection conn)
{
string geomSQL;
string geographiesTablename = this.GetGeographyTablename();
string shapeSQL = string.Empty;
switch (this.SummaryLevel)
{
//census_regions
//census_divisions
//states
//case "050":
// //counties
// // Note: original sql for files fount at:
// // "ShapeFileCountiesURL": "http://www.census.gov/geo/cob/bdy/co/co00shp/"
// // "ShapeFileCountiesFilename": "co{FIPS-code}_d00_shp.zip"
// // was:
// // "select trim(COUNTY) as county, AsBinary(Geometry) as Geometry, '' as GEOID from counties ";
// //
// //
// // Current source:
// // "ShapeFileCountiesURL": "http://www2.census.gov/geo/tiger/TIGER2010/COUNTY/2010/",
// // "ShapeFileCountiesFilename": "tl_2010_{FIPS-code}_county10.zip",
// // Docs for current source: http://www.census.gov/geo/maps-data/data/pdfs/tiger/tgrshp2010/TGRSHP10SF1AA.pdf
// //
// shapeSQL = "select trim(COUNTYFP10) as county, AsBinary(Geometry) as Geometry, '' as GEOID from counties ";
// geomSQL = "select LOGRECNO, trim(COUNTY) as county, GEOID from geographies_all where SUMLEVEL = '050' order by county ";
// break;
////subdivisions
//case "060":
// shapeSQL = "select trim(COUNTY) as county, trim(COUSUB) as cousub, AsBinary(Geometry) as Geometry, '' as GEOID from county_subdivisions ";
// geomSQL = "select LOGRECNO, trim(COUNTY) as county, trim(COUSUB) as cousub, GEOID from geographies_all where SUMLEVEL = '060' order by county, cousub";
// break;
case "140":
//tracts
shapeSQL = "select trim(COUNTY) as county, trim(TRACT) as tract, '' as blkgroup, AsBinary(Geometry) as Geometry, '' as GEOID from census_tracts order by county, tract, blkgroup";
geomSQL = "select LOGRECNO, trim(COUNTY) as county, trim(TRACT) as tract, trim(BLKGRP) as blkgroup, GEOID from geographies where SUMLEVEL = '140' order by county, tract, blkgrp ";
break;
case "150":
default:
if (this.SummaryLevel != "150")
{
_log.Error("No Summary Level Selected -- defaulting to Block groups \"150\" -- ");
this.SummaryLevel = "150";
}
/*
* NOTE! In the 2000 census shapefiles the block-group column name was "BLKGROUP",
* in 2010, it was "BLKGRP"... so, lets magically recover and adjust based on what we find.
*/
bool hasAbbrevColumn = DataClient.HasColumn("BLKGRP", "census_blockgroups", conn, DbClient);
string blkGroupColumn = (hasAbbrevColumn) ? "BLKGRP" : "BLKGROUP";
//block groups
shapeSQL = string.Format("select trim(COUNTY) as county, trim(TRACT) as tract, trim({0}) as blkgroup, AsBinary(Geometry) as Geometry, '' as GEOID from census_blockgroups order by county, tract, blkgroup",
blkGroupColumn);
geomSQL = "select LOGRECNO, trim(COUNTY) as county, trim(TRACT) as tract, trim(BLKGRP) as blkgroup, GEOID from geographies where SUMLEVEL = '150' order by county, tract, blkgrp ";
break;
}
var wholeGeomTable = DataClient.GetMagicTable(conn, DbClient, geomSQL);
var wholeShapeTable = DataClient.GetMagicTable(conn, DbClient, shapeSQL);
if (wholeShapeTable == null)
{
_log.Error("This shapefile had different columns than I was expecting, bailing");
return null;
}
//
// Construct the appropriate key for whatever summary level we're using
//
GetGeometryRowKey keyDelegate = GetGeometryRowKeyGenerator();
//
// organize our freshly pulled geometries table by composite_id
//
var geomKeys = new Dictionary<string, DataRow>();
foreach (DataRow row in wholeGeomTable.Rows)
{
string key = keyDelegate(row);
geomKeys[key] = row;
}
var dict = new Dictionary<string, DataRow>();
GisSharpBlog.NetTopologySuite.IO.WKBReader binReader = new WKBReader(ShapefileHelper.GetGeomFactory());
GisSharpBlog.NetTopologySuite.IO.WKBWriter binWriter = new WKBWriter();
foreach (DataRow row in wholeShapeTable.Rows)
{
string key = keyDelegate(row);
if (geomKeys.ContainsKey(key))
{
string logrecno = geomKeys[key]["LOGRECNO"] as string;
row["GEOID"] = geomKeys[key]["GEOID"];
if (dict.ContainsKey(logrecno))
{
/* In theory, there should be a one-to-one relationship between logrecno and Geometry.
* In practice, there are a small number of geometries that are discontiguous or self-
* intersecting. Because the geometry shapefile only uses single polygons and not
* multipolygons, they solve this problem by having duplicate rows in the geometry
* shapefile with the same county, tract, and blkgroup numbers, one for each part of
* the multipolygon. This is undocumented AFAIK, but can be seen in several places (see
* logrecno 0014948 for PA for example). To account for this, we union geometries
* together whenever we encounter duplicates and inform the user, so we don't end up
* with missing geometries in the output.
*
* As this link indicates, tracts 9400 - 9499 are especially prone to this phenomenon:
* http://www.census.gov/geo/www/cob/tr_metadata.html
*/
_log.DebugFormat("Duplicate records encountered for logical record number {0} (key {1}).", logrecno, key);
if (row["GEOID"] != dict[logrecno]["GEOID"])
{
_log.DebugFormat("GEOID {0} collided with GEOID {1}", row["GEOID"], dict[logrecno]["GEOID"]);
}
_log.DebugFormat("Attempting to merge geometries for duplicates together.");
try
{
byte[] geomBytes = (byte[])row["Geometry"];
IGeometry geomToAdd = binReader.Read(geomBytes);
geomBytes = (byte[])dict[logrecno]["Geometry"];
IGeometry currentGeom = binReader.Read(geomBytes);
if (!geomToAdd.Equals(currentGeom))
{
geomBytes = binWriter.Write(currentGeom.Union(geomToAdd));
row["Geometry"] = geomBytes; //saved to dict at end of current if clause
_log.Debug("Geometry merge succeeded! Please double check all features in the output that match the above logrecno for consistency.");
}
}
catch (Exception)
{
_log.Error("Geometry merge failed; only one geometry will be used! Output may be missing geometries!");
}
}
dict[logrecno] = row;
}
else
{
_log.DebugFormat("Couldn't find a geometry matching KEY (county/tract/blkgrp) {0}", key);
}
}
return dict;
}