Skip to content

Superset

Jump to bottom
samadelli edited this page Jul 8, 2021 · 4 revisions

To create a Superset Dashboard, based on osmhistorydb, you first need to find out your Superset Version. Older Version may not Support virtual Datasets. If your Version doesn't, you will have to create a View in the Database and add it to Superset as a Dataset. You need this, because Superset can't Query the Tables with Database specific functions outside of the SQL Lab. To improve Performance, it is also Possible to create a Materialized View. This Way, the Data doesn't have to be calculated each time. The Downside is, that the Table must be manually refreshed. To create a Materialized View, just replace View with Materialized View.

Dataset

To create a Dataset, you just need your Query, which selects the Data you need for your Charts, which can't be accessed with standard sql functions, and saves it into Columns. If you execute the Query in the SQL Lab, it will show a Button 'Explore', with which a virtual Dataset can be created. After that, you can use it to create Charts.

Example Dataset

select nwr.id, nwr.version, nwr.deleted, nwr.changeset_id, nwr.created, nwr.uid, users.username, (nwr.tags ->> 'emergency') as emergency, nwr.tags, nwr.type, nwr.lat::decimal(10,7), nwr.lon::decimal(10,7),row_number() over(order by created) as count from (
    select id, version, deleted, changeset_id, created, uid, tags, 'n' as type, lat, lon 
  from nodes
    union all
  select id, version, deleted, changeset_id, created, uid, tags, 'w' as type, st_x(node.geom) as lat, st_y(node.geom) as lon 
  from ways, lateral (select geom from nodes where id=(ways.nodes[(array_length(ways.nodes,1)+1)/2]) limit 1) as node
    union all
  select id, version, deleted, changeset_id, created, uid, tags, 'r' as type, st_x(node.geom) as lat, st_y(node.geom) as lon 
  from relations, lateral (select geom from nodes where id=jsonb_path_query_first(members, '$ ? (@.type=="n") .ref')::bigint) as node
) as nwr
left join users on nwr.uid = users.uid
where (nwr.tags ->> 'emergency') = 'defibrillator'

View

If you have to create a View, you can also do that from the SQL Lab, and than import it as a Dataset.

Example View

begin;
create view test_view as
select nwr.id, nwr.version, nwr.deleted, nwr.changeset_id, nwr.created, nwr.uid, users.username, (nwr.tags ->> 'emergency') as emergency, nwr.tags, nwr.type, nwr.lat::decimal(10,7), nwr.lon::decimal(10,7),row_number() over(order by created) as count from (
    select id, version, deleted, changeset_id, created, uid, tags, 'n' as type, lat, lon 
  from nodes
    union all
  select id, version, deleted, changeset_id, created, uid, tags, 'w' as type, st_x(node.geom) as lat, st_y(node.geom) as lon 
  from ways, lateral (select geom from nodes where id=(ways.nodes[(array_length(ways.nodes,1)+1)/2]) limit 1) as node
    union all
  select id, version, deleted, changeset_id, created, uid, tags, 'r' as type, st_x(node.geom) as lat, st_y(node.geom) as lon 
  from relations, lateral (select geom from nodes where id=jsonb_path_query_first(members, '$ ? (@.type=="n") .ref')::bigint) as node
) as nwr
left join users on nwr.uid = users.uid
where (nwr.tags ->> 'emergency') = 'defibrillator'
commit;

Notes:

Clone this wiki locally