Geo Plot - Use an Arrow Table Object as Data Source?

[enlore]

Hey team,

We are working with some GeoJSON data that we want to enrich with, let's say, a value that represents the population of each US state.

        // stateFeatures is an array of Feature objects fetched from a JSON file on disk
        // see attached file at end of post
        await db.registerFileText('state_features.json', JSON.stringify(stateFeatures))

        // we load the GeoJSON into a table so we can join into it
        await vg.coordinator().exec(`
            create temp table if not exists state_features as
                select * from read_json_auto('state_features.json', records = true)
        `);

        // embed the population count for each state into its properties object
        await vg.coordinator().exec(`
            create temp table if not exists features as
                select 
                    a['type'] as type,
                    a['geometry'] as geometry,
                    struct_insert(a['properties'],
                        population := b.population
                    ) as properties
                from state_features as a
                join population_by_state_name_year_temp b
                on (a['properties']['NAME'] = b.state_name)
        `);

That all works fine. We run into an issue while attempting to feed that data to the Geo Plot.

        const mapFeaturesTable = await vg.coordinator().query(`select type, properties, geometry from features`)

        const geoPlot = vg.plot(
            vg.geo(
                mapFeatures.toArray(), 
                {
                    //fill: (d) => d.properties.population,
                    //title: (d) => `${d.properties.NAME}: ${d.properties.population}`,
                    fillOpacity: 0.2,
                    stroke: 'white',
                    strokeWidth: 0.2,
                }
            ),
            vg.projectionType("albers-usa"),
            vg.colorScale('quantile'),
            vg.colorScheme('blues'),
            vg.colorLabel('The Label for the Color Scheme'),
            vg.colorLegend(),
            vg.width(1400),
        )

The resulting Arrow Table from our query above represents the geometry.coordinates property as a set of nested Vectors.

The Vector class does not implement square bracket notation for indexing into arrays so we get into trouble on line 41 in the screenshot below:

Any advice for how best to feed our Geo plot the GeoJSON it needs?

Thanks in advance.

us-states.json

[jheer]

I think part of the problem here is that the Arrow data is likely not a normal array of GeoJSON features (it's probably a struct containing a FeatureCollection object). This might cause problems at various points if the data inside arrow isn't properly converted to "standard" JS objects.

But instead of worrying about that, you might want to back up a step. Your example loads a file in the browser, registers it with DuckDB, performs some queries, and then pulls the data back out of DuckDB -- all before creating a visualization. However, Mosaic is intended to query DuckDB directly!

I can't perform the join (as you did not share that file), but here is a simpler example you might build off of. The following assumes you've already set up Mosaic to use a DuckDB wasm connector in the browser. You'll also need the most recent release of Mosaic (0.5.0 or later) for this to work, as we use the spatial extension to load and process GeoJSON geometry directly in DuckDB.

  // construct url to us states geojson
  // this assumes the file is in the same directory as the current web page
  const url = new URL('us-states.json', window.location);

  // load spatial extension, then load geojson file
  await vg.coordinator().exec([
    vg.loadExtension('spatial'),
    vg.loadSpatial('state_features', url)
  ]);

  // create a state map
  // the geo mark queries DuckDB to extract the state geometry as GeoJSON
  const geoPlot = vg.plot(
    vg.geo(vg.from('state_features'), {
      fill: 'gray',
      fillOpacity: 0.2,
      stroke: 'white',
      strokeWidth: 1
    }),
    vg.projectionType('albers-usa'),
    vg.width(700)
  );

  // add map to page
  document.body.appendChild(geoPlot);

[enlore]

Hey folks. Dropping a note here to let you know this thread is still on my mind. I will revisit your solution at some point in the future, hopefully sooner than later.

A bit of context: we are integrating with a hosted DuckDB service called MotherDuck. MotherDuck is impressive, we're fans.

MotherDuck publishes a custom build of the DuckDB Wasm client that integrates with their backend. Docs.

I tried to implement the above when you suggested it but ran into issues. For instance, this throws an exception complaining about passing multiple statements in one exec call:

  // load spatial extension, then load geojson file
  await vg.coordinator().exec([
    vg.loadExtension('spatial'),
    vg.loadSpatial('state_features', url)
  ]);

I strongly suspect this is the reason:

Currently, MotherDuck Wasm only supports DuckDB 0.9.2.
Support for DuckDB 0.10.2 will be added in the near future.

The MotherDuck team is working on updating their Wasm client build to allow injecting whatever version of DuckDB Wasm you like, but we aren't there yet.

I appreciate your help working with Mosaic. We are fans of it, too. More questions to come, no doubt!

[jheer]

Thanks for the note! I believe extension loading support was added in DuckDB 0.10, so I think you're spot on. It sounds like we need MotherDuck to "modernize" their custom build.

[domoritz]

IIRC they plan to not even have a special build anymore but load motherduck as an extension into whatever DuckDB wasm you will have.

[kylebarron]

FWIW:

The resulting Arrow Table from our query above represents the geometry.coordinates property as a set of nested Vectors.

Aside from the metadata to describe the geometry type of the column, this overlaps with the GeoArrow spec for defining a column of geometries in Arrow. Once duckdb/duckdb-spatial#153 is implemented, DuckDB spatial will be able to ingest and export GeoArrow data with nearly zero overhead, and it'll be much faster than converting to/from GeoJSON

[jheer]

Yes, that would be great. Right now we can already use GeoParquet (with geometry represented as a WKB blob) for ingest, though this requires a conversion call to duckdb spatial's GEOMETRY type. Better metadata support should hopefully clear that up.

Of course there's also the "last mile" of the visualization software. Mosaic vgplot builds on Observable Plot, which expects GeoJSON data. So in this context our geo mark performs GeoJSON conversion in-database and ship that over the wire in an Arrow utf8 string column, which we then extract and parse to JSON. Once GeoArrow is supported, we can see if we get better performance by shipping GeoArrow data and converting to GeoJSON on the client instead. (Of course, even better would be if Plot or another renderer processed the Arrow data directly...)

p.s. nice work on lonboard!