03-PostGIS.doc - OpenGeo

assistantashamedData Management

Nov 29, 2012 (4 years and 6 months ago)

262 views

1




MODULE 3: Working with PostGIS



Module 3

Working with PostGIS


This module focuses on how to use the PostGIS spatial database with
GeoServer. The reader will begin by walking through the installation process, and
continue on to learn how to load spatial data into PostGIS.


In this mod
ule you will:




Install PostgreSQL and PostGIS.



Learn how to load spatial data into PostGIS.



Learn how to configure GeoServer to interact with a
PostGIS database.



Learn how to convert a shapefile into a PostGIS table.

Before you start:




Ensure that
Module

2: The Basics

has been
completed.

2




MODULE 3: Working with PostGIS

Section 1: Installing PostgreSQL


1.

Navigate to the workshop
software

directory.


2.

Run the
PostgreSQL

installer by double
-
clicking on the file named

postgresql
-
8.2.msi
.




3.

Begin the installer by selecting the language of yo
ur choice and
clicking
Start
.



3




MODULE 3: Working with PostGIS


4.

On the
Welcome

screen click
Next
.


5.

Accept the
License

Agreement

/
Installation Notes

and click
Next
.




6.

On the
Installation Options

screen accept all the defaults and click
Next
.





T
he PostGIS extensions
should be left
disabled
. They
will be installed in the next
section.

4




MODULE 3: Working with PostGIS


7.

On the
Service Configuration

screen,

enter the password
“postgres2008” and click
Next
. When asked to create a new
account
,
click
Yes
.






Any warnings about
weak passwords can be
ignored.


For security
reasons PostgreSQL
requires a non
-
privileged account to
be installed. The
installer will create
one for you. This
account can be
removed later.

5




MODULE 3: Working with PostGIS


8.

On the
Initialize Database Cluster

screen, enter the password
“postgres” (note that this is different from the password in the previous
step), and cl
ick
Next
.





9.

Continue through the rest of the installer accepting the defaults.





The database super
user is
d
ifferent

from the
windows user account
created in the previous
step.

6




MODULE 3: Working with PostGIS




7




MODULE 3: Working with PostGIS


10.

Finalize the installation and exit the installer.




8




MODULE 3: Working with PostGIS


Section 2: Installing PostGIS


1.

Navigate to the workshop
software
.


2.

Run the
PostGIS

installer by double
-
cli
cking the file
named
postgis
-
pg82
-
setup
-
1.3.3
-
1.exe
.




3.

Accept the
License Agreement

and click
Next
.



9




MODULE 3: Working with PostGIS


4.

Continue through the next three screens accepting the
defaults.





10




MODULE 3: Working with PostGIS


5.

On the
Database Connection

screen specify the
password “postgres” and click
Nex
t
.




6.

Accept the default database name “postgis”, and click
Install
.






11




MODULE 3: Working with PostGIS


7.

Finalize the installation and exit the installer.




12




MODULE 3: Working with PostGIS


Section 3: Creating a Spatial Database


1.

Open a PostgreSQL command line prompt by opening the
Start Menu

and navigating to
Pr
ograms

PostgreSQL
8.2

Command Line
.




2.

Create a new database called “nyc” with the following
command. Use the password “postgres” when prompted.



psql

U postgres

T template_postgis



13




MODULE 3: Working with PostGIS


Section 4: Loading Spatial Data


1.

Open the
PostgresSQL Administrat
ion Console

by opening the
Start
Menu

and navigating to
PostgreSQL 8.2

pgAdmin III
.




2.

In
pgAdmin

connect to the database by right
-
clicking on
PostgreSQL
Database Server 8.2 (localhost:5432)

and selecting
connect
.



14




MODULE 3: Working with PostGIS


3.

In the
Connect To Server

dialog, ente
r the password “postgres”, check
off
Store Password
, and click
OK
.




4.

In the
Object Browser

navigate to
Databases

nyc
, and click the
SQL
Query
button

in the toolbar.



15




MODULE 3: Working with PostGIS


5.

In the
Query

window, open the
File

