1

I’m working on a database update, at the moment this database is simply an excel spreadsheet not formatted to be used in a GIS project. The database has to be updated and modified over time. Some fields will be added along the way, some entries too. These operations will be done by different people not familiar with GIS. In the end the database have to be associated with polygons in existing shapefiles.

I would like to limit the number of manipulations between the modifiable database and the GIS software. So exporting a new .CSV every time a modification is done, is not what I’m looking for.

I would like to know how (or in what “software”) to setup the database so it would be easy to modify it for non-GIS users (like in Google Spreadsheet) and use it at the same time in QGIS or ArcGIS to link its data to existing shapefiles.

Google Spreadsheet is easily modifiable and shareable, if there was a way to link it with QGIS or ArcGIS so it would constantly update, it would be perfect. But if anyone now another way to proceed, I’m listening!

closed as too broad by PolyGeo Feb 1 '17 at 20:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You can write a python script to get/update the DB with the a hard-coded path of a .csv(the one that is constantly) be updated. You can then schedule this script to run every hour, 20 minutes, every day etc... whatever your preference is. Now the trick would be getting this set .csv file accessible to your data editors and still have it accessible by your script. – NULL.Dude Feb 1 '17 at 18:47
3

We use PostgreSQL with the PostGIS extension to manage non-spatial and spatial data.

When spatial data has to be updated, obviously we use QGIS.

When non-spatial data has to be updated, we used to use Excel and load the excel file into PostgreSQL - until we found out about LibreOffice Base (database) and Calc (spreadsheet) applications. Now any non-spatial data gets updated through either the LibreOffice Base database application, or through a data-connected Calc (spreadsheet) - both of which connect directly to PostgreSQL.

So now we have both views and materialized views in our database that dynamically link spatial and non-spatial database tables together that are consumed by both QGIS, GeoServer, and other enterprise applications.

In terms of user experience, any user who is making updates can be given access to LibreOffice (free) and set up with sheets that consume the data they update. When they hit 'save', the data goes into the database tables. And of course they are connecting via PostgreSQL user accounts that control what access they have to each table.

  • Do you have any ressources to help me create a DB in LibreOffice Base database or a data-connected Calc, and connect it to PostgreSQL? – Rashomon Feb 1 '17 at 19:35
  • @Rashomon do you have PostgreSQL set up already? – DPSSpatial Feb 1 '17 at 19:40
  • No, I've never worked with it. But I would like to learn. – Rashomon Feb 2 '17 at 20:09

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