Introduction to PostGIS - Spatial Database and Spatial Data Mining ...

seasoningalluringΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 4 μήνες)

388 εμφανίσεις

Introduction to PostGIS


I. Getting Started

Lot of stuff is going to come here …


I.1. How to start a postgis server?



pg_ctl

is a utility to start, stop, restart, reload configuration files, or report the
status of a PostgreSQL server.



To get help on pg_ct
l, type the following command at your unix prompt

o

%
pg_ctl

help

o

The contents of pg_ctl help are shown below



postgres@flounder (~) pg_ctl
--
help

pg_ctl is a utility to start, stop, restart, reload configuration files,

or report the status of a PostgreSQL server.


Usage:


pg_ctl start

[
-
w] [
-
D DATADIR]
[
-
s] [
-
l FILENAME] [
-
o "OPTIONS"]


pg_ctl stop

[
-
W] [
-
D DATADIR] [
-
s] [
-
m SHUTDOWN
-
MODE]


pg_ctl restart

[
-
w] [
-
D DATADIR] [
-
s] [
-
m SHUTDOWN
-
MODE] [
-
o "OPTIONS"]


pg_ctl reload

[
-
D DATADIR] [
-
s]


pg_ctl status

[
-
D DATADIR]


Common options:


-
D DATADIR

Location of the database storage area


-
s

Only print errors, no informational messages


-
w

Wait until operation completes


-
W

Do not wait until operation completes

(The default is t
o wait for shutdown, but not for start or restart.)


If the
-
D option is omitted, the environment variable PGDATA is used.


Options for start or restart:


-
l FILENAME

Write (or append) server log to FILENAME. The





use
of this option is highly recommended.


-
o OPTIONS

Command line options to pass to the postmaster




(PostgreSQL server executable)


-
p PATH
-
TO
-
POSTMASTER

Normally not necessary


Options for stop or restart:


-
m SHUTDO
WN
-
MODE May be 'smart', 'fast', or 'immediate'


Shutdown modes are:


smart

Quit after all clients have disconnected


fast

Quit directly, with proper shutdown


immediate

Quit without complete shutdown; will
lead to recovery run on restart


Report bugs to <pgsql
-
bugs@postgresql.org>.



Here is the output of a sample run





o

IF there are no errors, then you should get “postmaster successfully
started” message. Here

D option t
ells the location (path) of your
database directory, so change it to suit your local installation
environment.

o

Your interaction with the database can be logged using

l option. The
log files are very useful in tracing users activities and debugging.
Perio
dically check the contents of tmplog file (you can use any text
editor like vi, emacs or xemacs).

o

In general postgis needs to be started once, may be at the system boot
time, or interactively when you need it. Once started you can do all
database related a
ctivities like creating new databases, querying and
modifying existing databases, etc. till the postgis server is stopped.


I.2. How to create a new database?

Creating a new database with postgis is a 3 step process.



First you need to create a database us
ing createdb command, help is shown
below





















Here is output of county database creation





postgres@flounder (~) pg_ctl start
-
D /data2/pgsql/data/
-
l tmplog

postmaster successfully started

postgres@flounder (~) createdb
--
help

createdb creates a PostgreSQL database.


Usage:


createdb [options] dbname [description]


Options:


-
D,
--
location=PATH

Alternative place to store the database


-
T,
--
template=TEMPLATE

Template database to copy


-
E,
--
encoding=ENCODING

Multibyte encoding for the database


-
h,

--
host=HOSTNAME

Database server host


-
p,
--
port=PORT

Database server port


-
U,
--
username=USERNAME

Username to connect as


-
W,
--
password

Prompt for password


-
e,
--
echo


Show the

query being sent to the backend


-
q,
--
quiet


Don't write any messages


By default, a database with the same name as the current user is created.

postgres@flounder (~) createdb country
-
U postgres

CREATE DATABASE

o

If database is created successfully, then you should get “CREATE
DATABASE” message.


At this point check the contents of your log file.




