Earthsys 144: Fundamentals of GIScience
Tables Relational Databases
& Structured Query Language (SQL)
where is your data? gis.stanford.edu
Tables & Attributes
Tables & Attributes
entities = records
attributes
data types
- Numeric values:
- INTEGER - No decimals
- DOUBLE - Very large numbers
- FLOAT - like double, but less precision
- Alphanumeric values:
- STRING - letters, numbers and other characters
- Special (Reserved) values:
- DATE
- FID/OBJECTID - Feature or Object ID
- Binary data (Media, Executables, etc...)
- BLOB - Binary Large Object
Boolean Selection
Boolean Selection
Complex Boolean Selection
Table Selections are Spatial Selections
Relational Databases
relational algebra
keys or keyfields
Primary Keys
lookup tables & domains
Inner & Outer Joins
Inner Join
Outer Join
one-to-one &
one-to-many join
many-to-many joins
(just, nope)
Join Table
Normalization
"First Normal Form" (1NF)
Normalization
SQL (Structured Query Language) is a programming language specifically designed to manage and manipulate databases.
SELECT STATEMENT
Syntax
SELECT column1, column2, ...
FROM table_name;Example
Return columns:
SELECT CustomerName, City FROM Customers;
The SELECT statement is used to select data from a database.
SELECT DISTINCT STATEMENT
Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example
Select distinct countries from the "Customers" table:
SELECT DISTINCT Country FROM Customers;
The SELECT DISTINCT statement is used to return only distinct (different) values.
WHERE CLAUSE
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
Select all customers from Mexico:
SELECT * FROM Customers
WHERE Country='Mexico';
The WHERE clause is used to filter records.
ORDER BY Keyword
Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Example
Sort the products by price:
SELECT * FROM Products
ORDER BY Price;
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
AND, OR, NOT Operators
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 OR condition3 NOT condition4
Example
Select all customers from Spain that starts with the letter 'G':
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
The AND operator is used to filter records based on more than one condition
NULL Values
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
A field with a NULL value is a field with no value. It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
Aggregate Functions
Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Example
Find the total number of rows in the Products table:
SELECT COUNT(*)
FROM Products;
The COUNT() function returns the number of rows that match a specified criterion. MIN(), MAX(), SUM() & AVG() are used in the same way. These functions often work with the `GROUP BY` clause to aggregate data by categories.
GROUP BY Statement
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);
Example
The following SQL statement lists the number of customers in each country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
LIKE Operator
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Example
Select all customers that starts with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
- The percent sign
%represents zero, one, or multiple characters - The underscore sign
_represents one, single character
Wildcards
| % | Represents zero or more characters |
| _ | Represents a single character |
Example
Select all customer 5-letter names that start with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a____';
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Example
Select all customer names that start with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
Joins
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Joins, con't...
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
- The
INNER JOINkeyword selects records that have matching values in both tables. - The
LEFT JOINkeyword returns all records from the left table (table1), and the matching records from the right table (table2). - The
RIGHT JOINkeyword returns all records from the right table (table2), and the matching records from the left table (table1). - The
FULL OUTER JOINkeyword returns all records when there is a match in left (table1) or right (table2) table records.
For More: W3 Schools - Introduction to SQL
QGIS DB Manager uses the SQLite & SpatiaLite SQL implementations.
SpatiaLite allows you to leverage the geometries of your data, as well as the attributes, with SQL
ST_GeomFromText (WKT)
Syntax:
ST_GeomFromText(wktText, srid)
Example:
SELECT ST_GeomFromText('POINT(30.0 10.0)', 4326) AS geom;
This example creates a point geometry with coordinates (30, 10) in the WGS 84 spatial reference system (SRID 4326).
ST_Distance
Syntax:
ST_Distance(geom1, geom2)
Example:
SELECT ST_Distance(a.geometry, b.geometry) AS Distance
FROM table1 AS a, table2 AS b
WHERE a.id = 1 AND b.id = 2;
Calculates the distance between geometries from two rows identified by their IDs.
ST_Intersects
Syntax:
ST_Intersects(geom1, geom2)
Example:
SELECT a.name, b.name
FROM parks AS a, buildings AS b
WHERE ST_Intersects(a.geometry, b.geometry);
Finds pairs of parks and buildings that overlap.
ST_Buffer
Syntax:
ST_Buffer(geom, distance)
Example:
SELECT ST_Buffer(geometry, 100.0) AS BufferGeom
FROM rivers;
Creates a 100-meter buffer around each river.
ST_Contains
Syntax:
ST_Contains(geom1, geom2)
Example:
SELECT a.name, b.name
FROM districts AS a, schools AS b
WHERE ST_Contains(a.geometry, b.geometry);
Lists districts and the schools contained within them.
-
ST_Union: This function merges two or more geometries into a single geometry. It is particularly useful for simplifying datasets by combining adjacent or overlapping polygons, such as merging land parcels or administrative boundaries for a simplified map view.
-
ST_Area: Calculates the area of a geometry, typically used for polygons. This function is crucial for spatial analysis involving size measurements, such as calculating the area of parks, lakes, or building footprints.
-
ST_Centroid: Computes the centroid of a geometry. The centroid is the geometric center point of a figure. This function is valuable for locating the center of mass of a spatial feature, useful in various applications like urban planning and geostatistics.
-
ST_MakePoint: Creates a point geometry from x and y coordinates. This is a basic yet powerful function for generating point data from coordinate pairs, essential for plotting locations on a map or creating new spatial data entries.
-
ST_AsGeoJSON: Converts a geometry into a GeoJSON format string. This function is crucial for integrating spatial data with web applications and services that use GeoJSON as a standard format, facilitating easy sharing and visualization of spatial data in modern web and mobile applications.