[Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers

GeoServer GeoServer User
1

Hello everyone,

I am having an issue where WMS GetCapabilities (and other WMS requests) are running very slow for PostGIS-stored layers with a time dimension enabled. Note that I DO have an index on the database field for my time dimension. On the surface, this seems similar to the thread found in: http://osgeo-org.1560.x6.nabble.com/WMS-request-getcapabilities-takes-a-loooooooooooooong-time-to-return-td5003095.html but, again, I ** DO ** have my time field indexed. What I am seeing looks like geoserver (or geotools) is not even asking for distinct or max/min on the time field in some cases.

Here is the setup:

  1. Geoserver Version: 2.3.3 running on ubuntu with Postgres 9.1 and postgis 1.5
  2. Simple database table “latest” with geometry point and time field/dimension called “timestamp”.
  3. About 15 distinct timestamps with approx 20,000 points at each timestamp for +/- 300,000 rows.

Here is what I am seeing:

  1. Calling GetCapabilities when only a layer using “latest” table (I will call this NO SQL VIEW) is enabled takes many seconds. Enabling GEOTOOLS level debugging shows an SQL statement of SELECT “timestamp” FROM “public”.“latest” when the database is being queried to gather the range of available times. It does NOT look like the query is asking for DISTINCT timestamps at all … but instead it is asking for ALL of them. Is that correct?

  2. Calling GetCapabilities when only a layer using “latest” table is enabled … but that layer is defined via a SQL VIEW … returns extremely fast! This layer is exactly the same using exactly the same data and store setup as the NON-SQL-VIEW layer in #1, but just with a view defined. It returns all of the same timestamps. In fact, I am just doing a SELECT * from “latest” as my SQL VIEW … not even filtering by any viewparams. However, in this case, I see the SQL request for determining the range of available timestamps as SELECT distinct(“timestamp”) FROM … so it looks like the database is being queried only for distinct timestamps, not ALL of them as #1 appears.

  3. When calling GetMap on the NO SQL VIEW layer in #1 above, but without specifying the time (meaning it will default to the most recent time and has to search the database for it), I again see SELECT “timestamp” FROM “public”.“latest” as the query. It does not seem to ask for the MAX. This takes 4 seconds.

  4. When calling GetMap on the SQL VIEW version of the layer, again without specifying the time, the SQL statement is SELECT max(“timestamp”) FROM (SELECT * from latest) as “vtable”, so the database is being asked for the MAX timestamp, not all of them. This is sub-second response.

  5. I see the same behavior for GetFeatureInfo WMS requests when the time defaults to current. The database has to be searched for the default (most recent) time. The standard NO SQL VIEW layer queries the db without asking for the MAX timestamp, whereas the SQL VIEW version asks for the MAX timestamp. The NO SQL VIEW version takes 4 seconds, whereas the SQL VIEW version is sub-second.

Why the difference? Most importantly, is this the expected and/or actual behavior for Postgis-enabled layers without a view defined in geoserver? Is the expected behavior that all times in the table are queried, not distinct and max times as appropriate?

Excerpts from my logs are provided below … numbered according to the descriptions above.

I appreciate any help and response on this!

Thanks,

Mike Grogan

SCENARIO #1: GetCapabilities … NO SQL VIEW

31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
31 Jul 00:22:28 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #2: GetCapabilities … SQL VIEW

31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - Gathering sql view result structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”) FROM (SELECT * from latest) as “vtable”
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #3 … GetMap … time not specified (use default/current) … NO SQL VIEW

31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
31 Jul 00:32:51 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #4 … GetMap … time not specified (use default/current) … SQL VIEW

31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM (SELECT * from latest) as “vtable”
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #5 … Seeing the same behavior for GetFeatureInfo request when time not specified

31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
31 Jul 00:35:29 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM (SELECT * from latest) as “vtable”
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

2

Ciao Mike,
quick question before reading more in depth the email. When you say
"PostGIS-stored layers with a time dimension enabled" this means you
explicitly enable the TIME dimension in the Dimension tab for the
Layer page?

If so which strategy did you set for the Dimension Presentation? If
you are using List this might be a bit slow (It might be that the
queries need to be optimized further) as we usually recommend using
Continuos Interval or interval. As I have more time I will try to look
into the rest of the email with more time.

