[Geoserver-users] DB2 store needs Loose bbox

GeoServer GeoServer User
1

The PostGIS data store interface allows one to select "Loose bbox" but this isn't available for DB2.

The "Loose bbox" can improve query performance by a factor of 2-5 and CPU utilization by a factor of 10.

How can this get added?

Regards,
David

2

On Thu, Mar 22, 2012 at 2:23 PM, David Adler <dadler@anonymised.com> wrote:

The PostGIS data store interface allows one to select “Loose bbox” but
this isn’t available for DB2.

The “Loose bbox” can improve query performance by a factor of 2-5 and
CPU utilization by a factor of 10.

How can this get added?

Loose bbox in postgis uses a sql function (st_estimated_extent) that queries the root
of the spatial index instead of accessing the whole data set:
http://postgis.refractions.net/documentation/manual-1.3/ch06.html

To implement something similar in DB2 I guess you need an equivalent
sql function for DB2 (and then write code to support it)

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

3

Hi Andrea - thanks for the quick response.

DB2 already has the sql function to do this and the DB2 support for GeoTools has this capability as well.

How does it get added to the GeoServer GUI and the value set so that it can be used by the DB2 store?

On 3/22/2012 9:53 AM, Andrea Aime wrote:

On Thu, Mar 22, 2012 at 2:23 PM, David Adler <dadler@anonymised.com> wrote:

The PostGIS data store interface allows one to select “Loose bbox” but
this isn’t available for DB2.

The “Loose bbox” can improve query performance by a factor of 2-5 and
CPU utilization by a factor of 10.

How can this get added?

Loose bbox in postgis uses a sql function (st_estimated_extent) that queries the root
of the spatial index instead of accessing the whole data set:
http://postgis.refractions.net/documentation/manual-1.3/ch06.html

To implement something similar in DB2 I guess you need an equivalent
sql function for DB2 (and then write code to support it)

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

4

Hi David, unfortunately I am under a heavy load this week. Could you create a JIRA ticket for geotools and attach a patch for 2.7.x. (Would be happy if you can attach a patch for trunk too). I would commit after the test pass.

Christian

Zitat von David Adler <dadler@anonymised.com>:

Hi Andrea - thanks for the quick response.

DB2 already has the sql function to do this and the DB2 support for
GeoTools has this capability as well.

How does it get added to the GeoServer GUI and the value set so that it
can be used by the DB2 store?

On 3/22/2012 9:53 AM, Andrea Aime wrote:

On Thu, Mar 22, 2012 at 2:23 PM, David Adler <dadler@anonymised.com <mailto:dadler@anonymised.com>> wrote:

   The PostGIS data store interface allows one to select "Loose bbox" but
   this isn't available for DB2.

   The "Loose bbox" can improve query performance by a factor of 2-5 and
   CPU utilization by a factor of 10.

   How can this get added?

Loose bbox in postgis uses a sql function (st_estimated_extent) that queries the root
of the spatial index instead of accessing the whole data set:
http://postgis.refractions.net/documentation/manual-1.3/ch06.html

To implement something similar in DB2 I guess you need an equivalent
sql function for DB2 (and then write code to support it)

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 339 8844549

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

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

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.