Publishing PostGIS Views as GeoServer Layers in Python

TL;DR: Publish a PostGIS view as a GeoServer layer by POSTing a featureType payload to /rest/workspaces/{ws}/datastores/{ds}/featuretypes. A real database view only needs its nativeName; a GeoServer SQL view (virtual table) needs a metadata.entry virtualTable carrying the raw SQL plus an explicit geometry element (name, type, srid). Always send recalculate=nativebbox,latlonbbox and set srs, then confirm the layer in a GetCapabilities response.

The Core Challenge: GeoServer Cannot Introspect Arbitrary SQL

Publishing an ordinary PostGIS table is nearly automatic — GeoServer reads the geometry column, its type, and its SRID straight from the PostGIS geometry_columns view and fills in the layer metadata for you. That machinery is covered end-to-end in the Layer Publishing Workflows in Python guide.

A view breaks part of that automation. There are two distinct things people call a “view”, and they behave differently:

  • A real PostGIS view created with CREATE VIEW … AS SELECT …. It is a first-class database object. PostGIS can register its geometry in geometry_columns (especially if you use a typed, materialised, or CREATE VIEW that preserves the geometry’s typmod), so GeoServer can often introspect it like a table.
  • A GeoServer SQL view, also called a virtual table. This exists only inside GeoServer’s layer configuration as a JDBC_VIRTUAL_TABLE metadata entry. Nothing is created in the database — GeoServer wraps your SELECT in a subquery at request time. Because the SQL is opaque to the driver, GeoServer has no way to discover which output column is the geometry, what geometry type it is, or which SRID it uses.

That last point is the crux: for a SQL view you must declare the geometry column and its SRID explicitly, or GeoServer publishes an attribute-only layer with no spatial dimension. The srs you set on the featureType tells clients which coordinate reference system to advertise, and the srid inside the geometry element tells GeoServer’s SQL wrapper how to bind the geometry when it queries PostGIS. Both must agree, and both must be stated by hand.

Publishing a PostGIS View: Real View vs SQL Virtual Table A decision and data-flow diagram. From a decision node the left branch is a real PostGIS database view published by nativeName; the right branch is a GeoServer SQL view carrying raw SQL plus a declared geometry element. Both branches POST a featureType payload with recalculate to the datastore feature-types endpoint, producing a layer that is confirmed through a GetCapabilities request. What kind of view? real DB object or ad-hoc SQL Real PostGIS view CREATE VIEW in the database featureType.nativeName = view name GeoServer SQL view virtualTable, no DB object raw SQL + declared geometry name / type / srid POST /…/featuretypes ?recalculate=nativebbox,latlonbbox verify via GetCapabilities

Production-Ready Code

The script below publishes either flavour of view against a datastore that already points at your PostGIS database. It targets the GeoServer REST API with JSON bodies and the recalculate query parameter. The only dependency is requests.

"""
publish_postgis_view.py — publish a PostGIS view as a GeoServer layer.

pip install requests>=2.31
"""
from __future__ import annotations

import requests
from requests.auth import HTTPBasicAuth


