Helper method and/or API route to join query results to geography

[jc-harrison]

A common pattern when using FlowClient is to get the result of a spatially-aggregated query, get the corresponding geography data, and join them so they can be used together to create a map visualisation. This join is an extra step that the user has to implement themselves. The simplest way to do this is to load the geography geojson into a geopandas GeoDataFrame and join this to the pandas DataFrame query result, but geopandas is not a dependency of flowclient, and has its own dependencies that can sometimes cause conflicts, so some users may prefer not to use geopandas.

It would be useful to add an API route from which a user could get a geojson object with geography data and the results of one or more spatially-aggregated queries as properties. This could either be implemented as a new query kind, an additional parameter in the geography endpoint (which could take one or more query IDs to join), or a new API endpoint. I'd favour one of the last two options, since the data type returned (geojson) will be different from that of an ordinary query.

I expect this would also be useful in scenarios where a different application (such as a dashboard) is interacting with the API, and it would be convenient to get a single geojson object for visualisation purposes with minimal client-side manipulation of the results.

[greenape]

I'm not convinced on this - geojson is (relatively) big and unwieldy, and I'd argue that generally you want boundaries once and then multiple datasets separately. For example, most of the Kepler/deckgl stuff is set up on the premise that you have a geojson file and n csv files and join.

I'm not against a helper method in flowclient that does this I think, but I don't see a compelling reason for any of the server components to provide it.

[jc-harrison]

Ah, I see. I haven't used Kepler/deckgl - both mapboxgl and leaflet seem to be premised on having a single geojson object and using its properties to set colours etc., so I thought being able to get everything as geojson might be useful for that.

I certainly think a helper function in flowclient would be a good idea.

[jc-harrison]

Advantages of joining to geography in the server:

• Other API users could use this functionality without having to re-implement in each client
• The data are in a database, so the join is easy and efficient
• FlowMachine knows which are the location ID columns to join on (which differ depending on spatial unit - this is something that could be improved when refactoring to use the new schema, e.g. so that the API always returns spatially-aggregated results with a column called 'location_id')

Advantages of doing it in the client:

• A helper method could handle results that have been manipulated after retrieval from FlowKit (e.g. in the flows above normal worked example, the inflows/outflows above normal are calculated outside of FlowKit, so joining to geography has to happen client-side)

Kepler doesn't currently support joining geojson geometries to data in a separate csv file, although it's on their roadmap. I expect there will always be situations that require query results to be in the same file as geometries (either as geojson with query results as properties, csv including geometry data, or shapefiles), so providing a helper method to do this would be beneficial.

[greenape]

Hmm.

Ok, I don't like the untidiness though. ;)

The get results endpoint has always been intended to support different return types, so let's have it return geojson if that's what the request is for?