[Geoserver-users] Using Postgres Materialized Views in Geoserver

GeoServer GeoServer User
1

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?

Thanks

Mark

2

On Wed, Apr 23, 2014 at 3:23 AM, Mark Grantham <mark@anonymised.com>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

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

3

Hi Andrea,

Not trying to hi-jack the thread, but is this problem unique to PostGIS Materialised Views or would it also carry other to other RDBMS’ (i.e. Oracle?)

Cheers,
Jonathan

This transmission is intended for the named addressee(s) only and may contain sensitive or protectively marked material up to RESTRICTED and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation.

···

On 23 April 2014 05:46, Andrea Aime <andrea.aime@anonymised.com> wrote:

Start Your Social Network Today - Download eXo Platform
Build your Enterprise Intranet with eXo Platform Software
Java Based Open Source Intranet - Social, Extensible, Cloud Ready
Get Started Now And Turn Your Intranet Into A Collaboration Platform
http://p.sf.net/sfu/ExoPlatform

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

On Wed, Apr 23, 2014 at 3:23 AM, Mark Grantham <mark@anonymised.com> 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

4

On Thu, Apr 24, 2014 at 9:50 AM, Jonathan Moules <
jonathanmoules@anonymised.com> wrote:

Hi Andrea,
  Not trying to hi-jack the thread, but is this problem unique to PostGIS
Materialised Views or would it also carry other to other RDBMS' (i.e.
Oracle?)

Afaik, it should be PostGIS specific

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

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