Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Thanks.

CLARIFICATION: I appreciate the suggestions to move to a spatial database, but that really isn't what I am after. 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... 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.

share|improve this question
    
Then to my knowledge, you cannot do as you ask with QGIS. I agree there would be some use in an ODBC equivalent of CSV import, but for any sort of performance it would have to be an import rather than a dynamic connection - just as the CSV plugin is. So there isn't likely to be any uptake unless you support/develop it yourself. –  MerseyViking May 9 '11 at 19:16
    
@MV- I generally agree with you & see your points. However, for non-gis users who are primarily interested in simply mapping their data and not analyzing data, performance of QGIS would not be as significant of an issue. I would imagine having a dynamic, "always live" connection to their data would almost certainly outweigh the performance concerns (again thinking of this from a non-GIS users perspective). –  RyanDalton May 9 '11 at 20:25
add comment

5 Answers

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.

share|improve this answer
    
@MV- This is good information to have. I did not realize you could add tabular data through the "add vector layer" option. You are right, though, because it is not spatially enabled, you still would have to somehow export to a spatial format. And you're right, the user isn't going to switch to a spatially enabled db because they are neither a GIS or a database. They have just enough Access training to be dangerous... –  RyanDalton May 9 '11 at 20:50
add comment

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.

share|improve this answer
    
Ah! That's really useful to know. It involves some hoops, but at least it should be a one-off set up. Will it reload the data if you change the view in QGIS? Or does it just cache it until you manually rerun the query? –  MerseyViking May 10 '11 at 10:44
    
@MV- From what I can tell through experimentation, eVis appears to create a cached layer in QGIS. For example, after I loaded the layer with eVis, I deleted one of the records from the Access DB, refreshed QGIS screen, and the "deleted" point persisted. But when I created a new cached layer, the deleted point was not displayed (as expected). Panning and zooming had no effect on the cached layer display. –  RyanDalton May 10 '11 at 15:55
add comment

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

share|improve this answer
add comment
up vote 3 down vote accepted

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 !

  1. Click on the user DSN tab and then ‘add’
  2. Scroll down to Microsoft Access Driver (*mdb) and click ‘finish’
  3. 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:

  1. Go to add vector layer and select database option.
  2. Ensure the database ‘type’ box says ‘ODBC’ and click ‘new’
  3. In the name box type a name for connection
  4. The host is localhost
  5. The database name is the name of the dsn you created in step 4
  6. All other boxes should be blank but you can tick ‘save password’ box.
  7. Test the connection and make a note of any errors. When successful, click ‘ok’ to return to the ‘add vector layer’ dialogue
  8. 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:

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

Points Layer from Table

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.