1. Transfer from flash drive

2. View presentation on web:

• https://rpubs.com/afisch/wlia-sqlite-workshop
• https://gitlab.com/asruesch/wlia-sqlite-workshop/-/archive/main/wlia-sqlite-workshop-main.zip

Aaron Fisch:

Water quality modeler with the Dept. of Natural Resources. Interested in computer programming for automation in modeling/statistics.

Dave Evans:

Soil scientist with a background in GIS, data science and data engineering. He does some consulting work with soils and web development.

Matt Haffner:

Assistant professor at the University of Wisconsin–Eau Claire. He is interested in spatial data science, geocomputation, and urban analytics.

./wlia-sqlite-workshop/

• ./sqlite_presentation.html
  • This presentation
• ./run_spatialite_gui
  • A shortcut to run Spatialite software on Windows.
• ./dbs/ (contains example SQLite databases)
  • esri_mobile.geodatabase (ESRI mobile geodatabase)
  • SpatiaLite.sqlite (Spatialite database)
  • GeoPackage.gpkg (Geopackage)

1. Open this presentation in a browser window
2. Open another window for executing SQL code

• Introducing software and concepts

• Think of a database as a collection of tables
• A database allows rows to be added, changed, or deleted
• Facilitates combining data from multiple tables.
• Allows for data manipulations

1. A relational database engine
2. Allows for storing, transforming, updating, and deleting tables of data
3. Widely used, so lots of tutorials and help available
4. Low overhead: a single file

• Little to no install
• Easy to load and export data
• Can handle lots of data
• Access with
  • command line
  • programming languages
  • GUI

• Geopackage
• SpatiaLite
• ESRI Mobile Geodatabase

• Allows for storage of rasters and vectors
• Can build views, store styles (QGIS styles), spatial indices
• Allows for much faster viewing from a GIS

Link to supplemental materials

ESRI Mobile Geodatabase and SpatiaLite

• A format for storing rasters and vectors
• Allows for GIS operations (intersect, dissolve, etc) on the geometry contained within

Any questions?

Table A

1. Tables can be extended
2. Can drastically reduce duplication in a database
3. Can improve data integrity

A table can function like a feature class with the addition of a geometry column.

Feature Class

Syntax:

select * from countries

Anatomy of a select statement:

We can choose to limit our selection to only certain columns

select name, pop_est
from countries

We can filter rows by using a where clause

select name
from countries
where continent = 'South America'

We can also do math by column:

select
  name,
  pop_est * 1e-6 as pop_est_mil
from countries

Grouping (AKA aggregation) allows us to do math across categories, much like how the Dissolve tools works in Arc.

select
  continent,
  sum(pop_est) as total_pop_continent
from countries
group by continent

And we can sort columns numerically or alphabetically:

select name
from countries
order by pop_est desc

We can join tables using the SQL join command:

select name, head_of_state, pop_est
from countries
join south_american_heads_of_state using (name)

Join by an ID when possible, not by a column containing long character strings. Values must match exactly in order for the join to work successfully.

select name, head_of_state, pop_est
from countries
join south_american_heads_of_state using (name)

When we join tables, sometimes SQL gets confused if there are multiple tables with the same column name. We can fix this with either table references or aliases and the . (dot) operator.

select a.name, b.head_of_state, a.pop_est
from countries as a
join south_american_heads_of_state as b using (name)

Using SpatiaLite or QGIS

Level 1:

How many people live in Australia?

Level 2:

How many millions of citizens live in Gabriel Boric’s country?

Simple: a view is a saved query.

1. Data exploration and analysis
2. Building blocks for repeated or complex SQL queries
3. Reducing data storage
4. Can make tables more readable
5. Security and data integrity

Working with the wi_counties feature class we can extract the area of each Wisconsin county using the spatial st_area function. We can save this view using the create view view_name as syntax.

create view wi_county_area as
select
  fips,
  st_area(shape) * 3.86102e-7 as area_sq_miles
from wi_counties

Let’s convert total population to density by using the view we just created.

create view wi_pop_dens as
select
  fips,
  round(pop_est / area_sq_miles) as pop_per_sq_mile
from wi_counties_pop
join wi_county_area using (fips)