Second ste
p involves binding a procedural language to the newly created
database. This binding adds the ability to use functions written in ‘plpgsql’ to
the new database. Here is the syntax


















o

Here is sample run for our country database.






Here, ‘p
lpgsql’ is the standard procedural language (PL) that
comes with postgresql database. There are several stadard PLs
that comes with postgresql, they are, PL/pgSQL, PL/Tcl,
PL/python. One can crate their own functions and can install
(bind) them into any da
tabase.



The third step is to start a postgres interactive terminal. Before doing that, we
want to run a standard script that comes with postgis called ‘postgis.sql.’ This
script adds several utility functions that operate on ‘geometry’ data type, and
two d
efault tables to keep tack of geometry columns and spatial reference
systems. Here is the sample run on our country database





o

You will observe lot of CREATE, INSERT, DROP messages and
finally a COMMIT message. If there are errors and you miss them to
notice on the screen, then you can look into your log file.


Congratulations, you have just created country database
successfully using postgis. Now its time to populate this database
postgres@flounder
(~) createlang
--
help

createlang installs a procedural language into a PostgreSQL database.


Usage:


createlang [options] langname [dbname]


Options:


-
h,
--
host=HOSTNAME

Database server host


-
p,
--
port=PORT

Database serve
r port


-
U,
--
username=USERNAME

Username to connect as


-
W,
--
password

Prompt for password


-
d,
--
dbname=DBNAME

Database to install language in


-
L,
--
pglib=DIRECTORY

Find language interpreter file in DIRE
CTORY


-
l,
--
list


Show a list of currently installed languages

postgres@flounder (~) createlang
-
U postgres plpgsql country

postgres@flounder (~) psql
-
U postgres country < /usr/local/pgsql/shar
e/contrib/postgis.sql

with various GIS database layers. Before that let us get familiarize
our
selves with simple SQL commands.


I.3. How to connect to the new database and use SQL?

Any database can be accessed is several ways. For now let us start with ‘interactive
session.’




A simple way to start interactive session is by simply typing the follow
ing
command at the unix prompt:





o

Here is the sample run output
















Now we are connected to the county database and ready to do some serious
business with the database.

o

Now one must be wondering what this database contains? So how do
we find o
ut what’s in there, or in the first place is there anything in this
new database?

o

Let us start with typing ‘
\
?’ at the county=# prompt

o

The output is provided in the following table. Did you see any
interesting commands? Now its time to explore what some of

these
commands do?


postgres@flounder (~) psql country

postgres@flounder (~) psql
-
U postgres country

Welcome to psql, the PostgreSQL interactive terminal.


Type:
\
copyright for distribution terms


\
h for help with SQL commands


\
? for help on internal slash commands


\
g or terminate with semicolon to execute query


\
q to quit


country=#

country=#
\
?


\
a



toggle between unaligned and aligned output mode


\
c[onnect] [DBNAME|
-

[USER]]

connect to new database (currently "country")


\
C TITLE



se
t table title


\
cd [DIRNAME]


change the current working directory


\
copy ...



perform SQL COPY with data stream to the client host


\
copyright



show PostgreSQL usage and distribution terms


\
d TABLE



describe table (or view, index,
sequence)


\
d{t|i|s|v}...



list tables/indexes/sequences/views


\
d{p|S|l}



list access privileges, system tables, or large objects


\
da



list aggregate functions


\
dd NAME



show comment for table, type, function, or operator


\
df



list functions


\
do



list operators


\
dT



list data types


\
e FILENAME


edit the current query buffer or file with external editor


\
echo TEXT



write text to standard output


\
encoding ENCODING


set cl
ient encoding


\
f STRING



set field separator


\
g FILENAME


send SQL command to server (and write results to file or |pipe)


\
h NAME



help on syntax of SQL commands, * for all commands


\
H



toggle HTML output mode (current
ly off)


\
i FILENAME



execute commands from file


\
l



list all databases


\
lo_export,
\
lo_import,
\
lo_list,
\
lo_unlink


large object operations


\
o FILENAME


send all query results to file or |pipe


\
p



show the content

of the current query buffer


