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


4 comments: