Tutorial: How to Connect GeoServer to PostGIS Spatial Datasets

This beginner GeoServer tutorial explains how to connect to PostGIS spatial datasets by setting up GeoServer stores and layers.  To complete this tutorial you should either have a PostgreSQL with PostGIS database server installed locally, or have the necessary connection information for a remotely hosted database server.  This tutorial also requires that you have pre-existing spatial datasets contained within your PostGIS database.

If you do not have spatial data already in your PostGIS database, sample data can be downloaded here, to add the data to your database please complete our tutorial on ‘Importing Shapefile GIS Data into PostgreSQL’ using 4326 as the SRID. 

This tutorial was designed around locally installed PostgreSQL/PostGIS database servers, but for those individuals loading from remotely hosted servers should replace ‘localhost’ with the necessary host information.  To start off we will need to login to the GeoServer Web Admin Interface which is located athttp://localhost:8080/geoserver/web.  Enter the proper username and password to access the internal administrator menu options.

Setting up a GeoServer PostGIS Store

On the left hand side under the Data menu, click on the Stores link.  This should bring up a list of all the existing stores which are providing data to your GeoServer.

Click on Add new Store, and then select PostGIS – PostGIS Database.  For this tutorial we will be adding our store to the existing Workspace called cite, select cite in the Workspace* drop-down menu.  For Data Source Name* enter ‘PostGIS_Dataset’.  Optionally you can enter a description of the Store, such as ‘PostGIS spatial datasets’.  Leave the checkbox next to Enabled.

Now continuing on to the Connection Parameters section.  Leave dbtype* set to ‘postgis’, host* ‘localhost’ and port* ‘5432’.  Note: if your server is configured to run on a different port then update accordingly.  Next, enter the name of your PostGIS enabled database.  You can leave schema set to ‘public’ unless your server is configured differently.  For username enter ‘postgres’ and provide the postgres user password in the passwd field.  The remainder of the settings can be left as default.  Scroll down to the bottom and click Save if all settings look appropriate.

Setting up a GeoServer PostGIS Layer

After clicking the save button on your new GeoServer Store, it will take you to the New Layer chooser menu which should display a list of your PostGIS spatial datasets.  This list is composed of tables which contain the geometry data type that also have a reference entry within the PostGIS system table named ‘geometry_columns’.  If for some reason your spatial table does not contain a reference in the geometry_columns table, you will have to manually add it before it will become available for publishing under GeoServer.

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'public', 'tablename', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM public.tablename LIMIT 1;

Note: replace ‘tablename’ and ‘the_geom’ with the correct name of your existing spatial table and spatial data column name.

Scroll through the list of Layer names and when you’ve found the Layer you wish to setup, click the Publish button.

The most important information for GeoServer Layer setup is store in the Coordinate Reference Systems section.  If your layer has a blank value under Native SRS, you will have to define your projection manually by clicking the Find button next to Declared SRS.  For global data using the WGS 84 projection with code 4326 is recommended.  For more localized datasets you must browse through the list of supported projections to find the one that is correct for your dataset.  Once you have found the correct projection in the list, add it to the Declared SRS by clicking on the code number.  You should now see a value such as EPSG:4326.

Now that the Coordinate Reference System has been defined we need to define the bounds or spatial extent of your dataset.  Scroll down to the Bounding Boxes section and click the Compute from data button underneath Native Bounding Box.  Then scroll down further and click the Compute from native bounds button found underneath Lat/Lon Bounding Box.  If you set your Declared SRS to EPSG:4326 then these values will appear to be the same because the WGS 84 projection is notated using Latitude and Longitude coordinates.  Scroll down and click the Save button to save your PostGIS GeoServer Layer settings.  This should take you back to the list of all Layers published by your GeoServer, you should see your new PostGIS layer as follows:

Workspace	Store	Layer Name	Enabled?	Native SRS
cite	PostGIS_Dataset	layer	checked	EPSG: 4326

