Publishing a Materialized View in Geoserver

GeoServer GeoServer User
,
1

Hi,

I am having issues with a materialized view publication on Geoserver. This is how my materialized view looks like:


CREATE MATERIALIZED VIEW master.inspire_admin_boundaries_cont AS
SELECT 
	row_number() over () as id,
	'http://id.igeo.pt/so/AU/AdministrativeBoundaries/' || t.identificador || '/' || to_char(t.inicio_objecto, 'YYYYMMDD') AS "inspireId",
	'PT' AS country,
	nla.nome_en AS "nationalLevel",
	t.inicio_objecto::timestamp AS "beginLifespanVersion",
	NULL::timestamp AS "endLifespanVersion", -- A definir
	'agreed' AS "legalStatus",
	'notEdgeMatched' AS "technicalStatus",
	ARRAY[CASE WHEN char_length(t.ea_esquerda) > 5 THEN 'PT1' || t.ea_esquerda END,
		  CASE WHEN char_length(t.ea_direita) > 5 THEN 'PT1' || t.ea_direita END] AS "admUnit",
	t.geometria::geometry(linestring, 3763) AS geometry
FROM base.cont_troco AS t
	JOIN dominios.nivel_limite_administrativo AS nla ON t.nivel_limite_admin = nla.identificador;

CREATE UNIQUE INDEX ON master.inspire_admin_boundaries_cont(id);
CREATE INDEX ON master.inspire_admin_boundaries_cont USING gist(geometry);

On the geoserver side, the people that is configuring it say it works fine in WMS and in WFS 1.1, but not WFS 2.0.

First suspect was the lack of an ID column, that’s why row_number() over () was added and the a unique index created, but made no difference. I am not geoserver user, so I am wondering if there’s something else I need to do on postgis side, or if there’s something missing on geoserver.

I could convert everything to tables, but…

UPDATE: Actually it works on WFS 2.0 but complains with the following:

" java.io.IOExceptionCannot do natural order without a primary key, please add it or specify a manual sort over existing attributes"

Thanks,

Alexandre Neto

2

WFS 2.0 needs a primary key to work in OGC compliant mode (for stable paging).
If the data source does not have a primary key that can be naturally looked up using database metadata, you can configure one using the “primary key metadata table”, see Controlling feature ID generation in spatial databases — GeoServer 2.25.x User Manual

If instead you really don’t have a unique key in there (something both unique and stable over time) then warning is warranted and the WFS from that table won’t be fully WFS 2.0 compliant (and it might still be ok, if you don’t need stable paging anyways).

3

Thanks @aaime !

4

I noticed you are using

row_number() over ()

to generate your unique key. I would suggest putting an order by in there so that it’s more likely a feature maintains the same id over each refresh.

So something like:

CREATE MATERIALIZED VIEW master.inspire_admin_boundaries_cont AS
SELECT 
	row_number() over ( ORDER BY t.identificador,  t.inicio_objecto,  nla.identificador ) as id,
	'http://id.igeo.pt/so/AU/AdministrativeBoundaries/' || t.identificador || '/' || to_char(t.inicio_objecto, 'YYYYMMDD') AS "inspireId",
	'PT' AS country,
	nla.nome_en AS "nationalLevel",
	t.inicio_objecto::timestamp AS "beginLifespanVersion",
	NULL::timestamp AS "endLifespanVersion", -- A definir
	'agreed' AS "legalStatus",
	'notEdgeMatched' AS "technicalStatus",
	ARRAY[CASE WHEN char_length(t.ea_esquerda) > 5 THEN 'PT1' || t.ea_esquerda END,
		  CASE WHEN char_length(t.ea_direita) > 5 THEN 'PT1' || t.ea_direita END] AS "admUnit",
	t.geometria::geometry(linestring, 3763) AS geometry
FROM base.cont_troco AS t
	JOIN dominios.nivel_limite_administrativo AS nla ON t.nivel_limite_admin = nla.identificador;
1 Like