using mapserver with postgresql / postgis
Post on 16-Jan-2017
260 Views
Preview:
TRANSCRIPT
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 Column to 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 columnGeometry 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 command line
Link : http://www.pgadmin.org/
Release for exercises : 1.6.2 for WindowsLicense : http://www.pgadmin.org/licence.php
(Cre
ativ
e C
omm
ons b
y-nc
-sa
, 3.0
)
8D.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 serverFile Add server
4.1 - Connect a remote PostgreSQL server
A - Service name: GIS course
AB
B - Host name: 192.168.157.30C – Manager DB: gisD - User name: ugisNRE - Password: ugisNR
CDE
3. Click OK
8
NR is 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
Tablesgeometry_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
NR is yourPSF number
(Cre
ativ
e C
omm
ons b
y-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 = nameType = character varyingLength = 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 id column as primary key
5) Click Aggiungi and 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 OKCorrespondent 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 INDEX mygistNR 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 table2. 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 table2. Click on A to display all rows
or B to 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 automatically6. Press Enter to confirm
Open Free SQL Box and writeINSERT 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
Map file
BROWSER
2
1
3
5
PostgreSQLData
Spatially DBMS enabler
3.a4.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 file2. 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 PostGISConnection parameters
If PostgreSQL and MapServer share the same host,it’s possible to write host=127.0.0.1 or 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 tableNR and 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 cd command• Enter 8.2\bin\ folder• Run the pgsql2shp command, 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 .dbf files)
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 cd command• Enter 8.2\bin\ folder• Run the shp2pgsql command, with the following syntax:
shp2pgsql -c -s [SRID] -g [geometryColumnName] -I
[path\ShapefileName.shp] [newTableName] >
[path\outputFilename.sql]
-c means 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.-I creates a GiST index for the new table
4.8 – Shapefile to PostgreSQL table conversion
5. Check that the output message is :
(Cre
ativ
e C
omm
ons b
y-nc
-sa
, 3.0
)
27
GIS (A) - 2006/2007
6. Open Free SQL Box
7. Load your .sql file (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/layerhttp://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.0Creative Commons , Attribuzione - Non commerciale - Condividi allo stesso modo , 3.0
More information
Use conditionshttp://creativecommons.org/licenses/by-nc-sa/3.0http://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
top related