class GeoServerClient:
    def __init__(self, base_url: str, user: str, password: str) -> None:
        # base_url e.g. "http://localhost:8080/geoserver"
        self.rest = base_url.rstrip("/") + "/rest"
        self.session = requests.Session()
        self.session.auth = HTTPBasicAuth(user, password)
        self.session.headers.update(
            {"Content-Type": "application/json", "Accept": "application/json"}
        )

    def _featuretypes_url(self, workspace: str, datastore: str) -> str:
        return (
            f"{self.rest}/workspaces/{workspace}"
            f"/datastores/{datastore}/featuretypes"
        )

    def publish_database_view(
        self,
        workspace: str,
        datastore: str,
        view_name: str,
        srs: str,
        title: str | None = None,
    ) -> requests.Response:
        """Publish a real CREATE VIEW object. GeoServer introspects geometry."""
        payload = {
            "featureType": {
                "name": view_name,
                "nativeName": view_name,   # the actual view name in PostGIS
                "title": title or view_name,
                "srs": srs,
                "enabled": True,
            }
        }
        return self._post(workspace, datastore, payload)

    def publish_sql_view(
        self,
        workspace: str,
        datastore: str,
        layer_name: str,
        sql: str,
        geometry_name: str,
        geometry_type: str,
        srid: int,
        srs: str,
        key_columns: list[str] | None = None,
        escape_sql: bool = False,
        title: str | None = None,
    ) -> requests.Response:
        """Publish a GeoServer SQL view (virtual table) from arbitrary SQL."""
        virtual_table: dict = {
            "name": layer_name,
            "sql": sql,
            "escapeSql": escape_sql,
            "geometry": {
                "name": geometry_name,   # output column carrying the geometry
                "type": geometry_type,   # Point, LineString, Polygon, Geometry, ...
                "srid": srid,            # numeric EPSG code, must match `srs`
            },
        }
        if key_columns:
            # Stable feature IDs for WFS / transactional clients.
            virtual_table["keyColumn"] = key_columns

        payload = {
            "featureType": {
                "name": layer_name,
                "nativeName": layer_name,
                "title": title or layer_name,
                "srs": srs,
                "enabled": True,
                "metadata": {
                    "entry": {
                        "@key": "JDBC_VIRTUAL_TABLE",
                        "virtualTable": virtual_table,
                    }
                },
            }
        }
        return self._post(workspace, datastore, payload)

    def _post(
        self, workspace: str, datastore: str, payload: dict
    ) -> requests.Response:
        resp = self.session.post(
            self._featuretypes_url(workspace, datastore),
            json=payload,
            # Force GeoServer to compute both bounding boxes from the data.
            params={"recalculate": "nativebbox,latlonbbox"},
            timeout=60,
        )
        resp.raise_for_status()
        return resp


if __name__ == "__main__":
    gs = GeoServerClient("http://localhost:8080/geoserver", "admin", "geoserver")

    # 1) A real PostGIS view — GeoServer reads geometry metadata itself.
    gs.publish_database_view(
        workspace="urban",
        datastore="pg_urban",
        view_name="v_high_traffic_roads",
        srs="EPSG:4326",
        title="High Traffic Roads",
    )

    # 2) A GeoServer SQL view — geometry and SRID declared by hand.
    gs.publish_sql_view(
        workspace="urban",
        datastore="pg_urban",
        layer_name="busy_roads",
        sql=(
            "SELECT id, name, aadt, geom "
            "FROM roads WHERE aadt > 10000"
        ),
        geometry_name="geom",
        geometry_type="LineString",
        srid=4326,
        srs="EPSG:4326",
        key_columns=["id"],
        title="Busy Roads (AADT > 10k)",
    )

Step-by-Step Walkthrough

Reusing one authenticated session. GeoServerClient wraps a requests.Session with HTTPBasicAuth and JSON headers set once. Reusing the session keeps the TCP connection warm across the many REST calls a provisioning run makes — the same pattern the Automating GeoServer with the Python REST API guide builds on for workspace and datastore setup.

The feature-types endpoint. Both publish methods POST to /rest/workspaces/{ws}/datastores/{ds}/featuretypes. The {ds} datastore must already exist and point at the PostGIS database; this call publishes a layer within that store, it does not create the connection.

nativeName for a real view. In publish_database_view, nativeName is the exact name of the CREATE VIEW object in PostGIS, while name is the published layer name (they can differ). Because a real view is registered in the database, GeoServer resolves its geometry column, type, and SRID through the driver — you only supply srs as the advertised CRS.

The virtualTable for a SQL view. In publish_sql_view the metadata.entry carries @key set to JDBC_VIRTUAL_TABLE and a virtualTable object holding the raw sql. The nested geometry object is the critical part: name is the output column that holds the geometry, type is its geometry type (Point, LineString, Polygon, or the generic Geometry if mixed), and srid is the numeric EPSG code. GeoServer uses srid to bind the geometry when it wraps your SELECT; it must match the numeric part of the srs string (4326 ↔ EPSG:4326).

