Export a Riverscapes Compliant Riverscapes Project from FM

[joewheaton]

We should make sure that you have a script that allows creation/population of a riverscapes project from the FileMaker App or by reading the database, and that you have the same or similar function that can update that project when new stuff is added to filemaker database (e.g. revise design, implementation, maintenance and/or monitoring).

You already (in app) show how to export:

• Photo Points
• MAterial Lists
• Fill Estimates
• Metric Summaries
• etc. into things like photo files (w/ metadata), csv tables, PDFs, etc. So the goal is to adapt the basic folder structure of:
• Project Parent
  • Inputs
  • Intermediates
  • Analyses or Outputs and adapt it to have multiple realizations of those non filemaker *.fmp12 database files have homes. This will include (likely):
• [ ] Populating and defining a rs_project.xml file to catalog everything that gets created and added to project with metadata
• [ ] Saving a copy of FM12 database somewhere (likely in Inputs\FMdb\
• [ ] Potentially refomatting and converting the FM12 database into something else (e.g. SQLlite)
• [ ] Exporting all the spatial information (e.g. structure locations, reach boundaries, photo points, etc.) into shapefiles
• [ ] Exporting all photos as geo-tagged photos accessible in folder structure (instead of only buried in database)
• [ ] Defining business logic for RAVE so that all these things are visible and the geospatial stuff is added to map using Anabranch data symbology standards.

[nick4rivers]

@joewheaton - Ideally this shouldn't be that hard, fm even has some built in export data as .xml functions that might help. I'll try to get to this soonish.

[philipbaileynar]

@nick4rivers and @joewheaton I would like to quickly discuss where this is all going and the best way to move the FM app forward. There are lots of options:

1. FM app creates a riverscapes project XML file that references the FM database:
   1. The project XML file could actually store all the database contents as text within the XML.
   2. The project folder structure could contain a copy of the database contents in something more open source and portable (either SQLite or JSON).
2. The FM pro exports the data to a centralized database (postgreSQL with PostGIS provides some cool web mapping opportunities)
   1. The database schema could mirror that of the FM database (aka an "explicit schema") that is most powerful for queries and an API but is brittle if the schema is still changing.
   2. The schema could be simplified with a single record for each FM database and the contents of each FM database stored in a single column as JSON. This wouldn't support useful queries but it would be robust for future db schema changes and would be the quickest way to deliver some kind of data synchronization between the databases on iPads).

There are advantages and disadvantages to both approaches. Option 1 is superficially simpler but feels odd to take a well designed database and flatten it to a text file (either project XML or JSON). Option 2 is more powerful but moves towards a centralized web viewer with mapping capabilities and side-steps the riverscapes warehouse initiative.

So I feel like I need to know more about the objectives and future direction of the app before I can recommend a path forward.

[nick4rivers]

@philipbaileynar - here's the manual that compliments the app: https://ecologicalresearchinc.box.com/s/efoew37h21dml0zk8vfd6gaixdz5w9xa

[philipbaileynar]

There will be a riverscapes data warehouse that stores the File Maker pro databases. Each database will be stored as a riverscapes project (see below). This will be a one-way-trip, meaning that Anabranch employees can export a riverscapes project from the LTPBR app and then upload it into the data warehouse. The project will include the File Maker Pro database as well as all the data stored as plain text and the photos. Anyone, non app users included, will be able to browse the data warehouse and retrieve part or all of the riverscapes project. i.e. a user might just download the photos. Or they might download all the data as plain text and write some kind of script to generate a report summarizing all projects in the warehouse.

We will not build any kind of central database to combine and store LTPBR app databases at this time. The app is still in flux and the cost to build such a database requires substantial funding that is yet to be secured.

LTPBR App Riverscapes Projects

The following is a proposed folder structure for the LTPBR riverscapes project:

data/ltpbr.json
data/features.geojson
data/ltpbr.flmk
photos/2019_10_28_image_xxxx.jpg
photos/2020_01_01_image_xxxx.jpg
reports/structures_report.pdf
reports/reaches_report.pdf
reports/photo_points.pdf
project.rs.xml

• ltpbr.json this is a single JSON file that contains all the tables and data within the File Maker pro database. Don't worry if it's big! Don't worry about the structure! Just dump ALL the data in one file.
• features.geojson this is a single GeoJSON file with all the spatial data stored as separate "feature collections". It also includes any attributes that you might want to use to symbolize or label features in GIS. We can discuss the separate collections but the goal is that when you drag and drop this file into QGIS it will ask you which collection do you want to add? Structures, Reaches, Complexes or all of them etc.
• ltpbr.flmk this is the original File Maker Pro database file
• photos dump all the photos from the database into a single folder. Use their original file names that are refered to in the ltpbr.json data file.
• reports put all the separate report PDFs into a single folder. Each file should be referenced in the project.rs.xml riverscapes project file. No code will ever process these PDF files.
• project.rs.xml this is the riverscapes project XML file. When the time comes we can discuss the structure of this file. At a minimum it will have:
  • relative paths to ltpbr.json, features.geojson, ltpbr.flmk.
  • A series of metadata describing which warehouse and project it refers to.
  • Metrics summarizing the project (total number of structures, river miles treated etc).

Note that the file names should be static and predictable. File names referenced in the project.rs.xml should not have variable information in them (such as the date or project name etc), especially the ltpbr.json and features.geojson files, both of which could potentially be scraped by other processes in the cloud. Photos and reports can have variable names.