[Geoserver-users] Using Postgres Materialized Views in Geoserver [SEC=UNCLASSIFIED]

GeoServer GeoServer User
1

Yep I had the same experience in 2.5.

I updated the postgresql jar file to postgresql-9.3-1101.jdbc3.jar. Standard views showed up but not materialized views.

Simon

From: Andrea Aime [mailto:andrea.aime@…1107…]
Sent: Wednesday, 23 April 2014 3:47 PM
To: Mark Grantham
Cc: GeoServer Mailing List List
Subject: Re: [Geoserver-users] Using Postgres Materialized Views in Geoserver

On Wed, Apr 23, 2014 at 3:23 AM, Mark Grantham <mark@…6367…> wrote:

Hi,

I am currently testing reading vector data from a PostGIS DB (version
9.3) using GeoServer 2.5. I wanted to try using a materialized view for
performance (as suggested in
http://www.slideshare.net/geosolutions/geoserver-on-steroids).

I created a “normal” view, added it to geometry_columns and was able to
add it as a layer in GeoServer. I then created a Materialized View using
the same method, and added it to geometry_columns. When I went to add
the new layer in GeoServer, the materialized view did not display. I
then created a view which did a SELECT * on the materialized view, added
it to geometry_columns and was able to add it to GeoServer.

I thought that this should work in 2.5 after
https://jira.codehaus.org/browse/GEOT-4635 was fixed - has anyone else
tried this?

We also need to upgrade the jdbc driver, but trying to do so breaks other things,

see linked issue in there

Cheers

Andrea

–

==

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK

for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

Geoscience Australia Disclaimer: This e-mail (and files transmitted with it) is intended only for the person or entity to which it is addressed. If you are not the intended recipient, then you have received this e-mail by mistake and any use, dissemination, forwarding, printing or copying of this e-mail and its file attachments is prohibited. The security of emails transmitted cannot be guaranteed; by forwarding or replying to this email, you acknowledge and accept these risks.

2

On Wed, Apr 23, 2014 at 10:32 AM, <Simon.Oldfield@anonymised.com> wrote:

Yep I had the same experience in 2.5.

I updated the postgresql jar file to postgresql-9.3-1101.jdbc3.jar.
Standard views showed up but not materialized views.

Even after the driver upgrade? Sigh, there must be something else too going
on I guess.
I don't have postgresql 9.3 so cannot try myself

Anyways, upgrading the driver will break GeoServer if you use any column
that requires quoting (that is, columns
that are using explicit upper/lower case, spaces and the like)

Cheers
Andrea

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

3

On Wed, Apr 23, 2014 at 10:32 AM, <Simon.Oldfield@anonymised.com> wrote:

Yep I had the same experience in 2.5.

I updated the postgresql jar file to postgresql-9.3-1101.jdbc3.jar.
Standard views showed up but not materialized views.

Materialized views do work fine over here, in GeoServer 2.5 with 9.3 JDBC
driver as shown above.

--
Damiano Albani