SpatiaLite

Dr. Huidae Cho
Institute for Environmental and Spatial Analysis...University of North Georgia

1   Relational database management system (RDBMS)

....

1.1   Basic SQLite commands

  • .tables to list all tables in the database.
  • .schema table-name to see the schema of a table.
  • create table to create a new table.
  • autoincrement only support integer so we will use integer for ID columns.
  • not null to create a not nullable column.
  • drop table to delete an existing table.
  • insert to insert a new record to a table.
  • select to show records in a table.
  • delete to delete records from a table.

2   Nonspatial RDBMS example

  • Campuses
    • Campus ID
    • Address
  • Students
    • Student ID
    • Campus ID
    • First name
    • Last name
    • On campus housing
    • Address
  • Products
    • Product ID
    • Name
    • Description
    • Cost
  • Orders
    • Order ID
    • Student ID
    • Product ID

2.1   On Bash

# create a temporary directory
mkdir tmp
# see if tmp is created
ls
# move into the tmp directory
cd tmp
# create a new SQLite database file called ung_students.db
sqlite3 ung_students.db

3   Standard query language (SQL)

3.1   Student table

create table students (
    student_id integer primary key autoincrement,
    campus_id integer not null,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    on_campus_housing boolean,
    address varchar(100)
);

3.2   Orders table

create table orders (
    order_id integer primary key autoincrement,
    student_id integer not null,
    product_id integer not null
);

3.3   Campuses table

create table campuses (
    campus_id integer primary key autoincrement,
    address varchar(100));

3.4   Products table

create table products (                                                            
    product_id integer primary key autoincrement,
    name varchar(100) not null,
    description text not null,
    cost double precision not null);

4   SQLite

https://www.sqlite.org/

5   SpatiaLite: Spatial extension to SQLite

https://www.gaia-gis.it/fossil/libspatialite/index