Skip to content

Instantly share code, notes, and snippets.

@andrewbt

andrewbt/cartojs-layercontrols.html

Last active August 17, 2016 18:35
Show Gist options
  • Save andrewbt/2137b8436dec401bb6ebd47a2de998ae to your computer and use it in GitHub Desktop.
Save andrewbt/2137b8436dec401bb6ebd47a2de998ae to your computer and use it in GitHub Desktop.
Download ZIP
PostGIS Spatial analysis Basic Training
Raw
cartojs-layercontrols.html
<!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: '&copy; <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>
Raw
cartojs-lessons.html
<!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>
Raw
cartojs-multilayer.html
<!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: '&copy; <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>
Raw
postgis_functions.sql
-- 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/
@andrewbt
Copy link
Author

datasets for SQL:

https://team.carto.com/u/athompson/tables/mo_inbound_origin/public
https://team.carto.com/u/athompson/tables/mo_inbound_destination/public

@andrewbt
Copy link
Author

Also, ZIP code leading zero SQL:

--Fix zip codes with leading zeroes that were turned to text instead of number or zeroes removed
--First add a new column to your dataset that's string type. Then run:

update my_zip_code_table
set new_string_column = to_char(zip_code_column, 'fm00000')

--For some reason if you only wanted to modify the zip codes that had 4 digits and not all the codes, you could do:

update my_zip_code_table
set new_string_column = to_char(zip_code_column, 'fm00000') where length(zip_code_column::text) = 4

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment