Wednesday, July 27, 2011

C# Esri Shapefile Reader

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

            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)
                    dsCur = CreateNewDataSet();


            if (dsCur.Tables[0].Rows.Count > 0)

            // 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"]))

                    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");
                    if (con.State == ConnectionState.Open)


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

            return dsTemp;

        public static double ConvertDegreesToRadians(double degrees)
            double radians = (Math.PI / 180) * degrees;
            return (radians);

1 comment:

  1. How can I save geom column of shapefile where data type is geometry