Simon Willison's Weblog: openstreetmap

OpenTimes

2025-03-17T22:49:59+00:00

Spectacular new open geospatial project by Dan Snow:

OpenTimes is a database of pre-computed, point-to-point travel times between United States Census geographies. It lets you download bulk travel time data for free and with no limits.

Here's what I get for travel times by car from El Granada, California:

The technical details are fascinating:

The entire OpenTimes backend is just static Parquet files on Cloudflare's R2. There's no RDBMS or running service, just files and a CDN. The whole thing costs about $10/month to host and costs nothing to serve. In my opinion, this is a great way to serve infrequently updated, large public datasets at low cost (as long as you partition the files correctly).

Sure enough, R2 pricing charges "based on the total volume of data stored" - $0.015 / GB-month for standard storage, then $0.36 / million requests for "Class B" operations which include reads. They charge nothing for outbound bandwidth.

All travel times were calculated by pre-building the inputs (OSM, OSRM networks) and then distributing the compute over hundreds of GitHub Actions jobs. This worked shockingly well for this specific workload (and was also completely free).

Here's a GitHub Actions run of the calculate-times.yaml workflow which uses a matrix to run 255 jobs!

Relevant YAML:

  matrix:
    year: ${{ fromJSON(needs.setup-jobs.outputs.years) }}
    state: ${{ fromJSON(needs.setup-jobs.outputs.states) }}

Where those JSON files were created by the previous step, which reads in the year and state values from this params.yaml file.

The query layer uses a single DuckDB database file with views that point to static Parquet files via HTTP. This lets you query a table with hundreds of billions of records after downloading just the ~5MB pointer file.

This is a really creative use of DuckDB's feature that lets you run queries against large data from a laptop using HTTP range queries to avoid downloading the whole thing.

The README shows how to use that from R and Python - I got this working in the duckdb client (brew install duckdb):

INSTALL httpfs;
LOAD httpfs;
ATTACH 'https://data.opentimes.org/databases/0.0.1.duckdb' AS opentimes;

SELECT origin_id, destination_id, duration_sec
  FROM opentimes.public.times
  WHERE version = '0.0.1'
      AND mode = 'car'
      AND year = '2024'
      AND geography = 'tract'
      AND state = '17'
      AND origin_id LIKE '17031%' limit 10;

In answer to a question about adding public transit times Dan said:

In the next year or so maybe. The biggest obstacles to adding public transit are:

Collecting all the necessary scheduling data (e.g. GTFS feeds) for every transit system in the county. Not insurmountable since there are services that do this currently.

Finding a routing engine that can compute nation-scale travel time matrices quickly. Currently, the two fastest open-source engines I've tried (OSRM and Valhalla) don't support public transit for matrix calculations and the engines that do support public transit (R5, OpenTripPlanner, etc.) are too slow.

GTFS is a popular CSV-based format for sharing transit schedules - here's an official list of available feed directories.

This whole project feels to me like a great example of the baked data architectural pattern in action.

Via Hacker News

Tags: census, geospatial, open-data, openstreetmap, cloudflare, parquet, github-actions, baked-data, duckdb, http-range-requests

OpenStreetMap embed URL

2024-11-25T19:29:16+00:00

OpenStreetMap embed URL

I just found out OpenStreetMap have a "share" button which produces HTML for an iframe targetting https://www.openstreetmap.org/export/embed.html, making it easy to drop an OpenStreetMap map onto any web page that allows iframes.

As far as I can tell the supported parameters are:

bbox= then min longitude, min latitude, max longitude, max latitude
marker= optional latitude, longitude coordinate for a marker (only a single marker is supported)
layer=mapnik - other values I've found that work are cyclosm, cyclemap, transportmap and hot (for humanitarian)

Here's HTML for embedding this on a page using a sandboxed iframe - the allow-scripts is necessary for the map to display.

<iframe
  sandbox="allow-scripts"
  style="border: none; width: 100%; height: 20em;"
  src="https://www.openstreetmap.org/export/embed.html?bbox=-122.613%2C37.431%2C-122.382%2C37.559&amp;layer=mapnik&amp;marker=37.495%2C-122.497"
></iframe>

Thanks to this post I learned that iframes are rendered correctly in NetNewsWire, NewsExplorer, NewsBlur and Feedly on Android.

Tags: geospatial, iframes, openstreetmap, sandboxing

OpenStreetMap vector tiles demo

2024-11-19T23:39:18+00:00

OpenStreetMap vector tiles demo

Long-time OpenStreetMap developer Paul Norman has been working on adding vector tile support to OpenStreetMap for quite a while. Paul recently announced that vector.openstreetmap.org is now serving vector tiles (in Mapbox Vector Tiles (MVT) format) - here's his interactive demo for seeing what they look like.

Via Mark Litwintschik

Tags: geospatial, mapping, openstreetmap

OpenFreeMap

2024-09-28T21:41:15+00:00

OpenFreeMap

New free map tile hosting service from Zsolt Ero:

OpenFreeMap lets you display custom maps on your website and apps for free. […] Using our public instance is completely free: there are no limits on the number of map views or requests. There’s no registration, no user database, no API keys, and no cookies. We aim to cover the running costs of our public instance through donations.

The site serves static vector tiles that work with MapLibre GL. It deliberately doesn’t offer any other services such as search or routing.