Regards,
Simone Giannecchini

Our support, Your Success! Visit http://opensdi.geo-solutions.it for
more information.

Ing. Simone Giannecchini
@simogeo
Founder/Director

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

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

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

On Wed, Jul 31, 2013 at 3:18 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Hello everyone,

I am having an issue where WMS GetCapabilities (and other WMS requests) are
running very slow for PostGIS-stored layers with a time dimension enabled.
Note that I DO have an index on the database field for my time dimension.
On the surface, this seems similar to the thread found in:
http://osgeo-org.1560.x6.nabble.com/WMS-request-getcapabilities-takes-a-loooooooooooooong-time-to-return-td5003095.html
but, again, I ** DO ** have my time field indexed. What I am seeing looks
like geoserver (or geotools) is not even asking for distinct or max/min on
the time field in some cases.

Here is the setup:

1. Geoserver Version: 2.3.3 running on ubuntu with Postgres 9.1 and postgis
1.5
2. Simple database table "latest" with geometry point and time
field/dimension called "timestamp".
3. About 15 distinct timestamps with approx 20,000 points at each timestamp
for +/- 300,000 rows.

Here is what I am seeing:

1. Calling GetCapabilities when only a layer using "latest" table (I will
call this NO SQL VIEW) is enabled takes many seconds. Enabling GEOTOOLS
level debugging shows an SQL statement of SELECT "timestamp" FROM
"public"."latest" when the database is being queried to gather the range of
available times. It does NOT look like the query is asking for DISTINCT
timestamps at all ... but instead it is asking for ALL of them. Is that
correct?

2. Calling GetCapabilities when only a layer using "latest" table is
enabled ... but that layer is defined via a SQL VIEW ... returns extremely
fast! This layer is exactly the same using exactly the same data and store
setup as the NON-SQL-VIEW layer in #1, but just with a view defined. It
returns all of the same timestamps. In fact, I am just doing a SELECT *
from "latest" as my SQL VIEW ... not even filtering by any viewparams.
However, in this case, I see the SQL request for determining the range of
available timestamps as SELECT distinct("timestamp") FROM ... so it looks
like the database is being queried only for distinct timestamps, not ALL of
them as #1 appears.

3. When calling GetMap on the NO SQL VIEW layer in #1 above, but without
specifying the time (meaning it will default to the most recent time and
has to search the database for it), I again see SELECT "timestamp" FROM
"public"."latest" as the query. It does not seem to ask for the MAX. This
takes 4 seconds.

4. When calling GetMap on the SQL VIEW version of the layer, again without
specifying the time, the SQL statement is SELECT max("timestamp") FROM
(SELECT * from latest) as "vtable", so the database is being asked for the
MAX timestamp, not all of them. This is sub-second response.

5. I see the same behavior for GetFeatureInfo WMS requests when the time
defaults to current. The database has to be searched for the default (most
recent) time. The standard NO SQL VIEW layer queries the db without asking
for the MAX timestamp, whereas the SQL VIEW version asks for the MAX
timestamp. The NO SQL VIEW version takes 4 seconds, whereas the SQL VIEW
version is sub-second.

Why the difference? Most importantly, is this the expected and/or actual
behavior for Postgis-enabled layers without a view defined in geoserver? Is
the expected behavior that all times in the table are queried, not distinct
and max times as appropriate?

Excerpts from my logs are provided below ... numbered according to the
descriptions above.

I appreciate any help and response on this!

Thanks,

Mike Grogan

SCENARIO #1: GetCapabilities ... NO SQL VIEW

31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
"public"."latest"
31 Jul 00:22:28 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #2: GetCapabilities ... SQL VIEW

31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - SELECT distinct("timestamp")
FROM (SELECT * from latest) as "vtable"
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #3 ... GetMap ... time not specified (use default/current) ... NO
SQL VIEW

31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
"public"."latest"
31 Jul 00:32:51 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #4 ... GetMap ... time not specified (use default/current) ... SQL
VIEW

31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - SELECT max("timestamp") FROM
(SELECT * from latest) as "vtable"
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #5 ... Seeing the same behavior for GetFeatureInfo request when
time not specified

