I've been trying to populate a Sql Server database with shapefile information from the National Weather Service in an automated fashion, I finally found a tool that helps me do just that. It's called
ESRI Shapefile Reader from
CodePlex.
I had first tried
Shape2Sql, but it is closed source and for some reason would not import when I had Create Spatial Index checked. It would run through all the rows in the Shapefile, but no table or rows would be created. So I pre-created the table, still no rows were created. That was with the GUI, so when I tried it as a command line tool, again, it would not import the shape file because there is no command line option to disable creating the spatial index. I don't know where the problem lies, and Shape2Sql appears to be a good tool. For me, it works great for doing the import by hand, which is fine for data that doesn't change often, but I need automation and if the command line doesn't work, I'm not about to try and manipulate mouse clicks on a GUI from a windows service....
I found various other tools, but they require subscription $, and I am trying to do this with a minimal budget since this is being used for a hobby.
Anyways, I needed to load this data into
SqlServer Express. Using Shape2Sql, it took about 14 minutes for my particular datafile. I hate waiting and I need to automate this thing, so I decided to use ESRI Shapefile Reader to import a National Weather Service Precipitation file (which can be found at
http://water.weather.gov/precip/download.php) and only grab the columns I'm interested in. Doing this, and threading the import, it now only takes 45 seconds to import the data. Best of all, I met my goal of doing this in an automated fashion. Here's my code snippet that shows you how I did this, if you have any questions, just post it.
public static void CreateWorkForThreads()
{
DataSet ds = CreateNewDataSet();
DataTable dtNWS = ds.Tables[0];
// Parse the shapefile into a DataTable, grabbing the columns we are interested in
using (Shapefile shapefile = new Shapefile(Path.Combine(weatherFileDir, "nws_precip_1day_observed_" + dateToLoad.ToString("yyyyMMdd") + ".shp")))
{
foreach (Shape shape in shapefile)
{
string[] metadataNames = shape.GetMetadataNames();
decimal lat = 0m;
decimal lon = 0m;
decimal globvalue = 0m;
if (metadataNames != null)
{
foreach (string metadataName in metadataNames)
{
if (metadataName == "lat")
lat = decimal.Parse(shape.GetMetadata(metadataName));
else if (metadataName == "lon")
lon = decimal.Parse(shape.GetMetadata(metadataName));
else if (metadataName == "globvalue")
globvalue = decimal.Parse(shape.GetMetadata(metadataName));
}
}
DataRow drNWS = dtNWS.NewRow();
drNWS["lat"] = lat;
drNWS["lon"] = lon;
drNWS["globalvalue"] = globvalue;
drNWS["precipDate"] = dateToLoad;
drNWS["XAxis"] = Math.Cos(ConvertDegreesToRadians((double)lat)) * Math.Cos(ConvertDegreesToRadians((double)lon));
drNWS["YAxis"] = Math.Cos(ConvertDegreesToRadians((double)lat)) * Math.Sin(ConvertDegreesToRadians((double)lon));;
drNWS["ZAxis"] = Math.Sin(ConvertDegreesToRadians((double)lat));
dtNWS.Rows.Add(drNWS);
}
}
List; listOfDataSetsForThreads = new List();
DataSet dsCur = CreateNewDataSet();
// Create a list of datasets, each containing the rows the thread will import
foreach (DataRow dr in dtNWS.Rows)
{
if (dsCur.Tables[0].Rows.Count % 3000 == 0)
{
listOfDataSetsForThreads.Add(dsCur);
dsCur = CreateNewDataSet();
}
dsCur.Tables[0].ImportRow(dr);
}
if (dsCur.Tables[0].Rows.Count > 0)
listOfDataSetsForThreads.Add(dsCur);
// Spawn off the threads to import our datasets in parallel
foreach (DataSet dsThreadWork in listOfDataSetsForThreads)
{
WaitCallback wcb = new WaitCallback(ImportDataSet);
ThreadPool.QueueUserWorkItem(wcb, dsThreadWork);
}
}
public static void ImportDataSet(object o)
{
DataSet ds = (DataSet)o;
using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["myDb"]))
{
con.Open();
try
{
SqlDataAdapter da = new SqlDataAdapter("select top 1 * from nws_precip_history", con);
SqlCommandBuilder bldr = new SqlCommandBuilder(da);
da.InsertCommand = bldr.GetInsertCommand();
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateBatchSize = 500;
da.Update(ds, "nws_precip_history");
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
}
public static DataSet CreateNewDataSet()
{
DataSet dsTemp = new DataSet();
DataTable dtNWSTemp = new DataTable("nws_precip_history");
dtNWSTemp.Columns.Add("lat", typeof(decimal));
dtNWSTemp.Columns.Add("lon", typeof(decimal));
dtNWSTemp.Columns.Add("globalvalue", typeof(decimal));
dtNWSTemp.Columns.Add("precipDate", typeof(DateTime));
dtNWSTemp.Columns.Add("XAxis", typeof(float));
dtNWSTemp.Columns.Add("YAxis", typeof(float));
dtNWSTemp.Columns.Add("ZAxis", typeof(float));
dsTemp.Tables.Add(dtNWSTemp);
return dsTemp;
}
public static double ConvertDegreesToRadians(double degrees)
{
double radians = (Math.PI / 180) * degrees;
return (radians);
}
