Zoom default table map to account for points

[simonw]

The map here starts at the entire world: https://calands.datasettes.com/calands/CPAD_2020a_SuperUnits

But there are only points in California:

https://calands.datasettes.com/calands?sql=select+AsGeoJSON%28Extent%28geometry%29%29+from+CPAD_2020a_SuperUnits

select AsGeoJSON(Extent(geometry)) from CPAD_2020a_SuperUnits

Returns:

{
    "type": "Polygon",
    "coordinates": [
        [
            [
                -124.4217819344615,
                32.53467578774561
            ],
            [
                -114.1314611195629,
                32.53467578774561
            ],
            [
                -114.1314611195629,
                42.00950399539816
            ],
            [
                -124.4217819344615,
                42.00950399539816
            ],
            [
                -124.4217819344615,
                32.53467578774561
            ]
        ]
    ]
}

[simonw]

I think the right place to do this is in the extra_body_script hook - but it doesn't have the current SQL query for the page, so it can't easily calculate this.

datasette-leaflet-freedraw/datasette_leaflet_freedraw/__init__.py

Lines 74 to 106 in 3ecd505

	@hookimpl
	def extra_body_script(request, datasette, database, table):
	async def inner():
	has_geometry = False
	if table:
	has_geometry = bool(
	await geometry_columns_for_table(datasette, database, table)
	)
	current_geojson = None
	freedraw = request.args.get("_freedraw")
	if freedraw:
	try:
	current_geojson = json.loads(freedraw)
	except ValueError:
	pass
	return textwrap.dedent(
	"""
	window.datasette = window.datasette || {{}};
	datasette.leaflet_freedraw = {{
	FREEDRAW_URL: '{}',
	show_for_table: {},
	current_geojson: {}
	}};
	""".format(
	datasette.urls.static_plugins(
	"datasette-leaflet-freedraw", "leaflet-freedraw.esm.js"
	),
	"true" if has_geometry else "false",
	htmlsafe_json_dumps(current_geojson),
	)
	)
	return inner

Instead, I'm going to have the JavaScript on the page use a fetch() request to figure out the bounds for the column.

[simonw]

Partial prototype:

let innerSql = Array.from(document.getElementsByTagName("span")).filter(
    el => el.innerText == "View and edit SQL"
)[0].parentElement.getAttribute("title")

let outerSql = `with inner as (${innerSql}) select AsGeoJSON(Extent(geometry)) as bounds from inner`;

let queryPath = location.pathname.split("/").slice(0, -1).join("/") + ".json"

fetch(
  queryPath + "?sql=" + encodeURIComponent(outerSql) +
  "&_shape=array&_json=bounds"
).then(d => d.json()).then(r => console.log(r))

[simonw]

I think I then need this: map.fitBounds(L.geoJSON(...).getBounds())

[simonw]

This has all got too messy. I'm going to block work on this until there's a cleaner way to get at the necessary information without reading title attributes and similar:

• Documented JavaScript variables on different templates made available for plugins datasette#1565 (comment)

[simonw]

Here's as far as I got:

diff --git a/datasette_leaflet_freedraw/static/datasette-leaflet-freedraw.js b/datasette_leaflet_freedraw/static/datasette-leaflet-freedraw.js
index 16b86b8..754c75b 100644
--- a/datasette_leaflet_freedraw/static/datasette-leaflet-freedraw.js
+++ b/datasette_leaflet_freedraw/static/datasette-leaflet-freedraw.js
@@ -53,6 +53,23 @@ window.addEventListener("load", () => {
   });
 });
 
+function zoomMapToExtent(map) {
+  let tableSql = Array.from(document.getElementsByTagName("span")).filter(
+    el => el.innerText == "View and edit SQL"
+  )[0].parentElement.getAttribute("title");
+  let sql = `with inner as (${tableSql}) select AsGeoJSON(Extent(geometry)) as bounds from inner`;
+  let queryPath = location.pathname.split("/").slice(0, -1).join("/") + ".json"
+  fetch(
+    queryPath + "?sql=" + encodeURIComponent(sql) +
+    "&_shape=array&_json=bounds" + location.search.replace(/^\?/, '&')
+  ).then(d => d.json()).then(rows => {
+    if (rows.length == 1) {
+      let bounds = rows[0].bounds;
+      map.fitBounds(L.geoJSON(bounds).getBounds())
+    }
+  });
+}
+
 function configureMap(input) {
   let div = document.createElement("div");
   div.style.marginTop = "1em";
@@ -64,6 +81,12 @@ function configureMap(input) {
     zoom: 2,
     layers: [tiles],
   });
+
+  // If table page, fire off code to zoom to extent of bounds
+  if (datasette.leaflet_freedraw.show_for_table && !input.value) {
+    zoomMapToExtent(map);
+  }
+
   let freeDraw = new FreeDraw();
   /* If input.value is GeoJSON, add those to the map */
   let allPoints = [];

Then I realized I needed special code to ensure that I copied over the &p0=... variables from the "View and edit SQL" link, and at that point it was just getting too messy not to implement the supporting feature in Datasette first.