Created
July 11, 2013 21:44
-
-
Save andrewxhill/5979532 to your computer and use it in GitHub Desktop.
Link two datasets into lines between nearest points
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
ST_MakeLine( --This function can take two or more points and make a line | |
the_geom_webmercator, --We select the_geom_webmercator, since CartoDB will need it to draw your maps | |
( --This is a nested query that will run for every row in our outer query | |
SELECT the_geom_webmercator FROM plout10 -- Here we select the geometry from our second dataset | |
ORDER BY the_geom <-> c.the_geom -- We then order it by its distance to the geometry in the first dataset (c.the_geom) | |
LIMIT 1 -- And limit it to just 1, i.e. we find just the closest | |
) | |
) the_geom_webmercator -- Here we alias the result to a column we call, 'the_geom_webmercator', so that CartoDB will draw it | |
FROM citibike_stations c -- Here we alias our table to 'c' so we can type it nicely above :) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment