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); }