From the project README looks like it’s hosted on two Hetzner machines. I don’t think the public server is behind a CDN.

Part of the trick to serving the tiles efficiently is the way it takes advantage of Btrfs:

Production-quality hosting of 300 million tiny files is hard. The average file size is just 450 byte. Dozens of tile servers have been written to tackle this problem, but they all have their limitations.

The original idea of this project is to avoid using tile servers altogether. Instead, the tiles are directly served from Btrfs partition images + hard links using an optimised nginx config.

The self-hosting guide describes the scripts that are provided for downloading their pre-built tiles (needing a fresh Ubuntu server with 300GB of SSD and 4GB of RAM) or building the tiles yourself using Planetiler (needs 500GB of disk and 64GB of RAM).

Getting started is delightfully straightforward:

const map = new maplibregl.Map({
  style: 'https://tiles.openfreemap.org/styles/liberty',
  center: [13.388, 52.517],
  zoom: 9.5,
  container: 'map',
})

I got Claude to help build this demo showing a thousand random markers dotted around San Francisco. The 3D tiles even include building shapes!

Zsolt built OpenFreeMap based on his experience running MapHub over the last 9 years. Here’s a 2018 interview about that project.

It’s pretty incredible that the OpenStreetMap and open geospatial stack has evolved to the point now where it’s economically feasible for an individual to offer a service like this. I hope this turns out to be sustainable. Hetzner charge just €1 per TB for bandwidth (S3 can cost $90/TB) which should help a lot.

Via Tim Bray

Tags: geospatial, maps, openstreetmap, ai-assisted-programming

Optimizing Large-Scale OpenStreetMap Data with SQLite

2024-07-02T14:33:09+00:00

Optimizing Large-Scale OpenStreetMap Data with SQLite

JT Archie describes his project to take 9GB of compressed OpenStreetMap protobufs data for the whole of the United States and load it into a queryable SQLite database.

OSM tags are key/value pairs. The trick used here for FTS-accelerated tag queries is really neat: build a SQLite FTS table containing the key/value pairs as space concatenated text, then run queries that look like this:

SELECT
    id
FROM
    entries e
    JOIN search s ON s.rowid = e.id
WHERE
    -- use FTS index to find subset of possible results
    search MATCH 'amenity cafe'
    -- use the subset to find exact matches
    AND tags->>'amenity' = 'cafe';

JT ended up building a custom SQLite Go extension, SQLiteZSTD, to further accelerate things by supporting queries against read-only zstd compresses SQLite files. Apparently zstd has a feature that allows "compressed data to be stored so that subranges of the data can be efficiently decompressed without requiring the entire document to be decompressed", which works well with SQLite's page format.

Via r/sqlite

Tags: go, openstreetmap, sqlite, zstd

How The Post is replacing Mapbox with open source solutions

2023-02-17T18:45:33+00:00

How The Post is replacing Mapbox with open source solutions

Kevin Schaul describes the Washington Post’s emerging open source GIS stack: OpenMapTiles, Maputnik, PMTiles and Maplibre-gl-js.

Via Hacker News

Tags: maps, opensearch, openstreetmap, washington-post

A tiny web app to create images from OpenStreetMap maps

2022-06-12T05:49:35+00:00

Earlier today I found myself wanting to programmatically generate some images of maps.

I wanted to create a map centered around a location, at a specific zoom level, and with a marker in a specific place.

Some cursory searches failed to turn up exactly what I wanted, so I decided to build a tiny project to solve the problem, taking advantage of my shot-scraper tool for automating screenshots of web pages.

The result is map.simonwillison.net - hosted on GitHub Pages from my simonw/url-map repository.

Here's how to generate a map image of Washington DC:

shot-scraper 'https://map.simonwillison.net/?q=washington+dc' \
  --retina --width 600 --height 400 --wait 3000

That command generates a PNG 1200x800 image that's a retina screenshot of the map displayed at https://map.simonwillison.net/?q=washington+dc - after waiting three seconds to esure all of the tiles have fully loaded.

The website itself is documented here. It displays a map with no visible controls, though you can use gestures to zoom in and pan around - and the URL bar will update to reflect your navigation, so you can bookmark or share the URL once you've got it to the right spot.

You can also use query string parameters to specify the map that should be initially displayed:

https://map.simonwillison.net/?center=51.49,0&zoom=8 displays a map at zoom level 8 centered on the specified latitude, longitude coordinate pair.
https://map.simonwillison.net/?q=islington+london geocodes the ?q= text using OpenStreetMap Nominatim and zooms to the level that best fits the bounding box of the first returned result.
https://map.simonwillison.net/?q=islington+london&zoom=12 does that but zooms to level 12 instead of using the best fit for the bounding box
https://map.simonwillison.net/?center=51.49,0&zoom=8&marker=51.49,0&marker=51.3,0.2 adds two blue markers to the specified map. You can pass &marker=lat,lon as many times as you like to add multiple markers.

Annotated source code

The entire mapping application is contained in a single 68 line index.html file that mixes HTML and JavaScript. It's built using the fantastic Leaflet open source mapping library.

Since the code is so short, I'll enclude the entire thing here with some additional annotating comments.

It started out as a copy of the first example in the Leaflet quick start guide.

