1. Download the 64-bit version on the MS Access ODBC driver (AccessDatabaseEngine_x64.exe): http://www.microsoft.com/en-gb/download/details.aspx?id=13255
2. If you don’t have 32-bit MS Office installed you can just run the exe, however if you do have 32-bit office installed then you need to start an admin command prompt and run the installer in passive mode as follows:

AccessDatabaseEngine_x64.exe /passive

3. For QGIS 2: Then you need to edit the qgis-ltr.bat (normally in C:\Program Files\QGIS x.xx\bin, in previous versions used to be qgis.bat) and add the following lines (before the ‘start’ command)

set OGR_SKIP=ODBC
set PGEO_DRIVER_TEMPLATE=DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%%s

4. For QGIS 3: In QGIS Settings panel -> Options | System | Environment add the following two variables:

   variable name: PGEO_DRIVER_TEMPLATE 
   value: DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s
   Or if being used on a shared server add a system environment variable 
   PGEO_DRIVER_TEMPLATE=DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s

5. Now you should be able to start QGIS and simply drag the geodatabase mdb file onto the QGIS workspace and it will open.

If you have multiple installations it can be confusing working out which copy you are actually running.  Looking in the registry at:

HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{EDFA6FE2-C2C5-4BE1-9F12-F1ED37D85EE2}\InprocServer32

will tell you where the file(s) are being run from.

If you want to manually register the dll to a particular location you need to open a cmd window as Administrator and then run:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe /codebase /tlb C:\AMS\CMSI_v2.2.0a\code\XGMapLinkArcgisExtension.dll

C:\Program Files (x86)\Common Files\ArcGIS\bin\ESRIRegasm.exe" /p:Desktop C:\AMS\CMSI_v2.2.0a\code\XGMapLinkArcgisExtension.dll

Your edits could not be saved
Error HRESULT E-FAIL has been returned from a call to a COM component.
Would you like to keep the temporary edit layers in the map?

The problem is due to a fault with the MS Access database that forms the geodatabase, which means after a feature has been moved or deleted the database tries to reuse that ID value and this upsets the autonumber process, or so we believe.

At present we do not have a solution to this problem but we do have a bit of code that can be used to reset the autonumber values for any chosen field. This code can be used as a function and bundled in an mdb with a macro that runs the function and sent out to clients as needed. The code for the functions is as follows (this was originally Hartmut's code but I modified from the a sub to a function adding the return and msgbox, just so I could see that something was happening):

Public Function ResetAutoNumber(ByVal tableName As String, ByVal autoNumberColumnName As String)
    Dim maxAutoNumber As Long
    maxAutoNumber = DMax(autoNumberColumnName, tableName) + 1
    DoCmd.RunSQL "ALTER TABLE " & tableName & " ALTER COLUMN " & autoNumberColumnName & " COUNTER(" & maxAutoNumber & ",1)"
    ResetAutoNumber = True
    MsgBox tableName + " done!"
End Function

And macro simple needs to have a number of RunCode lines that call this function for each of the layers in the personal geodatabase, like so:

RunCode

       Function Name ResetAutoNumber ("Mon_point", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_poly", "OBJECTID")

RunCode

       Function Name ResetAutoNumber ("Mon_line", "OBJECTID")

• Add a new field to the feature class called, for example, VertexCount
• Use the Calculate Field tool within the attribute table, or in ArcToolbox, with the following Python expression - !shape!.pointcount
• Sort the feature class on the VertexCount field to find the worst offenders. If there is any bad geometry the expression may trip up, which is informative in itself.
• You can now select only the most complex features for generalising.

See the following article for more information. I have not tried the dicing method that is discussed later in the article: http://blogs.esri.com/esri/arcgis/2010/07/23/dicing-godzillas-features-with-too-many-vertices/

Basically the user was complaining that they could not turn layers on and off in the Table of Contents (the left hand pane) but they could go in to the properties of the layer and turn things on and off from there, but this was very frustrating and tedious. They had tried multiple MXD files, both their own and those created by other users and still no joy, and other people had opened their MXDs and they were fine.

What the problem actually came down, after some serious google-ling,  to was the fact that the user was using two monitors and had them set-up with the right monitor as the primary and the left as the secondary. Once I got them to switch them around, i.e. the left monitor as the primary display, everything worked fine.

An odd a very obscure issue, which I thought I share in case anybody every encounters it again. 

To turn the snapping off you need to first turn on the Snapping toolbar, you can then use the Snapping menu and un-tick the ‘use snapping’ option.

To revert to ‘classic snapping’ select ‘Options’ from the Editor menu, on the Editor toolbar, and tick the ‘use classic snapping’ option on the ‘General’ tab.

On this same tab you can also turn off the ‘mini toolbar’ that follows you around whilst editing, which some people find annoying.

1. setting up and accessing SQL Azure databases
2. using SQL Azure with ArcGIS + ArcSquirrel and MapLink
3. using SQL Azure with Quantum GIS and MapServer.

Azure Management Portal

Although I’ve never set up any cloud services before (other than Arvixe web hosting) it was incredibly easy to setup a trial SQL Azure subscription (“Windows Azure Platform Introductory Special”), create a database, and add some firewall rules to control access. I didn’t dig deep, but the “Management Portal” seems nice and simple:

It helpfully tells you your connection string for ADO.Net, ODBC and PHP (passwords not included!):

See this page for details of the Windows Azure Platform Introductory Special including what you get for no charge and the pricing for the rest.

MapLink, ArcSquirrel and Azure

Hartmut connected up our MapLink to the database and pushed some spatial data into my Azure database from ArcGIS – 20,000 polygons took about 75 minutes.  Hartmut had the layers drawing in ArcMAP, performing as well as in a normal SQL Server instance apparently, with only minor mods to our code to make the initial connection. However editing will not work without some more tweaks - MapLink and ArcSquirrel rely on some SQL metadata that is a little different on Azure.

SQL Server Management Studio and MS Access

Connecting with SSMS is simple, however you get reduced functionality – for example you cannot right-click > edit top 200 records or anything, so quick edits of a small table need to be done with SQL or in MS Access or some other client. Fortunately connecting with MS Access (ODBC + SQL Native Client 10) is also simple, and  the Azure database behaves just like a local SQL instance – remarkable querying speed (all things considered), though slower inserts (10,000 records took something like 15 minutes). NB you cannot directly link a spatial table into Access because it doesn’t recognize the geometry data type; but to update attributes you can link a view.

Also, in SSMS you get no designer tools, e.g. for creating a view; everything has to be done with T-SQL.

SQL Azure Database Manager

After writing the above I found the database manager in the Azure control panel, which has functionality similar to SSMS, including the ability to edit the tables*:

* spatial data types not supported for view or edit.

You get a designer for creating/modifying tables, but views and stored procedures are T-SQL only.

MapServer, OGR and Azure

Next I wanted to try serving the data through MapServer, and viewing/editing the data in Quantum GIS or MapInfo.  MapServer and QGIS require the “geometry_columns” and “spatial_ref_sys” tables, and the easiest way to create these is to push some data in with ogr2ogr.

So I tried this from one of our London servers. I loaded a few hundred linear features, but got four errors like “INSERT command for new feature failed… incorrect syntax…”. On checking the results, a) while the geometries had loaded fine, all the attribute fields were NULL, and b) the spatial_ref_sys table was empty.  I had four attribute fields, so my guess is that each syntax error was one field update across all records using a join on the incoming data.