31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - SELECT "timestamp" FROM
"public"."latest"
31 Jul 00:35:29 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as "vtable" where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - SELECT max("timestamp") FROM
(SELECT * from latest) as "vtable"
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

------------------------------------------------------------------------------
Get your SQL database under version control now!
Version control is standard for application code, but databases havent
caught up. So what steps can you take to put your SQL databases under
version control? Why should you start doing it? Read more to find out.
http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

3

I forgot to copy my response to the list …

---------- Forwarded message ----------
From: Mike Grogan <d.michael.grogan@anonymised.com>
Date: Wed, Jul 31, 2013 at 7:13 AM
Subject: Re: [Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers
To: Simone Giannecchini <simone.giannecchini@anonymised.com>

Hello, Simone.

Yes, time dimension is explicitly enabled.

Based on logs, the issue seems to be that geoserver is querying the database for times from all rows instead of asking for distinct or max timestamp. But this is just for layers created without a SQL view in geoserver. For layers with a view (even ones that don’t really filter or reduce the data in any way), the database is queried for distinct or max times appropriately and get capabilities and other requests return quickly.

When you get a chance this is detailed in depth in the logs and writeup.

Thanks again,

Mike Grogan

On Jul 31, 2013 4:01 AM, “Simone Giannecchini” <simone.giannecchini@anonymised.com> wrote:

Ciao Mike,
quick question before reading more in depth the email. When you say
“PostGIS-stored layers with a time dimension enabled” this means you
explicitly enable the TIME dimension in the Dimension tab for the
Layer page?

If so which strategy did you set for the Dimension Presentation? If
you are using List this might be a bit slow (It might be that the
queries need to be optimized further) as we usually recommend using
Continuos Interval or interval. As I have more time I will try to look
into the rest of the email with more time.

Regards,
Simone Giannecchini

Our support, Your Success! Visit http://opensdi.geo-solutions.it for
more information.

Ing. Simone Giannecchini
@simogeo
Founder/Director

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

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

On Wed, Jul 31, 2013 at 3:18 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Hello everyone,

I am having an issue where WMS GetCapabilities (and other WMS requests) are
running very slow for PostGIS-stored layers with a time dimension enabled.
Note that I DO have an index on the database field for my time dimension.
On the surface, this seems similar to the thread found in:
http://osgeo-org.1560.x6.nabble.com/WMS-request-getcapabilities-takes-a-loooooooooooooong-time-to-return-td5003095.html
but, again, I ** DO ** have my time field indexed. What I am seeing looks
like geoserver (or geotools) is not even asking for distinct or max/min on
the time field in some cases.

Here is the setup:

  1. Geoserver Version: 2.3.3 running on ubuntu with Postgres 9.1 and postgis
    1.5
  2. Simple database table “latest” with geometry point and time
    field/dimension called “timestamp”.
  3. About 15 distinct timestamps with approx 20,000 points at each timestamp
    for +/- 300,000 rows.

Here is what I am seeing:

  1. Calling GetCapabilities when only a layer using “latest” table (I will
    call this NO SQL VIEW) is enabled takes many seconds. Enabling GEOTOOLS
    level debugging shows an SQL statement of SELECT “timestamp” FROM
    “public”.“latest” when the database is being queried to gather the range of
    available times. It does NOT look like the query is asking for DISTINCT
    timestamps at all … but instead it is asking for ALL of them. Is that
    correct?

  2. Calling GetCapabilities when only a layer using “latest” table is
    enabled … but that layer is defined via a SQL VIEW … returns extremely
    fast! This layer is exactly the same using exactly the same data and store
    setup as the NON-SQL-VIEW layer in #1, but just with a view defined. It
    returns all of the same timestamps. In fact, I am just doing a SELECT *
    from “latest” as my SQL VIEW … not even filtering by any viewparams.
    However, in this case, I see the SQL request for determining the range of
    available timestamps as SELECT distinct(“timestamp”) FROM … so it looks
    like the database is being queried only for distinct timestamps, not ALL of
    them as #1 appears.

  3. When calling GetMap on the NO SQL VIEW layer in #1 above, but without
    specifying the time (meaning it will default to the most recent time and
    has to search the database for it), I again see SELECT “timestamp” FROM
    “public”.“latest” as the query. It does not seem to ask for the MAX. This
    takes 4 seconds.

  4. When calling GetMap on the SQL VIEW version of the layer, again without
    specifying the time, the SQL statement is SELECT max(“timestamp”) FROM
    (SELECT * from latest) as “vtable”, so the database is being asked for the
    MAX timestamp, not all of them. This is sub-second response.

  5. I see the same behavior for GetFeatureInfo WMS requests when the time
    defaults to current. The database has to be searched for the default (most
    recent) time. The standard NO SQL VIEW layer queries the db without asking
    for the MAX timestamp, whereas the SQL VIEW version asks for the MAX
    timestamp. The NO SQL VIEW version takes 4 seconds, whereas the SQL VIEW
    version is sub-second.

Why the difference? Most importantly, is this the expected and/or actual
behavior for Postgis-enabled layers without a view defined in geoserver? Is
the expected behavior that all times in the table are queried, not distinct
and max times as appropriate?

Excerpts from my logs are provided below … numbered according to the
descriptions above.

I appreciate any help and response on this!

Thanks,

Mike Grogan

SCENARIO #1: GetCapabilities … NO SQL VIEW

31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:22:28 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #2: GetCapabilities … SQL VIEW

31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”)
FROM (SELECT * from latest) as “vtable”
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #3 … GetMap … time not specified (use default/current) … NO
SQL VIEW