<!DOCTYPE html>
<!-- Regular HTML boilerplate -->
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>map.simonwillison.net</title>
<!--
  Leaflet's CSS and JS are loaded from the unpgk.com CDN, with the
  Subresource Integrity (SRI) integrity="sha512..." attribute to ensure
  that the exact expected code is served by the CDN.
-->
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.8.0/dist/leaflet.css" integrity="sha512-hoalWLoI8r4UszCkZ5kL8vayOGVae1oxXe/2A4AO6J9+580uKHDO3JdHb7NzwwzK5xr/Fs0W40kiNHxM9vyTtQ==" crossorigin=""/>
<script src="https://unpkg.com/leaflet@1.8.0/dist/leaflet.js" integrity="sha512-BB3hKbKWOc9Ez/TAwyWxNXeoV9c1v6FIeYiBieIWkpLjauysF18NzgR1MBNBXf8/KABdlkX68nAhlwcDFLGPCQ==" crossorigin=""></script>
<!-- I want the map to occupy the entire browser window with no margins -->
<style>
html, body {
  height: 100%;
  margin: 0;
}
</style>
</head>
<body>
<!-- The Leaflet map renders in this 100% high/wide div -->
<div id="map" style="width: 100%; height: 100%;"></div>
<script>
function toPoint(s) {
  // Convert "51.5,2.1" into [51.5, 2.1]
  return s.split(",").map(parseFloat);
}
// An async function so we can 'await fetch(...)' later on
async function load() {
  // URLSearchParams is a fantastic browser API - it makes it easy to both read
  // query string parameters from the URL and later to generate new ones
  let params = new URLSearchParams(location.search);
  // If the starting URL is /?center=51,32&zoom=3 this will pull those values out
  let center = params.get('center') || '0,0';
  let initialZoom = params.get('zoom');
  let zoom = parseInt(initialZoom || '2', 10);
  let q = params.get('q');
  // .getAll() turns &marker=51.49,0&marker=51.3,0.2 into ['51.49,0', '51.3,0.2']
  let markers = params.getAll('marker');
  // zoomControl: false turns off the visible +/- zoom buttons in Leaflet
  let map = L.map('map', { zoomControl: false }).setView(toPoint(center), zoom);
  L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a>',
    // This option means retina-capable devices will get double-resolution tiles:
    detectRetina: true
  }).addTo(map);
  // We only pay attention to ?q= if ?center= was not provided:
  if (q && !params.get('center')) {
    // We use fetch to pass ?q= to the Nominatim API and get back JSON
    let response = await fetch(
      `https://nominatim.openstreetmap.org/search.php?q=${encodeURIComponent(q)}&format=jsonv2`
    )
    let data = await response.json();
    // data[0] is the first result - it has a boundingbox array of four floats
    // which we can convert into a Leaflet-compatible bounding box like this:
    let bounds = [
      [data[0].boundingbox[0],data[0].boundingbox[2]],
      [data[0].boundingbox[1],data[0].boundingbox[3]]
    ];
    // This sets both the map center and zooms to the correct level for the bbox:
    map.fitBounds(bounds);
    // User-provided zoom over-rides this
    if (initialZoom) {
      map.setZoom(parseInt(initialZoom));
    }
  }
  // This is the code that updates the URL as the user pans or zooms around.
  // You can subscribe to both the moveend and zoomend Leaflet events in one go:
  map.on('moveend zoomend', () => {
    // Update URL bar with current location
    let newZoom = map.getZoom();
    let center = map.getCenter();
    // This time we use URLSearchParams to construct a center...=&zoom=... URL
    let u = new URLSearchParams();
    // Copy across ?marker=x&marker=y from existing URL, if they were set:
    markers.forEach(s => u.append('marker', s));
    u.append('center', `${center.lat},${center.lng}`);
    u.append('zoom', newZoom);
    // replaceState() is a weird API - the third argument is the one we care about:
    history.replaceState(null, null, '?' + u.toString());
  });
  // This bit adds Leaflet markers to the map for ?marker= query string arguments:
  markers.forEach(s => {
    L.marker(toPoint(s)).addTo(map);
  });
}
load();
</script>
</body>
</html>
<!-- See https://github.com/simonw/url-map for documentation -->

Tags: geospatial, maps, openstreetmap, projects, shot-scraper, leaflet

Quoting Joe Morrison

2020-11-20T21:11:36+00:00

The open secret Jennings filled me in on is that OpenStreetMap (OSM) is now at the center of an unholy alliance of the world’s largest and wealthiest technology companies. The most valuable companies in the world are treating OSM as critical infrastructure for some of the most-used software ever written. The four companies in the inner circle— Facebook, Apple, Amazon, and Microsoft— have a combined market capitalization of over six trillion dollars.

— Joe Morrison

Tags: amazon, apple, facebook, microsoft, openstreetmap

Announcing Daylight Map Distribution

2020-03-12T11:44:55+00:00

Announcing Daylight Map Distribution

Mike Migurski announces a new distribution of OpenStreetMap: a 42GB dump of the version of the data used by Facebook, carefully moderated to minimize the chance of incorrect or maliciously offensive edits. Lots of constructive conversation in the comments about the best way for Facebook to make their moderation decisions more available to the OSM community.

Tags: facebook, michal-migurski, openstreetmap

Statistical NLP on OpenStreetMap

2018-01-08T19:33:23+00:00

Statistical NLP on OpenStreetMap

