Simon Willison's Weblog: leaflet

Coding agents for data analysis

2026-03-16T20:12:32+00:00

Here's the handout I prepared for my NICAR 2026 workshop "Coding agents for data analysis" - a three hour session aimed at data journalists demonstrating ways that tools like Claude Code and OpenAI Codex can be used to explore, analyze and clean data.

Here's the table of contents:

Coding agents

Warmup: ChatGPT and Claude

Setup Claude Code and Codex

Asking questions against a database

Exploring data with agents

Cleaning data: decoding neighborhood codes

Creating visualizations with agents

Scraping data with agents

I ran the workshop using GitHub Codespaces and OpenAI Codex, since it was easy (and inexpensive) to distribute a budget-restricted API key for Codex that attendees could use during the class. Participants ended up burning $23 of Codex tokens.

The exercises all used Python and SQLite and some of them used Datasette.

One highlight of the workshop was when we started running Datasette such that it served static content from a viz/ folder, then had Claude Code start vibe coding new interactive visualizations directly in that folder. Here's a heat map it created for my trees database using Leaflet and Leaflet.heat, source code here.

I designed the handout to also be useful for people who weren't able to attend the session in person. As is usually the case, material aimed at data journalists is equally applicable to anyone else with data to explore.

Tags: data-journalism, geospatial, python, speaking, sqlite, ai, datasette, generative-ai, llms, github-codespaces, nicar, coding-agents, claude-code, codex, leaflet

wolf-h3-viewer.glitch.me

2025-03-09T14:51:55+00:00

wolf-h3-viewer.glitch.me

Neat interactive visualization of Uber's H3 hexagonal geographical indexing mechanism.

Here's the source code.

Why does H3 use hexagons? Because Hexagons are the Bestagons:

When hexagons come together, they form three-sided joints 120 degrees apart. This, for the least material, is the most mechanically stable arrangement.

Only triangles, squares, and hexagons can tile a plane without gaps, and of those three shapes hexagons offer the best ratio of perimeter to area.

Via Hacker News comment

Tags: geospatial, javascript, leaflet

Visualizing local election results with Datasette, Observable and MapLibre GL

2024-11-09T23:32:06+00:00

Alex Garcia and myself hosted the first Datasette Open Office Hours on Friday - a live-streamed video session where we hacked on a project together and took questions and tips from community members on Discord.

We didn't record this one (surprisingly not a feature that Discord offers) but we hope to do more of these and record them in the future.

This post is a detailed write-up of what we built during the session.

San Mateo County election results

I live in El Granada, a tiny town just north of Half Moon Bay in San Mateo County, California.

Every county appears to handle counting and publishing election results differently. For San Mateo County the results are published on this page, and detailed per-precinct and per-candidate breakdowns are made available as a CSV file.

(I optimistically set up a Git scraper for these results in simonw/scrape-san-mateo-county-election-results-2024 only to learn that the CSV is updated just once a day, not continually as the ballots are counted.)

I'm particularly invested in the results of the Granada Community Services District board member elections. Our little town of El Granada is in "unincorporated San Mateo County" which means we don't have a mayor or any local officials, so the closest we get to hyper-local government is the officials that run our local sewage and parks organization! My partner Natalie ran the candidate forum event (effectively the debate) featuring three of the four candidates running for the two open places on the board.

Let's explore the data for that race using Datasette.

Importing CSV data into Datasette

I ran my part of the demo using Datasette Cloud, the beta of my new hosted Datasette service.

I started by using the pre-configured datasette-import plugin to import the data from the CSV file into a fresh table:

Modifying the schema

The table imported cleanly, but all of the columns from the CSV were still being treated as text. I used the datasette-edit-schema plugin to switch the relevant columns to integers so that we could run sums and sorts against them.

(I also noted that I really should add a "detect column types" feature to that plugin!)

The resulting 15,589 rows represent counts from individual precincts around the county for each of the races and measures on the ballot, with a row per precinct per candidate/choice per race.

Faceting and filtering the table

Since I'm interested in the Granada Community Services District election, I applied a facet on "Contest_title" and then used that to select that specific race.

I applied additional facets on "candidate_name" and "Precinct name".

This looks right to me: we have 7 precincts and 4 candidates for 28 rows in total.

Importing geospatial precinct shapes

Those precinct names are pretty non-descriptive! What does 33001 mean?

To answer that question, I added a new table.

San Mateo County offers precinct maps in the form of 23 PDF files. Our precincts are in the "Unincorporated Coastside" file:

Thankfully the county also makes that data available as geospatial data, hosted using Socrata with an option to export as GeoJSON.

The datasette-import plugin can handle JSON files... and if a JSON file contains a top-level object with a key that is an array of objects, it will import those objects as a table.

Dragging that file into Datasette is enough to import it as a table with a properties JSON column containing properties and a geometry JSON columnn with the GeoJSON geometry.

Here's where another plugin kicks in: datasette-leaflet-geojson looks for columns that contain valid GeoJSON geometries and... draws them on a map!

So now we can see the shape of the individual geometries.

Enriching that data to extract the precinct IDs

The precinctid is present in the data, but it's tucked away in a JSON object in that properties JSON blob. It would be more convenient if it was a top-level column.

Datasette's enrichments feature provides tools for running operations against every row in a table and adding new columns based on the results.

My Datasette Cloud instance was missing the datasette-enrichments-quickjs plugin that would let me run JavaScript code against the data. I used my privileged access on Datasette Cloud to add that plugin to my requirements and restarted the instance to install it.

I used that to run this JavaScript code against every row in the table and saved the output in a new precinct_id column:

function enrich(row) {
  return JSON.parse(row.properties).precinctid;
}

This took less than a second to run, adding and populating a new precinct_id column for the table.

Running a join

I demonstrated how to run a join between the election results and the precincts table using the Datasette SQL query editor.

I tried a few different things, but the most interesting query was this one:

select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where 
  Contest_title = "Granada Community Services District Members, Board of Directors"
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;

Creating an API token to access the data

I was nearly ready to hand over to Alex for the second half of our demo, where he would use Observable Notebooks to build some custom visualizations on top of the data.

A great pattern for this is to host the data in Datasette and then fetch it into Observable via the Datasette JSON API.

Since Datasette Cloud instances are private by default we would need to create an API token that could do this.

I used this interface (from the datasette-auth-tokens plugin) to create a new token with read-only access to all databases and tables in the instance:

Since we're running a dedicated instance just for Datasette Public Office Hours there's no reason not to distribute that read-only token in publically accessible code.

Getting CORS working

Embarrassingly, I had forgotten that we would need CORS headers in order to access the data from an Observable notebook. Thankfully we have another plugin for that: datasette-cors. I installed that quickly and we confirmed that it granted access to the API from Observable as intended.

I handed over to Alex for the next section of the demo.

Working with Datasette in Observable

Alex started by running a SQL query from client-side JavaScript to pull in the joined data for our specific El Granada race:

sql = `
select
  Precinct_name,
  precincts.geometry,
  Split_name,
  Reporting_flag,
  Update_count,
  Pct_Id,
  Pct_seq_nbr,
  Reg_voters,
  Turn_Out,
  Contest_Id,
  Contest_seq_nbr,
  Contest_title,
  Contest_party_name,
  Selectable_Options,
  candidate_id,
  candidate_name,
  Candidate_Type,
  cand_seq_nbr,
  Party_Code,
  total_ballots,
  total_votes,
  total_under_votes,
  total_over_votes,
  [Vote Centers_ballots],
  [Vote Centers_votes],
  [Vote Centers_under_votes],
  [Vote Centers_over_votes],
  [Vote by Mail_ballots],
  [Vote by Mail_votes],
  [Vote by Mail_under_votes],
  [Vote by Mail_over_votes]
from
  election_results join precincts on election_results.Precinct_name = precincts.precinct_id
where "Contest_title" = "Granada Community Services District Members, Board of Directors"
limit 101;`

And in the next cell:

raw_data = fetch(
  `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?_shape=array&sql=${encodeURIComponent(
    sql
  )}`,
  {
    headers: {
      Authorization: `Bearer ${secret}`
    }
  }
).then((r) => r.json())

Note the ?_shape=array parameter there, which causes Datasette to output the results directly as a JSON array of objects.

That's all it takes to get the data into Observable. Adding another cell like this confirms that the data is now available:

Inputs.table(raw_data)

Visualizing those with MapLibre GL

There are plenty of good options for visualizing GeoJSON data using JavaScript in an Observable notebook.

Alex started with MapLibre GL, using the excellent OpenFreeMap 3D tiles:

viewof map = {
  const container = html`<div style="height:800px;">`;
  yield container;
  const map = (container.value = new maplibregl.Map({
    container,
    zoom: 2,
    //style: "https://basemaps.cartocdn.com/gl/voyager-gl-style/style.json",
    style: "https://tiles.openfreemap.org/styles/liberty",
    scrollZoom: true
  }));
  yield container;

  map.on("load", function () {
    map.fitBounds(d3.geoBounds(data), { duration: 0 });
    map.addSource("precincts", {
      type: "geojson",
      data: data
    });
    map.addLayer({
      id: "precincts",
      type: "fill",
      source: "precincts",
      paint: {
        "fill-opacity": 0.4,
        "fill-color": [
          "case",
          ["==", ["get", "ratio"], null], "#000000",
          [
            "interpolate",
            ["linear"],
            ["get", "ratio"],
            0.0, "#0000ff",
            0.5, "#d3d3d3",
            1.0, "#ff0000"
          ]
        ]
      }
    });
    map.on("click", "precincts", (e) => {
      const { precinct, ratio } = e.features[0].properties;
      const description = JSON.stringify();

      new maplibregl.Popup()
        .setLngLat(e.lngLat)
        .setHTML(description)
        .addTo(map);
    });
  });
  invalidation.then(() => map.remove());
}