31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:32:51 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #4 … GetMap … time not specified (use default/current) … SQL
VIEW

31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM
(SELECT * from latest) as “vtable”
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #5 … Seeing the same behavior for GetFeatureInfo request when
time not specified

31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:35:29 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM
(SELECT * from latest) as “vtable”
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

Get your SQL database under version control now!
Version control is standard for application code, but databases havent
caught up. So what steps can you take to put your SQL databases under
version control? Why should you start doing it? Read more to find out.
http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk

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

4

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

Another oddity on this is that, if I rename my SQL VIEW layer, the sql query changes to the VERY SLOW SELECT “timestamp” instead of SELECT distinct(“timestamp”). If I change the name of the view-based layer back to its original name, the queries revert back to the VERY FAST SELECT distinct(“timestamp”) … with no other changes made besides the name.

As I have added more times to my time dimension, the disparity in the distinct/non-distinct query for the time dimension is even worse … seeing about 8+ seconds for GetCapabilities for the non-sql-view layer, with <1 second for the sql view layer.

Thanks,

Mike Grogan

···

On Wed, Jul 31, 2013 at 10:12 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

I forgot to copy my response to the list …

---------- Forwarded message ----------
From: Mike Grogan <d.michael.grogan@anonymised.com>
Date: Wed, Jul 31, 2013 at 7:13 AM
Subject: Re: [Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers
To: Simone Giannecchini <simone.giannecchini@anonymised.com>

Hello, Simone.

Yes, time dimension is explicitly enabled.

Based on logs, the issue seems to be that geoserver is querying the database for times from all rows instead of asking for distinct or max timestamp. But this is just for layers created without a SQL view in geoserver. For layers with a view (even ones that don’t really filter or reduce the data in any way), the database is queried for distinct or max times appropriately and get capabilities and other requests return quickly.

When you get a chance this is detailed in depth in the logs and writeup.

Thanks again,

Mike Grogan

On Jul 31, 2013 4:01 AM, “Simone Giannecchini” <simone.giannecchini@anonymised.com> wrote:

Ciao Mike,
quick question before reading more in depth the email. When you say
“PostGIS-stored layers with a time dimension enabled” this means you
explicitly enable the TIME dimension in the Dimension tab for the
Layer page?

If so which strategy did you set for the Dimension Presentation? If
you are using List this might be a bit slow (It might be that the
queries need to be optimized further) as we usually recommend using
Continuos Interval or interval. As I have more time I will try to look
into the rest of the email with more time.

Regards,
Simone Giannecchini

Our support, Your Success! Visit http://opensdi.geo-solutions.it for
more information.

Ing. Simone Giannecchini
@simogeo
Founder/Director

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

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

On Wed, Jul 31, 2013 at 3:18 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Hello everyone,

I am having an issue where WMS GetCapabilities (and other WMS requests) are
running very slow for PostGIS-stored layers with a time dimension enabled.
Note that I DO have an index on the database field for my time dimension.
On the surface, this seems similar to the thread found in:
http://osgeo-org.1560.x6.nabble.com/WMS-request-getcapabilities-takes-a-loooooooooooooong-time-to-return-td5003095.html
but, again, I ** DO ** have my time field indexed. What I am seeing looks
like geoserver (or geotools) is not even asking for distinct or max/min on
the time field in some cases.

Here is the setup:

  1. Geoserver Version: 2.3.3 running on ubuntu with Postgres 9.1 and postgis
    1.5
  2. Simple database table “latest” with geometry point and time
    field/dimension called “timestamp”.
  3. About 15 distinct timestamps with approx 20,000 points at each timestamp
    for +/- 300,000 rows.

Here is what I am seeing:

  1. Calling GetCapabilities when only a layer using “latest” table (I will
    call this NO SQL VIEW) is enabled takes many seconds. Enabling GEOTOOLS
    level debugging shows an SQL statement of SELECT “timestamp” FROM
    “public”.“latest” when the database is being queried to gather the range of
    available times. It does NOT look like the query is asking for DISTINCT
    timestamps at all … but instead it is asking for ALL of them. Is that
    correct?

  2. Calling GetCapabilities when only a layer using “latest” table is
    enabled … but that layer is defined via a SQL VIEW … returns extremely
    fast! This layer is exactly the same using exactly the same data and store
    setup as the NON-SQL-VIEW layer in #1, but just with a view defined. It
    returns all of the same timestamps. In fact, I am just doing a SELECT *
    from “latest” as my SQL VIEW … not even filtering by any viewparams.
    However, in this case, I see the SQL request for determining the range of
    available timestamps as SELECT distinct(“timestamp”) FROM … so it looks
    like the database is being queried only for distinct timestamps, not ALL of
    them as #1 appears.

  3. When calling GetMap on the NO SQL VIEW layer in #1 above, but without
    specifying the time (meaning it will default to the most recent time and
    has to search the database for it), I again see SELECT “timestamp” FROM
    “public”.“latest” as the query. It does not seem to ask for the MAX. This
    takes 4 seconds.

  4. When calling GetMap on the SQL VIEW version of the layer, again without
    specifying the time, the SQL statement is SELECT max(“timestamp”) FROM
    (SELECT * from latest) as “vtable”, so the database is being asked for the
    MAX timestamp, not all of them. This is sub-second response.

  5. I see the same behavior for GetFeatureInfo WMS requests when the time
    defaults to current. The database has to be searched for the default (most
    recent) time. The standard NO SQL VIEW layer queries the db without asking
    for the MAX timestamp, whereas the SQL VIEW version asks for the MAX
    timestamp. The NO SQL VIEW version takes 4 seconds, whereas the SQL VIEW
    version is sub-second.

Why the difference? Most importantly, is this the expected and/or actual
behavior for Postgis-enabled layers without a view defined in geoserver? Is
the expected behavior that all times in the table are queried, not distinct
and max times as appropriate?

Excerpts from my logs are provided below … numbered according to the
descriptions above.

I appreciate any help and response on this!

Thanks,

Mike Grogan

SCENARIO #1: GetCapabilities … NO SQL VIEW

31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:22:24 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:22:28 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #2: GetCapabilities … SQL VIEW

31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”)
FROM (SELECT * from latest) as “vtable”
31 Jul 00:21:11 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #3 … GetMap … time not specified (use default/current) … NO
SQL VIEW