libpostal is ferociously clever: it’s a library for parsing and understanding worldwide addresses, built on top of a machine learning model trained on millions of addresses from OpenStreetMap. Al Barrentine describes how it works in this fascinating and detailed essay.

Tags: machine-learning, nlp, openstreetmap

Building a location to time zone API with SpatiaLite, OpenStreetMap and Datasette

2017-12-12T15:52:20+00:00

Given a latitude and longitude, how can we tell what time zone that point lies within? Here’s how I built a simple JSON API to answer that question, using a combination of data from OpenStreetMap, the SpatiaLite extension for SQLite and my Datasette API tool.

This article is out of date and no longer relevant. It has been replaced by a new tutorial: Building a location to time zone API with SpatiaLite, on the official Datasette website.

The API

You can try the API out here: feed it a latitude and longitude and it will return the corresponding time zone ID: https://timezones.datasette.io/timezones/by_point

Some examples:

Brighton, England is in Europe/London (in JSON)
San Francisco, USA is in America/Los_Angeles (in JSON)
Tokyo, Japan is Asia/Tokyo (in JSON)

The data

I was first introduced to Eric Muller’s tz_world shapefile by Nick Williams at Eventbrite, who used it to build us an internal time zone lookup API on top of MySQL’s geospatial data types. Eric’s project is no longer updated and he recommends Evan Siroky’s timezone-boundary-builder project as an alternative, which derives time zone shapes from OpenStreetMap and makes the resulting data available under the Open Database License. The shapefile itself can be downloaded from the GitHub releases page for the project.

Loading the data into SpatiaLite

SpatiaLite is a powerful open source extension for SQLite, which adds a comprehensive suite of geospatial functions - including the ability to ingest shapefiles, convert them into geometries and run point within queries against the resulting shapes.

The easiest way to get it running on OS X is via Homebrew:

$ brew install spatialite-tools
$ brew install gdal

Having installed SpatiaLite, we can ingest the shapefile using .loadshp combined_shapefile timezones CP1252 23032 - here’s the full process, from downloading the shapefile to ingesting it into a new SQLite database file called timezones.db:

$ wget https://github.com/evansiroky/timezone-boundary-builder/releases/download/2017c/timezones.shapefile.zip
$ unzip timezones.shapefile.zip
$ cd dist
$ spatialite timezones.db
SpatiaLite version ..: 4.3.0a   Supported Extensions:
...
Enter SQL statements terminated with a ";"
spatialite> .loadshp combined_shapefile timezones CP1252 23032
========
Loading shapefile at 'combined_shapefile' into SQLite table 'timezones'

BEGIN;
CREATE TABLE "timezones" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"tzid" TEXT);
SELECT AddGeometryColumn('timezones', 'Geometry', 23032, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 414 rows into 'timezones' from SHAPEFILE
========
spatialite>

Let’s try it out with a query. Here’s the SQL needed to find the time zone for a point in Tokyo:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);

Let’s run that in SpatiaLite and see what we get:

spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);
Asia/Tokyo

Looks good so far! How long is it taking though? We can find out by running .timer on in the spatialite prompt:

spatialite> .timer on
spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry);
Asia/Tokyo
CPU Time: user 0.108479 sys 0.064778

That’s a tenth of a second, or 100ms. Fast, but not brilliant.

Speeding it up with a geospatial index

It turns out SpatiaLite includes support for spatial indices, based on SQLite’s R*Tree module. R-Tree indexes can massively accelerate boundary box searches. Our searches are a lot more complex than that, acting as they do against extremely complex polygon shapes - but we can use a boundary box search to dramatically reduce the candidates we need to consider. Let’s create an index against our Geometry column:

SELECT CreateSpatialIndex('timezones', 'geometry');

To take advantage of this index, we need to expand our original SQL to first filter by geometries where their bounding box contains the point we are searching for. SpatiaLite has created an index table called idx_timezones_Geometry against which we can run an R-Tree optimized query. Here’s the SQL we will use:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry)
    and rowid in (
        SELECT pkid FROM idx_timezones_Geometry
        where xmin < 139.7819661
        and xmax > 139.7819661
        and ymin < 35.6631424
        and ymax > 35.6631424
    );

How does this affect our performance?

spatialite> select tzid
   ...> from
   ...>     timezones
   ...> where
   ...>     within(GeomFromText('POINT(139.7819661 35.6631424)'), timezones.Geometry)
   ...>     and rowid in (
   ...>         SELECT pkid FROM idx_timezones_Geometry
   ...>         where xmin < 139.7819661
   ...>         and xmax > 139.7819661
   ...>         and ymin < 35.6631424
   ...>         and ymax > 35.6631424
   ...>     );
Asia/Tokyo
CPU Time: user 0.001541 sys 0.000111

From 100ms down to 1.5ms - nearly a 70x speedup! Not bad at all.

Building and publishing the API with Datasette

Now that we have a fast SQL query for finding a time zone for a latitude and longitude we can use Datasette to turn it into a JSON API.

The simplest way to do that looks like this:

datasette timezones.db \
    --load-extension=/usr/local/lib/mod_spatialite.dylib

This will start Datasette on port 8001 and load the SpatiaLite extension. You can then navigate to localhost:8001/timezones in your browser and paste in the SQL query… which should look something like this:

