darrell

-- 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

-- 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

-- 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

-- 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)
)

darrell

 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

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

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
#