menu and select
Open…




6.

In the
File Explorer
n
avigate to the workshop
data

directory and open
the
buildings.sql

file.




The
buildings.sql

file contains the SQL
statements to create a
table named roads and
populate it with data.

16




MODULE 3: Working with PostGIS


7.

Execute the SQL by clicking the
Execute Query
button in the toolbar.







8.

Repeat steps 5 through 7 with the
hydrography.sql

and
roads.sql

files.
17




MODULE 3: Working with PostGIS


Section 5: Adding a PostGIS Da
tabase


1.

Open a web browser and navigate to the GeoServer
Welcome Page
at
http://localhost:8080/geoserver
.


2.

From the
Welcome Page

navigate to
Config

Data.






3.

Select the
DataStores

link and click
New.





18




MODULE 3: Working with PostGIS


4.

Select
Postgis

from the dropdown list and enter “postgis” in the text
box.




5.

On the
Feature Data Set Editor

page:


a.


E
nter “nyc” in the
database

field

b.


Enter “postgres” in the
user

field

c.


Enter “postgres” in the
password

field

d.


Click
Submit

at the bott
om of the page




The editor page is
used to enter all the
database connection
information. It also
provides a n
umber of
parameters that can be
used to tune database
performance such as
connection pool size,
which specifies how
many open database
connections can exist
concurrently.

19




MODULE 3: Working with PostGIS


6.

Click
Apply

and then
Save

located in the top left corner of the page.


20




MODUL
E 3: Working with PostGIS


Section 6: Adding a PostGIS Table


1.

From the GeoServer
Welcome

page navigate to
Config

Data

FeatureTypes.




2.

Click
New
.




3.

Select
postgis:::buildings

from drop do
wn list and click
New
.




The dropdown list
contains a list of the
tables which are available
to be publishe
d. When a
table is chosen from the
list a new Feature Type is
created from it.

21




MODUL
E 3: Working with PostGIS


4.

On the
FeatureType Editor

page change the style by selecting
polygon

from the
Style

dropdown list.




5.

Generate the bounds for the layer by clicking
Generate

located on the
middle of the page.




6.

Scroll to the bottom of the pag
e and click
Submit
.




7.

Apply

and
Save

changes.

22




MODUL
E 3: Working with PostGIS


8.

View the new layer from the
Map Preview

and clicking the
topp:buildings

link.




23




MODULE 3: Working with PostGIS



9.

Add the
roads

table repeating steps 1 through 7. Remember to:


a.

Set the Style dropdown to
line

b.

Set the bounds with the
Gen
erate

button

c.

Click
Apply

after clicking
Submit









10.


Add the
hydrography

table repeating steps 1 through 7.
24




MODULE 3: Working with PostGIS


Extra Credit: Importing Shapefile Data


1.

Navigate to the workshop
data

directory.


2.

Copy the following files onto the desktop:


sights.shp, si
ghts.shx, sights.dbf, sights.prj




3.

From the
Start

menu, open a
Command Line Prompt
.




25




MODULE 3: Working with PostGIS


4.

Change directory to the desktop by executing the command line:


cd Desktop


5.

Convert the
sights.shp

file to SQL with the
shp2pgsql

utility by
executing the command li
ne:


“C:
\
Program Files
\
PostgreSQL
\
8.2
\
bin
\
shp2pgsql.exe”


-
s 4326 sights.shp sights > sights.sql




6.

Execute the
sights.sql

script against the database by
executing the command line:


“C:
\
Program Files
\
PostgreSQL
\
8.2
\
bin
\
psql.exe”



U postgres nyc

< sights.sql





Note

the double
quotes “” around the
path to shp2pgsql.
They are
necessary

because of the space
in the file path.


shp2pgsql

is a
utility program used
to convert a shapefil
e
into a database table.
It comes with every
PostGIS installation.


If prompted for a
password remember
that the password is
“postgres”.


psql

is a
program used to
interact with a
Postgres database
from the command
line.

26




MODULE 3: Working with PostGIS



7.

Using skills learned in the previous section, publish the
sights

table
created in the previous step.