#Lab Session 8 - Building a Spatial Database using PostGIS
###By Mitchell Welch

###University of New England
---

## Reading
* PostgreSQL Documentation: [http://www.postgresql.org/docs/9.4/static/index.html](http://www.postgresql.org/docs/9.4/static/index.html)
* PostGIS documentation:[http://postgis.net/docs/manual-2.1/](http://postgis.net/docs/manual-2.1/)

---

## Summary

* Introduction
* The `Geometry` datatype
* Spatial Queries
* Exercises for You


---

## Introduction

A *spatial database*, or *geo-database* is a database that is optimized to store and query data that represents objects defined in a geometric space. Most spatial databases allow representing simple geometric objects such as points, lines and polygons. Some examples:

* Roads and railways can be represented by line objects
* Buildings and land parcels can be represented by polygons
* Weather stations or interest points can be represented by point objects. 

Geospatial databases allow for the implementation of a wide range of application that contain spatial data.

The PostGIS database extension provides the `Geometry` datatype and a suite of functions to the PostgreSQL DBMS for implementing spatial database tables and spatial queries. 

## House Keeping.

For todays practical, you have to use the database `prac_08`. This database has already been created for you and has has the spatial database extensions enabled. (Enabling the extensions for a database requires administrator access that your accounts don't have). Because you will be sharing this database with other users, you should prefix your database tables with your UNE username. Note that other users can't access your database tables and you cannot access other users database tables.

```

[mwelch8@turing mwelch8]$ psql prac_08
psql (9.4.4)
Type "help" for help.

prac_08=> \dt
             List of relations
 Schema |      Name       | Type  |  Owner  
--------+-----------------+-------+---------
 public | capital         | table | jknife
 public | roads           | table | mwelch8
 public | spatial_ref_sys | table | stadmin
 public | tablename       | table | mwelch8
 public | testing         | table | mwelch8
(5 rows)

prac_08=>


```


## The `Geometry` Datatype

The `Geometry` datatype extends the existing data-model in PostgreSQL and can be used to create `Geometry`-valued attributes within database tables. The `Geometry` datatype can be used to represent:
   
   * Point
   * Lines
   * Polygons
   * Multi-polygons

Tables with geometry-typed columns can be generated in the same way as any other postgreSQL table:

```sql

CREATE TABLE ROADS ( 
    ID int4, 
    ROAD_NAME varchar(25), 
    geom geometry(LINESTRING,4326) );

```

This creates a table called ROADS that contains a `Geometry`-typed column. In this example, we have (optionally) specified that the geometry will contain line objects and use the WGS84 coordinate system (with ID:4326).

```sql

CREATE TABLE ROADS ( 
    ID int4, 
    ROAD_NAME varchar(25), 
    geom geometry );

```

This declaration is also valid, and will allow any geometry object to be assigned to the column. Defaults will be used for the coordinate system.

Once we have our spatial table, we can populate it with some spatial data. To do this we will make use of the *well-known text* representation. Spatial object are created using one of the constructor functions listed in: [http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors](http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors)

In our first example, we will insert 2 records into the ROADS table that contain a simple line in `geom` column.

```sql
INSERT INTO ROADS(ID,ROAD_NAME,geom) 
VALUES (1, 'Test Road', ST_LineFromText('LINESTRING(1 2, 3 4)'));
INSERT INTO ROADS(ID,ROAD_NAME,geom) 
VALUES (1, 'Test Road', ST_LineFromText('LINESTRING(5 6, 3 7)'));

```

Run:

```sql
SELECT * FROM ROADS;

```

And observe the result. Notice that the geometry column contains binary data. The only way to access the data in a meaningful way is through the accessor functions: [http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors](http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors)

For example, we can return the end point of each line in our table:

```sql
SELECT ST_EndPoint(geom) FROM ROADS;

```

This returns the binary representation of the line objects. We can access the individual points through the use of the `ST_AsText` function:

```sql
SELECT ST_AsText(ST_EndPoint(geom)) FROM ROADS;

```

Alternatively, we can access the individual x and y coordinate values from the geometry using the `ST_X` and `ST_Y` functions. These return float-typed values:

```sql
SELECT ST_X(ST_EndPoint(geom)), ST_Y(ST_EndPoint(geom)) FROM ROADS;

```

## Spatial Queries

Once we have some spatial data within our database, we can carry out some queries on this data. Spatial queries are usually based around conditions that are related to the spatial properties of the geometry object. We can build out spatial queries using the spatial relationship functions provided by PostGIS: [http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements](http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements)

Example 1: find the distance between all geometry objects within the ROADS table:

```sql

SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_Distance(R1.geom, R2.geom)
FROM ROADS as R1, ROADS as R2;

```

In this query we include the ROADS relation twice so that we can compare each geometry object against every other geometry object. The objects are compared using the `ST_distance` function.

Example 2: Determine if any part of each geometry within the database table touches any other geometry object:

```sql
SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_touches(R1.geom, R2.geom)
FROM ROADS as R1, ROADS as R2;

```

The `ST_Touches` returns true if there are any points in common between the geometries: [http://postgis.net/docs/manual-2.1/ST_Touches.html](http://postgis.net/docs/manual-2.1/ST_Touches.html)

Example 3: Return the shortest line between the closest two points of each geometry in the ROADS table:

```sql
SELECT ST_AsText(R1.geom), ST_AsText(R2.geom), ST_AsText(ST_ShortestLine(R1.geom, R2.geom))
FROM ROADS as R1, ROADS as R2;

```

## Exercises for You

Your task is to construct a database table that stores the name and location of each state capital within Australia. Your database table should include a POINT-type geometry object that represents the location of each city.

* You will need to construct a `CREATE TABLE` statement to create you database table.
* Construct a set of insert statements that will upload the data for each city. Use wikipedia.org to obtain the precise latitude and longitude for each capital city.

Once you have constructed the table, create a query that finds the distance between each capital city and every other capital city. You should do this by using the `ST_Distance` function.

Construct a second query that returns all capital cities that are within 1000Km of Melbourne. 


---