public void TestFile()
{
DBaseIIIFile db = new DBaseIIIFile("../../shape_eg_data/mexico/states.dbf");
Assert.AreEqual(new DateTime(1996, 4, 30), db.LastUpdated);
Assert.AreEqual(3, db.Records.Columns.Count);
Assert.AreEqual(typeof(double), db.Records.Columns[0].DataType);
Assert.AreEqual("AREA", db.Records.Columns[0].ColumnName);
Assert.AreEqual(typeof(string), db.Records.Columns[1].DataType);
Assert.AreEqual("CODE", db.Records.Columns[1].ColumnName);
Assert.AreEqual(typeof(string), db.Records.Columns[2].DataType);
Assert.AreEqual("NAME", db.Records.Columns[2].ColumnName);
Assert.AreEqual(32, db.Records.Rows.Count);
Assert.AreEqual(28002.325, db.Records.Rows[0][0]);
Assert.AreEqual("MX02", db.Records.Rows[0][1]);
Assert.AreEqual("Baja California Norte", db.Records.Rows[0][2].ToString().Trim());
Assert.AreEqual(27564.808, db.Records.Rows[db.Records.Rows.Count-1][0]);
Assert.AreEqual("MX30", db.Records.Rows[db.Records.Rows.Count-1][1]);
Assert.AreEqual("Veracruz-Llave", db.Records.Rows[db.Records.Rows.Count-1][2].ToString().Trim());
}
public void TestAnotherFile()
{
DBaseIIIFile db = new DBaseIIIFile("../../shape_eg_data/mexico/cities.dbf");
Assert.AreEqual(new DateTime(1996, 4, 30), db.LastUpdated);
Assert.AreEqual(4, db.Records.Columns.Count);
Assert.AreEqual(36, db.Records.Rows.Count);
}
public static void Main(string[] args)
{
#region handle parameters and configuration
// application variables
string connectionString = null;
int srid = 0;
string idColumn = "gid";
string geomColumn = "the_geom";
string tableName = null;
bool showHelp = false;
bool createIndex = false;
bool useGeography = false;
bool append = false;
bool showVersion = false;
Encoding encoding = null;
// set up parameters
OptionSet options = new OptionSet();
options.Add("s|srid=",
"The Spatial Reference ID (SRID). If not specified it defaults to -1.",
delegate (string v) { srid = int.Parse(v); });
options.Add("g|geometry_column=",
"The name of the geometry column",
delegate (string v) { geomColumn = v; });
options.Add("t|table_name=",
"The table name to use",
delegate (string v) { tableName = v; });
options.Add("k|key_column=",
"The name of the identity column to create for a primary key",
delegate (string v) { idColumn = v; });
options.Add("i|index",
"Create a spatial index",
delegate (string v) { createIndex = v != null; });
options.Add("l|latlong",
"Add spatial data as geography type",
delegate (string v) { useGeography = v != null; });
options.Add("a|append",
"Append data. If not specified, table will be created",
delegate (string v) { append = v != null; });
options.Add("h|help", "show this message and exit",
delegate (string v) { showHelp = v!= null; });
options.Add("v|version",
"shows the version and exits",
delegate (string v) { showVersion = v != null; });
options.Add("e|encoding=",
"Specifies the encoding to use for reading the DBF file (e.g. \"utf-32\")",
delegate(string v) { encoding = Encoding.GetEncoding(v); });
// parse the command line args
List<string> rest = options.Parse(args);
if (showVersion)
{
System.Console.WriteLine("Version " +
System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString());
return;
}
if (showHelp)
{
ShowHelp(options);
return;
}
if (rest.Count == 0)
{
System.Console.WriteLine("Error: A connection string is required");
ShowHelp(options);
return;
}
if (rest.Count == 1)
{
System.Console.WriteLine("Error: No path to shapefile provided");
ShowHelp(options);
return;
}
connectionString = rest[0];
string path = rest[1];
if (string.IsNullOrEmpty(tableName))
{
// use the shapefile name as table name if none provided
tableName = Path.GetFileNameWithoutExtension(path);
}
#endregion
#region load shp and dbf
Shapefile.Shapefile shp = new Shapefile.Shapefile(path);
DBaseIIIFile dbf = new DBaseIIIFile(Path.Combine(Path.GetDirectoryName(path),
Path.GetFileNameWithoutExtension(path)) +
".dbf",
encoding);
#endregion
#region drop table if not exists
if (!append)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
StringBuilder sql = new StringBuilder();
SqlCommand command = new SqlCommand(string.Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL drop table {0}", tableName), connection);
command.ExecuteNonQuery();
}
}
#endregion
#region create table
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
StringBuilder sql = new StringBuilder();
SqlCommand command = null;
if (!append)
{
sql.AppendFormat("create table {0} (", tableName);
sql.AppendFormat("{0} int identity(1,1) not null", idColumn);
foreach (DataColumn dc in dbf.Records.Columns)
{
sql.Append(",");
string dataType = "nvarchar(256)";
if (dc.DataType == typeof(DateTime))
{
dataType = "date";
}
else if (dc.DataType == typeof(double))
{
dataType = "real";
}
else if (dc.DataType == typeof(bool))
{
dataType = "bit";
}
sql.AppendFormat("{0} {1}", dc.ColumnName, dataType);
}
sql.AppendFormat(",{0} {1},", geomColumn, useGeography ? "geography" : "geometry");
sql.AppendFormat("CONSTRAINT PK_{0} PRIMARY KEY CLUSTERED ({1} ASC) ON [PRIMARY]", tableName, idColumn);
sql.Append(")");
command = new SqlCommand(sql.ToString(), connection);
command.ExecuteNonQuery();
}
#endregion
#region insert rows
int idx = 0;
var features = shp.GetFeatures();
var count = features.Count;
int index = 0;
foreach (Feature f in features)
{
index++;
Console.WriteLine(string.Format("{0}/{1} ({2}%)", index, count, Math.Round((((decimal)index / (decimal)count) * 100), 2)));
sql = new StringBuilder();
sql.AppendFormat("insert into {0} values (", tableName);
for (int ii=0; ii < dbf.Records.Columns.Count; ii++)
{
DataColumn dc = dbf.Records.Columns[ii];
object field = dbf.Records.Rows[idx][ii];
if (dc.DataType == typeof(DateTime))
{
if (field is DBNull)
{
sql.Append(SqlDateTime.Null);
}
else
{
DateTime dtField = ((DateTime)field);
string dtFieldString = string.Format("{0:yyyyMMdd}", dtField);
sql.AppendFormat("CAST('{0}' AS date)", dtFieldString);
}
}
else if (dc.DataType == typeof(double))
{
if (field is DBNull)
{
sql.Append(SqlDouble.Null);
}
else
{
sql.Append(field.ToString());
}
}
else if (dc.DataType == typeof(bool))
{
if (field is DBNull)
{
sql.Append(SqlBinary.Null);
}
else
{
sql.AppendFormat("{0}", ((bool)field) ? 1 : 0);
}
}
else
{
if (field is DBNull)
{
sql.Append(SqlString.Null);
}
else
{
sql.AppendFormat("'{0}'", field.ToString().Replace("'", "''"));
}
}
sql.Append(", ");
}
string wkt = string.Empty;
if (shp.SourceFeatureType == FeatureType.Polygon)
{
Polygon polygon = f as Polygon;
// Sql Server 2008 ring order is reverse of shapefiles
// http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx
foreach (Ring r in polygon.Rings)
{
r.Points.Reverse();
}
wkt = WellKnownText.CreateFromPolygon(polygon, PolygonHoleStrategy.InteriorToLeft);
}
else if (shp.SourceFeatureType == FeatureType.Point)
{
wkt = WellKnownText.CreateFromPoint(f as Point);
}
else
{
wkt = WellKnownText.CreateFromPolyLine(f as PolyLine);
}
sql.AppendFormat("{2}::STGeomFromText('{0}', {1})",
wkt,
srid,
useGeography ? "geography" : "geometry");
sql.Append(")");
idx++;
command = new SqlCommand(sql.ToString(), connection);
command.ExecuteNonQuery();
}
#endregion
#region create spatial index
if (createIndex)
{
sql = new StringBuilder();
sql.AppendFormat("CREATE SPATIAL INDEX {0}_sidx", tableName);
sql.AppendFormat(" ON {0}({1})", tableName, geomColumn);
sql.AppendFormat(" USING {0}_GRID WITH ( ", useGeography ? "GEOGRAPHY" : "GEOMETRY");
if (!useGeography)
{
sql.AppendFormat(System.Globalization.CultureInfo.InvariantCulture,
"BOUNDING_BOX = (xmin={0}, ymin={1}, xmax={2}, ymax={3}),",
shp.Extents.Min.X,
shp.Extents.Min.Y,
shp.Extents.Max.X,
shp.Extents.Max.Y);
}
sql.Append(" GRIDS = (LEVEL_1 = LOW, LEVEL_2 = LOW, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16)");
command = new SqlCommand(sql.ToString(), connection);
command.CommandTimeout = 0; // no timeout
command.ExecuteNonQuery();
}
#endregion
}
}
void LoadDbf()
{
if (isDbfLoaded) return;
dbfFile = new DBaseIIIFile(Path.GetDirectoryName(fileName) +
Path.DirectorySeparatorChar +
Path.GetFileNameWithoutExtension(fileName) +
".dbf");
isDbfLoaded = true;
}
