PostGIS Extension to PostgreSQL for GeoServer Hosting

PostGIS extends PostgreSQL with support for geographic objects and spatial SQL queries. Combined with GeoServer, PostgreSQL can work as a backend spatial database for publishing, sharing, and managing geospatial datasets.

In this article

Create the environment
Deploy GeoServer
Enable PostGIS
Import a dataset
Connect GeoServer to PostgreSQL
Publish and preview the dataset

Create the Environment

Create an environment that contains both a Java application server and a PostgreSQL database. The source tutorial uses Tomcat 7 and PostgreSQL 9, with an example environment name of geoserver.

Create PostGIS environment — Create an environment with Tomcat and PostgreSQL.

GeoServer environment — The created environment appears in the dashboard.

GeoServer Deployment

Download GeoServer

Download the latest stable GeoServer release as a web archive from the official GeoServer website.

Extract the downloaded archive.

Extracted GeoServer archive — Extract the downloaded GeoServer archive.

Upload geoserver.war

Return to the platform dashboard and upload the geoserver.war file from the extracted archive.

Deploy GeoServer

Click Deploy to… beside the WAR file and select the environment created earlier.

GeoServer deployment — GeoServer is deployed to the application server.

PostGIS Configuration

Open phpPgAdmin for the PostgreSQL node, sign in with the PostgreSQL credentials received after environment creation, and create a new database.

Open PostgreSQL admin panel — Open the PostgreSQL administration interface from the dashboard.

Create PostgreSQL database — Create a new PostgreSQL database for PostGIS.

The source example creates a database named nyc with UTF8 encoding.

New PostgreSQL database creation — Create the nyc database with UTF8 encoding.

Select the new database and open its SQL tab.

SQL tab for nyc database — Open the SQL tab for the new PostgreSQL database.

Run the following SQL statements:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

postgisEnables PostGIS, including raster support.

postgis_topologyEnables topology support.

fuzzystrmatchProvides fuzzy matching required by Tiger.

postgis_tiger_geocoderEnables the US Tiger Geocoder.

Paginate results

The source tutorial instructs you to tick Paginate results below the SQL form before executing the statements to avoid errors.

Execute PostGIS extension queries — Run the SQL statements that enable PostGIS and related extensions.

Dataset Import

The source example imports the nyc_buildings dataset. Download and extract nyc_buildings.zip to obtain nyc_buildings.sql.

Open the SQL tab for the PostGIS-enabled nyc database, browse to the extracted nyc_buildings.sql file on the local computer, upload it as an SQL script, tick Paginate results, and execute the script.

Upload SQL dataset — Upload and execute the nyc_buildings SQL dataset.

Connect GeoServer with PostgreSQL

Open the GeoServer application from the platform dashboard. The source tutorial uses the default GeoServer credentials admin / geoserver.

Log in to GeoServer using the default credentials from the source guide.

Go to Data > Stores, click Add new Store, and select PostGIS – PostGIS Database.

Add new GeoServer store — Open Data > Stores and add a new store.

Select PostGIS data source — Choose PostGIS – PostGIS Database as the new data source.

Configure the new vector data source.

WorkspaceSelect the required workspace.

Data Source NameEnter a name such as nyc_buildings.

DescriptionAdd a brief description if required.

hostUse the PostgreSQL database host shown through the node Info button.

databaseUse the PostGIS-enabled database name, nyc in the example.

user / passwdUse the PostgreSQL credentials supplied when the environment was created.

GeoServer basic store information — Enter the workspace, data source name, and description.

PostgreSQL database host — Get the PostgreSQL host information from the database node.

PostGIS connection parameters — Provide the PostgreSQL host, database, username, and password.

Leave the remaining fields at their defaults unless changes are required. Ensure Validate connections is enabled and click Save.

Validate GeoServer connection — Enable connection validation and save the PostGIS data store.

After the connection is established, the uploaded nyc_buildings resource appears in GeoServer.

Publish and Preview the Dataset

The newly saved vector data source appears in the list of new layers. Click Publish beside nyc_buildings.

Publish GeoServer layer — Publish the imported nyc_buildings layer.

In the Edit Layer page, enter a Title and Abstract.

GeoServer layer title — Add a title and abstract to the layer.

In the Bounding Boxes section, click Compute from data and Compute from native bounds.

GeoServer bounding boxes — Compute bounding boxes from the data and native bounds.

Open the Publishing tab and ensure the polygon layer style is selected as the default style, then save the layer.

GeoServer layer style — Confirm the polygon layer style in the Publishing tab.

Go to Data > Layer Preview. Locate the published layer and open it with OpenLayers. Other preview or download formats can also be selected from the available list.

GeoServer layer preview — Open the Layer Preview page.

Launch the OpenLayers preview for the published layer.

The source example displays an interactive map of New York City buildings. The preview supports zooming, panning, and viewing feature attributes.

New York City buildings map — Preview the imported New York City buildings dataset as an interactive map.

Expected Result

PostgreSQL is extended with PostGIS, the geospatial SQL dataset is imported successfully, GeoServer connects to the PostGIS database through a configured data store, and the spatial layer can be published and previewed through OpenLayers.

Important Notes

The source tutorial uses Tomcat 7 and PostgreSQL 9; these are source-specific example versions.
The example PostGIS database is named nyc and uses UTF8 encoding.
The documented PostGIS setup enables postgis, postgis_topology, fuzzystrmatch, and postgis_tiger_geocoder.
The source instructs enabling Paginate results in phpPgAdmin when running the PostGIS statements and importing the SQL dataset.
The example dataset is nyc_buildings.sql.
The source tutorial uses default GeoServer credentials admin / geoserver.
Use the PostgreSQL node host, database name, and PostgreSQL credentials when configuring the GeoServer PostGIS data source.