31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:32:47 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:32:51 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #4 … GetMap … time not specified (use default/current) … SQL
VIEW

31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM
(SELECT * from latest) as “vtable”
31 Jul 00:30:57 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

SCENARIO #5 … Seeing the same behavior for GetFeatureInfo request when
time not specified

31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:35:25 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM
“public”.“latest”
31 Jul 00:35:29 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - Gathering sql view result
structure: select * from (SELECT * from latest) as “vtable” where 1 = 0
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - SELECT max(“timestamp”) FROM
(SELECT * from latest) as “vtable”
31 Jul 00:36:31 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

Get your SQL database under version control now!
Version control is standard for application code, but databases havent
caught up. So what steps can you take to put your SQL databases under
version control? Why should you start doing it? Read more to find out.
http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk

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

5

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com>wrote:

Can anyone point me to the point in the source where these queries are
made to determine the times for the time dimensions for WMS GetCapabilities
and postgis stores? The problem described in my earlier post continues to
perplex me ... and I can't find the location in the source to look for
myself. I have tried!

GeoServer code almost never knows about the nature of the underlying
storage, it uses the GeoTools API
to access data, in this case, it's using a UniqueVisitor if possible (or a
min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between
the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that
misses the appropriate delegation.
Which is actually something Justin (cc'ed) tried to fix some time ago, but
not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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

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

6

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan
···

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API
to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.
Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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

7

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195
http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

···

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API
to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.
Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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

8

Justin,

Yes, the layer name does seem to be causing an issue. To clarify, this is for the NON-SQL-VIEW layer. The underlying postgis table is “latest”, as is the name of the store.

If I name the layer “latest” in Geoserver “Edit Layer”, I see the following query upon a GetCapabilities:

12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”) FROM “public”.“latest”
12 Aug 16:36:09 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

