2

The background to this question comes from a specific requirement I have in trying manage borehole (point) data.

Currently I have an MSAccess database which holds borehole information, the key data being borehole name, x, y, z, and total depth. My desire at the moment is to be able to have the boreholes in the database link directly to QGIS so I don't have to export weekly CSV's from Access to update the current borehole shapefile, or create a new shapefile.

I would just like the link to exist so that when I add a new borehole in the database, it will add into my mapping workspace also.

As I have searched for ways to 'live' link to my MSAccess datasheet however I keep finding references to using a spatial database for my data. What I don't understand is how a spatial database, like PostGIS which I see mentioned a lot, practically varies from my current database.

Does a spatial database such as PostGIS hold table data with z,y,z, and other attributes, or does it hold the actual spatial files, i.e. shp files.

If it does hold the actual files then how does that work in with the folder structure I currently use to store my GIS data?

I have spent the last couple of weeks searching and reading but I fear there is some logic gap I am just not making to put together how these pieces all relate and are used effectively.

Improve this question
1
  • One question per Question, please. GIS SE uses a "focused question / best answer" model. I would suggest that even one week is too long to research this topic, when it only takes minutes to install and a day or two to learn how to use PostGIS in PostgreSQL.
    – Vince
    Commented Nov 24, 2016 at 3:55

2 Answers 2

Reset to default
3

A spatial database doesn't hold shapefiles per se. However, it does store geometry associated with a feature/row. In your case, the boreholes would be stored as points with an x,y,z coordinate. It's preferable to store this information as points rather than as attributes of a record in a non-spatially enabled table. However, attributes can be populated with the coordinates of the point.

Having said that, it is possible to spatially enable an access database. This is exactly what ESRI did with personal geodatabases. However, they are very limited in their capabilities and ESRI replaced them with file geodatabases. See this post.

PostGIS is a more powerful database than MSAccess. It's a fully fledged multi-user database with spatial tools available.

What if you wanted to find out how many boreholes were within a certain distance of a toxic spill source? Which wells were they? Which properties are they on? You couldn't answer these questions with an access table that had no spatial component to it.

EDIT: Looking back on my post I'm not sure I have answered your question. The spatial database holds attributes as well as geometry (sometimes as an attribute).

Improve this answer
4
  • Thank you for your comments @Fezter. You have definitely answered part of my question. My concern as I get my head around this more is does a PostGIS DB operate the same as my Access DB? My current DB has a number of tables with referential links and input validations. Can I retain all this structure and data in a PostGIS databse? Also I'm considering your oil spill example. How does the hole data relate to the spill, does the spill data need to exist in my hole database or can it just be a spatial layer in qgis?
    – Ben
    Commented Nov 24, 2016 at 3:50
  • @Ben, yes, a PostGIS database can have relationships with other tables and can be restricted to input validation as an Access DB does. And no, the spill data wouldn't need to exist in your database. It could exist in a separate data source. QGIS can work with multiple sources. However, I feel that that is worthy of a separate question.
    – Fezter
    Commented Nov 24, 2016 at 3:54
  • I have a whole Q&A worth of questions, half of which I haven't even figured out how to ask yet. I don't to bug you too much though. I appreciate your responding. If you can point me in the way of any good basic explanations of spatial databases and there use or implementation would be appreciated.
    – Ben
    Commented Nov 24, 2016 at 4:05
  • en.wikipedia.org/wiki/Spatial_database
    – Fezter
    Commented Nov 24, 2016 at 4:07
1

This answer doesn't directly address the issue of whether you need a spatially-enabled database, but does address this:

I would just like the link to exist so that when I add a new borehole in the database, it will add into my mapping workspace also

If you add a connection directly from MS Access to QGIS, then any new boreholes added to the Access database would be seen in QGIS without the need to export to CSV or shapefile.

See Connecting QGIS to query on MS Access database? and its answer for instructions on how to do this.

Improve this answer
3
  • Thanks @Stehen Lead. I actually was in the process of following those instructions when I stopped to ask this question. The issue for me at the moment isn't so much how to link Access, as to whether I should or if there is a better way to go about this process with a spatial database.
    – Ben
    Commented Nov 24, 2016 at 3:53
  • If you only need to see boreholes, Access will probably suffice. But for "serious" GIS-type questions, like the ones @fetzer mentioned above, a spatially enabled database is probably better. Once you've gone GIS you'll never go back ;)
    – Stephen Lead
    Commented Nov 24, 2016 at 3:55
  • I think this is now where I see that there might be a benefit in going to a spatial DB. While at the moment I am just looking for boreholes, I store all the seam data for those boreholes linked in the Access DB. The ability to visually display a query/search for holes in an area with a certain seam in them is tempting, assuming I am interpreting all this right in that using QGIS and a spatial database would indeed allow me to do that.
    – Ben
    Commented Nov 24, 2016 at 4:01

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