\
pset VAR



set table output option (VAR := {format|border|expanded|





fieldsep|null|recordsep|tuples_only|title|tableattr|pager})


\
q



quit psql


\
qecho TEXT



write text to query outp
ut stream (see
\
o)


\
r



reset (clear) the query buffer


\
s FILENAME


print history or save it to file


\
set NAME VALUE


set internal variable


\
t



show only rows (currently on)


\
T TEXT



set HTML table tag attribu
tes


\
unset NAME



unset (delete) internal variable


\
w FILENAME


write current query buffer to file


\
x



toggle expanded output (currently off)


\
z



list table access privileges


\
! [COMMAND]


execute command in shel
l or start interactive shell




Are there any tables in the country database?













Where did these tables come from? What fields (columns) these tables
contains? What are the contents present at this point in these tables?


I.4 Creating Tables and Adding
Geometry types

Let us start with creating a table called countries and then add a geometry column to it.






























country=#
\
d



List of relations



Name

| Type

| Owner

--------------------------
+
--------
+
----------


geometry_columns

| table

| postgres


spatial_ref_sys

| table

| postgres

(2 rows)

country=# create table countries (id integer primary key
, name varchar (20) not null);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'countries_pkey' for
table 'countries'

CREATE


// Check the structure of this table


country=#
\
d countries


Table "countries"


Column | Type



| Modifiers

----------
+
-------------------------------
+
-----------


id

| integer



| not null


name | character varying(20)

| not null

Primary key: countries_pkey


// Add geometry to it


country=# select AddGeometryColumn

('country', 'countries', 'geom',
-
1, 'GEOMETRY', 2);


addgeometrycolumn

-----------------------------------------------------------------------------------


Geometry column geom added to tab
le countries WITH a SRID of
-
1 and type GEOMETRY

(1 row)


country=#
\
d countries


Table "countries"


Column | Type


| Modifiers

----------
+
-------------------------------
+
-----------


id

| integer


| not n
ull


name

| character varying(20)

| not null


geom

| geometry

|

Primary key: countries_pkey

Check constraints: "$1" (srid(geom) =
-
1)





What is the syntax of ‘AddGeometryColumn’ or for that matter how do I
know what new functions and datatypes are availabl
e? (see documentation).









