QGIS 2.8.1 Getting Shape Files into SQL Server 2008 Express R2

For digital mapping the shp extension is the equivalent of csv files – A significant amount of information is still held in shape files and even if it is not, nearly every GIS package can export to shape format. It’s therefore pretty vital that you understand how to get this format into your backends.

Turns out QGIS 2.8.1 comes with a very handy excecutable called ogr2ogr.exe
On my computer this was downloaded with my installation of QGIS 2.8.1 and placed in the the following directory

C:\Program Files\QGIS Wien\bin

It looks like this executable has been a part of the the download for sometime so if you are on a different version of QGIS I suspect the file will be on your machine but in a directory other that QGIS Wien – whatever your version is.

If in doubt a simple search on ogr2ogr should give you the location.

From the command prompt you need to either navigate to the location of ogr2ogr.exe or place the full path into the instruction. In the following I have navigated to the appropriate directory using change directory command at the prompt. I then input the following.

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=MARK-LENOVO\SQLEXPRESS;database=Geodatabase;trusted_connection=yes" "C:\Users\Mark\Documents\shp\polygon\n_america.shp"

On return it will start to import the information creating a new table in your SQL Server instance within the database listed in your parameter string. It looks like it just names the table the same as the shape file, I suspect if that name already exists as a tablename in SQL Server that table will be overwritten with the new shape file. Also note that the import process can take a fair bit of time for large files so be patient. I tested it initially with a small import which it did relatively quickly, I then went and hit it with 500 thousand records and it took a little over 2 hours. Still good to know that it can cope.

Once you have imported the information into SQL you should perform some form of spatial indexing on the table.
I have noted that layers that have spatial indexing are drawn differently than non spatial indexed layers. Layers with spatial indexes are drawn in more rapidly all over the district much like a spray from a can. Non spatial indexed layers appear on screen slower as if painted from one side to the other.

Dealing with Dates can still catch you out

The other day I was dealing with dates and I found a cracking little gotcha that might just be catching a few people out.

When is 07/11/14 not 07/11/14

When one is 17 November 2014 and one is 17 November 1914

I’ve been dealing with legal dates and I needed to set dates some 25 years in the future for payments of moneys. That’s fine just typing in dates I’ve been putting dates in which turn out to be a century in the past. This led me to investigate.

Within windows we get very used to typing in dates by using 6 digits – 01/01/14 or 09/05/15 – most users computers would recognise these dates as 1st of January 2014 and 9th September 2015. What many users may not consider , myself included , is that the interpretation of the year in this scenario is relative. That is relative to a century. We happen to be in the 21st century but if you type in 99 most computers will interpret the year as 1999 and not 2099. Fine but at what time does it change the century. Well turns out there is a configuration setting.

Here’s how you get to the configuration settings in Win 8.1

Control Panel
Clock, language and Region
Set the date and timeā€¦
Change date and time

Should get you to the following;

Win8ControlPanelDateTime

Then hit Change calendar settings;

Win8CustomizeDateFormat

The danger is of course that users will type in 07/11/30 thinking this will be 2030 when the two digit interpretation configuration will interpret it as 7 November 1930. How it interprets this comes down to the above setting. I believe at present this is the default setting.

Its one to watch out for and doubtless it may be catching some organisations out.