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> |
