PostGIS¶

PostGIS is an open source spatial database based on PostgreSQL, and is currently one of the most popular open source spatial databases today.

Adding a PostGIS database¶

As with all formats, adding a shapefile to GeoServer involves adding a new store to the existing Stores through the Web Administration Interface.

Using default connection¶

To begin, navigate to Stores ‣ Add a new store ‣ PostGIS NG.

Adding a PostGIS database

Option	Description
Workspace	Name of the workspace to contain the database. This will also be the prefix of any layer names created from tables in the database.
Data Source Name	Name of the database. This can be different from the name as known to PostgreSQL/PostGIS.
Description	Description of the database/store.
Enabled	Enables the store. If disabled, no data in the database will be served.
dbtype	Type of database. Leave this value as the default.
host	Host name where the database exists.
port	Port number to connect to the above host.
database	Name of the database as known on the host.
schema	Schema in the above database.
user	User name to connect to the database.
passwd	Password associated with the above user.
namespace	Namespace to be associated with the database. This field is altered by changing the workspace name.
max connections	Maximum amount of open connections to the database.
min connections	Minimum number of pooled connections.
fetch size	Number of records read with each interaction with the database.
Connection timeout	Time (in seconds) the connection pool will wait before timing out.
validate connections	Checks the connection is alive before using it.
Loose bbox	Performs only the primary filter on the bounding box. See the section on Using loose bounding box for details.
preparedStatements	Enables prepared statements.

When finished, click Save.

Using JNDI¶

GeoServer can also connect to a PostGIS database using JNDI (Java Naming and Directory Interface).

To begin, navigate to Stores ‣ Add a new store ‣ PostGIS NG (JNDI).

Adding a PostGIS database (using JNDI)

Option	Description
Workspace	Name of the workspace to contain the store. This will also be the prefix of all of the layer names created from the store.
Data Source Name	Name of the database. This can be different from the name as known to PostgreSQL/PostGIS.
Description	Description of the database/store.
Enabled	Enables the store. If disabled, no data in the database will be served.
dbtype	Type of database. Leave this value as the default.
jndiReferenceName	JNDI path to the database.
schema	Schema for the above database.
namespace	Namespace to be associated with the database. This field is altered by changing the workspace name.

When finished, click Save.

Configuring PostGIS layers¶

When properly loaded, all tables in the database will be visible to GeoServer, but they will need to be individually configured before being served by GeoServer. See the section on Layers for how to add and edit new layers.

Using loose bounding box¶

When the option loose bbox is enabled, only the bounding box of a geometry is used. This can result in a significant performance gain, but at the expense of total accuracy; some geometries may be considered inside of a bounding box when they are technically not.

If primarily connecting to this data via WMS, this flag can be set safely since a loss of some accuracy is usually acceptable. However, if using WFS and especially if making use of BBOX filtering capabilities, this flag should not be set.

Publishing a PostGIS view¶

Publishing a view follows the same process as publishing a table. The only additional step is to manually ensure that the view has an entry in the geometry_columns table.

For example consider a table with the schema:

my_table( id int PRIMARY KEY, name VARCHAR, the_geom GEOMETRY )

Consider also the following view:

CREATE VIEW my_view as SELECT id, the_geom FROM my_table;

Before this view can be served by GeoServer, the following step is necessary to manually create the geometry_columns entry:

INSERT INTO geometry_columns VALUES ('','public','my_view','my_geom', 2, 4326, 'POINT' );

Performance considerations¶

GEOS¶

GEOS (Geometry Engine, Open Source) is an optional component of a PostGIS installation. It is recommended that GEOS be installed with any PostGIS instance used by GeoServer, as this allows GeoServer to make use of its functionality when doing spatial operations. When GEOS is not available, these operations are performed internally which can result in degraded performance.

Spatial indexing¶

It is strongly recommended to create a spatial index on tables with a spatial component (i.e. containing a geometry column). Any table of which does not have a spatial index will likely respond slowly to queries.

Common problems¶

Primary keys¶

In order to enable transactional extensions on a table (for transactional WFS), the table must have a primary key. A table without a primary key is considered read-only to GeoServer.

GeoServer has an option to expose primary key values (to make filters easier). Please keep in mind that these values are only exposed for your convenience - any attempted to modify these values using WFS-T update will be silently ignored. This restriction is in place as the primary key value is used to define the FeatureId. If you must change the FeatureId you can use WFS-T delete and add in a single Transaction request to define a replacement feature.

Multi-line¶

To insert multi-line text (for use with labeling) remember to use escaped text:

INSERT INTO place VALUES (ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), E'Westfield\nTower');