Reading ODBC connection using QGIS? [closed]

Question

I have a co-worker who manages a large MS Access database that contains X & Y fields for various locations he tracks. We have previously exported the data to CSV and used the QGIS Delimited Text Plugin to plot the locations.

We would now like to be plot this data directly in QGIS by connecting to the MS Access db via an ODBC connection (as opposed to export to CSV).

1. Can QGIS consume the data from the ODBC connection to plot the X-Y locations?
2. Can it be "live linked" to the ODBC connection so that when a project is re-opened, the layer "replots" all old and newly added locations dynamically (as opposed to exporting to shapefile, which would create a snapshot in time)?
3. Can you create tabular ODBC connections that can be joined to spatial tables within QGIS?

You can do all of this in ArcGIS ("Add XY Data" tool), so it would be great if this capability existed in QGIS. I just cannot find anywhere that mentions its existence.

I am not planning to move to a spatial database. As a GIS person myself, yes I know that it makes sense to move to a spatial database, and yes I have used PostGIS to manage this kind of data. But these are not options right now. The user is not a GIS person nor wants to be, and he manages his own database in MS Access and that will not change. I am and am pulling him kicking and screaming into the GIS world and want to make it easy as possible. He can live with exporting to CSV, I was just hoping there is a better solution USING ODBC FOR MS ACCESS and displaying the XY data in QGIS.

Answer 1

With the new "Processing" tools available in QGIS 2, this becomes a bit easier. While I have still not found a way to "live-link" the data (so that if you change the values in the MS Access table, the point moves automatically), this method seems to work pretty well.

Credit goes to "Christina" and "Bernd" in the comments section of this page (and of course @underdark for the blog) for how to create the ODBC connection to your MS Access database:

Create an ODBC Connection:

For Win7, 32 bit: Go to control panel/administrtive tools/data sources (ODBC)

For Win7, 64 bit: Instead of using the default “control panel/administrative tools/data sources (ODBC)”-way, with 64bit versions, you have to use C:\Windows\SysWOW64\odbcad32.exe !

2. Click on the user DSN tab and then ‘add’
3. Scroll down to Microsoft Access Driver (*mdb) and click ‘finish’
4. Give the connection a name then choose ‘select’ to browse to the location of the database, then click ‘ok’ to exit the dialogue and again to close the dsn window.

Load the layer into QGIS:

5. Go to add vector layer and select database option.
6. Ensure the database ‘type’ box says ‘ODBC’ and click ‘new’
7. In the name box type a name for connection
8. The host is localhost
9. The database name is the name of the dsn you created in step 4
10. All other boxes should be blank but you can tick ‘save password’ box.
11. Test the connection and make a note of any errors. When successful, click ‘ok’ to return to the ‘add vector layer’ dialogue
12. Click ‘open’ to open this connection, and if asked for password, click ‘ ok’. Choose your layer (table from database), bearing in mind it may not have any geometry.

Convert the XY data to Points:

13. With the table in the TOC, go to "Processing"--> Toolbox--> Geoalgorithms--> Vector--> Creation--> "Points layer from table".
14. Fill out the form as seen below:

Answer 2

Caveat: I've not tried this, so I'm willing to bow to anyone with direct experience.

You can connect to an ODBC datasource in the "add vector layer" dialog, just select "database" as the source type and ODBC as the database type. QGIS uses the ODBC driver for OGR, so the same caveats will apply - that is you will need a table called GEOMETRY_COLUMNS and so on, so your colleague will probably need to modify the database.

I've not used Access since shortly after the release of version 1.0, but as it is not spatially enabled, you will probably find performance to be an issue. Presumably you're not going to just drop Access and start using a spatially enabled database, because that's unlikely to be economically viable, but if your co-worker ever feels the urge for change, I can strongly recommend going over to something a bit more powerful.

Answer 3

While I am still interested in hearing if there are other options available, I did come across a message post that mentioned this was possible using the eVis Plugin.

The documentation describes in the Database Connection section how you can connect to an MS Access or ODBC connection to create XY point locations from the database, without creating a static snapshot. I simply typed "select * from " and eVis popped up a dialog box that asks for the layer name and X-Y fields.

This unfortunately doesn't appear to create a "live-linked" connection to the MS Access db because the next time the project opens, QGIS is confused ("unable to open one or more project layers"). The solution to this, though, is to create an predefined XML query that the user can select.

So while not a perfect solution, this is one step better than exporting to CSV. In the future I think it would be great to see the Delimited Text plugin expanded to accept ODBC connections for this purpose.

Answer 4

an alternative approach would be to move the data to postgis. you can read the csv-file directly with the postgresql copy-command. then you can create points fom the xy-fields.

then if you want to use some reports or other funtionality in access you can connect to postgis from access.

the benefit would be that you put the spatial data where it belongs, in a spatial database and then use odbc back to access to handle the nonspatial data if you for some reason still want to see your data in access.

there is great tutorials for postgis/postgresql to access connection in postgresonline.com. tutorials about impöorting to postgis/postgresql you can find by the same authors at bostongis.org

/Nicklas

Answer 5

There are several conversion products which can convert ms-access data to postgres or SQLite and back again. The move the data very quickly from one side to the other. Using one of these applications may give you the option to run QGIS on a snapshot of the data.