What if we wanted to make a view that we can use in a map? We can create a spatial view that is analogous to using the join tool in ArcPro.

create view wi_pop_dens_sv as
select
  objectid,
  shape,
  fips,
  name,
  pop_per_sq_mile
from wi_counties
join wi_pop_dens using (fips)

When tables used in views are updated, the views are dynamically updated at the same time

Using SpatiaLite or QGIS

Level 1:

Create a new view of county populations expressed as thousands of people.

Level 2:

Make a point spatial view of county centroids including the population field (you may use multiple views), and make a map that labels those centroids with county population.

1. Topological: looks at relationships between geometries; return a 1 or 0, if they relate or not Examples: ST_Intersects(a.geometry, b.geometry) and ST_Within(a.geometry, b.geometry)
2. Geometry functions: change the input geometry; return a geometry Examples: ST_Buffer(geometry, distance) and ST_Centroid(geometry)
3. Overlay functions: compute the results of different overlay relationships: return a geometry Examples: ST_Union(a.geometry, b.geometry) and ST_Intersection(a.geometry, b.geometry)
4. Measurement functions: allow for measuring geometry; return a number of the measurement Example: ST_Area(geometry) and ST_Length(geometry)

select name, contaminated, st_within(fake_well_data.geom, wi_counties.shape)
from wi_counties
join fake_well_data
on st_within(fake_well_data.geom, wi_counties.shape)

Buffer each well point 5 km (5000 m)

select fid, st_buffer(geom, 5000) as geom
from fake_well_data
where contaminated=1

select fid, st_union(st_buffer(geom, 5000)) as geom
from fake_well_data
where contaminated=1    

Scenario: We want to resample all uncontaminated wells which are with 5 km of a contaminated well.

Find the wells that need resampling.

1. Find areas which are within 5km - buffer
2. Make sure we don’t double count wells - union the buffer
3. Find uncontaminated wells that touch the buffer - spatial join

select uncont.*
from (

    select fid, st_union(st_buffer(geom, 5000)) as geom
    from fake_well_data
    where contaminated=1    

) as cont_ubuff
join (

    select *
    from fake_well_data
    where contaminated = 0
    
) uncont
on st_within(uncont.geom, cont_ubuff.geom)

• Find all uncontaminated wells within 10km of a contaminated well
  • Challenge: Rewrite using views?
• Create a view of rivers in South America (Should be 79 rivers)
• Create a view of world land boundaries
  • Challenge: Just continent boundaries (You’ll need a group by here)

• Just start using SQLite as your chosen file format!
  • Start assuming it’s your file format of choice
  • Challenge yourself to stop creating shapefiles and file geodatabases entirely
  • See how far you can take your analysis with just SQL views
  • Use it as a local staging area for enterprise database development

• SQLite will likely be around for a long-time
• The future of SpatiaLite, GeoPackage, and mobile geodatabases is not as certain.

Level 1:

How many people live in Australia?

select pop_est
from countries
where name = 'Australia'

Level 2:

How many millions of citizens live in Gabriel Boric’s country?

select pop_est * 1e-6 as pop_est_mil
from countries
join south_american_heads_of_state using (name)
where head_of_state = 'Gabriel Boric'

Level 1:

Create a new view of county populations expressed as thousands of people.

create view wi_counties_pop_thousands as
select
  name,
  pop_est * 1e-3 as pop_est_thousands
from wi_counties_pop

Level 2:

Make a point spatial view of county centroids including the population field (you may use multiple views), and make a map that labels those centroids with county population. It may require the use of the SpatiaLite GUI, QGIS DB Manager, or the ArcPro Create Database View tool to register the view properly, in which case line 1 can be omitted from the SQL code below:

create view wi_county_centroids_pop as
select
  a.objectid,
  st_centroid(shape) as shape,
  a.name,
  pop_est
from wi_counties as a
join wi_counties_pop as b using (fips)

select
  rivers.name,
  countries.name,
  st_intersects(rivers.shape, countries.shape)
from rivers
join countries
on st_intersects(rivers.shape, countries.shape)
where countries.continent = "South America"

select 
  max(objectid) as objectid, 
  CONTINENT, 
  st_union(shape) as shape 
from countries 
Group by CONTINENT