Processing 2BT reported points

[nesdolya]

One of the first processing steps required for the 2BT spatial data processing workflow is 'spatializing' the latitude and longitude reported in the aspatial data. I have a hardcoded script that takes in an excel/csv and generates three spatial layers in a GDB: (1) lat/lon points in WGS84 (4326), (2) lat/lon points reprojected to Canada Albers Equal Area (102001), & (3) these points buffered to a circle that represents the reported planting area. Output 2 and 3 need to be added to the PostgreSQL DB.

Please utilize the existing script/tool and modify it (or recreate it) to ensure that these output are correctly stored in the PostgreSQL DB. And, modify the PostgreSQL schema to include mechanisms for version control of the site_points and site_buffered_points tables, specified below.

The most recent version of the existing tool is located here: \vic-fas1\projects_a\2BT\02_Tools\spatial_data_processing_workflow\create_buffered_points_2023DRAFT_v2.py Additional ArcPy class scripts used in the above tool are located: \vic-fas1\projects_a\2BT\02_Tools\spatialArcpyUtils

Checking for updates/changes to the existing data can either be done through Python or PostgreSQL trigger functions - whichever you are more familiar with.

Processing Steps:

1. User provides aspatial 2BT data via Excel through command-line argument
2. User provides .ini file for DB connection as command-line argument
3. Tool reads required fields from Excel and ensures they are in the correct format/clean
4. Any null geometry is dropped (empty lat/lon fields)
5. Either use ArcPy (see scripts) or GeoPandas to generate the site_points and site_buffered_points and store in PostgreSQL DB (see below)

GeoPandas (GPD) Implementation (will likely be simpler b/c no intermediate datasets need to be stored locally):

1. Create GPD dataframe using the lat/lon fields as the geometry in WGS84 projection (EPSG: 4326)
2. [DEBUGGING ONLY] Store as shapefile locally
3. Reproject GPD to Canada Albers Equal Area projection (ESRI: 102001)
4. Store reprojected points to the site_points table in the PostgreSQL database, consider: 4.a. If a point already exists in site_points then compare the geometry. 4.a. If for a given SiteID the geometry is identical then do not commit 4.b. If for a given SiteID the geometry is not-identical then set dropped to True for that SiteID and commit new point to site_points table
5. Using the reported site size derive the radius of a circle
6. Buffer the GPD reprojected points by their given radius
7. [DEBUGGING ONLY] Store as shapefile locally
8. Store buffered points to the site_buffered_points table in the PostgreSQL DB, consider: 8.a. If a point already exists in the site_buffered_points table, then do not commit 8.b. If for a given SiteID the geometry is identical and the site size/buffer radius is identical = do not commit 8.c. If for a given SiteID the geometry is identical and the site size/buffer radius is not identical = set old record to dropped and commit new record 8.d. If for a given SiteID the geometry is not identical = set old record to dropped and commit new record

Requirements:

• Command line interface for user parameters
• Can chain/integrate into Python processing scripts and overall workflow (called from another Python Script)
• Extensible and modular – consider flexiblity for multiple uses and upcoming processing steps (e.g. reading .ini for DB connection, just recreating the buffered points and not the OG points)
• Accepts either excel/csv (but most likely excel used)
• Generates a single log file that contains the tool's version, the parameters passed to the tool, and any warnings or errors in a user-specified location

PostgreSQL Schema modifications required:

• [x] Copy the site_points and site_buffered_points tables from the production schema to the development schema
• [x] Ensure each of these table's created by/at and edited by/at are functioning as expected (if you need the sql for these please let me know)
• [x] Ensure these tables include a 'dropped' column
• [x] Create valid_points and valid_buffered_points view similar to the valid_geometry view, so analysts do not see the 'dropped' records

[AnthonyRodway]

Changes have been made to the branch: https://github.com/cat-cfs/twobilliontoolkit/tree/BufferBuilder-Processing-Reported-Points

All requirements have been completed and I have tried to catch some edge cases while updating/inserting into the database so hopefully didn't miss any!