Skip to content

Instantly share code, notes, and snippets.

Ryan Cooper maptastik

View GitHub Profile
View update_table.sql
-- 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 -----------------------------------------------------------------------
View activate_run.bat
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.
View tnm_edit.arcade
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 / numeric_test_points.geojson
Last active Jul 9, 2019
Plotting data-driven proportional symbol markers with geopandas
View numeric_test_points.geojson
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
View int_to_auto_increment.sql
-- 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 / LineIntersectionsGeopandas.ipynb
Created Apr 4, 2019
Using geopandas to find line intersection points within a dataset
View LineIntersectionsGeopandas.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@maptastik
maptastik / MultipleGeometryGDF.ipynb
Created Apr 3, 2019
Creating and working with multiple geometry columns in a single GeoDataFrame
View MultipleGeometryGDF.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
View intersectingPolygonAttributes.arcade
var polygon = FeatureSetByName($map, 'Polygon Layer Name', ['Field1', 'Field2', 'Field3'])
var polygonUnderFeatureInfo = Intersects(polyogn, $feature) //
return First(Intersects(parcels, $feature))
View create_facilityid_values.py
# 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)
View README.md

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.

You can’t perform that action at this time.