keyColumn and escapeSql. Passing key_columns=["id"] writes a keyColumn list onto the virtual table so GeoServer builds stable feature IDs from those columns — essential for WFS clients that reference features by ID. escapeSql stays False for trusted, hand-written SQL; set it True only when column or table identifiers might collide with SQL reserved words.

Forcing bounding-box recalculation. _post always sends params={"recalculate": "nativebbox,latlonbbox"}. This tells GeoServer to run an extent query against the view and populate both the native bounding box and the reprojected lat/lon bounding box. Skipping it leaves the boxes at their zeroed defaults, so clients cannot zoom to the layer.

Verification

Publish the layer, then request a GetCapabilities document and grep for it. WFS is convenient because it lists each feature type’s name and its WGS 84 bounding box.

curl -s "http://localhost:8080/geoserver/urban/wfs?service=WFS&version=2.0.0&request=GetCapabilities" \
  | grep -A6 "busy_roads"

Expected output shape (truncated):

<FeatureType>
  <Name>urban:busy_roads</Name>
  <Title>Busy Roads (AADT &gt; 10k)</Title>
  <DefaultCRS>urn:ogc:def:crs:EPSG::4326</DefaultCRS>
  <ows:WGS84BoundingBox>
    <ows:LowerCorner>-0.51 51.28</ows:LowerCorner>
    <ows:UpperCorner>0.33 51.69</ows:UpperCorner>
  </ows:WGS84BoundingBox>
</FeatureType>

Confirm three things: the layer Name is present under the right workspace prefix, DefaultCRS matches the srs/srid you declared, and the bounding box has real coordinates rather than -180 -90 / 180 90 or a zeroed extent. A zeroed or global box almost always means the recalculate parameter was dropped. Parsing that same document programmatically is covered in How to Parse OGC WMS GetCapabilities XML in Python if you want an automated post-publish check.

Gotchas & Edge Cases

Why must I declare the geometry column and SRID explicitly for a GeoServer SQL view?

A SQL view is an arbitrary SELECT, not a registered table, so GeoServer cannot look its geometry up in the PostGIS geometry_columns view. Without an explicit geometry element giving name, type, and srid, GeoServer treats the result as attribute-only — the layer has no spatial column, and WMS or WFS requests fail or advertise an unknown CRS. Declaring the geometry restores the metadata that automatic introspection would otherwise supply for a plain table. Note the srid is numeric (4326) while the featureType srs is the authority string (EPSG:4326); the two must describe the same CRS.

What is the difference between publishing a real PostGIS view and a GeoServer SQL view?

A real PostGIS view is a database object created with CREATE VIEW; you publish it exactly like a table by setting the featureType nativeName to the view name, and GeoServer reads its geometry metadata from PostGIS automatically. A GeoServer SQL view (virtual table) lives only inside the layer configuration as a JDBC_VIRTUAL_TABLE metadata entry holding raw SQL — nothing is created in the database, but you must declare the geometry and SRID yourself and can parameterise the SQL. Choose a real view when the query is stable and shared across tools; choose a SQL view when the query is GeoServer-specific or you want request-time parameters without touching the database schema.

Why is my layer's bounding box empty or wrong after publishing?

GeoServer only computes the native and lat/lon bounding boxes when you ask it to. POST the featureType with the query parameter recalculate=nativebbox,latlonbbox so GeoServer runs a spatial extent query against the view. If you omit it, the boxes stay at their default 0,0,0,0 or a stale value, which makes clients zoom to the wrong place and can break tile pyramids. For large views the extent query is expensive, so recalculate deliberately at publish time rather than on every configuration edit. If you must set the box without a full scan, supply a nativeBoundingBox object explicitly in the payload and recalculate only latlonbbox.

What does escapeSql do and do I need a primary key for a SQL view?

escapeSql controls whether GeoServer escapes identifiers when it wraps your SQL. Keep it false for hand-written, trusted SQL and set it true only when identifiers might collide with reserved words. A SQL view has no natural primary key, so declare one or more keyColumn fields on the virtualTable if you need stable feature IDs for WFS or transactional clients — otherwise GeoServer synthesises volatile IDs that change between requests, which breaks feature-by-ID lookups and edit round-trips.

Back to Layer Publishing Workflows in Python

Related