If I rename the layer in Geoserver to something else … “latestxxx”, while leaving the postgis layer & store as “latest”, I see the following query:

12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
12 Aug 16:37:16 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

So, the distinct() aggregate does seem to be disappearing if the layer name is not the same as the store/table name.

Hope this helps, and thanks for looking at this.

  • Mike
···

On Mon, Aug 12, 2013 at 12:01 PM, Justin Deoliveira <jdeolive@anonymised.com> wrote:

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195
http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API
to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.
Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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

9

Hello:

Is there a way to export workspaces and layers available in one older geoserver installation to a new installation?

We are switching to linux and I don’t know how to preserve all 100+ layers I’ve published into this new linux install.

Any help will be appreciated.

···

From: Justin Deoliveira [mailto:jdeolive@anonymised.com]
Sent: Monday, August 12, 2013 12:02 PM
To: Mike Grogan
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195

http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com…> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API

to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,

maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.

Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?

It might be that 2.4 beta does not exhibit this problem anymore.

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

–
Justin Deoliveira

OpenGeo - http://opengeo.org

Enterprise support for open source geospatial.

10

Great, thanks Mike. That definitely narrows it down. Looks like we need to update the wrapper that comes into play when renaming to pass down the function natively. Do you mind opening a jira ticket for this? Thanks.

···

On Mon, Aug 12, 2013 at 10:44 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Justin,

Yes, the layer name does seem to be causing an issue. To clarify, this is for the NON-SQL-VIEW layer. The underlying postgis table is “latest”, as is the name of the store.

If I name the layer “latest” in Geoserver “Edit Layer”, I see the following query upon a GetCapabilities:

12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”) FROM “public”.“latest”
12 Aug 16:36:09 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

If I rename the layer in Geoserver to something else … “latestxxx”, while leaving the postgis layer & store as “latest”, I see the following query:

12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
12 Aug 16:37:16 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

So, the distinct() aggregate does seem to be disappearing if the layer name is not the same as the store/table name.

Hope this helps, and thanks for looking at this.

  • Mike

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Mon, Aug 12, 2013 at 12:01 PM, Justin Deoliveira <jdeolive@anonymised.com> wrote:

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195
http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API
to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.
Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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

11

Hi Ivan,
This can easily be done by copying over the GeoServer Data Directory which is where all that information resides.

You may want to see this:
http://docs.geoserver.org/stable/en/user/datadirectory/index.html

and in particular:
http://docs.geoserver.org/stable/en/user/datadirectory/migrating.html#migrating-data-directory

Jonathan

On 12 August 2013 22:18, Ivan Santiago <isantiago@anonymised.com> wrote:

Hello:

Is there a way to export workspaces and layers available in one older geoserver installation to a new installation?

We are switching to linux and I don’t know how to preserve all 100+ layers I’ve published into this new linux install.

