[Geoserver-users] Temporary Tables/Columns + PostGIS performance

GeoServer GeoServer User
1

Hi,

I’ve recently started using GeoServer and built a small prototype to evaluate its performance and features to evaluate if GeoServer could be a viable solution for our project. I ran into some issues that I hope you can help me solve.

I’ve been running GeoServer 2.2.4 and PostGIS 2.0.1 (on top of PostGres 9.2) on a Windows machine.

In our project the user has the ability to create temporary tables and/or columns dynamically. Example: The user wants the “States” table to have a dynamically calculated “Clients per State” column. These datasets are temporary and disappear after the user has logged out. Does GeoServer have any kind of mechanism to help me with temporary datasets? If not, how would you advise me to implement it?

I did a couple of simple performance tests with GeoServer and a Local PostGIS database and the results were pretty good. A cross layer spatial intersection (around 350k geometries) took around 8 seconds which is a pretty okay. When I tested with a remote PostGIS database the same test took around 6 minutes. I’m unsure if latency is enough of a factor to justify such a difference and I would like to know if there is anything I can tune to improve this performance. I found this issue to be very awkward since I feel that network alone doesn’t justify my problem.

Many thanks in advance!

Cosme Benito

2

On Mon, Mar 11, 2013 at 12:36 PM, Cosme Benito <cosme.benito@anonymised.com…> wrote:

Hi,

I’ve recently started using GeoServer and built a small prototype to evaluate its performance and features to evaluate if GeoServer could be a viable solution for our project. I ran into some issues that I hope you can help me solve.

I’ve been running GeoServer 2.2.4 and PostGIS 2.0.1 (on top of PostGres 9.2) on a Windows machine.

In our project the user has the ability to create temporary tables and/or columns dynamically. Example: The user wants the “States” table to have a dynamically calculated “Clients per State” column. These datasets are temporary and disappear after the user has logged out. Does GeoServer have any kind of mechanism to help me with temporary datasets? If not, how would you advise me to implement it?

There is no support for such a requirement, OGC services are stateless, so there is no notion of session on service calls.
You should manage it from your front end application using the REST configuration services:
http://docs.geoserver.org/stable/en/user/rest/index.html

I did a couple of simple performance tests with GeoServer and a Local PostGIS database and the results were pretty good. A cross layer spatial intersection (around 350k geometries) took around 8 seconds which is a pretty okay. When I tested with a remote PostGIS database the same test took around 6 minutes. I’m unsure if latency is enough of a factor to justify such a difference and I would like to know if there is anything I can tune to improve this performance. I found this issue to be very awkward since I feel that network alone doesn’t justify my problem.

350K geometries are a lot to transfer over a network (and makes little sense to paint a map with so many, they are unlikely to generate
a readable map, the rule of thumb is not to go above 1000), but I agree it should not take 6 minutes… however I don’t know what might
make it so slow.
GeoServer has no notion of whether the database is local or remote, so the interaction between GeoServer and the database
is the same in the two cases.

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it 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

I figured as much, thanks a lot for the confirmation.

You are right, I probably didn’t explain very well. What I meant was that the intersection operation is big and there were about 350k records processed although only about 10k are actually shown. I know that is still a lot to show on a map but does GeoServer have any clustering mechanism that I can take advantage of?

Thanks a lot Andrea, not just for this answer but for all the other answers I found you answering all over the internet that solve most my problems J

Cosme Benito

From: andrea.aime@anonymised.com… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: segunda-feira, 11 de Março de 2013 13:02
To: Cosme Benito
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Temporary Tables/Columns + PostGIS performance

On Mon, Mar 11, 2013 at 12:36 PM, Cosme Benito <cosme.benito@anonymised.com> wrote:

Hi,

I’ve recently started using GeoServer and built a small prototype to evaluate its performance and features to evaluate if GeoServer could be a viable solution for our project. I ran into some issues that I hope you can help me solve.

I’ve been running GeoServer 2.2.4 and PostGIS 2.0.1 (on top of PostGres 9.2) on a Windows machine.

In our project the user has the ability to create temporary tables and/or columns dynamically. Example: The user wants the “States” table to have a dynamically calculated “Clients per State” column. These datasets are temporary and disappear after the user has logged out. Does GeoServer have any kind of mechanism to help me with temporary datasets? If not, how would you advise me to implement it?

There is no support for such a requirement, OGC services are stateless, so there is no notion of session on service calls.

You should manage it from your front end application using the REST configuration services:

http://docs.geoserver.org/stable/en/user/rest/index.html

I did a couple of simple performance tests with GeoServer and a Local PostGIS database and the results were pretty good. A cross layer spatial intersection (around 350k geometries) took around 8 seconds which is a pretty okay. When I tested with a remote PostGIS database the same test took around 6 minutes. I’m unsure if latency is enough of a factor to justify such a difference and I would like to know if there is anything I can tune to improve this performance. I found this issue to be very awkward since I feel that network alone doesn’t justify my problem.

350K geometries are a lot to transfer over a network (and makes little sense to paint a map with so many, they are unlikely to generate

a readable map, the rule of thumb is not to go above 1000), but I agree it should not take 6 minutes… however I don’t know what might

make it so slow.

GeoServer has no notion of whether the database is local or remote, so the interaction between GeoServer and the database

is the same in the two cases.

Cheers

Andrea

–

==

Our support, Your Success! Visit http://opensdi.geo-solutions.it 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