https://timezones.datasette.io/timezones?sql=select+tzid%0D%0Afrom%0D%0Atimezones%0D%0Awhere%0D%0Awithin(GeomFromText(‘POINT(139.7819661+35.6631424)’)%2C+timezones.Geometry)%0D%0Aand+rowid+in+(%0D%0A++++SELECT+pkid+FROM+idx_timezones_Geometry%0D%0Awhere+xmin+<+139.7819661%0D%0Aand+xmax+>+139.7819661%0D%0Aand+ymin+<+35.6631424%0D%0Aand+ymax+>+35.6631424%0D%0A++++)%3B

This works (click the JSON link to get the result as JSON) but it’s a little inconvenient to use: you have to construct a URL with the same latitude and longitude repeated in multiple places.

We can improve things using Datasette’s support for SQLite named parameters. Here’s that same SQL query using parameters instead of hard-coded latitude and longitude points:

select tzid
from
    timezones
where
    within(GeomFromText('POINT(' || :longitude || ' ' || :latitude || ')'), timezones.Geometry)
    and rowid in (
        SELECT pkid FROM idx_timezones_Geometry
        where xmin < :longitude
        and xmax > :longitude
        and ymin < :latitude
        and ymax > :latitude)

If you paste this into Datasette it will detect the named parameters and turn them into URL querystring parameters hooked up (in thu UI) to HTML form fields.

To save us from having to include the full SQL in the URL every time we call our new API, let’s take advantage of a new feature introduced in Datasette 0.14: canned queries. These are named, pre-packaged queries that can be defined in a metadata.json file. The file looks like this:

{
    "title": "OpenStreetMap Time Zone Boundaries",
    "license": "ODbL",
    "license_url": "http://opendatacommons.org/licenses/odbl/",
    "source": "timezone-boundary-builder",
    "source_url": "https://github.com/evansiroky/timezone-boundary-builder",
    "databases": {
        "timezones": {
            "queries": {
                "by_point": "select tzid\nfrom\n    timezones\nwhere\n    within(GeomFromText('POINT(' || :longitude || ' ' || :latitude || ')'), timezones.Geometry)\n    and rowid in (\n        SELECT pkid FROM idx_timezones_Geometry\n        where xmin < :longitude\n        and xmax > :longitude\n        and ymin < :latitude\n        and ymax > :latitude\n    )"
            }
        }
    }
}

The canned query is defined as the by_point key in the queries nested object. I’m also adding license and source information here for the project, because it’s good manners.

We can try this in Datasette on our local machine like so:

datasette timezones.db -m metadata.json \
    --load-extension=/usr/local/lib/mod_spatialite.dylib

Now visiting localhost:8001/timezones/by_point will provide the interface for the query - and adding .json to the URL will turn it into an API.

Vizualizing time zones with Leaflet and GeoJSON

If you browse around in Datasette you’ll quickly run into a rather unfortunate problem. The localhost:8001/timezones/timezones page, which shows the first 100 rows in the table, takes a shockingly long time to load. When it eventually does load you’ll see why: each record includes an enormous binary string containing its the geometry. On my machine just that one page weighs in at 62MB of HTML!

This is bad: rendering that much HTML in one go can block the event loop and cause the application to become unresponsive. That giant blob of binary data isn’t exactly useful for humans, either.

We can make some major improvements here using another Datasette 0.14 feature: custom templates. Let’s start with a replacement template that shows just the length of the binary string instead of attempting to render it.

We’ll do that by over-riding the _rows_and_columns.html include template, which is used by Datasette on both the table page and the page used to display individual rows. Since we only want to over-ride this template for one particular table we’ll create a file called _rows_and_columns-timezones-timezones.html - the file name means this will only over-ride the template timezones table in our timezones database. Here’s our new template:

<table>
    <thead>
        <tr>
            {% for column in display_columns %}
                <th scope="col">{{ column }}</th>
            {% endfor %}
        </tr>
    </thead>
    <tbody>
    {% for row in display_rows %}
        <tr>
            {% for cell in row %}
                <td>
                    {% if cell.column == 'Geometry' %}
                        {{ cell.value|length }} bytes
                    {% else %}
                        {{ cell.value }}
                    {% endif %}
                </td>
            {% endfor %}
        </tr>
    {% endfor %}
    </tbody>
</table>

If we put that in a directory called templates/ we can tell Datasette to use it like this:

datasette timezones.db -m metadata.json \
    --load-extension=/usr/local/lib/mod_spatialite.dylib \
    --template-dir=templates/

Our localhost:8001/timezones/timezones page now looks like this:

But wouldn’t it be neat if we could see the actual shapes of these time zones? It turns out that’s actually pretty easy, using the combination of GeoJSON and the Leaflet mapping library.

GeoJSON is a neat, widely supported standard for encoding geographic information such as polygon shapes as JSON. SpatiaLite ships with built-in GeoJSON support in the form of the AsGeoJSON SQL function. We can use that function to turn any of our time zone geometries into a GeoJSON string:

select AsGeoJSON(Geometry) from timezones where tzid = 'Asia/Tokyo';

If you run that with Datasette you’ll get back a string of GeoJSON. You can paste that into geojson.io to instantly visualize it.

The Leaflet mapping library supports GeoJSON out of the box. We can construct a custom row.html template for our Datasette that loads Leaflet from unpkg.com, uses fetch() to execute the AsGeoJSON query and renders the result in a map on the page. Here’s the result:

