Chapter 7. Connect to the PostgreSQL/PostGIS databases 1. 7.1 ...

shrubberystatuesqueData Management

Dec 1, 2012 (4 years and 7 months ago)

202 views


Created by
XMLmind XSL
-
FO Converter
.

Spatial Databases by Open Standards
and Software 7.

Connect to the PostgreSQL/PostGIS
databases

Gábor Nagy







Created by
XMLmind XSL
-
FO Converter
.

Spatial Databases by Open Standards and Software 7.: Connect
to the PostgreSQL/PostGIS databases

Gábor Nagy

Lector: Zoltán Siki

This module was
created within TÁMOP
-

4.1.2
-
08/1/A
-
2009
-
0027 "Tananyagfejlesztéssel a GEO
-
ért"
("Educational material development for GEO") project. The project was funded by the European Union and the
Hungarian Government to the amount of HUF 44,706,488.

v 1.0

Publicati
on date 2010

Copyright © 2010 University of West Hungary Faculty of Geoinformatics

Abstract

Connect to an PostGIS/PostgreSQL database from different applications

The right to this intellectual property is protected by the 1999/LXXVI copyright law. Any unau
thorized use of this material is prohibited. No
part of this product may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photoc
opying,
recording, or by any information storage and retrieval system without expre
ss written permission from the author/publisher.





iii


Created by
XMLmind
XSL
-
FO Converter
.

Table of Contents

7. Connect to the
PostgreSQL/PostGIS databases


................................
................................
..............


1

1. 7.1 Introduction


................................
................................
................................
.....................


1

2. 7.2 Connecting from desktop GIS applications


................................
................................
.....


1

2.1. 7.2.1 QGIS


................................
................................
................................
................


1

2.2. 7.2.2 uDIG


................................
................................
................................
................


3

3. 7.3 Connecting from web applications


................................
................................
..................


7

3.1. 7.3.1 MapServer


................................
................................
................................
.......


7

3.2. 7.3.2

GeoServer


................................
................................
................................
........


8

4. 7.4 Connecting from simple script programs


................................
................................
........


8





1


Created by
XMLmind XSL
-
FO Converter
.

Chapter

7.

Connect to the
PostgreSQL/PostGIS databases

1.

7.1 Introduction

In this module we learn how to connect to an PostgreSQL/PostGIS database, and access the stored geospatial
data.

The connecting applications may be deskto
p GIS software, web based maps, WMF and WFS servers, or simple
custom programs. This module introduces some usable solutions for these connections.

Every connection, independent of the connected program, needs some data: the location of the server (IP
addr
ess or host name, and the port if it is different from 5432, the default value), the name of the database (a
database server may have more databases) and the name of the user. Optionally the connection requires the type
of the connection (we can use SSL fo
r the safe data transfer) or authentication data (for example the password of
the user).

Most PostGIS clients use the
geometry_columns

table. The clients use this table to discover the available
geospatial data in the database. This table is modified when
we use
AddGeometryColumn()

and
DropGeometryColumn()

functions to create or remove a geometry type column. We should use these functions,
and don't forget to grant at least SELECT privilege to the client’s user on the
geometry_column

and the
spatial_ref_sys

tables.

2.

7.2 Connecting from desktop GIS applications

2.1.

7.2.1 QGIS

Quantum GIS (QGIS) is an Open Source Geographic Information System (GIS) licensed under the GNU
General Public License. QGIS is an official project of the Open Source Geospatial Found
ation (OSGeo). It runs
on Linux, Unix, Mac OSX, and Windows and supports numerous vector, raster, and database formats and
functionalities.