(This is just one of several iterations, I didn't capture detailed notes of every change Alex made to the code.)

Observable Plot

Observable notebooks come pre-loaded with the excellent Observable Plot charting library - Mike Bostock's high-level charting tool built on top of D3.

Alex used that to first render the shapes of the precincts directly, without even needing a tiled basemap:

Plot.plot({
  width,
  height: 600,
  legend: true,
  projection: {
    type: "conic-conformal",
    parallels: [37 + 4 / 60, 38 + 26 / 60],
    rotate: [120 + 30 / 60, 0],
    domain: data
  },
  marks: [
    Plot.geo(data, {
      strokeOpacity: 0.1,
      fill: "total_votes",
      title: (d) => JSON.stringify(d.properties),
      tip: true
    })
  ]
})

The parallels and rotate options there come from the handy veltman/d3-stateplane repo, which lists recommended settings for the State Plane Coordinate System used with projections in D3. Those values are for California Zone 3.

Bringing it all together

For the grand finale, Alex combined everything learned so far to build an interactive map allowing a user to select any of the 110 races on the ballot and see a heatmap of results for any selected candidate and option:

You can try this out in Alex's notebook. Here's the relevant code (Observable cells are divided by // --- comments). Note that Observable notebooks are reactive and allow variables to be referenced out of order.

// Select the contest
viewof contest = Inputs.select(contests, { label: "Choose a contest" })

// ---

// And the candidate
viewof candidate = Inputs.radio(
  candidates,
  {
    label: "Choose a candidate",
    value: candidates[0]
  }
)

// ---

// Show the map itself
Plot.plot({
  width,
  height: 600,
  legend: true,
  color: { scheme: "blues", legend: true },
  projection: {
    type: "mercator",
    domain: data2
  },
  marks: [
    Plot.geo(data2, {
      strokeOpacity: 0.1,
      fill: "ratio",
      tip: true
    })
  ]
})
# ---
data2 = ({
  type: "FeatureCollection",
  features: raw_data2.map((d) => ({
    type: "Feature",
    properties: {
      precinct: d.Precinct_name,
      total_ballots: d.total_ballots,
      ratio: JSON.parse(d.votes_by_candidate)[candidate] / d.total_ballots
    },
    geometry: JSON.parse(d.geometry)
  }))
})

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_grop_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

// Fetch the available contests
contests = query("select distinct Contest_title from election_results").then(
  (d) => d.map((d) => d.Contest_title)
)

// ---

// Extract available candidates for selected contest

candidates = Object.keys(
  JSON.parse(raw_data2[0].votes_by_candidate)
)

// ---

function query(sql, params = {}) {
  return fetch(
    `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?${new URLSearchParams(
      { sql, _shape: "array", ...params }
    ).toString()}`,
    {
      headers: {
        Authorization: `Bearer ${secret}`
      }
    }
  ).then((r) => r.json());
}

We'll be doing this again

This was our first time trying something like this and I think it worked really well. We're already thinking about ways to improve it next time:

I want to record these sessions and make them available on YouTube for people who couldn't be there live
It would be fun to mix up the format. I'm particularly keen on getting more people involved giving demos - maybe having 5-10 minute lightning demo slots so we can see what other people are working on

Keep an eye on this blog or on the Datasette Discord for news about future sessions.

Tags: geospatial, mapping, politics, projects, datasette, datasette-cloud, alex-garcia, datasette-public-office-hours, leaflet

datasette-leaflet-geojson 0.8.2

2024-04-25T05:04:35+00:00

Release: datasette-leaflet-geojson 0.8.2

Tags: datasette, leaflet

tiny-world-map

2024-04-21T22:11:14+00:00

tiny-world-map

I love this project. It’s a JavaScript file (694K uncompressed, 283KB compressed) which can be used with the Leaflet mapping library and provides a SVG base map of the world with country borders and labels for every world city with a population more than 48,000—10,000 cities total.

This means you can bundle an offline map of the world as part of any application that doesn’t need a higher level of detail. A lot of smaller island nations are missing entirely though, so this may not be right for every project.

It even includes a service worker to help implement offline mapping support, plus several variants of the map with less cities that are even smaller.

Via Hacker News

Tags: geospatial, javascript, mapping, svg, serviceworkers, leaflet

Claude and ChatGPT for ad-hoc sidequests

2024-03-22T19:44:12+00:00

Here is a short, illustrative example of one of the ways in which I use Claude and ChatGPT on a daily basis.

I recently learned that the Adirondack Park is the single largest park in the contiguous United States, taking up a fifth of the state of New York.

Naturally, my first thought was that it would be neat to have a GeoJSON file representing the boundary of the park.

A quick search landed me on the Adirondack Park Agency GIS data page, which offered me a shapefile of the "Outer boundary of the New York State Adirondack Park as described in Section 9-0101 of the New York Environmental Conservation Law". Sounds good!

I knew there were tools for converting shapefiles to GeoJSON, but I couldn't remember what they were. Since I had a terminal window open already, I typed the following:

llm -m opus -c 'give me options on macOS for CLI tools to turn a shapefile into GeoJSON'

Here I am using my LLM tool (and llm-claude-3 plugin) to run a prompt through the new Claude 3 Opus, my current favorite language model.

It replied with a couple of options, but the first was this:

ogr2ogr -f GeoJSON output.geojson input.shp

So I ran that against the shapefile, and then pasted the resulting GeoJSON into geojson.io to check if it worked... and nothing displayed. Then I looked at the GeoJSON and spotted this:

"coordinates": [ [ -8358911.527799999341369, 5379193.197800002992153 ] ...

That didn't look right. Those co-ordinates aren't the correct scale for latitude and longitude values.

So I sent a follow-up prompt to the model (the -c option means "continue previous conversation"):

llm -c 'i tried using ogr2ogr but it gave me back GeoJSON with a weird coordinate system that was not lat/lon that i am used to'

It suggested this new command:

ogr2ogr -f GeoJSON -t_srs EPSG:4326 output.geojson input.shp

This time it worked! The shapefile has now been converted to GeoJSON.

Time elapsed so far: 2.5 minutes (I can tell from my LLM logs).

I pasted it into Datasette (with datasette-paste and datasette-leaflet-geojson) to take a look at it more closely, and got this:

That's not a single polygon! That's 106 line segments... and they are fascinating. Look at those descriptions:

thence westerly along the northern line of lots 204 and 203 to the midpoint of the northern line of lot 203

This is utterly delightful. The shapefile description did say "as described in Section 9-0101 of the New York Environmental Conservation Law", so I guess this is how you write geographically boundaries into law!

But it's not what I wanted. I want a single polygon of the whole park, not 106 separate lines.

I decided to switch models. ChatGPT has access to Code Interpreter, and I happen to know that Code Interpreter is quite effective at processing GeoJSON.

I opened a new ChatGPT (with GPT-4) browser tab, uploaded my GeoJSON file and prompted it:

This GeoJSON file is full of line segments. Use them to create me a single shape that is a Polygon

OK, so it wrote some Python code and ran it. But did it work?

I happen to know that Code Interpreter can save files to disk and provide links to download them, so I told it to do that:

Save it to a GeoJSON file for me to download

I pasted that into geojson.io, and it was clearly wrong:

So I told it to try again. I didn't think very hard about this prompt, I basically went with a version of "do better":

that doesn't look right to me, check that it has all of the lines in it

It gave me a new file, optimistically named complete_polygon.geojson. Here's what that one looked like:

This is getting a lot closer! Note how the right hand boundary of the park looks correct, but the rest of the image is scrambled.

I had a hunch about the fix. I pasted in a screenshot of where we were so far and added my hunch about the solution:

That almost works but you need to sort the line segments first, it looked like this:

Honestly, pasting in the screenshot probably wasn't necessary here, but it amused me.

... and ChatGPT churned away again ...

sorted_polygon.geojson is spot on! Here's what it looks like:

Total time spent in ChatGPT: 3 minutes and 35 seconds. Plus 2.5 minutes with Claude 3 earlier, so an overall total of just over 6 minutes.

Here's the full Claude transcript and the full transcript from ChatGPT.

This isn't notable

The most notable thing about this example is how completely not notable it is.

I get results like this from these tools several times a day. I'm not at all surprised that this worked, in fact, I would've been mildly surprised if it had not.

Could I have done this without LLM assistance? Yes, but not nearly as quickly. And this was not a task on my critical path for the day - it was a sidequest at best and honestly more of a distraction.

So, without LLM tools, I would likely have given this one up at the first hurdle.

A year ago I wrote about how AI-enhanced development makes me more ambitious with my projects. They are now so firmly baked into my daily work that they influence not just side projects but tiny sidequests like this one as well.

This certainly wasn't simple

Something else I like about this example is that it illustrates quite how much depth there is to getting great results out of these systems.

In those few minutes I used two different interfaces to call two different models. I sent multiple follow-up prompts. I triggered Code Interpreter, took advantage of GPT-4 Vision and mixed in external tools like geojson.io and Datasette as well.

I leaned a lot on my existing knowledge and experience:

I knew that tools existed for commandline processing of shapefiles and GeoJSON
I instinctively knew that Claude 3 Opus was likely to correctly answer my initial prompt
I knew the capabilities of Code Interpreter, including that it has libraries that can process geometries, what to say to get it to kick into action and how to get it to give me files to download
My limited GIS knowledge was strong enough to spot a likely coordinate system problem, and I guessed the fix for the jumbled lines
My prompting intuition is developed to the point that I didn't have to think very hard about what to say to get the best results

If you have the right combination of domain knowledge and hard-won experience driving LLMs, you can fly with these things.

Isn't this a bit trivial?

Yes it is, and that's the point. This was a five minute sidequest. Writing about it here took ten times longer than the exercise itself.

I take on LLM-assisted sidequests like this one dozens of times a week. Many of them are substantially larger and more useful. They are having a very material impact on my work: I can get more done and solve much more interesting problems, because I'm not wasting valuable cycles figuring out ogr2ogr invocations or mucking around with polygon libraries.

Not to mention that I find working this way fun! It feels like science fiction every time I do it. Our AI-assisted future is here right now and I'm still finding it weird, fascinating and deeply entertaining.

LLMs are useful

There are many legitimate criticisms of LLMs. The copyright issues involved in their training, their enormous power consumption and the risks of people trusting them when they shouldn't (considering both accuracy and bias) are three that I think about a lot.

The one criticism I wont accept is that they aren't useful.

One of the greatest misconceptions concerning LLMs is the idea that they are easy to use. They really aren't: getting great results out of them requires a great deal of experience and hard-fought intuition, combined with deep domain knowledge of the problem you are applying them to.

I use these things every day. They help me take on much more interesting and ambitious problems than I could otherwise. I would miss them terribly if they were no longer available to me.

Tags: geospatial, shapefiles, geojson, ai, openai, generative-ai, chatgpt, llms, ai-assisted-programming, anthropic, claude, code-interpreter, coding-agents, leaflet

datasette-leaflet-geojson 0.8.1

2023-12-07T20:54:37+00:00

Release: datasette-leaflet-geojson 0.8.1

Tags: datasette, leaflet

Exploration de données avec Datasette

2023-05-27T00:36:52+00:00

Exploration de données avec Datasette

One of the great delights of open source development is seeing people run workshops on your project, even more so when they’re in a language other than English! Romain Clement presented this French workshop for the Python Grenoble meetup on 25th May 2023, using GitHub Codespaces as the environment. It’s pretty comprehensive, including a 300,000+ row example table which illustrates Datasette plugins such as datasette-cluster-map and datasette-leaflet-geojson.

Tags: tutorials, datasette, github-codespaces, leaflet

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

datasette-leaflet-freedraw 0.3.1

2022-02-03T20:24:38+00:00

Release: datasette-leaflet-freedraw 0.3.1

Tags: datasette, leaflet

datasette-leaflet-freedraw 0.3

2022-01-14T02:04:12+00:00

Release: datasette-leaflet-freedraw 0.3

Tags: datasette, leaflet

Creating a minimal SpatiaLite database with Python

2021-12-17T23:57:29+00:00

TIL: Creating a minimal SpatiaLite database with Python

Tags: leaflet

Weeknotes: Mostly messing around with map tiles

2021-02-07T05:53:19+00:00

Most of what I worked on this week was covered in Serving map tiles from SQLite with MBTiles and datasette-tiles. I built two new plugins: datasette-tiles for serving map tiles, and datasette-basemap which bundles map tiles for zoom levels 0-6 of OpenStreetMap. I also released download-tiles for downloading tiles and bundling them into an MBTiles database.

sqlite-utils 3.4.1

I added one new feature to sqlite-utils: the sqlite-utils import command can now be configured to read CSV files using alternative delimiters, by passing the --delimiter option or the --quotechar option.

This is covered in the documentation, which provides the following example:

name;description
Cleo;|Very fine; a friendly dog|
Pancakes;A local corgi

Imported using:

sqlite-utils insert dogs.db dogs dogs.csv \
  --delimiter=";" --quotechar="|"

Datasette 0.54.1

I spotted a subtle but nasty regression in Datasette: a change I made to how hidden form fields worked on the table page meant that clearing the _search search input and re-submitting the form didn't take effect, and the search would persist. Datasette 0.54.1 fixes that bug.

Releases this week

datasette-jellyfish: 1.0.1 - 2021-02-06
Datasette plugin adding SQL functions for fuzzy text matching powered by Jellyfish
sqlite-utils: 3.4.1 - 2021-02-06
Python CLI utility and library for manipulating SQLite databases
datasette-tiles: 0.5 - 2021-02-04
Mapping tile server for Datasette, serving tiles from MBTiles packages
download-tiles: 0.4 - 2021-02-03
Download map tiles and store them in an MBTiles database
datasette-basemap: 0.2 - 2021-02-02
A basemap for Datasette and datasette-leaflet
datasette: 0.54.1 - 2021-02-02
An open source multi-tool for exploring and publishing data
datasette-cluster-map: 0.17.1 - 2021-02-01
Datasette plugin that shows a map for any data with latitude/longitude columns
datasette-leaflet: 0.2.2 - 2021-02-01
Datasette plugin adding the Leaflet JavaScript library

TIL this week

Tags: csv, openstreetmap, projects, datasette, weeknotes, sqlite-utils, leaflet

Serving map tiles from SQLite with MBTiles and datasette-tiles

2021-02-04T01:09:30+00:00

Working on datasette-leaflet last week re-kindled my interest in using Datasette as a GIS (Geographic Information System) platform. SQLite already has strong GIS functionality in the form of SpatiaLite and datasette-cluster-map is currently the most downloaded plugin. Most importantly, maps are fun!

MBTiles

I was talking to Tom MacWright on Monday and I mentioned that I'd been thinking about how SQLite might make a good mechanism for distributing tile images for use with libraries like Leaflet. "I might be able to save you some time there" he said... and he showed me MBTiles, a specification he started developing ten years ago at Mapbox which does exactly that - bundles tile images up in SQLite databases.

(My best guess is I read about MBTiles a while ago, then managed to forget about the spec entirely while the idea of using SQLite for tile distribution wedged itself in my head somewhere.)

The new datasette-tiles plugin

I found some example MBTiles files on the internet and started playing around with them. My first prototype used the datasette-media plugin, described here previously in Fun with binary data and SQLite. I used some convoluted SQL to teach it that hits to /-/media/tiles/{z},{x},{y} should serve up content from the tiles table in my MBTiles database - you can see details of that prototype in this TIL: Serving MBTiles with datasette-media.

The obvious next step was to write a dedicated plugin: datasette-tiles. Install it and run Datasette against any MBTiles database file and the plugin will set up a /-/tiles/db-name/z/x/y.png endpoint that serves the specified tiles.

It also adds a tile explorer view with a pre-configured Leaflet map. Here's a live demo serving up a subset of Stamen's toner map - just zoom levels 6 and 7 for the country of Japan.

Here's how to run this on your own computer:

# Install Datasette
brew install datasette
# Install the plugin
datasette install datasette-tiles
# Download the japan-toner.db database
curl -O https://datasette-tiles-demo.datasette.io/japan-toner.db
# Launch Datasette and open a browser
datasette japan-toner.db -o
# Use the cog menu to access the tile explorer
# Or visit http://127.0.0.1:8001/-/tiles/japan-toner

Creating MBTiles files with my download-tiles tool

A sticking point when I started playing with MBTiles was finding example files to work with.

After some digging, I came across the amazing HOT Export Tool. It's a project by the Humanitarian OpenStreetMap Team that allows anyone to export subsets of data from OpenStreetMap in a wide variety of formats, including MBTiles.

I filed a minor bug report against it, and in doing so took a look at the source code (it's all open source)... and found the code that assembles MBTiles files. It uses another open source library called Landez, which provides functions for downloading tiles from existing providers and bundling those up as an MBTiles SQLite file.

I prefer command-line tools for this kind of thing over using Python libraries directly, so I fired up my click-app cookiecutter template and built a thin command-line interface over the top of the library.

The new tool is called download-tiles and it does exactly that: downloads tiles from a tile server and creates an MBTiles SQLite database on disk containing those tiles.

Please use this tool responsibly. Downloading large numbers of tiles is bad manners. Be sure to familiarize yourself with the OpenStreetMap Tile Usage Policy, and use the tool politely when pointing it at other tile servers.

Basic usage is as follows:

download-tiles world.mbtiles

By default the tool pulls tiles from OpenStreetMap. The above command will fetch zoom levels 0-3 of the entire world - 85 tiles total, well within acceptable usage limits.

Various options (described in the README) can be used to customize the tiles that are downloaded. Here's how I created the japan-toner.db demo database, linked to above:

download-tiles japan-toner.mbtiles \ 
    --zoom-levels 6-7 \
    --country Japan \
    --tiles-url "http://{s}.tile.stamen.com/toner/{z}/{x}/{y}.png" \
    --tiles-subdomains "a,b,c,d" \
    --attribution 'Map tiles by Stamen Design, under CC BY 3.0. Data by OpenStreetMap, under CC BY SA.'

The --country Japan option here looks up the bounding box for Japan using Nominatim. --zoom-levels 6-7 fetches zoom levels 6 and 7 (in this case that makes for 193 tiles total). --tiles-url and --tiles-subdomain configure the tile server to fetch them from. The --attribution option bakes that string into the metadata table for the database - which is then used to display it correctly in the tile explorer (and eventually in other Datasette plugins).

datasette-basemap

Out of the box, Datasette's current Leaflet plugins (datasette-cluster-map, datasette-leaflet-geojson and so on) serve tiles directly from the OpenStreetMap tile server.

I've never felt particularly comfortable about this. Users can configure the plugins to run against other tile servers, but pointing to OpenStreetMap as a default was the easiest way to ensure these plugins would work for people who just wanted to try them out.

Now that I have the tooling for bundling map subsets, maybe I can do better.

datasette-basemap offers an alternative: it's a plugin that bundles a 22.7MB SQLite file containing zoom levels 0-6 of OpenStreetMap - 5,461 tiles total.

Running pip install datasette-basemap (or datasette install datasette-basemap) will install the plugin, complete with that database - and register it with Datasette.

Start Datasette with the plugin installed and /basemap will expose the bundled database. Install datasette-tiles and you'll be able to browse it as a tile server: here's a demo.

(I recommend also installing datasette-render-images so you can see the tile images themselves in the regular table view, like this.)

Zoom level 6 is close enough that major cities and the roads between them are visible, for all of the countries in the world. Not bad for 22.7MB!

This is the first time I've built a Datasette plugin that bundles a full SQLite database as part of the Python package. The pattern seems to work well - I'm excited to explore it further with other projects.

Bonus feature: tile stacks

I added one last feature to datasette-tiles before writing everything up for my blog. I'm calling this feature tile stacks - it lets you serve tiles from multiple MBTiles files, falling back to other files if a tile is missing.

Imagine you had a low-zoom-level world map (similar to datasette-basemap) and a number of other databases providing packages of tiles for specific countries or cities. You could run Datasette like this:

datasette basemap.mbtiles japan.mbtiles london.mbtiles tokyo.mbtiles

Hitting /-/tiles-stack/1/1/1.png would seek out the specified tile in the tokyo.mbtiles file, then fall back to london.mbtiles and then japan.mbtiles and finally basemap.mbtiles if it couldn't find it.

For a demo, visit https://datasette-tiles-demo.datasette.io/-/tiles-stack and zoom in on Japan. It should start to display the Stamen toner map once you get to zoom levels 6 and 7.

Next steps

I've been having a lot of fun exploring MBTiles - it's such a natural fit for Datasette, and it's exciting to be able to build new things on top of nearly a decade of innovation by other geo-hackers.

There are plenty of features missing from datasette-tiles.

It currently only handles .png image data, but the MBTiles 1.3 specification also defines .jpg and .webp tiles, plus vector tiles using Mapbox's .pbf gzip-compressed protocol buffers.

UTFGrid is a related specification for including "rasterized interaction data" in MBTiles databases - it helps efficiently provide maps with millions of embedded objects.

As a newcomer to the MBTiles world I'd love to hear suggestions for new features and feedback on how I can improve what I've got so far in the datasette-tiles issues.

Being able to serve your own map tiles like this feels very much in the spirit of the OpenStreetMap project. I'm looking forward to using my own tile subsets for any future projects that fit within a sensible tile subset.

Tags: geospatial, mapping, openstreetmap, projects, sqlite, datasette, tom-macwright, leaflet

Serving MBTiles with datasette-media

2021-02-03T23:12:05+00:00

TIL: Serving MBTiles with datasette-media

Tags: leaflet

datasette-basemap 0.2

2021-02-02T23:33:57+00:00

Release: datasette-basemap 0.2

Tags: datasette, leaflet

datasette-basemap 0.1

2021-02-02T07:07:43+00:00

Release: datasette-basemap 0.1

Tags: datasette, leaflet

datasette-basemap 0.1a1

2021-02-02T02:02:51+00:00

Release: datasette-basemap 0.1a1

Tags: datasette, leaflet

datasette-basemap 0.1a

2021-02-02T01:59:05+00:00

Release: datasette-basemap 0.1a

Tags: datasette, leaflet

datasette-leaflet 0.2.2

2021-02-01T22:20:28+00:00

Release: datasette-leaflet 0.2.2

Tags: datasette, leaflet

Weeknotes: datasette-leaflet, datasette-plugin cookiecutter upgrades

2021-01-31T07:39:10+00:00

This week I shipped Datasette 0.54, sent out the latest Datasette Newsletter and then mostly worked on follow-up projects.

datasette-leaflet

Datasette 0.54 introduced support for JavaScript modules. datasette-leaflet aims to solve a growing problem: there are now three Datasette plugins that use the Leaflet JavaScript mapping library, and all three were loading it in different ways and using different versions.

Those three plugins - datasette-leaflet-freedraw, datasette-leaflet-geojson and datasette-cluster-map - now all depend on datasette-leaflet. This should mean that even if multiple plugins are installed they will all load and use the same copy of the Leaflet library.

There's just one problem: datasette-leaflet-freedraw uses the Leaflet.FreeDraw module, which it turns out already bundles its own copy of Leaflet. I still need to figure out how to build a version of that package which can take advantage of the shared copy of Leaflet - see open issue #5.

datasette-plugin and cookiecutter tests

One of the tricker aspects of building a Datasette plugin is bundling static assets such as JavaScript and CSS in the plugin to be distributed via PyPI. I know this is hard because I frequently mess up the package_data= section of setup.py and have to ship another release to fix my mistake!

I've upgraded the datasette-plugin cookiecutter template to help with this. The template now asks you if you want to create static/ or template/ directories as part of the interactive configuration - if you say yes to either of those it will create the directories for you and configure package_data= to correctly bundle any files contained within them.

This lead to a couple of new TILs: Testing cookiecutter templates with pytest and Conditionally creating directories in cookiecutter.

datasette-export-notebook improvements

datasette-export-notebook is a plugin that adds copy-and-paste instructions for exporting data from Datasette to Jupyter or Observable notebooks.

The "stream all rows" option for Jupyter uses Datasette's CSV streaming export, which has the downside of losing any type information.

As of issue #8 the copy-and-paste code for Jupyter now uses the dtype= option to specify the correct types.

You can see it in action on this page, where the Jupyter example code now looks like this:

df = pandas.read_csv(
    "https://covid-19.datasettes.com/covid/us_census_county_populations_2019.csv?_stream=on",
    dtype={
        "fips": int,
        "population": int,
    }
)

Releases this week

datasette-export-notebook: 0.3 - 2021-01-29
Datasette plugin providing instructions for exporting data to Jupyter or Observable
datasette-debug-asgi: 1.0 - 2021-01-29
Datasette plugin for dumping out the ASGI scope
datasette-template-sql: 1.0.2 - 2021-01-29
Datasette plugin for executing SQL queries from templates
datasette-cluster-map: 0.17 - 2021-01-29
Datasette plugin that shows a map for any data with latitude/longitude columns
datasette-leaflet-geojson: 0.8 - 2021-01-26
Datasette plugin that replaces any GeoJSON column values with a Leaflet map.
datasette-leaflet-freedraw: 0.2.1 - 2021-01-25
Draw polygons on maps in Datasette
datasette-leaflet: 0.2 - 2021-01-25
Datasette plugin adding the Leaflet JavaScript library
datasette: 0.54 - 2021-01-25
An open source multi-tool for exploring and publishing data

TIL this week

Tags: projects, datasette, weeknotes, leaflet

datasette-leaflet 0.2.1

2021-01-31T07:23:01+00:00

Release: datasette-leaflet 0.2.1

Tags: datasette, leaflet

datasette-leaflet-geojson 0.8

2021-01-26T02:41:07+00:00

Release: datasette-leaflet-geojson 0.8

Tags: datasette, leaflet

datasette-leaflet-freedraw 0.2.1

2021-01-25T19:23:59+00:00

Release: datasette-leaflet-freedraw 0.2.1

Tags: datasette, leaflet

datasette-leaflet-freedraw 0.2

2021-01-25T19:05:34+00:00

Release: datasette-leaflet-freedraw 0.2

Tags: datasette, leaflet

datasette-leaflet 0.2

2021-01-25T19:03:10+00:00

Release: datasette-leaflet 0.2

Tags: datasette, leaflet

datasette-leaflet 0.1

2021-01-24T18:19:32+00:00

Release: datasette-leaflet 0.1

Tags: datasette, leaflet

Drawing shapes on a map to query a SpatiaLite database (and other weeknotes)

2021-01-24T06:50:29+00:00

This week I built a Datasette plugin that lets you query a database by drawing shapes on a map!

datasette-leaflet-freedraw

SpatiaLite is a SQLite extension that adds a plethora of geospatial querying features. Datasette works well with SpatiaLite already, but every now and then I dip in to a geospatial project to see if there are any neat ways I can extend Datasette's spatial querying support.

datasette-leaflet-freedraw is my new plugin which brings the excellent FreeDraw Leaflet module to Datasette.

It's a pure-JavaScript plugin which looks for Datasette SQL parameters called :freedraw and enhances them with an interactive map. You can then draw on the map to populate the form field with a GeoJSON MultiPolygon representing the shape that you drew. Submit that to the server to excute it as part of a query.

This is best illustrated with a demo. Here's a SQL query that let you draw a shape to find California protected areas (parks, nature reserves and suchlike) that intersect with the polygon that you draw:

select
  AsGeoJSON(geometry), *
from
  CPAD_2020a_SuperUnits
where
  Intersects(GeomFromGeoJSON(:freedraw), geometry) = 1
  and CPAD_2020a_SuperUnits.rowid in (
    select
      rowid
    from
      SpatialIndex
    where
      f_table_name = 'CPAD_2020a_SuperUnits'
      and search_frame = GeomFromGeoJSON(:freedraw)
  )

This uses the California Protected Areas Database by GreenInfo Network, discussed here previously.

Try the SQL query here.

(That from SpatialIndex sub-select at the end is a pattern for taking advantage of SpatiaLite spatial indexes.)

The plugin itself is pretty simple: it scans for freedraw input fields and enhances them with a map interface that writes GeoJSON back to the form field.

You can then use SpatiaLite functions such as GeomFromGeoJSON() to transform that user input into something that can be used in a query.

Another fun query: let's add WHERE PARK_NAME like '%mini%' to the query to filter for just the "mini parks" in San Francisco:

select
  AsGeoJSON(geometry), *
from
  CPAD_2020a_SuperUnits
where
  PARK_NAME like '%mini%' and
  Intersects(GeomFromGeoJSON(:freedraw), geometry) = 1
  and CPAD_2020a_SuperUnits.rowid in (
    select
      rowid
    from
      SpatialIndex
    where
      f_table_name = 'CPAD_2020a_SuperUnits'
      and search_frame = GeomFromGeoJSON(:freedraw)
  )

Here are the mini parks for San Francisco and for Oakland.

This demo uses the user's shapes as part of a SELECT query, but it's also possible to use Datasette's Writable canned queries to create forms that will save the shapes to a database using an INSERT or UPDATE query.

If you want to try this plugin out note that it currently requires the Datasette 0.54a alpha. This is because it takes advantage of the new <script type="module"> support I added last week. Update 25th January 2021: Datasette 0.54 has now been released.

Other releases this week

I recorded my talk for FOSDEM - and in doing so I broke my unproductive streak and pushed out releases for a whole bunch of Datasette projects - partly through fixing bugs I spotted while trying to record the talk!

Here's the full list, with some annotations.

datasette-auth-github: 0.13 - 2021-01-24
Datasette plugin that authenticates users against GitHub. This is a big release: I finally finished upgrading the plugin to work against Datasette's new-ish authentication and permissions mechanism.
datasette-leaflet-freedraw: 0.1.4 - 2021-01-24
Draw polygons on maps in Datasette. See above.
datasette-cluster-map: 0.16 - 2021-01-23
Datasette plugin that shows a map for any data with latitude/longitude columns. Fixed some bugs related to the display of marker popups that included foreign key references to other tables.
asgi-csrf: 0.8 - 2021-01-23
ASGI middleware for protecting against CSRF attacks. I fixed a broken test caused by my use of an undocumented and unstable API in httpx - details here. I also added a new always_set_cookie=True option for cases where you always want to ensure a CSRF cookie has been set, see #16.
datasette-leaflet-geojson: 0.7 - 2021-01-20
Datasette plugin that replaces any GeoJSON column values with a Leaflet map. I fixed a long-standing point of concern: this plugin renders a map for every row on a page containing GeoJSON, which can potentially mean hundreds of maps for queries that return may results. The plugin now renders just the first ten maps and provides a grey clickable "Click to show map" widget for anything past the first ten.
datasette: 0.54a0 - 2021-01-19
Lots of stuff in this one, see the release notes. I pushed out an alpha so I could start using the JavaScript modules stuff in datasette-leaflet-freedraw.
sqlite-transform: 0.3.1 - 2021-01-18
Tool for running transformations on columns in a SQLite database. I improved the --help.
swarm-to-sqlite: 0.3.3 - 2021-01-18
Create a SQLite database containing your checkin history from Foursquare Swarm. I fixed a bug caused by new fields showing up in the Swarm JSON output.
sqlite-utils: 3.3 - 2021-01-18
Python CLI utility and library for manipulating SQLite databases. I added the alter=True argument to the .m2m() method, which I needed to fix the bug in swarm-to-sqlite.

TIL this week

Tags: geospatial, projects, spatialite, sqlite, datasette, weeknotes, leaflet

datasette-leaflet-freedraw 0.1.4

2021-01-24T06:46:58+00:00

Release: datasette-leaflet-freedraw 0.1.4

Tags: datasette, leaflet

datasette-leaflet-geojson 0.7

2021-01-20T20:21:25+00:00

Release: datasette-leaflet-geojson 0.7

Tags: datasette, leaflet