Using MapServer with PostgreSQL / PostGIS - Laboratorio di ...

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

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

537 εμφανίσεις

1
Using MapServer
with
PostgreSQL / PostGIS
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
(Creative Commons by-nc-sa, 3.0)
2
GIS (A) -2006/2007
1. PostgreSQL
ORDBMS: Object oriented Relational DataBase Management System
Link : http://www.postgresql.org/
Release for exercises : 8.2 for Windows
with PostGIS
with pgAdmin III
OpenSource License : BSD
D.Magni, Using MapServer with PostgreSQL / PostGIS
(Creative Commons by-nc-sa, 3.0)
3
GIS (A) -2006/2007
2. PostGIS
“PostGIS adds support for geographic objects to the PostgreSQL
object-relational database.
In effect, PostGIS "spatially enables" the PostgreSQL server,
allowing it to be used as a backend spatial database
for geographic information systems (GIS)”
(citation from PostGIS website)
Link : http://postgis.refractions.net/
Release for exercises : 8.2 for Windows
OpenSource License : GPL (General Public License)
D.Magni, Using MapServer with PostgreSQL / PostGIS
(Creative Commons by-nc-sa, 3.0)
4
GIS (A) -2006/2007
2. PostGIS
Allows to geo-refer tables of PostgreSQL (tables become spatial tables)
This is possible by adding a Geometry Columnto tables
The Geometry Column (type=geometry) contains data with the syntax:
SRID=<crs_id>;<GEOMETRY_TYPE>(<COORDINATES>,<COORDINATES>)
Example:
SRID=3003;LINESTRING(1503032.67 5071234.09,1503052.55 5073234.21)
For Gauss-Boaga (West) SRID=3003; if the CRS is not specified, set SRID=-1
D.Magni, Using MapServer with PostgreSQL / PostGIS
(Creative Commons by-nc-sa, 3.0)
5
GIS (A) -2006/2007
2. PostGIS
<GEOMETRY_TYPE>can be:
POINT(0 0)
LINESTRING(0 0,1 1,1 2)
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
MULTIPOINT(0 0,1 2)
MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)),
((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))
D.Magni, Using MapServer with PostgreSQL / PostGIS
(Creative Commons by-nc-sa, 3.0)
6
GIS (A) -2006/2007
2. PostGIS
A DB spatially enabled by PostGIS has two special tables:
geometry_columns: contains a row for each spatial table of that DB
spatial_ref_sys: is a list of CRSs, like the EPSG file seen for PROJ library
D.Magni, Using MapServer with PostgreSQL / PostGIS
Name of the spatial table
Name of geometry column
Geometry dimensions
CRS (SRID)Geometry type
(Creative Commons by-nc-sa, 3.0)
7
GIS (A) -2006/2007
3. pgAdmin III
Frontend application for working with PostgreSQL without commandline
Link : http://www.pgadmin.org/
Release for exercises : 1.6.2 for Windows
License : http://www.pgadmin.org/licence.php
(Creative Commons by-nc-sa, 3.0)
8
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
4. Exercises
1.Open pgAdmin III on your local PC (
Start Programs PostgreSQL pgAdmin III
)
2.Connect the PostgreSQL remote server
FileAggiungi server
FileAdd server
4.1 -Connect a remote PostgreSQL server
A-Service name: GIS course
A
B
B-Host name: 192.168.157.30
C–Manager DB: gis
D-User name: ugisNR
E-Password: ugisNR
C
D
E
3.Click OK
8
NRis the same number of your PSF;
Example: psf34 ugis34
(Creative Commons by-nc-sa, 3.0)
9
GIS (A) -2006/2007
4.Browse the server tree
Tables
geometry_columns
spatial_ref_sys
Database
(Creative Commons by-nc-sa, 3.0)
10
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
1.Tabelle Nuova tabella
TablesNew table
4.2 –Create a table
Name = tableNR
Holder = your user
With OID (select checkbox)
Then, OK
Properties
NRis your
PSF number
(Creative Commons by-nc-sa, 3.0)
11
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
4.2 –Create a table
Column name = id
Column type = integer
Is not null (select checkbox)
Then, OK
Columns
(Creative Commons by-nc-sa, 3.0)
12
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
4.2 –Create a table
Columns
Insert a new column:
Name = name
Type = character varying
Length = 25
12
(Creative Commons by-nc-sa, 3.0)
13
Constraints
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
4.2 –Create a table
1) Choose a primary key
2) Click Aggiungi(Add)
3) Choose columns tab
4) Select idcolumn
as primary key
5) Click Aggiungiand OK
13
(Creative Commons by-nc-sa, 3.0)
14
D.Magni, Using MapServer with PostgreSQL / PostGIS
GIS (A) -2006/2007
4.2 –Create a table
Confirm table creation with OK
Correspondent SQL statements
(Creative Commons by-nc-sa, 3.0)
15
GIS (A) -2006/2007
1.Open Free SQL Box
2.Write the SQL statement:
SELECT AddGeometryColumn(‘<dbName>’,’<tableName>’,
’<geometryColumnName>’,<SRID>,’<geometryType>’,
<geometryDimension>);
3. For the table just created write:
SELECT AddGeometryColumn(‘gis’,’tableNR’,’coord’,3003,’POINT’,2);
4.3 –Add a Geometry Column
D.Magni, Using MapServer with PostgreSQL / PostGIS
(Creative Commons by-nc-sa, 3.0)
16
GIS (A) -2006/2007
4.3 –Add a Geometry Column
D.Magni, Using MapServer with PostgreSQL / PostGIS
4.Confirm with Run button
5.Check result
(Creative Commons by-nc-sa, 3.0)
17
GIS (A) -2006/2007
1.Open Free SQL Box
2.Write the SQL statement:
CREATE INDEX <indexName>
ON <tableName>
USING GIST ( <geometryColumnName> );
4.4 –Add a GiST index
3.Confirm the SQL statement
Here:
CREATE INDEXmygistNR ON tableNR USING GIST (coord );
(Creative Commons by-nc-sa, 3.0)
18
GIS (A) -2006/2007
Primary key
Table columns
GiST index
Geometry column indexes
(Creative Commons by-nc-sa, 3.0)
19
GIS (A) -2006/2007
4.5 –Populate a table
1.Select the table
2.Right click on the table name
and select Visualizza
dati(View data)
3.Choose if display all rows
(Mostra tutte le
righe), first 100 rows
(Visualizza le prime
100 righe) or filtered rows
(Visualizza righe
filtrate...)
or
1.Select the table
2.Click on Ato display all rows
or Bto display filtered rows
A
B
(Creative Commons by-nc-sa, 3.0)
20
GIS (A) -2006/2007
4.5 –Populate a table
4.Fill in the rows (id,name,coord) as shown in the example
5.Don’t fill in oid: PostgreSQL does it automatically
6.Press Enterto confirm
Open Free SQL Box and write
INSERT INTO <tableName>(<column1>,<column2>,...)
VALUES (<value1>,<value2>,...);
If all columns are populated,<column1>,<column2>,...specification can be omitted
Here write:INSERT INTO table1 VALUES (2,’Monte Tre Croci’,
’SRID=3003;POINT(1507871 5070661)’);
It’s possible to populated tables by SQL directly (standard mode)
(Creative Commons by-nc-sa, 3.0)
21
Web Server
Template file
(HTML)
CGI
Mapfile
BROWSER
2
1
3
5
PostgreSQL
Data
Spatially DBMSenabler
3.a
4.b
4.a
3.b
4.6 –Display a PostgreSQL spatial table with MapServer
GIS (A) -2006/2007
Architecture
(Creative Commons by-nc-sa, 3.0)
22
GIS (A) -2006/2007
1.Open your map file
2.Write a new layer to load PostgreSQL/PostGIS data:
4.6 –Display a PostgreSQL spatial table with MapServer LAYER
NAME "layername"
TYPE point
STATUS default
CONNECTIONTYPE postgis
CONNECTION "dbname=<db> host=<host> port=5432 user=<user> password=<pwd>"
DATA "<geometryColumn> from <tableName>"
CLASS
NAME "voice of legend"
STYLE
COLOR 255 0 255
SYMBOL "45rotated_cross"
END
END
END
Geometry column from table
Connection by PostGIS
Connection parameters
If PostgreSQL and MapServer share the same host,
it’s possible to write host=127.0.0.1or host=localhost
(Creative Commons by-nc-sa, 3.0)
23
GIS (A) -2006/2007
3.Write a layer for tableNR data
4.Check that the layer is correctly drawn in the map
5.Insert other 5 rows in your tableNRand see the result on the map
4.6 –Display a PostgreSQL spatial table with MapServer
(Creative Commons by-nc-sa, 3.0)
24
GIS (A) -2006/2007
It’s possible to convert a PostgreSQL/PostGIS table to a shapefile
•Open an MS-DOS command prompt
•Enter the folder of your local PostgreSQL installation (refer to it as
$PostgreSQL), by cdcommand
•Enter 8.2\bin\folder
•Run the pgsql2shpcommand, with the following syntax:
pgsql2shp -h [hostName] -u [PostgreSQLUserName] -P
[password] -f [path\NewShapefileName] [dbName]
[tableName]
4.7 –PostgreSQL table to shapefile conversion
Number of table rows converted to shapefile features
5.Check that the output message is :
(Creative Commons by-nc-sa, 3.0)
25
GIS (A) -2006/2007
6.Convert your tableNR to a new shapefile:
pgsql2shp -h [hostName] -u [PostgreSQLUserName] -P
[password] -f C:\Temp\tableNR gis tableNR
4.7 –PostgreSQL table to shapefile conversion
7.Open target folder and check that the new shapefile has been created
correctly (.shp, .shx, and .dbffiles)
8.Open it in a desktop GIS (e.g. ArcGIS) and verify its structure
(Creative Commons by-nc-sa, 3.0)
26
GIS (A) -2006/2007
It’s also possible to convert a shapefile to a PostgreSQL/PostGIS table.
•Open an MS-DOS command prompt
•Enter the folder of your local PostgreSQL installation (refer to it as
$PostgreSQL), by cdcommand
•Enter 8.2\bin\folder
•Run the shp2pgsqlcommand, with the following syntax:
shp2pgsql -c -s [SRID] -g [geometryColumnName] -I
[path\ShapefileName.shp] [newTableName] >
[path\outputFilename.sql]
-cmeans that the table is created and populated (other options are: -a: append
shapefile into a current table; -d: drop the table and recreate it with shapefile data; ù
-p: only create the table, without populating it.
-Icreates a GiST index for the new table
4.8 –Shapefile to PostgreSQL table conversion
5.Check that the output message is :
(Creative Commons by-nc-sa, 3.0)
27
GIS (A) -2006/2007
6.Open Free SQL Box
7.Load your .sqlfile (A)
8.Run the SQL statement (B)
9.Check that the table has been created properly
4.8 –Shapefile to PostgreSQL table conversion
A
B
(Creative Commons by-nc-sa, 3.0)
28
GIS (A) -2006/2007
PostgreSQL documentation: http://www.postgresql.org/docs/
PostGIS documentation: http://postgis.refractions.net/documentation/
gdAdmin III documentation: http://www.pgadmin.org/docs/
Loading PostgreSQL/PostGIS tables in MapServer:
http://mapserver.gis.umn.edu/docs/reference/mapfile/layer
http://postgis.refractions.net/docs/ch04.html#id2879503
Atzeni P., Ceri S., Peraboschi S., Torlone R. (1999). “Basi di dati”-McGraw-Hill
Documentation and References
(Creative Commons by-nc-sa, 3.0)
29
GIS (A) -2006/2007
Last update: 24/10/2007
(Creative Commons by-nc-sa, 3.0)
License
This document is released under the following license:
Creative Commons , Attribution –Noncommercial -Share Alike , 3.0
Creative Commons , Attribuzione -Non commerciale -Condividi allo stesso modo , 3.0
More information
Use conditions
http://creativecommons.org/licenses/by-nc-sa/3.0
http://creativecommons.org/licenses/by-nc-sa/3.0/deed.it
Legal Code (the full license)
http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode