PostgreSQL/PostGIS: Introduction

hornbeastcalmData Management

Nov 27, 2012 (4 years and 8 months ago)

189 views

PostgreSQL/PostGIS:
Introduction
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Introduction
PostgreSQL

A standards-compliant SQL-based database
server
with
which a wide variety of
client
applications can
communicate
Server software
generally, but not always, runs on powerful computers
dedicated for exclusive use to running the business application.
Client
software
on the other hand generally runs on common PCs or
workstations. Clients get all or most of their information and rely on the
application server for things such as configuration files, stock quotes,
business application programs, or to offload computer-intensive application
tasks back to the server in order to keep the client computer (and client
computer user) free to perform other tasks..
- Wikipedia (4/3/2006)
2
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Introduction
PostGIS

An implementation of the OGC Simple Features for SQL
Specification within PostgreSQL for the storage of
geospatial data (points, lines, polygons) within an SQL-
based relational database management system (RDBMS).

Developed as a set of functions and data types that
‘spatially enable’ the PostgreSQL object-relational database
system.

An open source project developed by Refractions
Research (Victoria, BC)
http://postgis.refractions.net/

Technology analogous to ArcSDE (vector data component)
and Oracle Spatial.
3
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Capabilities
PostgreSQL

Fully-functional object-relational database that complies
with the SQL standard

Stored procedures

Triggers

Custom data types

Multiple supported scripting languages

Broad programming support for interaction

Broad availability of supporting database drivers
(including ODBC drivers for Windows platforms)

Supported on multiple hardware and software
platforms
4
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Capabilities
PostGIS

Geodatabase that supports the storage of vector data in
compliance with the OGC simple features specification

Enhanced geometry storage in support of 4D features

Spatial Reference System support and conversion

Spatial query capabilities

Relationship: distance, equivalence, intersection,
crossing, within, overlapping, containment

Geometry Processing: area. length, centroid, point-on-
surface, boundary, buffer, convex hull, intersection,
difference, union, editors, creators

Accessors: text, binary, SRID, dimension, envelope,
characteristics
5
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Important References
When working in PostgreSQL/PostGIS, there are important
references that you will want to have close at hand:

The documentation for the PostgreSQL database server.
The online documentation for various versions of
PostgreSQL may be found online:
http://www.postgresql.org/docs/

The documentation for the PostGIS extensions to
PostgreSQL. Available as a downloadable PDF file and
online from Refractions Research
http://postgis.refractions.net/documentation/
We will only scratch the capabilities of PostgreSQL and
PostGIS in the next couple of lectures. The full capabilities of
these combined tools only become fully apparent with use.
6
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Installation of PostgreSQL/PostGIS
Detailed installation instructions for PostgreSQL and PostGIS
are provided for each of the platforms for which it is available.
Two options are generally available for installation:

Installation of precompiled binary files for your platform

Compilation of the server and geospatial extensions from
scratch.
If you are working on a Microsoft Windows platform, you will
probably want to install precompiled Windows binary files. The
Windows binary installers for both PostgreSQL and PostGIS
are available for download from the class website. Installation
instructions are also available on the website.
Brief information relating to linux installation is found in the
resources discussed in Hack 87 in the
Mapping Hacks
text.
7
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Database Concepts
Key concepts that are critical to successful work in the client-
server database environment:

All interaction with the server is through the execution of
commands in Structured Query Language (SQL)

Different client interfaces provide alternative tools for
generating and submitting SQL to the server for action

Graphical User Interfaces translate your interactions
with the interface into SQL which is submitted to the
server

Text interfaces allow you to directly enter SQL for
submission and execution on the server

Client interfaces may access a server on the same system
as the client, or may interact with remote, network-
connected, servers.

Database servers can support multiple concurrent
requests
8
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Examples of Client Interfaces
Client interfaces to PostgreSQL include:

Programs that support the ODBC and JDBC driver
standards (i.e. MS Access, Excel, OpenOffice)

Cross-platform, PostgreSQL-specific client interfaces (i.e.
pgAdminIII, EMS PostgreSQL Manager)

Multi-database client applications (i.e. Aqua Data Studio,
Navicat)

Web-based clients (i.e. phpPgAdmin, Webmin)

Database drivers implemented in various programming/
scripting languages (i.e. PHP, Perl, Python)
This diversity of client interfaces provides great flexibility in
how the content of PostgreSQL databases are accessed. For
example, data may be entered using MS Access, may be
analyzed using R, and mapped using GRASS, with all three
applications accessing the same, high-performance database.
9
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Relational Data Model
SQL (pronounced
Sequel
) came out of the ideas proposed by
Dr. Edgar Codd in his 1970 paper:
A Relational Model of Data for
Large shared Data Banks
, which described the fundamental
concepts of the
relational data model
. Early SQL databases were
developed at UC Berkeley, IBM, and by Relational Software,
Inc. (now Oracle Corporation).
Relational data models define data structures in which
collections of attributes are broken down into tables of values
that are
related
to each other through common
key
values.
Through the process of
normalization
the tables that comprise
a database are defined to eliminate redundancy, and maximize
efficient storage and retrieval.
10
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Relational Data Model
11
Households
Persons
H
o
u
s
e
h
o
l
d
s
Address Line 1
Address Line 2
Address City
Address State
Latitude
Longitude
Unique ID
address_l1
address_l2
address_city
address_state
lat
lon
h_uid
P
e
r
s
o
n
s
First Name
Last Name
Age (yrs)
Sex (m|f)
Unique ID
Household ID

rstname
lastname
age
sex
p_uid
h_uid
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
Relational Data Model - SQL
A simple SQL statement allows for the combination of data
from these tables into a single combined representation:
SELECT address_l1, address_l2, lat, lon, firstname, lastname, p_uid
FROM households, persons
WHERE persons.h_uid = households.h_uid
This combined result represents a merging of the specified
columns based upon the matching
primary key
of
h_uid
. This
query represents a
one-to-many
relationship between tables.
12
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL
The implementation of SQL in PostgreSQL includes 109
commands that allow for the creation, modification, and
destruction of all components in the database. Key commands
that we will review include:

Commands to create database objects

CREATE DATABASE

CREATE TABLE

CREATE VIEW

CREATE INDEX
13
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL
Commands to alter existing database objects

ALTER DATABASE

ALTER TABLE

ALTER INDEX

INSERT

UPDATE
Commands to destroy existing database objects

DROP DATABASE

DROP TABLE

DROP VIEW

DROP INDEX
And, most importantly, a command to query the database

SELECT
14
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Examples
Creation of a new table within the current database:
CREATE TABLE households (
address_l1 character varying(100),
address_l2 character varying(100),
address_city character varying(100),
address_state character(2),
lat real,
lon real,
h_uid integer NOT NULL
);
15
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Examples
Insertion of records into the newly created table
INSERT INTO households VALUES ('123 Main St.', 'Apt.
4', 'Sunnydale', 'XZ', 35.542999, -138.446, 1);
INSERT INTO households VALUES ('456 Vine Blvd.', '',
'Pescaville', 'MY', 44.234001, -144.22301, 2);
INSERT INTO households VALUES ('78844 J Street', '',
'Marydale', 'BY', 37.875999, -122.753, 3);
16
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Examples
Selecting records from the database
SELECT * from households;
SELECT address_city, lat, lon from households;
SELECT address_city, lat, lon from households
WHERE h_uid = 3
17
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Examples
More database query examples
SELECT address_city, lat, lon from households
ORDER BY address_city
SELECT address_city, lat, lon from households
WHERE lat > 36
ORDER BY address_city
SELECT address_city, lat, lon from households
WHERE lat > 36 AND lon < -144
18
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Examples
SQL supports a large number of functions for modifying field
values that are returned by a query
SELECT address_city, round(lat) as lat, round(lon) as
lon from households
SELECT avg(lat) as lat_avg, avg(lon) as lon_avg from
households
SELECT (lat + lon)/3 as gibberish from households
19
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Views
Views within the database allow for the creation of named SQL
select commands that are stored in the database that may be
later treated like
virtual tables
.
CREATE VIEW combo
AS
SELECT address_l1, address_l2, lat, lon, firstname,
lastname, p_uid
FROM households, persons
WHERE persons.h_uid = households.h_uid;
SELECT * from combo;
20
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - Introduction
SQL Where Clauses
The
WHERE
clause appears in several SQL commands and
should be understood, both for managing data in the database,
and for use in external applications like GRASS and MapServer.
Things to remember

WHERE
clauses consist of one or more
logical
tests that
determine which records in the database will be returned
or processed.

Tests may be combined with the standard operators:
AND,
OR, and NOT

Tests usually include one of the following comparison
operators:
, , , , ,
and

or
!
.
21