(http://qgis.org/)

We can define more named connections to store the required connection data: the location and the
port number
of the server, the name of the database and the name of the user.


Connect to the PostgreSQL/PostGIS
databases



2


Created by
XMLmind XSL
-
FO Converter
.


Figure 1. Create new PostGIS connection in QGIS

We could choose a geospatial table from the connected database. The chosen tables can be added to the QSIG
project as vector
layers.


Connect to the PostgreSQL/PostGIS
databases



3


Created by
XMLmind XSL
-
FO Converter
.


Figure 2. Add a PostGIS table to QGIS as layer

If a table has more geometry type columns (and these columns are registered in the geometry_columns table),
then the list contains more lines for this table.

The PostGIS layers are vector layers in t
he QGIS. The QGIS users can edit these layers. The modification will
be sent to the database, when the user turns off editing.

2.2.

7.2.2 uDIG

uDig is an open source (LGPL) desktop GIS application framework, built with Eclipse Rich Client (RCP)
technology.

(http://udig.refractions.net/)


Connect to the PostgreSQL/PostGIS
databases



4


Created by
XMLmind XSL
-
FO Converter
.


Figure 3. Add PostGIS data to the uDIG, Step 1

In the first step PostGIS is chosen from the data sources. The uDIG supports several other types of data sources.


Connect to the PostgreSQL/PostGIS
databases



5


Created by
XMLmind XSL
-
FO Converter
.


Figure 4. Add PostGIS data to the uDIG, Step 2

In the secon
d step, the connection data is given to the database server. The location and the port of the database
server and the name of the database user are needed.


Connect to the PostgreSQL/PostGIS
databases



6


Created by
XMLmind XSL
-
FO Converter
.


Figure 5. Add PostGIS data to the uDIG, Step 3

In the third step we set the name of the database,
and choose the spatial tables from the list.


Connect to the PostgreSQL/PostGIS
databases



7


Created by
XMLmind XSL
-
FO Converter
.


Figure 6. Add PostGIS data to the uDIG, Step 4

In the last step click the “Finish” button, and the uDIG opens the selected spatial data sources.

3.

7.3 Connecting from web applications

3.1.

7.3.1 MapServer

MapServer is an Open Source platform for publishing spatial data and interactive mapping applications to the
web. Originally developed in the mid
-
1990’s at the University of Minnesota, MapServer is released under an
MIT
-
style license, and runs on all major

platforms (Windows, Linux, Mac OS X).

(http://mapserver.org/)

MapServer uses text configuration files (called mapfiles) to describe the distributed maps. This mapfile contains
the properties of the map, the layers with the display settings and the data so
urces. The vector layers of the
MapServer may be an PostGIS table.

For example a mapfile, which has a PostGIS based layer:


Connect to the PostgreSQL/PostGIS
databases



8


Created by
XMLmind XSL
-
FO Converter
.

MAP


NAME "counties"


STATUS ON


SIZE 600 400


SYMBOLSET "../etc/symbols.txt"


EXTENT 400 0 1000 400


UNITS meters


IMAGECOLOR

255 255 255


FONTSET "../etc/fonts.txt"


WEB


IMAGEPATH "/ms4w/tmp/ms_tmp/"


IMAGEURL "/ms_tmp/"


END


LAYER


NAME "counties"


STATUS ON


TYPE POLYGON


CONNECTIONTYPE POSTGIS


CONNECTION "host=127.0.0.1 port=5432 dbname=gisdata us
er=gisdata_client"


DATA "geom from county"


CLASS


NAME 'Counties'


STYLE


OUTLINECOLOR 0 0 0


END


END


END

END

3.2.

7.3.2 GeoServer

GeoServer is an open source software server written in Java that allows users to

share and edit geospatial data.
Designed for interoperability, it publishes data from any major spatial data source using open standards.

GeoServer is the reference implementation of the Open Geospatial Consortium (OGC) Web Feature Service
(WFS) and Web C
overage Service (WCS) standards, as well as a high performance certified compliant Web
Map Service (WMS). GeoServer forms a core component of the Geospatial Web.

(http://geoserver.org/)

The GeoServer has a complex web based administration interface. We can

specify the PostGIS based layers of
the maps, and PostGIS based WFS services through this user interface.

4.

7.4 Connecting from simple script programs

Here is a simple Ruby script, which connects to a PostgreSQL/PostGIS database, and creates point object

in the
points table from a text file:

require 'postgres'


dbconn=PGconn.connect('localhost', 5432, '', '', 'gisdata', 'gisdata_client')


File.open('survey.kor').each do |coordfilerow|


fields=coordfilerow.chomp.split


pnum=fields[0]


geomWKT="POI
NT(#{fields[1]} #{fields[2]} #{fields[3]})"


if fields.size==4 then


dbconn.exec("INSERT INTO points (pn, geom) VALUES


('#{pnum}', GeomFromEWKT('SRID=23700;#{geomWKT}'));")


end

end


dbconn.close

Another Ruby script, whic
h searches for a point by point number in the table, which was filled by the previous
script:


Connect to the PostgreSQL/PostGIS
databases



9


Created by
XMLmind XSL
-
FO Converter
.

require 'postgres'


dbconn=PGconn.connect('localhost', 5432, '', '', 'gisdata', 'gisdata_client')


print "The number of the point:"

pnum=gets.chomp


dbconn.exec("
SELECT ST_X(geom), ST_Y(geom), ST_Z(geom)


FROM points WHERE pn='#{pnum}';").each do |point|


puts "Y=#{point[0]} X=#{point[1]} Z=#{point[2]}"

end


dbconn.close


Bibliography

PostgreSQL Global Development Group: 1996
-
2010.

Refractions Resear
ch Inc.:
PostGIS 1.5.2 manual,

2010.