Visual Field Example 2 - Lead Levels in San Francisco Schools drinking water Map (with recorded level > 5Ug/l)

Visual Field Example 2 - Lead Levels in San Francisco Schools Map (with recorded level > 5Ug/l)

The source data this example is based upon

DataSF Schools CSV Dataset
Note DataSF has changed the format of this Schools dataset since this example was first made.
California Open Data Portal - Results of Lead Sampling of Drinking Water in California Schools CSV Dataset

Key aspects this example demonstrates

Fetching 2 CSV datasets, one with a spatial (WKT) component
Fetching the datasets from 2 different jurisdictions (2 different websites/domains)

Creating a database view by relationally joining the datasets and applying additional relational predicates to filter the data

create view if not exists schools_lead
as
select *,
       result||' '||rpt_unit as tooltip
from lead l, 
     schools s
where s."Campus Name" = l.school_name
      and l.water_system_county = s."County Name"
      and l.xmod in ('=','>')
      and rpt_unit = 'UG/L'
      and cast(result as REAL) > 5;

Creating a Map Visualization of the resultant view

Disclaimer

This example does not attempt to assert what might be safe or unsafe lead levels in drinking water, or indeed if the datasets have been validly joined correctly. It is for demonstration purposes only.

Additional information

visualfield.org