Skip to content

Instantly share code, notes, and snippets.

@darrell
darrell / median_population_center.sql
Last active Aug 5, 2019
How to calculate the median population center of Oregon using PostGIS
View median_population_center.sql
-- this assumes you have loaded the census blockgroup table
-- into postgres already. You should also reproject into a state plane system
-- in this case, I used EPSG:2992 which is the preferred state wide projection
-- for Oregon.
-- do some tidying on the block group files, to make the boundaries the same
-- as the state boundary data I'm using.
UPDATE acs_bg_2017 a
UPDATE acs_bg_2017 a
@darrell
darrell / calculate_hausdorff.sql
Created Nov 21, 2017
How to calculate the similarty of two polygons, located in different locations
View calculate_hausdorff.sql
-- how to calculate the similarty of two polygons, located in different locations
-- requires PostGIS
-- the idea here is to translate both polygons to the origin, then calculate the hausdorff distance
-- between them. We can't do them at their original locations, because.. well, that's not how Hausdorff works.
-- see https://en.wikipedia.org/wiki/Hausdorff_distance#Related_concepts
-- create a sample table with
DROP TABLE IF EXISTS example_hausdorff;
@darrell
darrell / nyt_top52_pgrouting_example.sql
Created Feb 17, 2017
how to calculate the shortest path using actual air routes between all the cities in the NYT 52 Places to visit in 2017
View nyt_top52_pgrouting_example.sql
-- Airport and Flight data can be downloaded at: http://openflights.org/data.html
-- you'll need airports.dat and routes.dat
-- (I added a couple of commercial flights that I know exist,
-- but weren't in the Open Flights data
-- OK, on to the good stuff.
-- The first thing we do is load the top 52 list, which has the place and
-- the nearest commerical airport. (This is data I just figured out by hand.
-- Where it's very ambiguous, like "Canada" I just used the appropriate capital (i.e. Ottawa))
@darrell
darrell / gist:7fe29b058cf87fd6f079
Last active Aug 29, 2015
adjacent parcels with shared ownership
View gist:7fe29b058cf87fd6f079
-- if you want to add a buffer, only do so around
-- one geometry, so you can still take advantage
-- of indexes
SELECT DISTINCT a.* from
taxlots a
JOIN taxlots b
ON (a.owner1 in (b.owner1, b.owner2, b.owner3)
OR a.owner2 in (b.owner1, b.owner2, b.owner3)
OR a.owner3 in (b.owner1, b.owner2, b.owner3)
)
View foss4g_2014_preliminary_attendance
rank | name | count | 2013_pop | per_1mil_residents
------+----------------+-------+------------+------------------------
1 | Cape Verde | 3 | 498897 | 6.01326526317055424266
2 | Norway | 21 | 5084190 | 4.1304514583443970
3 | Iceland | 1 | 323002 | 3.09595606219156537730
4 | Estonia | 3 | 1324612 | 2.2648141493509043
5 | Canada | 69 | 35158304 | 1.9625520047838485
6 | Luxembourg | 1 | 543202 | 1.84093578447796583960
7 | United States | 567 | 316128839 | 1.7935725250298977
8 | Denmark | 10 | 5613706 | 1.7813544207694525
@darrell
darrell / gist:8961708
Last active Aug 29, 2015
What's the best global map projection for calculating area?
View gist:8961708

What's up with the Gall Stereographic?

Prompted by https://twitter.com/RosemaryDaley/status/433634973061373954, I asked myself how different area calculations would vary by the equal area projection used.

So I grabbed a few equal-area projections to try it out:

  • US National Atlas Equal Area Azimuthal
  • Mollweide
  • Gall Stereographic
@darrell
darrell / voronoi.sql
Last active Nov 22, 2020
Code to generate Voronoi diagrams in PostGIS using Python. Many thanks to everyone who contributed to this code. Original blog post describing the work is here: http://geogeek.garnix.org/2012/04/faster-voronoi-diagrams-in-postgis.html
View voronoi.sql
CREATE OR REPLACE FUNCTION voronoi(table_name text,geom_col text) returns SETOF record as $$
#############################################################################
#
# Voronoi diagram calculator/ Delaunay triangulator
# Translated to Python by Bill Simons
# September, 2005
#
# Additional changes by Carson Farmer added November 2010
#