And here’s the row-timezones-timezones.html template:

{% extends "row.html" %}
{% block extra_head %}
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.2.0/dist/leaflet.css" integrity="sha512-M2wvCLH6DSRazYeZRIm1JnYyh22purTM+FDB5CsyxtQJYeKq83arPe5wgbNmcFXGqiSH2XR8dT/fJISVA1r/zQ==" crossorigin=""/>
<script src="https://unpkg.com/leaflet@1.2.0/dist/leaflet.js" integrity="sha512-lInM/apFSqyy1o6s89K4iQUKg6ppXEgsVxT35HbzUupEVRh2Eu9Wdl4tHj7dZO0s1uvplcYGmt3498TtHq+log==" crossorigin=""></script>
<style>
#map {
  margin-top: 1em;
  width: 100%;
  height: 400px;
}
</style>
{% endblock %}

{% block content %}
{{ super() }}
<div id="map"></div>
<script>
var pk = location.pathname.split('/').slice(-1)[0];
var tiles = L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    detectRetina: true,
    attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, Points &copy 2012 LINZ'
});
var sql = 'select AsGeoJSON(Geometry) from timezones where PK_UID = ' + pk;
fetch('/timezones.json?sql=' + encodeURIComponent(sql)).then(r => r.json()).then(d => {
  var map = L.map('map', {layers: [tiles]});
  var geoJSON = JSON.parse(d.rows[0][0]);
  var layer = L.geoJSON(geoJSON)
  layer.addTo(map);
  map.fitBounds(layer.getBounds());
});
</script>
{% endblock %}

Publishing it to the internet

Normally we would use the datasette publish command to publish our database to Heroku or Zeit Now, but the SpatiaLite dependency means that won’t work for this case. Instead, we need to construct a custom Dockerfile that builds the SpatiaLite module.

Since we’re using Docker, we may as well have the Dockerfile download the shapefiles and build the SpatiaLite database for us all in one go. Here’s a Dockerfile that does exactly that:

FROM python:3.6-slim-stretch

RUN apt update
RUN apt install -y python3-dev gcc spatialite-bin libsqlite3-mod-spatialite wget unzip

RUN pip install https://github.com/simonw/datasette/archive/master.zip

# Download and import the shapefiles
RUN wget https://github.com/evansiroky/timezone-boundary-builder/releases/download/2017c/timezones.shapefile.zip \
    && unzip timezones.shapefile.zip && \
    cd dist && \
    echo ".loadshp combined_shapefile timezones CP1252 23032\nSELECT CreateSpatialIndex('timezones', 'geometry');" | spatialite timezones.db && \
    mv timezones.db .. && \
    cd .. && rm -rf dist && rm timezones.shapefile.zip

ENV SQLITE_EXTENSIONS /usr/lib/x86_64-linux-gnu/mod_spatialite.so

ADD metadata.json metadata.json

ADD templates templates

RUN datasette inspect timezones.db --inspect-file inspect-data.json

EXPOSE 8001

CMD ["datasette", "serve", "timezones.db", "--host", "0.0.0.0", "--cors", "--port", "8001", "--inspect-file", "inspect-data.json", "-m", "metadata.json", "--template-dir", "templates"]

The full code, including the supporting templates, can be found in simonw/timezones-api on GitHub.

If you have Docker installed (Docker for Mac is a one-click install package these days, it’s impressively painless) you can build the container like this:

docker build . -t timezones-api
# Wait for the image to build
docker run -p 8001:8001 timezones-api

Then you can visit http://localhost:8001/ to see your freshly built Datasette in your browser.

The easiest way to publish it online is using Zeit Now. Simply run the now command in the directory containing the Dockerfile and Zeit will upload the entire directory, build the container in the cloud and deploy it with a fresh URL. It’s by far the easiest Docker deployment environment I’ve ever used.

Now can even deploy directly from a public GitHub repository… so you can deploy your own copy of the API by running the following command:

$ now simonw/timezones-api
> Didn't find directory. Searching on GitHub...
> Deploying GitHub repository "simonw/timezones-api" under simonw
> Ready! https://simonw-timezones-api-fbihjcbnog.now.sh (copied to clipboard) [2s]
> Initializing…

Canned queries + SpatiaLite = instant geospatial APIs

Hopefully this has helped illustrate the ease with which Datasette, SpatiaLite and canned queries can be used to create and publish geospatial APIs. Thanks to projects like OpenStreetMap the world is full of high quality open geospatial data. Go build something cool with it!

Tags: geospatial, openstreetmap, sqlite, timezones, docker, datasette, leaflet

simonepri/geo-maps

2017-11-21T16:06:38+00:00

simonepri/geo-maps

Neat project which publishes GeoJSON maps of the world automatically derived from OpenStreetMap. Three variants are available: country political maritime boundaries, country political coastline boundaries and a general outline of the world’s land territories.

Tags: geospatial, openstreetmap, geojson

We Need to Stop Google's Exploitation of Open Communities

2011-04-22T10:00:00+00:00

We Need to Stop Google's Exploitation of Open Communities

Mikel Maron from OpenStreetMap is justifiably angry about Google MapMaker, which copies OpenStreetMap’s model of crowdsourcing geographic data (even copying the OSM idea of Mapping Parties) but keeps the data under a much more restrictive license, and uses the Google brand to market itself to African governments.

