Page History

This page gives an overview of the exercise of loading a file that is vectors based in a PostgreSQL/PostGIS database on November 1th, 2013 by Gerrit Hendriksen and Frank Keppel. The exercise also describes the dissemination of vector data through the internet as WMS/WFS by geoserver.

Data

Every kind of data format that is not raster based (see for this working with netCDF's of the same workshop) is suitable. For this moment we focus on adding data to the PostGIS database using the GDAL utility OGR2OGR as well as psql (executable that comes with PostgreSQL (can be found in the bin directory of the installation directory, if the path has not been set for some reason you can navigate to C:\Program Files\PostgreSQL\9.2\bin).
As an example a dataset from pangea.de is used, for example Geographical, temperature and chemical features of sites on Bear Island and Spitsbergen. A very small data set.

Assumptions

The examples assume the exercise is done with a PostgreSQL/PostGIS database that is installed locally. This is done for savety issues. So the PostgreSQL/PostGIS database is installed on localhost.
host = localhost
port = 5432
username = yourusername (during installation you have entered a username)
pwd = yourpassword (during installation you have entered a password)

Furthermore the assumption is that you have a csv file with a header of exactly 1 header.

Software

In this tutorial the following software is used:
- PostgreSQL/PostGIS (locally installed, including psql.exe)
- OGR2OGR
- pgAdminIII
- QuantumGIS
- geoserver (locally installed)

Exercise

To be done:
- convert the data into a csv file with exactly 1 header
- load the csv into a table in the postGIS database
- add geometry column
- view the data in QuantumGIS
- create a geoserver WFS from the data
- get rid of commas where they should not be (within text strings like in "Area" column in the example below)
- also get rid of special characters, like ö, ë etc.)

Eventually the csv should look like this, so you should alter this file using excel for instance.

Code Block

Latitude,Longitude,Area,Depth_water_m,Temp_C_from,Temp_C_to,pH,Elect_cond_uS_cm,Cl-_meq_l,AT_mmoleq_l
74.51,18.96,"G: Temporary pond south of Kapp Posadowsky Bear Island",1,4,9.5,5.4,93,0.86,0.9
74.51,19.08,"K: Pond near Lundenaeringen Bear Island",1,5,10.5,6,133,0.76,1.24
74.47,19.25,"M: Mosavatnet northern bay Bear Island",1,4,8,5.8,214,1.81,0.84
74.45,19.04,"a: Southern pond of Thetingtjornene Bear Island",1,3,6.5,5.2,120,0.54,1.44
74.46,19.03,"b: South-eastern pond of Sveltiheltjornene Bear Island",1,4.5,,5.4,69,0.63,0.52
78.95,11.88,"Br. p.: Lagoon on Brandalspynten NW-Spitsbergen",1,8,8.5,5.8,680,6.85,1.54
79.47,13.28,"Jot.W.: Jotunwells Bockfjord NW-Spitsbergen",1,11.5,14.5,7,1736,12.28,10.43

With OGR2OGR (part of the GDAL library which is installed with QuantumGIS) you can load the csv via

Code Block
C:\QuantumGIS\OSGeo4W\bin\ogr2ogr.exe \-f "PostgreSQL" PG:"host=localhost user=<username> dbname=<dbname> password=<pwd>" <filename.csv> \-nln "<tablename>"

Note: all notations between <> have to be changed in your names (including the <>). The advantage of ogr2ogr (which can also installed by installing GDAL) it that it creates a table in an existing database. If you don't have an existing one please follow the steps for psql.exe (click here).

Right now the data is in the table <tablename>, let's say this is temperatures.

If you start pgAdmin, you should see something like the figure below.

Open a Query window by pressing the magnifier glass with SQL in it or by pressing Ctrl + E.

Code Block
alter table temperatures add column the_geom geometry

press Ctrl + E to execute the query. Delete the line above and type:

Code Block
update temperatures set the_geom = st_setsrid(st_point(longitude::double precision,latitude::double precision),4326)

press Ctrl + E to execute again. The line above converts the longitude and latitude into the geometry point (st_point) and gives it the spatial reference id (st_setsrid(makepoint statement),4326) of WGS84 (EPSG Code 4326, see http://spatialreference.org).
As you may have noticed, all columns appear as character varying in the temperatures table (see picture above). So the values have to be casted to double, this is done by ::double precision after the longitude and latitude.

Now you data is ready to be viewed in QuantumGIS and to be disseminated via geoserver. For QuantumGIS press the Add PostGIS layer button

Fill all credentials in the PostGIS connection dialog (see figure) and test the connection.
If ok then you can add the above example to QuantumGIS and you should see something like the image below.