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.