Next I hooked up a MapServer WMS to my SQL Azure tables. MapServer had no trouble connecting to my Azure tables, but initially wouldn’t draw anything…  on investigating I found that ogr2ogr had not correctly set the SRID of the geometries, so I had to update them “manually” with:

update esdm_waters set ogr_geometry.STSrid = 4326

(which, incidentally, took 1 minute 52 seconds for 788 rows, demonstrating that updating an Azure database ain’t quick).

Clearly OGR needs a little work to do on SQL Azure support, but this was enough to get me going.

The WMS worked, so to challenge it a little more I set up another WMS onto a table with about 8000 sub-tidal habitat polygons, showing red in this OpenLayers map:

And here showing the rivers WMS as well (blue lines):

Performance of these two layers was perfectly acceptable, though actually about 100% slower than the same WMS working from a local SQL Server 2008 Express instance. It would be interesting to see what it could do with MapServer also running on Azure.

I also tried a WFS – again, performance was acceptable though significantly slower than running with a local SQL instance (though I didn’t do strict comparisons).

Quantum GIS and SQL Azure

I found this really helpful blog post to get started: http://woostuff.wordpress.com/2011/03/13/opening-ms-sql-spatial-in-qgis/

Adding the layers requires some python script, but I’m sure someone will write a GUI plugin for QGIS soon.

First make sure you are running a version of QGIS that has GDAL/OGR 1.8 libraries – I’m on 1.7.0 which is OK.

Open the Plugins menu > Python Console

and type in these two commands (modified for your data):

uri = "MSSQL:server={serverName};database={databaseName};tables={tableName};trusted_connection=yes"
qgis.utils.iface.addVectorLayer(uri,'{yourLayerNameHere}','ogr')

In my case:

uri = "MSSQL:server=tcp:a0awtn7gg9.database.windows.net;database=ArcSquirrel1;tables=esdm_waters;Uid=exegesis@a0awtn7gg9;Pwd=myPassword;"
qgis.utils.iface.addVectorLayer(uri,'Rivers','ogr')

Without any fuss my rivers dataset appeared:

And I can create and edit features and their attributes, here adding a test line off the Hebrides:

And to prove it, here’s my new feature in OpenLayers…

And finally, here we have the habitat polygons (EPSG:27700) colour coded by habitat type, as well as the rivers (EPSG:4326), in QGIS. Polygon editing also worked fine, once I’d worked out why my new polygons kept vanishing (doh, classified symbols).

Lots to think about, and I look forward to editing the same data with ArcGIS and ArcSquirrel soon…

• If you have our ArcGIS Maplink product (or ArcSquirrel) then loading data in using this into a temporary SQL table is probably best as it will do a reasonable amount of checking.
• MapInfo has its own SQL loader tool ("Easy Loader" - find it in the Tool Manager)
• If you are just using open source you can use OGR2OGR

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=MyServer;database=MyDatabase;Trusted_Connection=yes;" "C:\Temp\MyShapeFile.shp" -a_srs "EPSG:27700"

Once the data is in SQL there can still be issues:

Incorrect SRID

• Make sure you specified the correct SRID when the data was loaded (if you didn’t specify it – it may well be 0). You can check this with the SQL: SELECT geom.STSrid from SitesGeom Where geom.STSrid <> 27700

Invalid ring

• You may also have invalid ring geometries – a good post on this is http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx but the key SQL command is:

UPDATE foo SET geom = geom.STUnion(geom.STStartPoint());

Invalid geometries

UPDATE foo SET geom = geom.MakeValid()

SRID not imported

Import geometry data into temp SQL table (e.g. foo) – with tempGeom column

Then try to populate a geometry column from the WKB of the validated column:

ALTER TABLE foo ADD geomFinal geometry;

SET geomFinal = geometry::STGeomFromWKB(geomTemp.STAsBinary(), 27700)

http://www.ordnancesurvey.co.uk/oswebsite/products/geo-referencing.html

These are available as *.tfw "world files" for ArcGIS^®, or *.tab files for MapInfo Professional^®