To confirm that your new PostGIS Layer is working properly, click the Layer Preview on the left hand side of the GeoServer Web Admin Interface Menu.  Scroll through your list of publish layers to find your new PostGIS Layer, and preview it using OpenLayers.  Pan and zoom into North America, if everything was a success it should look like the image below.

Your new PostGIS Layer is now published and ready for use within future web mapping applications such as OpenLayers.  Users should now have a greater understanding of how to setup a GeoServer PostGIS Store as well as how to publish their own spatial datasets stored within a PostgreSQL/PostGIS database.

REACH Database/GIS Intern, South Sudan

Closing Date: Thursday, 31 January 2013

Department: GIS
Position: REACH Database / GIS Intern Contract duration: 6 months Location: South Sudan Starting Date January 2013

I. Background on ACTED

ACTED is an independent international, private, non-partisan and non-profit organization that operates according to principles of strict neutrality, political and religious impartiality, and non discrimination.

ACTED was created in 1993 to support populations affected by the conflict in Afghanistan. Based in Paris, France, ACTED now operates in 30 countries worldwide, with over 200 international and 4000 national staff. ACTED has a 100 million � budget for over 340 projects spanning 8 sectors of intervention; including emergency relief, food security, health promotion, economic development, education & training, microfinance, local governance & institutional support, and cultural promotion. For more information, please visit our website at www.acted.org

II. Program Information

Capital Office : Juba National Staff : 125 (average May 2011) Areas : 4 areas On-going programmes : 8 Budget : 5 M

Supporting the new state of South Sudan through basic agricultural, water and sanitation services - ACTED is committed to meeting the emerging needs and challenges of the new state of South Sudan through the provision of basic services. ACTED South Sudan focuses on agricultural support, provision of water and sanitation services and support for basic infrastructure to communities in transition, particularly those who have returned from Sudan. Community-based approaches, including Community-Led Total Sanitation and Cash for Work, are emphasized in order to support the most vulnerable households and promote local sustainability. ACTED is also working is working to react to new areas of need such as the refugee crisis's in Upper Nile and Unity States, and the inter-tribal conflicts in Jonglei. ACTED is currently supporting refugee populations in Jamam camp as well as host populations, following massive displacement of populations from Sudan: camp coordination, delivery of basic goods and services, access to potable water, etc.

III. Position Profile

ACTED is aiming at strengthening information management in South Sudan's Upper Nile and Unity State refugee camps. Through this effort, ACTED, through its REACH initiative, will conduct assessments and create databases, maps and systems of information dissemination to relevant aid actors. The goal of the effort is to build a dynamic information management and sharing infrastructure that improves the planning, coordination and traceability of aid provision and services in each camp. This will in turn significantly improve the effectiveness of aid provided to refugees.

The Database and GIS Intern will contribute to the design and regular use of GIS and database materials used for the evaluation. The position will be based in Maban, South Sudan with frequent travel to field locations in which the evaluation is taking place.

1. Database Design & Management
   • Database Design:
   • Contribution to designing databases related to the evaluation, in close coordination with REACH country manager and IMPACT's Geneva team
   • Database Management:
   • Ensure all evaluation data is regularly entered into the databases
   • Ensure accessibility of data to key partners as well as security and confidentiality of data
2. GIS & Mapping
   • Produce maps:
   • Follow REACH mapping guidelines
   • Produce maps based on data collected during evaluation, as requested
3. Secondary Data
   • Secondary Data Collection:
   • Collect and synthesize secondary data for use in evaluation design and analysis
   • Manage external indicator data collection throughout evaluation timeline

IV. Qualifications:

Master degree or equivalent experience in Information Systems, GIS, or a relevant discipline Good experience in database and general computer software (such as SQL Server, PostgreSQL and Access) Experience with industry standard mapping tools, especially ESRI products Strong knowledge of applicable data management and database computer software (JavaScript APIs, Flex, Microsoft Excel/Access, SQL server) Ability to work independently Strong English communication skills; Arabic a benefit

V. Conditions:

Field Intern benefits include: - 300 USD per month living allowance - Coverage of all accommodation, guesthouse, food, and travel costs, a luggage allowance of 50 kg - The provision of medical, repatriation, and life insurance.

