search

waterthetrees / wtt_db

waterthetrees.com local dev database
Creative Commons Zero v1.0 Universal
0 stars 0 forks source link

Merge trees to database #48

Open tzinckgraf opened 1 year ago

tzinckgraf commented 1 year ago

This is the first iteration for keeping the tree data in the database.

This PR introduces two new tables, trees and trees_staging. The trees table would ideally replace the treedata table. The treedata table is currently a mix of time based data and reference data. There was a discussion around normalizing it further. However, we can also use the original treedata table instead of the proposed tree table with minimal updates. There is a migration file that shows how the data is interchangeable between those two. The trees_staging table is a unlogged table. This is similar to a temporary table in that there is no write to the WAL. It differs in that the data is global. This means you can write with one connection and then use that data with another. A temporary table would need to maintain data and operations on the same connection, which is a problem when using something like ogr2ogr for writing data. There is also a stored procedure that merges the two tables. This does the full upsert logic in three parts using a temporary table. The procedure is idempotent.

zoobot commented 1 year ago

HI @tzinckgraf Are you interested in working on this anymore or should I un-assign?