maptastik

-- ABOUT -----------------------------------------------------------------------
-- This script is meant to outline a procedure for updating a PostgreSQL table
-- with the latest features even if there are views that depend on the table.
-- In other words, this method allows you to update the table without deleting
-- it and recreating it with the latest data. There are definitely some
-- opportunities for improvement - there may be a bit too much redundancy - but
-- this should get the job done, especially on datasets where size is trivial.
--------------------------------------------------------------------------------

-- NOTES -----------------------------------------------------------------------

maptastik

rem If you want to open CMD and see your script run
@echo 

rem Call activate.bat located for your conda installation
call C:\Users\some_user\AppData\Local\Continuum\Miniconda3\Scripts\activate.bat

rem Activate your environment, change directory to location of your script, run script.
activate some_environment && cd C:\Users\some_user\python_script_directory && python python_script.py

rem I can't remember why I added this...You might not need it.

maptastik

var y = (2 * Atan(Exp(Geometry($feature).y / 6378137)) - PI / 2) / (PI / 180)

var x = Geometry($feature).x / (PI / 180) / 6378137

return "https://edits.nationalmap.gov/tnmcorps/?loc=" + y +"," + x + ",16"

maptastik

maptastik

-- See https://stackoverflow.com/a/15748896/3163905

-- Create a new sequence
CREATE sequence <column_id_seq>;
-- Alter the field you're converting to an auto-incrementing field be setting a default value based on a sequence
ALTER TABLE <table_name> alter <id_column> SET DEFAULT nextval(<'id_column_seq'>);
-- Set the current max value of the id column as the starting point for the sequence
SELECT setval(<'id_column_seq'>, <max_id_column_value> );

maptastik

maptastik

maptastik

var polygon = FeatureSetByName($map, 'Polygon Layer Name', ['Field1', 'Field2', 'Field3'])
var polygonUnderFeatureInfo = Intersects(polyogn, $feature) // 
return First(Intersects(parcels, $feature))

maptastik

# max = max FACILITYID value in dataset. This can be calculated with a function
with arcpy.da.UpdateCursor('feature_class', ['FACILITYID'], "FACILITYID IS NULL") as cursor:
    for row in cursor:
        new_facilityid_number = max + 1 
        row[0] = str(new_facilityid_number)
        max = new_facilityid_number
        cursor.updateRow(row)

maptastik

It is sort of tricky to run spatial queries against tables in a GeoPackage anywhere, but especially outside of QGIS. This is because we're reliant on Spatialite to carry out those queries and Spatialite is just not well supported. A real shame!

Luckily there are a few resources for helping you get up and going with installing Spatialite for working with GeoPackages in a SQL client like DB Browser.

There is a page in the DB Browser Wik on GitHub called " SpatiaLite on Windows" and it is dedicated to installing Spatialite for use with DB Browser. Note that there are special instructions for a Windows 10 issue.

Bryan McBride at Spatial Networks wrote up a nice piece on working with GeoPackages in DB Browser called "Working with Geospatial Data: An Introduction". It's got a nice walk-thru of how to use DB Browser with GeoPackage as well as some general installation instructions.