Any help will be appreciated.

Iván Santiago
GIS Specialist
Information Technologies
Office of Management and Budget
787.725.9420 x 2233
Calle Cruz 254
PO Box 9023228
San Juan, PR 00902-3228

From: Justin Deoliveira [mailto:jdeolive@anonymised.com]
Sent: Monday, August 12, 2013 12:02 PM
To: Mike Grogan
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetCapabilities (& other requests) very slow for time-enabled PostGIS layers

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195

http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com…> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API

to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,

maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.

Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?

It might be that 2.4 beta does not exhibit this problem anymore.

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

–
Justin Deoliveira

OpenGeo - http://opengeo.org

Enterprise support for open source geospatial.

Get 100% visibility into Java/.NET code with AppDynamics Lite!
It’s a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk

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

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.

12

Will do, hopefully w/in the next day or two.

Thanks again,

Mike Grogan

···

On Mon, Aug 12, 2013 at 8:58 PM, Justin Deoliveira <jdeolive@anonymised.com> wrote:

Great, thanks Mike. That definitely narrows it down. Looks like we need to update the wrapper that comes into play when renaming to pass down the function natively. Do you mind opening a jira ticket for this? Thanks.

On Mon, Aug 12, 2013 at 10:44 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Justin,

Yes, the layer name does seem to be causing an issue. To clarify, this is for the NON-SQL-VIEW layer. The underlying postgis table is “latest”, as is the name of the store.

If I name the layer “latest” in Geoserver “Edit Layer”, I see the following query upon a GetCapabilities:

12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:36:08 DEBUG [org.geotools.jdbc] - SELECT distinct(“timestamp”) FROM “public”.“latest”
12 Aug 16:36:09 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

If I rename the layer in Geoserver to something else … “latestxxx”, while leaving the postgis layer & store as “latest”, I see the following query:

12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
12 Aug 16:37:09 DEBUG [org.geotools.jdbc] - SELECT “timestamp” FROM “public”.“latest”
12 Aug 16:37:16 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION

So, the distinct() aggregate does seem to be disappearing if the layer name is not the same as the store/table name.

Hope this helps, and thanks for looking at this.

  • Mike

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Mon, Aug 12, 2013 at 12:01 PM, Justin Deoliveira <jdeolive@anonymised.com> wrote:

The changes I made where indeed backported to 9.x.

https://github.com/geotools/geotools/pull/195
http://jira.codehaus.org/browse/GEOT-4457

They should have been released for geotools 9.3 which i believe would have made it into geoserver 2.3.3?

So it may be in this case there is an intermediate wrapper being used that we are not accounting for. One thing that comes to mind might be a layer renaming.

@Mike: What is the published layer name (ie the layer you request from geoserver) in this case? Is it called “latest”. If the published layer is not named “latest” can you try temporarily renaming it to “latest” and see if that affects anything.

On Fri, Aug 9, 2013 at 8:49 AM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Thanks, Andrea and Justin. I’m not quite following the flow of the code yet, but trying. If Justin has a chance to review my description and log excerpts at some point, that would be great, too. I appreciate your response.

  • Mike Grogan

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

On Thu, Aug 8, 2013 at 1:45 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Aug 8, 2013 at 7:28 PM, Mike Grogan <d.michael.grogan@anonymised.com> wrote:

Can anyone point me to the point in the source where these queries are made to determine the times for the time dimensions for WMS GetCapabilities and postgis stores? The problem described in my earlier post continues to perplex me … and I can’t find the location in the source to look for myself. I have tried!

GeoServer code almost never knows about the nature of the underlying storage, it uses the GeoTools API
to access data, in this case, it’s using a UniqueVisitor if possible (or a min/max one depending on the representation type):

https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L947

This in turn should lead to the encoding of a aggregate function here:

https://github.com/geotools/geotools/blob/master/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L1279

However, there might be some hiccups in the intermediary classes between the first and the second code bits,
maybe a wrapper that does reprojection, crs forcing or something like that misses the appropriate delegation.
Which is actually something Justin (cc’ed) tried to fix some time ago, but not sure in which branch that work landed?
It might be that 2.4 beta does not exhibit this problem anymore.

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