kgjenkins

CartoDB

What is CartoDB? It's a popular online tool for creating dynamic maps and analyzing spatial data. CartoDB includes:

• a simple interface for creating a variety of beautiful maps based upon your data. Share links directly to your maps, or embed them in a webpage, or export static map images.
• a powerful PostGIS spatial database for processing and analyzing data.
• an infrastructure for creating more complex maps using custom styles (using CartoCSS), custom popups (using HTML), custom map projections (using PostGIS SQL), and custom interactions (using cartodb.js)

Cornell-affiliated CartoDB accounts

kgjenkins

UPDATE: QGIS 3.0 introduced a new built-in function is_selected()

Sometimes selected features in QGIS are hidden behind other (non-selected) features in the same layer, for example when there are a lot of coincident points. So I was looking for a way to force the selected features to be rendered on top, something like this:

In the QGIS layer style properties, there is an option at the bottom to "Control feature rendering order" where you can order by the values in a column, or by an expression. The problem is that there is currently no way to access the selection status. Clicking the expression symbol takes us to the expression editor. I was hoping that there would be a $selected function under Record, but there isn't.

So I started looking into how to create a custom function in QGIS using Python, and eventually came up with this:

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

Method using Carto SQL

WITH buf AS (
  -- 2mi (3218.69m) point buffers
  SELECT
  ST_Collect(ST_Buffer(the_geom::geography, 3218.69)::geometry) AS the_geom
  FROM sites
)
SELECT

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

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

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

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

Straighten lines in a layer using QGIS virtual layers

Suppose we have a layer of wiggly lines (black), and we want to straighten the lines (blue):

We can use QGIS virtual layers to create a new layer of straightened lines:

select
 make_line(start_point(geometry), end_point(geometry)) as geometry,