Tags: google, mapmaker, openstreetmap, recovered

Polymaps

2010-08-20T18:46:00+00:00

Polymaps

Absurdly classy: “a JavaScript library for image- and vector-tiled maps using SVG”. It can pull in image tiles from sources such as OpenStreetMap, then overlay SVG paths specified using GeoJSON. The demos make use of GeoJSON tiles for US states and counties hosted on AppEngine. The library is developed by Stamen and SimpleGeo, and released under a BSD license. SVG support in the browser is required.

Tags: google-app-engine, javascript, mapping, openstreetmap, stamen-design, svg, recovered, geojson, polymaps, simplegeo

MapOSMatic

2010-07-11T12:15:00+00:00

MapOSMatic

Clever service built on top of OpenStreetMap, which renders double sided city maps with a map and grid on one size and an A-Z street name index on the other. Runs on top of Mapnik, PostGIS and Cairo, with a few thousand additional lines of Python and Django.

Tags: cairo, django, mapping, openstreetmap, postgis, postgresql, python, recovered, maposmatic

getlatlon.com commit dae961a...

2010-07-10T12:22:00+00:00

getlatlon.com commit dae961a...

I’ve finally added an OpenStreetMap tab to getlatlon.com—here’s the diff, it turns out adding a custom OpenStreetMap layer to an existing Google Maps application only takes a few lines of boilerplate code.

Tags: getlatlon, google-maps, javascript, openstreetmap, projects, recovered

Why Google MapMaker is not Open

2010-03-16T10:41:39+00:00

Why Google MapMaker is not Open

Non-commercial use only, strict attribution requirements and you aren’t allowed to use the data for services that might compete with Google. This is why I’m disappointed every time I see Google encouraging people to contribute to Map Make, especially in the developing world—if those people contributed to OpenStreetMap instead they would be building something far more valuable for their community.

Tags: google, mapmaker, mikel-maron, openstreetmap

On walking into a disaster zone

2010-02-10T15:45:35+00:00

On walking into a disaster zone

Schuyler Erle: “The World Bank was looking for technical GIS professionals, ideally French-speaking, to go and advise the government [...] I can sort of speak French. Sure, why not?”

Tags: geospatial, haiti, openstreetmap, schuylererle, worldbank

OSM the default map in Haiti

2010-01-25T21:26:57+00:00

OSM the default map in Haiti

A search and rescue team member in Haiti sends word that digital maps constructed by the OpenStreetMap community are spreading by word of mouth and being loaded on to GPS units on the ground.

Tags: gps, haiti, inspiring, mapping, openstreetmap

The View from Above

2009-12-11T09:32:10+00:00

The View from Above

Andy Allan’s notes on three different projects that aerial imagery with OpenStreetMap. Andy and friends hired a small plane and took their own aerial photographs of Stratford-upon-Avon as a demo for a GIS conference. Aid agencies in the Philippines benefitted from OSM and a donation of high quality satellite imagery. Rural Georgia now has hiqh quality images from 2007 thanks to the Department of Agriculture.

Tags: aerialimagery, andy-allan, mapping, openstreetmap, satellites

Quoting Ivan Sanchez

2009-11-12T10:52:31+00:00

A set of geodata, or a map, is libre only if somebody can give you a cake with that map on top, as a present.

— Ivan Sanchez

Tags: caketest, geospatial, ivansanchez, mapping, openstreetmap

Temporary Mapping: Solar Decathlon

2009-10-13T15:18:13+00:00

Temporary Mapping: Solar Decathlon

The OpenStreetMap default renderer supports start_date and end_date tags, meaning you can map temporary installations (in this case the 2009 Solar Decathlon on the DC National Mall) and have them automatically appear and disappear at the correct times.

Tags: andrew-turner, mapping, openstreetmap, solardecathlon, tagging

OSM static map api

2009-10-12T13:37:42+00:00

OSM static map api

A very welcome addition to the OpenStreetMap world (with plenty of options for overlaying points, polygons etc) slightly marred by the size and relative ugliness of the OpenStreetMap watermark.

Tags: apis, mapping, openstreetmap, staticmaps, watermarks

OpenStreetMap Rendering Database

2009-10-10T13:05:43+00:00

OpenStreetMap Rendering Database

Amazon have added an OpenStreetMap snapshot as a public data set, thanks to some smart prompting by Jeremy Dunck.

Tags: amazon, ec2, jeremy-dunck, mapping, openstreetmap, publicdatasets, s3

openstreetmap genuine advantage

2009-09-29T09:49:52+00:00

openstreetmap genuine advantage

The OpenStreetMap data model (points, ways and relations, all allowing arbitrary key/value tags) is a real thing of beauty—simple to understand but almost infinitely extensible. Mike Migurski’s latest project adds PGP signing to OpenStreetMap, allowing organisations (such as local government) to add a signature to a way (a sequence of points) and a subset of its tags, then write that signature in to a new tag on the object.

Tags: cryptography, geospatial, mapping, michal-migurski, openstreetmap, pgp

"That's maybe a bit too dorky, even for us."

2009-09-28T22:39:27+00:00

"That's maybe a bit too dorky, even for us."

Astonishingly exciting: Flickr now have machine tag support for OpenStreetMap—tag a photo with osm:way=WAY_ID and Flickr will figure out what OSM feature you are talking about and link to it with a human readable description.

