Showing posts with label SqlServer. Show all posts
Showing posts with label SqlServer. Show all posts

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


Monday, February 28, 2011

SqlServer: Saving changes is not permitted. The changes you have made require the followign tables to be dropped and re-created

Problem:
Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Solution:
This is different than the default in Sql Server 2005, where you can automatically drop and recreate a table in the designer.  Solution is simple, and doesn't require you to lose your changes.

Screenshots:
In Sql Server Management Studio 2008, go to tools à options, in the options window, left pane Designers, and on the right side uncheck Prevent saving changes that require table re-creation.




System.InvalidOperationException was unhandled by user code The model backing the 'SomeContext' context has changed since the database was created

Problem:
Received the following exception when adding a field to an Entity class:


System.InvalidOperationException was unhandled by user code
  Message=The model backing the 'MovieDBContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.
  Source=EntityFramework
Full Stack Trace at the end.

Code the exception occurred:
        MovieDBContext db = new MovieDBContext();

        public ActionResult Index()
        {
            var movies = from m in db.Movies
                         where m.ReleaseDate > new DateTime(1984, 6, 1)
                         select m;

            return View(movies.ToList());
        }


Solution:
Update the table in Sql Server to have the additional column you added to your entitiy class.



Full Stack Trace:
  StackTrace:
       at System.Data.Entity.Database.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)
       at System.Data.Entity.Database.DbDatabase.<>c__DisplayClass8.b__5()
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Database.DbDatabase.Initialize(Boolean force)
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.get_Provider()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
       at System.Linq.Queryable.Where[TSource](IQueryable`1 source, Expression`1 predicate)
       at MvcMovie.Controllers.MoviesController.Index() in xxxx\visual studio 2010\Projects\MovieApp\MvcMovie\Controllers\MoviesController.cs:line 16
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.b__12()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)