How to display Superset data as polygons in deck.gl

Question

Superset has a deck.gl Polygon visualisation type.

I am trying to use this type of chart to display polygons taken from a PostGIS enabled postgres database; however, no visual information is displayed other than the legend.

My database query is as follows:

SELECT *, ST_AsGeoJSON(ST_Transform((ST_DUMP(geom)).geom::geometry(Polygon, 27700), 4326)) as geom
  FROM <my-schema>.<my-table>;

This returns geojson Polygon records as such:

{
  "type": "Polygon",
  "coordinates": [[
    [-0.029742716, 51.492925448],
    [-0.028980512, 51.491626654],
    [-0.027819506, 51.490055839],
    ...

I've verified in QGIS that the query works and that the geoJSON information displays correctly.

However, this doesn't seem to work in superset: I've created a deck.gl Polygon chart for which I've selected the JSON lines encoding (as opposed to Polyline or Geohash). After selecting a Metric and centring the viewport coordinates to the correct geographical extents I've run the query, but see no spatial information displayed in the chart, just a legend showing the breaks for the selected metric.

Though the map is empty, the adjacent buttons for downloading the data as a CSV or JSON return complete data, so I'm inclined to think that I've misunderstood one of the settings or that I've provided the data in the incorrect format.

Any pointers on how to get this plugin working and what format it might be expecting the data to be in?

In general, I am struggling to find any documentation on how to use the superset deck.gl plugin: any examples or documentation that might be helpful?

score 9 · Accepted Answer · answered Nov 11 '20 at 09:16

For others running into the same problem, this is what ended up working for my scenario:

The JSON strings returned from postGIS using the ST_AsGeoJSON function contain only the geometry component of the geoJSON spec. This means that it is necessary to wrap these geometries in a full-fledged geoJSON object before deck.gl can recognise it.

For example:

SELECT *, json_build_object(
  'type',       'Polygon',
  'geometry',   ST_AsGeoJSON(ST_Transform(geom, 4326))::json)::text as geoJSON
FROM <my-schema>.<my-table>;

If you are working with MultiPolygons then you need to extract a Polygon first:

SELECT *, json_build_object(
  'type',       'Polygon',
  'geometry',   ST_AsGeoJSON(ST_Transform((ST_DUMP(geom)).geom::geometry(Polygon, 27700), 4326))::json)::text as geoJSON
FROM <my-schema>.<my-table>;

Note that in both cases, the geoJSON result has to be cast to a text type otherwise superset will have issues when it runs internal queries, which are otherwise unable to sort the results (duplicate checking?) if using JSON.

How to display Superset data as polygons in deck.gl

1 Answers1