Tags: flickr, machinetags, openstreetmap, photography

OpenStreetMap: QuadTiles

2009-09-10T15:54:36+00:00

OpenStreetMap: QuadTiles

Fascinating explanation of a proposal for replacing lat, lon pairs in the OpenStreetMap database with a QuadTile-based addressing system.

Tags: algorithms, geospatial, openstreetmap, quadtiles

Tile Drawer

2009-08-26T09:32:56+00:00

Tile Drawer

The most inspired use of EC2 I’ve seen yet: center a map on an area, pick a Cascadenik stylesheet URL (or write and link to your own) and Tile Drawer gives you an Amazon EC2 AMI and a short JSON snippet. Launch the AMI with the JSON as the “user data” parameter and you get your own OpenStreetMap tile rendering server, which self-configures on startup and starts rendering and serving tiles using your custom design.

Via Mike Migurski

Tags: amazon, cascadenik, cloud-computing, ec2, json, mapnik, mapping, michal-migurski, openstreetmap, userdata

Best of OpenStreetMap

2009-08-13T12:30:05+00:00

Best of OpenStreetMap

I keep on telling people OpenStreetMap is this year’s Wikipedia—at its best, it beats commercially available maps. This “best of” site highlights the areas where OSM really shines (the yellow stars)—the German mapping community in particular have produced some outstanding cartography.

Via OpenGeoData

Tags: cartography, mapping, maps, openstreetmap, wikipedia

Hack Day tools for non-developers

2009-07-28T14:23:53+00:00

We're about to run our second internal hack day at the Guardian. The first was an enormous amount of fun and the second one looks set to be even more productive.

There's only one rule at hack day: build something you can demonstrate at the end of the event (Powerpoint slides don't count). Importantly though, our hack days are not restricted to just our development team: anyone from the technology department can get involved, and we extend the invitation to other parts of the organisation as well. At the Guardian, this includes journalists.

For our first hack day, I put together a list of "tools for non-developers" - sites, services and software that could be used for hacking without programming knowledge as a pre-requisite. I'm now updating that list with recommendations from elsewhere. Here's the list so far:

Freebase

Originally a kind of structured version of Wikipedia, Freebase changed its focus last year towards being a "social database about things you know and love". In other words, it's the most powerful OCD-enabler in the history of the world. Create your own "Base" on any subject you like, set up your own types and start gathering together topics from the millions already available in Freebase - or add your own. Examples include the Battlestar Galactica base, the Tall Ships base and the fabulous Database base. If you are a developer the tools in the Make Things with Freebase section are top notch.

Dabble DB

Dabble is a weird combination of a spreadsheet, an online database and a set of visualisation tools. Watch the 8 minute demo to get an idea of how powerful this is - you can start off by loading in an existing spreadsheet and take it from there. You'll need to sign up for the free 30 day trial.

Google Docs

You can always build a hack in Excel, but Google Spreadsheets is surprisingly powerful and means that you can collaborate with others on your hack (including developers, who can use the Google Docs API to get at the data in your spreadsheet). Check out the following tutorials, which describe ways of using Google Spreadsheets to scrape in data from other webpages and output it in interesting formats:

There's also a simple way to create a form that submits data in to a Google Spreadsheet.

Yahoo! Pipes

Visual tools for combining, filtering and modifying RSS feeds. Combine with the large number of full-content feeds on guardian.co.uk for all sorts of interesting possibilities. Here's a tutorial that incorporates Google Docs as well.

Google My Maps

Google provide a really neat interface for adding your own points, lines and areas to a Google Map. Outputs KML, a handy file format for carting geographic data around between different tools.

If you already have a KML or GeoRSS feed URL from somewhere (e.g. the output of a Yahoo! Pipe), you can paste it directly in to the Google Maps search box to see the points rendered on a map.

Google SketchUp

A simple to use 3D drawing package that lets you create 3D models of real-world buildings and then import them in to Google Earth.

OpenStreetMap

Try your hand at some open source cartography on OpenStreetMap, the geographic world's answer to Wikipedia. If you have the equipment you can contribute GPS traces, otherwise there's a clever online editor that will let you trace out roads from satellite photos - or you could just make sure your favourite pub is included on the map. The export tools can provide vector or static maps, and if you export as SVG you can further edit your map in Illustrator or Inkscape.

CloudMade Maps

Commercial tools built on top of OpenStreetMap, the most exciting of which allows you to create your own map theme by setting your preferred colours and line widths for various types of map feature.

Many Eyes

IBM Research's suite of data visualisation tools, with a wiki-style collaboration platform for publishing data and creating visualisations.

Dapper

Dapper provides a powerful tool for screen scraping websites, without needing to write any code. Output formats include RSS, iCalendar and Google Maps.

TiddlyWiki

TiddlyWiki is a complete wiki in a single HTML file, which you can save locally and use as a notebook, collaboration tool and much more. There's a large ecosystem of plugins and macros which can be used to extend it with new features - see TiddlyVault for an index.

Your help needed

There must be dozens, if not hundreds of useful tools missing from the above. Tell me in the comments and I'll add them to the list.

Tags: freebase, google, google-maps, guardian, hackday, mapping, nondevelopers, openstreetmap, pipes, sketchup, tools, yahoo-pipes, computer-literacy