Skip to content

Instantly share code, notes, and snippets.

@auremoser
Last active November 13, 2015 16:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save auremoser/6e984c0ff866e04686c8 to your computer and use it in GitHub Desktop.
Save auremoser/6e984c0ff866e04686c8 to your computer and use it in GitHub Desktop.
MadBike SQL

This map visualizes bike lanes in Madrid available through the city's open data portal.

UPDATE table_name
SET the_geom_webmercator = ST_Translate(the_geom_webmercator,-148,-165)

It uses CartoDB to render the lanes as lines; but some fancy sql was necessary to reset the projection and align the bike lanes with the basemap roads

Data:

<!DOCTYPE html>
<html>
<head>
<title>HTML Template | 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="http://cartodb.com/assets/favicon.ico" />
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />
<style>
html, body, #map {
height: 100%;
padding: 2px;
margin: 0;
}
#info {
position: absolute;
left: 50px;
top: 10px;
}
.info {
background: rgba(255,255,255,0.9);
border: 1px solid #999;
border-radius: 5px;
font-family: 'Consolas', sans-serif;
font-size: 13px;
margin: 15px;
padding: 5px;
text-align: center;
width: 280px;
z-index: 100;
}
.info-title {
font-size: 13px;
font-weight: bold;
text-transform: uppercase;
text-align: center;
}
.info-sql {
font-size: 11px;
text-align: left;
padding: 5px;
}
.info-sql h4 {
color: #ff5c00;
}
footer {
background: rgba(255,255,255,0.9);
color: #00a3ff;
font-family: 'Consolas', sans-serif;
font-weight: bold;
font-size: 24px;
position: fixed;
padding: 20px;
top: 89%;
width: 100%;
}
footer h6 {
color: #ff5c00;
}
</style>
</head>
<body>
<div id="map"></div>
<div id="info" class="info">
<div class='info-title'>SQL to Move bikeline</div>
<hr>
<ul class="info-sql">
<li><h4>UPDATE</h4> table_name</li>
<li><h4>SET</h4> the_geom_webmercator = <strong>ST_Translate</strong>(the_geom_webmercator,-148,-165)</li>
</ul>
<div class='info-title'>OR</div>
<ul class="info-sql">
<li><h4>UPDATE</h4> table_name</li>
<li><h4>SET</h4> the_geom = <strong>ST_Transform</strong>(<strong>ST_setsrid</strong> the_geom, 4230), 4326)</li>
</ul>
</div>
<footer>MAD BIKES<h6>a small SQL projection demo</h6></footer>
<!-- include cartodb.js library -->
<script src="http://libs.cartocdn.com/cartodb.js/v3/cartodb.js"></script>
<script>
window.onload = function() {
var vizjson_url = 'https://team.cartodb.com/u/aureliamoser/api/v2/viz/09f857ce-8a25-11e5-8935-0ecfd53eb7d3/viz.json';
// DEFAULT DATA DOWNLOAD OFFSETS THE PROJECTION
// FOR BIKELANES IN MADRID
// USE THIS SQL TO MOVE THE DATA OVER SLIGHTLY
// UPDATE table_name
// SET the_geom_webmercator = ST_Translate(the_geom_webmercator,-148,-165)
//OR
// UPDATE table_name SET the_geom = ST_Transform(ST_setsrid(the_geom, 4230), 4326)
cartodb.createVis(map, vizjson_url) // <-- Change map_id to match your id in html
.done(function(vis, layers) {
// do stuff
console.log("Map successfully created");
})
.error(function(err) {
// report error
console.log("An error occurred: " + err);
});
}
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment