1

I know that it is possible to link databases from Access to QGIS via the ODBC Data Source administrator.

I am curious if it would be possible to create a python code/plugin that would allow the user to do the following:

  1. Search for a specific piece of data
  2. Create a buffer of x amount of distance from that one point
  3. Select the data that intersect with that buffer

( I am sure the first 3 steps in this are possible)

  1. Take the selected data and use it to spit out a report in Microsoft access via the ODBC Data Source Administrator.

Any thoughts?

Improve this question
3
  • for 2), what about it defines it as a 'spatial point', versus a non-spatial record in Access (which doesn't support geometry). In theory, if a record had a lat/lon, there are python libraries that can operate on that lat/lon, turn it into a point, and intersect it with another piece of spatial data outside of Access... re. reporting, not sure about that one... – DPSSpatial_BoycottingGISSE Jan 23 '20 at 17:54
  • Thank you! I appreciate your answer. – jpw173 Jan 23 '20 at 18:17
  • Also note that qgis has the ability to create report – J.R Jan 23 '20 at 23:33
1

I expect it should be possible, but I suspect it is overkill for what you are describing. Adding QGIS to your workflow is beneficial when you either need to create quality visual maps, or when QGIS/PyQGIS is a useful coordinator for a more complex geospatial workflow.

It's not 100% clear from your question, but it sounds as if your core, i.e. master, platform is Access. So I would stay there and not bring QGIS into the picture.

  1. You could access SpatiaLite extensions in SQLite, for instance ST_Buffer(), to do the buffering and selection, and communicate with that from Access via the SQLite ODBC driver (see Loading Spatialite extension via SQLite3 ODBC Driver? for an old question on that here, may be easier now, not something I've tried).

  2. If (as your question implies) you are only interested in [data]points that lie in a buffer around a specific geographic point, using any spatial platform may be overkill. Whatever your CRS is, distance is going to be approximately of the form SQRT(a*(x1-x2)^2+b*(y1-y2)^2), with a and b determined by the CRS and your location. So you could probably do your point selection within Access without having to involve multiple platforms.

  3. If the rest of your workflow is in QGIS, and you merely want to run final reports in Access, I would still try to avoid potentially fragile ODBC connections, and instead do your selection, buffering, intersection in QGIS (including processing algorithms and PyQGIS etc). Then export a table with whatever your Access report needs as a csv, and then import that into Access as part of the reporting run.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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