Last active
August 17, 2016 18:35
-
-
Save andrewbt/2137b8436dec401bb6ebd47a2de998ae to your computer and use it in GitHub Desktop.
PostGIS Spatial analysis Basic Training
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
<!DOCTYPE html> | |
<html> | |
<head> | |
<title>Lesson 2 | CARTO.js | CartoDB</title> | |
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" /> | |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/> | |
<link rel="shortcut icon" href="https://carto.com/favicon.ico" /> | |
<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css"> | |
<link rel="stylesheet" href="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css" /> | |
<link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css"> | |
<link rel="stylesheet" href="https://carto.com/academy/css/cdbui.css"> | |
<style> | |
html, body { | |
height: 100%; | |
padding: 0; | |
margin: 0; | |
} | |
#map { | |
height: 67%; | |
padding: 0; | |
margin: 0; | |
} | |
#dashboard { | |
height: 33%; | |
padding: 25px; | |
margin: 0; | |
border-top: 2px solid #333; | |
} | |
#dash { | |
margin: 25px 0; | |
width: 400px; | |
} | |
button { | |
margin-right: 10px; | |
font-family: "Proxima Nova W01", "Helvetica Neue", Helvetica, Arial, sans-serif; | |
background: transparent; | |
} | |
p { | |
font-size: 17px; | |
margin-bottom: 10px; | |
} | |
</style> | |
<script src="http://code.jquery.com/jquery-1.10.2.js"></script> | |
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.js"></script> | |
<script type="text/javascript" src="http://fast.fonts.net/jsapi/b6470a43-5105-49d3-95ae-75217a436bf8.js"></script> | |
</head> | |
<body> | |
<div id="map"></div> | |
<div id="dashboard"> | |
<h3>CARTO.js from the ground up, Lesson 2</h3> | |
<h4>Layer controls</h4> | |
<div id="buttons"> | |
<button id="sublayer0">Toggle Countries</button> | |
<button id="sublayer1">Toggle Lakes</button> | |
</div> | |
</div> | |
<!-- include cartodb.js library --> | |
<script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js"></script> | |
<!-- Place your code in the script tags below --> | |
<script> | |
window.onload = function() { | |
var layerSource = { | |
user_name: 'documentation', | |
type: 'cartodb', | |
sublayers: [{ | |
sql: "SELECT * FROM africa_adm0", // African countries | |
cartocss: '#africa_adm0{polygon-fill:#FF6600;polygon-opacity:0.7;line-color:#FFF;line-width:1;line-opacity:1;}' | |
}, | |
{ | |
sql: "SELECT * FROM ne_50m_lakes", // Natural and artificial lakes | |
cartocss: '#table_name_2 {polygon-fill: #0000FF;}' | |
}] | |
} | |
// Instantiate new map object, place it in 'map' element | |
var map_object = new L.Map('map', { | |
center: [43,0], // Southern France | |
zoom: 3 | |
}); | |
// Pull tiles from OpenStreetMap | |
L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', { | |
attribution: '© <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors' | |
}).addTo(map_object); | |
var sublayers; | |
// Add data layer to your map | |
cartodb.createLayer(map_object,layerSource) | |
.addTo(map_object) | |
.done(function(layer) { | |
sublayers = layer; | |
for (var i = 0; i < layer.getSubLayerCount(); i++) { | |
sublayers[i] = layer.getSubLayer(i); | |
alert("Congrats, you added sublayer #" + i + "!"); | |
} | |
}) | |
.error(function(err) { | |
console.log("error: " + err); | |
}); | |
var sublayer0Shown = true; | |
$("#sublayer0").on('click', function() { | |
if (sublayer0Shown) { | |
sublayers.getSubLayer(0).hide(); | |
} else { | |
sublayers.getSubLayer(0).show(); | |
} | |
sublayer0Shown = !sublayer0Shown; | |
}); | |
var sublayer1Shown = true; | |
$("#sublayer1").on('click', function() { | |
if (sublayer1Shown) { | |
sublayers.getSubLayer(1).hide(); | |
} else { | |
sublayers.getSubLayer(1).show(); | |
} | |
sublayer1Shown = !sublayer1Shown; | |
}); | |
} | |
</script> | |
</body> | |
</html> |
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
<!DOCTYPE html> | |
<html> | |
<head> | |
<title>Lesson 1 | CARTO.js | CartoDB</title> | |
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" /> | |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/> | |
<link rel="shortcut icon" href="https://carto.com/favicon.ico" /> | |
<!-- Map takes up full browser window --> | |
<style> | |
html, body, #map { | |
height: 100%; | |
padding: 0; | |
margin: 0; | |
} | |
</style> | |
<link rel="stylesheet" href="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css" /> | |
</head> | |
<body> | |
<div id="map"></div> | |
<!-- include cartodb.js library --> | |
<script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js"></script> | |
<!-- Drop your code between the script tags below! --> | |
<script> | |
/* Ex1: First createVis example | |
window.onload = function() { | |
var vizjson = 'link from share panel'; | |
cartodb.createVis('map', vizjson); | |
} | |
*/ | |
/* Ex2: Second createVis example with options object | |
window.onload = function() { | |
var vizjson = 'link from share panel'; | |
var options = { | |
center: [40.4000, -3.6833], // Madrid | |
zoom: 7, | |
scrollwheel: true | |
}; | |
cartodb.createVis('map',vizjson,options); | |
} | |
*/ | |
/* Ex3: First createLayer example | |
window.onload = function() { | |
// Choose center and zoom level | |
var options = { | |
center: [41.8369, -87.6847], // Chicago | |
zoom: 7 | |
} | |
// Instantiate map on specified DOM element | |
var map_object = new L.Map(map, options); | |
// Add a basemap to the map object just created | |
L.tileLayer('http://tile.stamen.com/toner/{z}/{x}/{y}.png', { | |
attribution: 'Stamen' | |
}).addTo(map_object); | |
//uncomment these when ready | |
// var vizjson = 'link from share panel'; | |
// cartodb.createLayer(map_object, vizjson).addTo(map_object); | |
} | |
*/ | |
/* Ex4: First callback createVis example | |
window.onload = function() { | |
var vizjson = 'link from share panel'; | |
cartodb.createVis('map', vizjson) | |
.done(function(vis, layers) { | |
// do stuff | |
alert("Layers has " + layers.length + " layers."); | |
}) | |
.error(function(err) { | |
// report error | |
console.log("An error occurred: " + err); | |
}); | |
} | |
*/ | |
</script> | |
</body> | |
</html> |
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
<!DOCTYPE html> | |
<html> | |
<head> | |
<title>Lesson 2 | CARTO.js | CartoDB</title> | |
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" /> | |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/> | |
<link rel="shortcut icon" href="https://carto.com/favicon.ico" /> | |
<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css"> | |
<link rel="stylesheet" href="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css" /> | |
<link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css"> | |
<link rel="stylesheet" href="https://carto.com/academy/css/cdbui.css"> | |
<style> | |
html, body { | |
height: 100%; | |
padding: 0; | |
margin: 0; | |
} | |
#map { | |
height: 67%; | |
padding: 0; | |
margin: 0; | |
} | |
#dashboard { | |
height: 33%; | |
padding: 25px; | |
margin: 0; | |
border-top: 2px solid #333; | |
} | |
#dash { | |
margin: 25px 0; | |
width: 400px; | |
} | |
button { | |
margin-right: 10px; | |
font-family: "Proxima Nova W01", "Helvetica Neue", Helvetica, Arial, sans-serif; | |
background: transparent; | |
} | |
p { | |
font-size: 17px; | |
margin-bottom: 10px; | |
} | |
</style> | |
<script src="http://code.jquery.com/jquery-1.10.2.js"></script> | |
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.js"></script> | |
<script type="text/javascript" src="http://fast.fonts.net/jsapi/b6470a43-5105-49d3-95ae-75217a436bf8.js"></script> | |
</head> | |
<body> | |
<div id="map"></div> | |
<div id="dashboard"> | |
<h3>CARTO.js from the ground up, Lesson 2</h3> | |
</div> | |
<!-- include cartodb.js library --> | |
<script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js"></script> | |
<!-- Place your code in the script tags below --> | |
<script> | |
window.onload = function() { | |
var layerSource = { | |
user_name: 'documentation', | |
type: 'cartodb', | |
sublayers: [{ | |
sql: "SELECT * FROM africa_adm0", // African countries | |
cartocss: '#africa_adm0{polygon-fill:#FF6600;polygon-opacity:0.7;line-color:#FFF;line-width:1;line-opacity:1;}' | |
}, | |
{ | |
sql: "SELECT * FROM ne_50m_lakes", // Natural and artificial lakes | |
cartocss: '#table_name_2 {polygon-fill: #0000FF;}' | |
}] | |
} | |
// Instantiate new map object, place it in 'map' element | |
var map_object = new L.Map('map', { | |
center: [43,0], // Southern France | |
zoom: 3 | |
}); | |
// Pull tiles from OpenStreetMap | |
L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', { | |
attribution: '© <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors' | |
}).addTo(map_object); | |
cartodb.createLayer(map_object, layerSource) | |
.addTo(map_object) | |
.done(function(layer) { | |
// do stuff | |
alert("Layer has " + layer.getSubLayerCount() + " sublayer(s)."); | |
}) | |
.error(function(err) { | |
// report error | |
console.log("An error occurred: " + err); | |
}); | |
} | |
</script> | |
</body> | |
</html> |
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
-- Lines: | |
SELECT o.cartodb_id, o.destination_city, o.destination_city_state, o.destination_state, o.load_count, o.origin_city, o.origin_city_state, o.origin_state, ST_MakeLine(o.the_geom,d.the_geom) as the_geom, ST_TRANSFORM(ST_MakeLine(o.the_geom,d.the_geom), 3857) as the_geom_webmercator | |
FROM athompson.mo_inbound_origin as o JOIN athompson.mo_inbound_destination as d | |
ON o.cartodb_id = d.cartodb_id | |
-- Buffers: | |
SELECT cartodb_id, origin_city_state, | |
ST_TRANSFORM( | |
ST_BUFFER( | |
the_geom::geography, | |
160000 | |
)::geometry, | |
3857 | |
) as the_geom_webmercator | |
FROM athompson.mo_inbound_origin | |
-- Buffers to find nearest shipments | |
with chi_buffer as (SELECT cartodb_id as cartodb_id_chi, origin_city_state as origin_city_state_chi, | |
ST_BUFFER( | |
the_geom::geography, | |
160000 | |
)::geometry as the_geom_chi, | |
ST_TRANSFORM( | |
ST_BUFFER( | |
the_geom::geography, | |
160000 | |
)::geometry, | |
3857 | |
) as the_geom_webmercator_chi | |
FROM athompson.mo_inbound_origin where cartodb_id = 26) | |
select * from athompson.mo_inbound_origin, chi_buffer where ST_INTERSECTS(chi_buffer.the_geom_chi,athompson.mo_inbound_origin.the_geom) | |
-- Union Buffers: | |
SELECT | |
ST_Transform( | |
ST_Union( | |
ST_Buffer( | |
the_geom::geography, | |
160000 | |
)::geometry | |
), | |
3857 | |
) as the_geom_webmercator | |
FROM athompson.mo_inbound_origin | |
--Nearest things: | |
SELECT * | |
FROM mo_inbound_origin | |
ORDER BY the_geom_webmercator | |
<-> ST_Transform(CDB_LatLng(39.0997, -94.5786), 3857) | |
LIMIT 20 | |
-- Geoprocessing: https://carto.com/blog/geoprocessing-in-postgis/ | |
-- nearest: https://carto.com/blog/nearest-neighbor-joins/ | |
-- https://carto.com/blog/author/pramsey/ | |
-- SQL and PostGIS in CARTO course: https://carto.com/academy/courses/sql-postgis/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Also, ZIP code leading zero SQL: