Getting data out the hard way
Why would we make anything easy for you in this course?.. We are looking at the data we bring out of SMASH in two ways. The way you may most likely bring it in if you are working as a GIS Analyst with no programming experience and one with the ability to look at the data structure of the relational database you are working with and perhaps use your programming skills to automate data flow.
Pull data off your phone
Whether you have an Android or or an iPhone, you will be grabbing the .gpap file from your smash folder on your mobile that your project details. We will work with a gpap file Scott has created for you matching the locations of the posts Matt collected with the RTK data (well not matching precisely but accurately…)
Grab some data from fileshare
You can get the data for this lab from the tutorials for Geog 413 folder on the fileshare site (the usual place) at:
https://fileshare.gis.unbc.ca/index.php/s/NmT5iDW4awTezfp
Go to the SMASH folder and grab the .sqlite file from the folder.
Why do you think it is called .sqlite and why is it the same size as the .gpap file?
NOTE: – grab the gvSig-Desktop file as well as the hydrologis4gvsig_0.3.1.gvspks file. You can also get the gvSig-Desktop file from this site:
http://downloads.gvsig.org/download/gvsig-desktop-testing/dists/2.5.0/builds/2930/gvSIG-desktop-2.5.0-2930-final-win-x86_64.zip
On your home computer – if you have not done so yet – install DB Browser for SQLite and perform the following steps:
Notes Table
- Open the .sqlite file in DB Browser
- Look at the tables – we wan to export a couple
- Open up the notes table the browse data tab
- Check it out – look at the form field – does it look familiar?
- Export the table as a CSV file File –> Export –> export table as CSV (too simple)
Images Table
- Open the photos table
- Check it out
- What do all the fields mean?
- Export it the same as the notes table
- Open the exported file (Excel LibreOffice …)
Imagedata table
- Open the imagedata table
- Check it out
- Click on the thumbnail cell for each row
- Set the datatype in the Edit Database Cell panel for image
- Find the export to file button and export each jpeg with the proper name from the csv file
Pre-config the data in Excel
You can make the data easily usable in QGIS by cleaning it up a bit in Excel
Notes:
- Open the notes.csv file inExcel
- Remove the last two columns
- Select the data in form column
- Data –> text to columns –> Delimited –> choose comma and Other with a colon for its value
- Find the column with the “Post” data in it –> label it as “Description”
- Remove all the other new columns
- Remove the other column called description and text column
- Change the time measurement system form UNIX time
- Insert a column between Description and ts
- In that column put in this formula where “tscellvalue” is the field that has the ts data
=(tscellvalue/86400000)+DATE(1970,1,1)- Format the cell with this custom value
yyyy/mm/dd hh:mm:ss Image data
You can perform the same steps on the images.csv file as well, but for the sake of the tutorial, there is no need to repeat the steps (we will leave that to the assignment…)
Bring your data into QGIS
You can get a point layer from the excel file very easily in QGIS – just like all activities in QGIS!
- Drag in your excel file into QGIS
- check out the attributes – all should be good
- Under the processing tools, find the create points layer from table
- Fill in the panel appropriately and you should see where the posts are for today’s tutorial
- You should now have a pointZ file – check to see if that is the case
Joining the data to create a linkage to your photos
If you brought in both the notes and the image data, you should have two layers with locations of the note and the photos. Notice the datasets have one photo per note. What would happen if there were several photos per note? What would have we needed to do to get all the photos links from the notes.csv file?
We are not going to perform the link today, but we will review this in lecture on Wednesday (again with the relational data management stuff!!)
The easy way to get data off – sort of..
Using the gvSIG/Horton Machine app to export from SMASH
If you downloaded the gvSIG and Horton machine links from above, you should be good to go. As this is a JAVA program, it is simple to run it without installation.
Installing and setting up gvSIG
- Unzip the gvSIG-desktop-..zip file to a folder you wold like to work from – if you get overwrite file requests – yes to all
- Once unzipped, go to that folder and launch the gvsig-desktop.exe executable
- You will get an GIS looking type interface
- Install the conversion plugin (HortonMachine)
- Go to Tools–> Addons manager
- Select the installation source as a file (middle option) and locate the hydrologis4gvsig_0.3.1.gvspks you also downloaded –> next
- scroll through the table of plugins until you fine the grouping starting with “org.hortonmachine” and select all of those plugins –> next –> continue
- Hit finish when done –> exit gvSIG –> restart gvSIG as above
- The canvas should now be geopaprazzi green
Translating a gpap file to layers and photos
If all worked well in the install , you should be able to convert your gpap file easily
- Find the “Add Layer Button”
- Open the GvSIG Mobile/Geopaprazzi tab
- Locate your gpap file – it is the duplicate of the .sqlite file from the downloads on fileshare. You can get away with copying or renaming the file to a .gpap extension
- A separate panel will launch –> Click accept in the main panel
- Close the extra panel and there should be layers in your canvas
Understanding the translation function
If you peruse the attribute table you can see that the software has carried out the heavy lifting you did above. See if you can figure out where the shape files and images are located. Open the layers in QGIS and close off gvSIG.