kgjenkins

Workshop prepared for Cornell Soil and Water Lab, 2016-07-18, by Keith Jenkins, kgj2@cornell.edu

In this workshop, we will use ArcGIS to map an Excel spreadsheet of water sample data, called All_Water_Data_Tompkins.xlsx, which contains various measurements, along with longitude and latitude coordinates. We will explore the data in relation to local roads and hydrographic features.

Download the workshop data

• Download the data from https://cornell.box.com/v/arcgis-swl
• Always unzip first! From the browser > show in folder
• Right-click the file > 7-Zip > Extract Here

kgjenkins

Calculate the county for Medicare Long Stay facilities

We want to calculate the county (name, FIPS) that corresponds to each row of this dataset: https://data.medicare.gov/Nursing-Home-Compare/Quality-Measures-Long-Stay/iqd3-nsf3

We will use CartoDB to add new columns that will contain the latitude, longitude, county name, and county FIPS code.

Step 1: Filter the data

kgjenkins

QGIS, SQLite, and SpatiaLite versions

QGIS 2.16.0: SQLite 3.7.17, SpatiaLite 4.3.0

QGIS 2.16.3: SQLite 3.14.1, SpatiaLite 4.3.0

kgjenkins

Python and scripting for GIS

Some motivations for scripting (code-based processing, as opposed to point-and-click):

• Reduce human repetition (save time, reduce human error)
• Easier to reproduce (run the same, or similiar, code on new datasets)
• Easier to modify (change a single line of code and re-run an entire process)
• Code is self-documenting (what you wrote is what was run)
• Get the just the output you want (without the clutter of temporary files and extraneous data at every step)
• You can take a break while the computer is still working away.

kgjenkins

Allocate polygon population values to a grid of points, and save as delimited text

We have population data (total population, male, female, etc.) by polygon (Census tracts), but want a CSV file of the population densities for a regular grid of points at 1km spacing.

Below are the steps to do this using QGIS. In this example, we have already added a polygon layer called "Tracts" to the map, and will calculate the male population densities. (Repeat this process for other subpopulations.)

Step 1: Transform the tract polygons to UTM

In order to calculate densities per square kilometer, we will to transform the tracts to a UTM (meters-based) coordinate system. For New York State tracts, we will use UTM zone 18N.

kgjenkins

Yes, it is possible to use images from an ArcGIS Online layer as a background when editing OpenStreetMap!

From an ArcGIS Online layer info page like this one, click the View In "WMTS" link.

That will open a viewer. The next step is to look at the network traffic when zooming in, so that we can get an image URL. In Google Chrome browser, press CTRL-SHIFT-J, then select the "Network" tab, then zoom in on the map. You'll see lots of image tiles listed. Grab the URL of any one. It should look something like this:

https://tiles.arcgis.com/tiles/7CRlmWNEbeCqEJ6a/arcgis/rest/services/Stanford_Aerial_1925/MapServer/WMTS/tile/1.0.0/Stanford_Aerial_1925/default/default028mm/18/101643/42111.png?cacheKey=b7130b6352a56641

The next step is to replace the z/y/x coordinates in that specific tile request with template variables, and also get rid of the trailing cacheKey parameter, ending up

kgjenkins

Faster, faster distance matrix

Suppose you have a set of points, and would like to calculate the distance from each point to every other point. QGIS provides a "Distance Matrix" tool that can do this. It can actually produce several types of distance matrices, but for now this post will focus on what the tool calls a "Linear (N*k x 3) distance matrix", which gives us an output that looks like this:

InputID	TargetID	Distance
1	1	0

kgjenkins

Alternative to using DCAT Distribution in GeoBlacklight records

DCAT is pretty complex, and I'm wondering if it perhaps is overkill for GeoBlacklight records. If we aren't planning to use the full DCAT standard (and, no, I'm not suggesting that we do), is it really that helpful to use just the DCAT distribution?

The following is an attempt to present metadata, webservices, and downloads as simply as possible, with a top-level property for each of those groupings. These groupings will make it easier to automatically render a list of metadata options, or download options, or webservices options, on the GeoBlacklight page.

Each grouping contains an array of objects which typically have just a type and a url. I've tried to keep the type values as simple as possible. The type value could be used as button or link labels in GeoBlacklight, but this could be overridden by the optional label value, which would be especially useful to distinguish multiple entries of the having the same basic `t

kgjenkins

Aggregate counts of multi-valued columns

Let's suppose we have a dataset of research studies conducted in various countries, and some studies are associated with multiple countries. Our studies table might have multiple comma-delimited values in the country column, and there might be spaces after some of those commas, looking something like this:

id	title	country
1	Study one	Mauritania
2	Study two	Mali
3	Study three	Guinea
4	Study four	Mali,Guinea

kgjenkins

geocorr2014

Download the GeoCorr2014 table via http://mcdc.missouri.edu/applications/geocorr2014.html

• Select a state.
• Select the "source" and "target" geographies. (For example, if you have demographic data at the tract level, and want to map it to Voting Tabulation Districts (VTD), set VTD as the source and tract as the target. (Yes, this seems counterintuitive.)
• Weight by population (2010 census)
• Generate CSV file. (The report is unnecessary.)

NOTE: Unfortunately, the output doesn't include the full tract FIPS codes, so we'll need to deal with that. Here are some rows showing the geocorr columns we'll be using: