[Geoserver-users] Problems with large amount of features from Oracle-DB

GeoServer GeoServer User
1

Hi,

Trying to optimize data in Oracle for making it to behave faster is one way. Another way is to stop and think if you could do something differently. I do not know your exact use case but here comes what I have been doing.

If the computer screen has 1000 by 1000 pixels it makes one million pixels. Rendering a four million feature layer as it is in the database means drawing 4 features per pixel on average. That is not the most intelligent thing to do if you are in a hurry.

What I have done in a bit similar case with 1,2 million polygons is to create an scale dependent layer group with three layers. The first layer contains the original data. Next layer has 10% of features and geometries are simplified. The third layer has only 1% of features as simplified geometries. The users can see no difference at all on the map because even with the 12000 polygons the whole map seems crowded within a proper scale range. In my case the parcel polygons are evenly distributed and about the same size so I could simply select all features with integer fids ending with one or two zeros. For uneven distribution some other method for reducing the number of features to be drawn could suit better. For the lake polygons I have used the polygon area as a criteria. With Mapserver this can be done on-the-fly if the date is selected as "order by area desc" and layer option "maxfeatures" is used http://mapserver.org/de/mapfile/layer.html. I am not sure if the same can be done with Geoserver and SLD. Anyway, stored reduced layers work always. There is seldom a need to update the reduced layers because users would not see any difference at those scales.

There is also a "pregeneralized features" extension http://docs.geoserver.org/stable/en/user/tutorials/feature-pregeneralized/feature-pregeneralized_tutorial.html.
However, generalizing works only with lines and polygons, and reducing the number of features is more effective than removing vertices. Perhaps the extension could be enhanced to remove small polygons and delete point features from clusters.

-Jukka Rahkonen-

frankzander wrote:

Hi,

I want to use a GeoServer to display a large collection of spatial data, stored in
an oracle database (~4 million features spreaded on a radius of
150 miles). I added the database as a data source (oracle plugin) and added a
layer to publish the data. The layer preview with openlayers usually fails after 60
seconds, because thats the preconfigured maximum rendering time (i also tried
to set the time to 180 seconds, also without success).

So i tried to add a cached layer and to fill the cache through the generate-
function. Some of the generated pictures in the cache show the correct data,
but most of the pictures in the cache are just black, because the rendering fails
again after 60 seconds. If i reduce the features to a few hundred feaures
everything works fine. If I export the features to a big shapefile and publish the
shapefile everything is ok, but the features can change everyday and i can't
export all the features everyday.

I think i'm doing sth. completely wrong, a lot of wms-servers have these
amounts of features and don't face any problems. I wonder why publishing a big
shapefile is really fast and publishing the same data from an oracle server (in the
same intranet) is such a big deal for me. Can i reduce the features to render on a
lower zoom level? Why is this a problem if loaded from a database and no
problem if loaded from a shapefile?

Thanks for your effort,

Frank

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Problems-
with-large-amount-of-features-from-Oracle-DB-tp5139088.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity • Requirements for
releasing software faster • Expert tips and advice for migrating your SCM
now http://p.sf.net/sfu/perforce
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

2

Excellent points Jukka.
A further thought - if you are getting millions of features from the database at a time, it’s not likely to matter which database you use - the limitation is more likely to be the network transactions as Geoserver has to request/receive millions of features.
This is less of a problem with a stateless shapefile which is accessed at a much lower level.
Cheers,
Jonathan

This transmission is intended for the named addressee(s) only and may contain confidential, sensitive or personal information 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 9 May 2014 13:31, Rahkonen Jukka (Tike) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Trying to optimize data in Oracle for making it to behave faster is one way. Another way is to stop and think if you could do something differently. I do not know your exact use case but here comes what I have been doing.

If the computer screen has 1000 by 1000 pixels it makes one million pixels. Rendering a four million feature layer as it is in the database means drawing 4 features per pixel on average. That is not the most intelligent thing to do if you are in a hurry.

What I have done in a bit similar case with 1,2 million polygons is to create an scale dependent layer group with three layers. The first layer contains the original data. Next layer has 10% of features and geometries are simplified. The third layer has only 1% of features as simplified geometries. The users can see no difference at all on the map because even with the 12000 polygons the whole map seems crowded within a proper scale range. In my case the parcel polygons are evenly distributed and about the same size so I could simply select all features with integer fids ending with one or two zeros. For uneven distribution some other method for reducing the number of features to be drawn could suit better. For the lake polygons I have used the polygon area as a criteria. With Mapserver this can be done on-the-fly if the date is selected as “order by area desc” and layer option “maxfeatures” is used http://mapserver.org/de/mapfile/layer.html. I am not sure if the same can be done with Geoserver and SLD. Anyway, stored reduced layers work always. There is seldom a need to update the reduced layers because users would not see any difference at those scales.

There is also a “pregeneralized features” extension http://docs.geoserver.org/stable/en/user/tutorials/feature-pregeneralized/feature-pregeneralized_tutorial.html.
However, generalizing works only with lines and polygons, and reducing the number of features is more effective than removing vertices. Perhaps the extension could be enhanced to remove small polygons and delete point features from clusters.

-Jukka Rahkonen-

frankzander wrote:

Hi,

I want to use a GeoServer to display a large collection of spatial data, stored in
an oracle database (~4 million features spreaded on a radius of
150 miles). I added the database as a data source (oracle plugin) and added a
layer to publish the data. The layer preview with openlayers usually fails after 60
seconds, because thats the preconfigured maximum rendering time (i also tried
to set the time to 180 seconds, also without success).

So i tried to add a cached layer and to fill the cache through the generate-
function. Some of the generated pictures in the cache show the correct data,
but most of the pictures in the cache are just black, because the rendering fails
again after 60 seconds. If i reduce the features to a few hundred feaures
everything works fine. If I export the features to a big shapefile and publish the
shapefile everything is ok, but the features can change everyday and i can’t
export all the features everyday.

I think i’m doing sth. completely wrong, a lot of wms-servers have these
amounts of features and don’t face any problems. I wonder why publishing a big
shapefile is really fast and publishing the same data from an oracle server (in the
same intranet) is such a big deal for me. Can i reduce the features to render on a
lower zoom level? Why is this a problem if loaded from a database and no
problem if loaded from a shapefile?

Thanks for your effort,

Frank

–
View this message in context: http://osgeo-org.1560.x6.nabble.com/Problems-
with-large-amount-of-features-from-Oracle-DB-tp5139088.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
� 3 signs your SCM is hindering your productivity � Requirements for
releasing software faster � Expert tips and advice for migrating your SCM
now http://p.sf.net/sfu/perforce

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

Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
� 3 signs your SCM is hindering your productivity
� Requirements for releasing software faster
� Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce

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