How to apply:

VI. Submission of applications:

Please send, in English, your cover letter, CV, and three references to stages@acted.org
Ref : REACHI/SSUD/SA

ACTED Att: Human Resources Department 33, rue Godot de Mauroy 75009 Paris FRANCE

Fax. + 33 (0) 1 42 65 33 46

For more information, visit us at http://www.acted.org

Working with your PostGIS Layers using Quantum GIS (QGIS)

This tutorial will explain to the user how to connect QGIS to your PostgreSQL/PostGIS database and spatially view any of your tables containing a geometry column. PostGIS is a powerful database for GIS functions and data storage so it is nice to have a program to visually see the GIS data stored within the database.  QGIS is an easy to use open source GIS application and is ideal for data viewing, editing, and making maps. Si vous trouvez ce document utile et interessant merci de faire un don en cliquant sur un lien publicitaire dans le cadre  You are required to have a PostgreSQL database with PostGIS installed.   To install PostGIS you can visit the following website  This database will need a GIS table (contains a geometry type column). Follow the next few steps to import the shapefile to PostgreSQL using QGIS. Importing Shapefile Data to PostGIS using QGIS Open Quantum GIS and click the “Import Shapefiles to PostgreSQL"  button. This will open SPIT (Shapefile to PostGIS Import Tool). Start by creating a connection to your PostgreSQL database.  Under “PostgreSQL Connections” click the “New” button.  Add a name for your connection, and then enter the parameters for your database connection (Host, Database, Port, Username, and Password).  Click the “Test Connection” button and you should see a message saying “Connection to databasename was successful”.  Once this is successful, click OK. Select your server from the dropdown list and click “Connect”, a list of your schemas will appear in the Global schema dropdown menu.  Choose the schema where you would like to import the data. Now click the “Add” button and add your shapefile. Ensure the information is correct concerning the shapefile and click “OK”. Your shapefile is now added to your database. Next, we will view the newly added PostGIS data. Viewing PostGIS Layers using QGIS Click the “Add PostGIS Layer” button . This will open up a window. Under “PostgreSQL Connections” select your server name from the dropdown list and click “Connect”.  You will see a list of your PostgreSQL schemas. Open the schema containing your GIS data to see a list of all available layers.  Select the n_america layer click “Add”. The GIS data for North America will appear in the data viewing area. Use the “Zoom In” tool  to draw a box closely around the US, Canada, and Mexico. To view the attribute table for this layer, right-click the layer and choose “Open Attribute Table” or click the icon  when the layer is selected. To edit your data, turn on the editor by clicking the “Toggle Editing” icon .  Save your edits using the “Save Edits” button . A simple way to identify features or query the attributes of a location is to use the “Identify Features” tool . Click a location on the map to view its attributes. Classifying your Data Right-click the n_america layer and choose “Properties”. Under Symbology -> Legend Type, choose “Unique Value”. Under “Classification field”, choose “country” and click Classify. Each unique value in the country column will be given a random color. Click “OK”. Now let’s add some labels.  Select the n_america layer and click the “Labeling” button . Put a check next to “Label this layer”. Under “Field with labels”, choose “country” and click “OK”. This concludes our tutorial on working with PostGIS data with QGIS. The user should now know how to import shapefile data into a PostGIS database, load that layer in QGIS, view/edit the attributes, and symbolize the map according to its values.

If you find this tutorial useful please make a donation by clicking one of the ads (Adsense) thanks 

What Is PostGIS?

PostGIS is an open source, freely available, and fairly OGC compliant spatial database extender for the PostgreSQL Database Management System. In a nutshell it adds spatial functions such as distance, area, union, intersection, and specialty geometry data types to the database. PostGIS is very similar in functionality to SQL Server 2008 Spatial support, ESRI ArcSDE, Oracle Spatial, and DB2 spatial extender. The latest release version now comes packaged with the PostgreSQL DBMS installs as an optional add-on. As of this writing PostGIS 2.0.0 is the latest stable release.