OpenGIS Functions
(http://postgis.refractions.net/docs/c664.html#AEN667)

AddGeometryColumn(varchar, varchar, varchar, integer, varchar,
integer)

Syntax: AddGeometryColumn(<db_name>, <table_name>,
<column_name>, <srid>, <type>
, <dimension>). Adds a geometry
column to an existing table of attributes. The
dbname

is the name of
the database instance. The
srid

must be an integer value reference
to an entry in the SPATIAL_REF_SYS table. The
type

must be an
uppercase string correspon
ding to the geometry type, eg, 'POLYGON'
or 'MULTILINESTRING'.

DropGeometryColumn(varchar, varchar, varchar)

Syntax: DropGeometryColumn(<db_name>, <table_name>,
<column_name>). Remove a geometry column from a spatial table.

AsBinary(geometry)

Returns t
he geometry in the OGC "well
-
known
-
binary" format, using
the endian encoding of the server on which the database is running.
This is useful in binary cursors to pull data out of the database without
converting it to a string representation.

Dimension(geom
etry)

Returns '2' if the geometry is two dimensional and '3' if the geometry
is three dimensional.

Envelope(geometry)

Returns a POLYGON representing the bounding box of the geometry.

GeometryType(geometry)

Returns the type of the geometry as a string.

Eg: 'LINESTRING',
'POLYGON', 'MULTIPOINT', etc.

X(geometry)

Find and return the X coordinate of the first point in the geometry.
Return NULL if there is no point in the geometry.

Y(geometry)

Find and return the Y coordinate of the first point in the g
eometry.
Return NULL if there is no point in the geometry.

Z(geometry)

Find and return the Z coordinate of the first point in the geometry.
Return NULL if there is no point in the geometry.

NumPoints(geometry)

Find and return the number of points in th
e first linestring in the
geometry. Return NULL if there is no linestring in the geometry.

PointN(geometry,integer)

Return the N'th point in the first linestring in the geometry. Return
NULL if there is no linestring in the geometry.







OpenGIS Functions
(http://postgis.refractions.net/docs/c664.html#AEN667)



ExteriorRing(geometry)

Return the exterior ring of the first polygon in the geometry. Return
NULL if there is no polygon in the geometry.

NumInteriorRings(geometry)

Return the number of interior rings of the first polygon in the
geometry. Return NULL if there is no polygon in

the geometry.

InteriorRingN(geometry,integer)

Return the N'th interior ring of the first polygon in the geometry.
Return NULL if there is no polygon in the geometry.

IsClosed(geometry)

Returns true of the geometry start and end points are coincident.

NumGeometries(geometry)

If geometry is a GEOMETRYCOLLECTION return the number of
geometries, otherwise return NULL.

GeometryN(geometry,int)

Return the N'th geometry if the geometry is a
GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING or
MULTIPOLYGON. O
therwise, return NULL.

Distance(geometry,geometry)

Return the cartesian distance between two geometries in projected
units.

AsText(geometry)

Return the Well
-
Known Text representation of the geometry. For
example: POLYGON(0 0,0 1,1 1,1 0,0 0)

SRID(geom
etry)

Returns the integer SRID number of the spatial reference system of
the geometry.

GeometryFromText(varchar, integer)

Syntax: GeometryFromText(<geometry>,<SRID>) Convert a Well
-
Known Text representation of a geometry into a geometry object.

GeomFro
mText(varchar, integer)

As above. A synonym for GeometryFromText.

SetSRID(geometry)

Set the SRID on a geometry to a particular integer value. Useful in
constructing bounding boxes for queries.

EndPoint(geometry)

Returns the last point of the geometry a
s a point.

StartPoint(geometry)

Returns the first point of the geometry as a point.

Centroid(geometry)

Returns the centroid of the geometry as a point.




Now look at the contents of geometry_columns table



















o

Note that initially the geometry_columns contains no records,
however, whenever we add a geometry column to a table, postgis
automatically adds a
record like the above. This is how postgis keeps
track of geometry columns and the same logic applies to ‘spatial
reference systems’ associated with the tables.

o

Also give attention to the 5
th

parameter to the AddGeometryColumn
function. This field tells wh
at is the geometry type of each spatial
object that is going to be stored in this table. Possible values are:
POINT, LINESTRING, POLYGON, MULTIPOINT,
MULTISTRING, MULTIPOLYGON, and GEOMETRY. We used the
GEOMETRY type for countries table, which allows us to

store any
possible geometry type, that is, point, line, polygon, or their multi
-

counterparts.


I.5. Populating our database

There are two broadways of populating any database. The first one interactively inserts
values into each record. The second method

is called bulk
-
loading, where entire database
is populated in offline manner. First let us start with the interactive one. Here is the
syntax for the insert command.










country=#
\
d geometry_columns


f_table_catalog

| character varying(256)

| not null


f_table_sche
ma

| character varying(256)

| not null


f_table_name

| character varying(256)

| not null


f_geometry_column

| character varying(256)

| not null


coord_dimension

| integer


| not null


srid


| integer



| not null


type


| character varying(30)

| not null


attrelid


| oid


|


varattnum

| integer


|


stats


| histogram2d


|


country=# select * from geo
metry_columns;


| country | countries | geom | 2 |
-
1 | GEOMETRY | 429823 | 3 |


country=#
\
h insert

Command: INSERT

Description: create new rows in a table

Syntax:

INSERT INTO table [ ( column [
, ...] ) ]


{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }


Populating non spatial data is pretty straight forward, however for geometry ty
pes we
need additional functions from the PostGIS. The PostGIS uses the Well
-
Known
-
Text
(WKT) format for describing geometries. Here is an example of how to insert various
geometry types into our countries table.































Certain coun
tries can not be represented by single polygon, for example,
Japan?, So how do you handle such situations? Enter Japan into countries table
(assume your own number of islands and their coordinates).



I.6. Querying and manipulating the database.

So far we
have created a database and populated it with sample data. The next natural
question is to find what is there inside this database and how to extract additional
information (for example, Area of a country) from this database. Here are couple of
examples.





country=#
\
d countries


id


| integer


| not null


name

| character varying(20)

| not null


geom

| geometry


|


// Now let us insert

a point (assuming a country can be represented as point at certain
scale)


country=# insert into countries (id, name, geom)


values (1, 'Sri Lanka', GeometryFromText('POINT(30 50)',
-
1));

INSERT 429830 1


// Note t
hat the ‘GeometryFromText’ function takes a WKT string and spatial reference
=
楤
iof䐩=a猠楮灵琠慮搠te瑵牮猠t⁧=潭整oy⁴y灥K
=
=
⼯⁎潷整⁵猠=湳n牴⁡⁰潬=杯渠ga獳畭楮i=a⁣潵湴oy=ca渠扥⁲e灲e獥湴敤⁡猠a=灯py杯渠g琠
瑨攠獡浥⁳male⤮
=
=
c潵湴oy=⌠楮獥#琠t湴漠捯n
湴物n猠⡩搬s浥Ⱐge潭⤠
=
癡汵敳
㈬O❉湤楡DⰠ䝥潭整ryc牯浔ex琨tmli奇低
=
=
††††††††††††=
⠲㔠E〬′〠㘵Ⱐ㌰‵㔬′〠㘵Ⱐ㈵‷〩=
=
††††††††††††=
⤧Ⱐ
-

=
††††††

=
f乓䕒吠㐲㤸㌱‱
=
=











How do you display geometry as plain text (hint AsText(geom.) function)?.




















Get country names and their geometries


country=# sele
ct name, geom from countries;


Sri Lanka

| SRID=
-
1;POINT(30 50)


India

| SRID=
-
1;POLYGON((25 70,20 65,30 55,20 65,25 70))


I.7. Working with real data

So far we have worked with factious data. In this section we learn how to import real
spatial data into PostGIS. We ar
e providing shapefile database of Europe consisting of
four layers; country boundaries (country.*), cities (cities.*), major roads (mjroads.*), and
major water ways (mjwater.*).




Before importing these shape files, first create a fresh database using the
procedure described above. Name the database as your group name (for
example, sdblab#). Once the database is created, we can directly import the
external data into database (called bulkload) using the appropriate utility, in
our case it is called shp2pgsql
. Here is the help on shp2pgsql:































Observe closely at the sqlpgsql command. The

s parameter allows to specify
the spatial reference id, for more information on SRID parameter, refer to
http://www.opengis.org/techno/interop/EPSG2WKT.TXT



You can get appropriate SRID by translating the project information provided
in the cover
-
name.prj file into corresponding EPSG code given in the
EPSG2WKT.TXT file.

postgres@flounder (~) pg_ctl start
-
D /data2/pgsql/data/
-
l tmplog

postmaster successfully started


// create database using t
he above described procedure

…..
=
…..
=
=
⼯⁩=灯牴⁳桡灥⁦楬=猠楮s漠瑨攠湥睬y⁣rea瑥搠ta瑡扡獥
=
=
灯獴g牥獀晬潵湤f爠⡾⤠F桰㉰h獱氠
-
猠㐳㈶⁣潵湴oy⁣潮畮瑲y瑢t⁳摢=a戠|⁰獱=⁳摢=ab
=
Co䕁呅
=
†††††††††††††††††††=
a摤de潭e瑲yc潬畭渠†††††††††
††††††††††
=
ⴭⴭⴭⴭⴭ-ⴭ-ⴭⴭⴭ-ⴭ-ⴭⴭⴭⴭⴭⴭⴭⴭ-ⴭ-ⴭⴭⴭ-ⴭ-ⴭⴭⴭⴭⴭⴭⴭⴭ-ⴭ-ⴭⴭⴭ-ⴭ-ⴭⴭⴭⴭⴭ-
=
=
䝥潭整oy⁣潬畭渠瑨敟来潭⁡摤o搠瑯⁴a扬攠c潮o湴ny瑢t⁗fq䠠e⁓of䐠潦=㐳㈶4
a湤⁴ype⁍=iqfmlivd低
=
⠱⁲潷o
=
=
B䕇fk
=
f乓䕒吠㐷㜳㘶‱
=
f乓䕒吠㐷㜳㘸
=
1
=
…..
=
…..
=
C位lfq
=
灯獴g牥獀晬潵湤f爠⡾F
=


Now import rest

of the shape files (cities, mjroads, and mjwater) into your
sdblab# database (replace # with your group number).


Once the database is populated, we can do spatial queries through SQL. In the reminder
of this section we provide sample queries to get famil
iarize with the PostGIS. Please note
that a given query can be written in several ways in SQL, some times one is the better
than the other. Also query syntax will be different from one system to the other.
However, the semantics are same as long as the sys
tem conforms to OGC’s simple
feature specification for SQL standard.




List all the countries and the country they belong to in the CITIESTBL table.




































// First connect to the database (once till you quit)

postgres@flounder (/data2/sdb
-
data/eu
-
data) psql sdblab

Welcome to psql, the PostgreSQL interactive terminal.


Type:
\
copyright for distribution terms


\
h

for help with SQL commands


\
? for help on internal slash commands


\
g or terminate with semicolon to execute query


\
q to quit


sdblab=#


// Before writing query, first understand the structure of each table


sdblab=#
\
d citiestbl



Table "citiestbl"


Column | Type | Modifiers

-----------
+
-------------------
+
-----------


gid | integer |


type | integer |


nation | integer |


cntryname | character varying |


level

| integer |


name | character varying |


namepre | character varying |


code | character varying |


province | integer |


provname | character varying |


unprov | integer |


conurb | character v
arying |


the_geom | geometry |

Check constraints: "$1" (srid(the_geom) = 4326)


"$2" ((geometrytype(the_geom) = 'POINT'::text) OR (the_geom IS NULL))


sdblab=# select name, cntryname from citiestbl;


name |

cntryname

----------------------------
+
------------------------


Santa Cruz de Tenerife | Spain


Palmas de Gran Canaria | Spain


Lisboa | Portugal


Porto | Portugal

…..

….

Nachicevan

| Azerbaydzhan


Kirovakan | Armenia


Baku | Azerbaydzhan


Gandza | Azerbaydzhan

(1267 rows)

Now let us look at some spatial queries. Here are couple of examples:

1.

Fi
nd area of all countries in the Europe database.

2.

Find all neighboring states of Minnesota

(use USA database)
.

3.

Find all states that I35 passes through.


Correlate these queries with the similar examples provided in the text book.


































How to get these datasets:

http://www.esri.com/data

sdblab=# select
cntry_name, area(the_geom) from cbndtbl
;


cntry_name

|
area



----------------------------
--
+
------------------------

Germany




| 45.872

France




| 64.348

………




sdblab=# select

s2.state from states s1, states s2 where s1.state = ‘Minnesota’ and
=
=
=
=
瑯畣桥猨猱⹴桥sge潭Ⱐo㈮瑨敟来潭⤻
=
=
p瑡瑥
=
ⴭⴭⴭⴭ-
=

湮n獯瑡
=
乯k瑨⁄t歯瑡
=
䵩捨楧an
=
t楳捯湳楮
=
p潵瑨⁄o歯瑡
=
fo睡
=
=
獤扬a戽⌠獥汥lt
=
s.state from states s, roads r where r.name = ‘Interstate Route 35’
=
=
=
=
=
a湤⁩湴e牳rc瑳t爮瑨r_geo洬⁳⹴桥彧e潭⤻
=
=
p瑡瑥
=
ⴭⴭⴭⴭ
=
䵩湮e獯瑡
=
fo睡
=
䵩M獯畲s
=
䭡湳ns
=
佫污桯浡
=
呥xas
=
=