[Geoserver-users] geoserver time support for vector data

Jacinto_Estima · April 13, 2014, 11:21am

Hi everyone,

I have a shapefile with measurement points and for each point a list of time-series measurements. My question is what is the best way to publish these data in a Geoserver? These data are stored in a PostGIS database where I have one point table to represent the measurement stations and another table that has all the measurements for each station (they are related through a stationID). Apparently the only possibility is to use complex features through application schemas.

I just wanted to check with the list if there are other possibilities/solutions.

Thanks in advance,
Jacinto

Andrea_Aime4 · April 13, 2014, 12:28pm

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima <jacinto.estima@anonymised.com>wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view (
http://docs.geoserver.org/stable/en/user/data/database/sqlview.html)
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I'm familiar with).

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

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

Jacinto_Estima · April 14, 2014, 7:41am

Thanks Andrea. I’ll try that approach. BTW, is there any progress on the SOS implementation?

Jacinto

···

On 04/13/2014 04:28 PM, Andrea Aime wrote:

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view (http://docs.geoserver.org/stable/en/user/data/database/sqlview.html)
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I’m familiar with).

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

Andrea_Aime4 · April 14, 2014, 7:53am

On Mon, Apr 14, 2014 at 9:41 AM, Jacinto Estima <jacinto.estima@anonymised.com>wrote:

Thanks Andrea. I'll try that approach. BTW, is there any progress on the
SOS implementation?

I believe it got abandoned because the effort required was too great for
the resources at hand.
Cc'ing Rini and Ben, I believe they were involved in that project

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

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

Ben_Caradoc-Davies · April 14, 2014, 8:04am

On 14/04/14 15:53, Andrea Aime wrote:

On Mon, Apr 14, 2014 at 9:41 AM, Jacinto Estima
<jacinto.estima@anonymised.com <mailto:jacinto.estima@anonymised.com>> wrote:
Thanks Andrea. I'll try that approach. BTW, is there any progress on
the SOS implementation?
I believe it got abandoned because the effort required was too great for
the resources at hand.
Cc'ing Rini and Ben, I believe they were involved in that project

I do not think the GeoServer SOS proposal got past a review of what would be required for a new service that would sit well within the GeoServer suite. If I recall correctly, the project was implemented with 52North SOS.

Kind regards,

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Jacinto_Estima · May 6, 2014, 8:21am

Thanks Andrea,

I did what you suggested but the rendering is very very slow and sometimes is even not showing anything.

Here is the query I am using to build the sqlview:
select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

Are you seeing anything wrong with the query?
Do you think I would have good results using the app-schema?

Thanks a lot,
Jacinto

···

On 04/13/2014 04:28 PM, Andrea Aime wrote:

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view (http://docs.geoserver.org/stable/en/user/data/database/sqlview.html)
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I’m familiar with).

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

Simone_Giannecchini3 · May 6, 2014, 9:59am

Simple check:

- change geoserver logging level to verbose
- send a request
- extract SQL query from log file
- send the query to Postgis directly

If the query is correct/expected but slow when executed directly in
Postgis then I would probably rethink/review the table layout.
Otherwise we might want to figure out why GeoServer is not doing the
right thing.

Regards,
Simone Giannecchini

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
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 Tue, May 6, 2014 at 10:21 AM, Jacinto Estima
<jacinto.estima@anonymised.com> wrote:

Thanks Andrea,

I did what you suggested but the rendering is very very slow and sometimes
is even not showing anything.

Here is the query I am using to build the sqlview:
select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction,
b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

Are you seeing anything wrong with the query?
Do you think I would have good results using the app-schema?

Thanks a lot,
Jacinto

On 04/13/2014 04:28 PM, Andrea Aime wrote:

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima <jacinto.estima@anonymised.com>
wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view
(http://docs.geoserver.org/stable/en/user/data/database/sqlview.html)
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I'm familiar with).

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

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

------------------------------------------------------------------------------
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

Jacinto_Estima · May 6, 2014, 1:35pm

Thank you very much Simone.

Bellow is the log file.
I ran the 2 select instructions highlighted below and got “2012-12-31 23:00:00” as a result for the first one and nothing for the second so I assume that something is wrong with the request. And I am also not getting the point of the where clause in the second request.

Thanks a lot for your help,
Jacinto

2014-05-06 17:12:12,000 INFO [geoserver.wms] -
Request: getMap
Filter = null
Buffer = 0
Format = image/png
Palette = null
Tiled = false
SRS = EPSG:4326
FeatureVersion = null
Styles = [StyleImpl[ name=point]]
Layers = [org.geoserver.wms.MapLayerInfo@anonymised.com]
MaxFeatures = null
Crs = GEOGCS[“WGS 84”,
DATUM[“World Geodetic System 1984”,
SPHEROID[“WGS 84”, 6378137.0, 298.257223563, AUTHORITY[“EPSG”,“7030”]],
AUTHORITY[“EPSG”,“6326”]],
PRIMEM[“Greenwich”, 0.0, AUTHORITY[“EPSG”,“8901”]],
UNIT[“degree”, 0.017453292519943295],
AXIS[“Geodetic longitude”, EAST],
AXIS[“Geodetic latitude”, NORTH],
AUTHORITY[“EPSG”,“4326”]]
Bbox = SRSEnvelope[76.21345703125 : 131.47654296875, 38.03818359375 : 56.76181640625]
RemoteOwsType = null
RemoteOwsURL = null
Env = {}
FormatOptions = {}
Angle = 0.0
CQLFilter = null
Elevation =
FeatureId = null
StartIndex = null
ViewParams = null
BgColor = java.awt.Color[r=255,g=255,b=255]
Transparent = false
SldBody = null
ValidateSchema = false
Sld = null
SldVersion = null
TilesOrigin = null
Filters = null
Exceptions = SE_XML
Height = 330
Width = 974
Time = [null]
Version = 1.1.1
RawKvp = {BBOX=76.21345703125,38.03818359375,131.47654296875,56.76181640625, VERSION=1.1.1, FORMAT=image/png, SERVICE=WMS, HEIGHT=330, REQUEST=GetMap, LAYERS=test:wind_stations, STYLES=, WIDTH=974, SRS=EPSG:4326}
BaseUrl = http://irena.masdar.ac.ae:8080/geoserver/
RequestCharset = UTF-8
Request = GetMap
Get = true
2014-05-06 17:12:12,005 DEBUG [wms.map] - Writing png image …
2014-05-06 17:12:12,005 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,005 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:12,113 DEBUG [wms.map] - Writing png image … done!
2014-05-06 17:12:12,114 DEBUG [geoserver.filters] - Not compressing output for mimetype: image/png
2014-05-06 17:12:12,114 DEBUG [filter.GeoServerSecurityContextPersistenceFilter$1] - SecurityContextHolder now cleared, as request processing completed
2014-05-06 17:12:12,136 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,136 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,137 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,137 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,138 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,138 DEBUG [ows.OWSHandlerMapping] - Mapping [/test/wms] to HandlerExecutionChain with handler [org.geoserver.ows.Dispatcher@anonymised.com] and 1 interceptor
2014-05-06 17:12:12,140 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,140 INFO [geoserver.wms] -
Request: getServiceInfo
2014-05-06 17:12:12,145 DEBUG [geoserver.ows] - Getting layers and styles from LAYERS and STYLES
2014-05-06 17:12:12,145 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:12,145 DEBUG [geoserver.ows] - establishing point style for test:wind_stations
2014-05-06 17:12:12,150 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,150 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,151 DEBUG [geoserver.wms] - setting up map
2014-05-06 17:12:12,152 DEBUG [geotools.jdbc] - CREATE CONNECTION
2014-05-06 17:12:12,154 DEBUG [geotools.jdbc] - SELECT max(“measure_time”) FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable”
2014-05-06 17:12:32,131 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2014-05-06 17:12:32,132 DEBUG [wms.map] - setting up 974x330 image
2014-05-06 17:12:32,132 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,132 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,135 DEBUG [geotools.rendering] - Computed scale denominator: 2.2557416347269792E7
2014-05-06 17:12:32,135 DEBUG [geotools.rendering] - Processing 1 stylers for http://www.test.org:wind_stations
2014-05-06 17:12:32,136 DEBUG [geotools.rendering] - creating rules for scale denominator - 22,557,416.347
2014-05-06 17:12:32,136 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,136 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,137 TRACE [geotools.styling] - ENTRY
2014-05-06 17:12:32,137 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,137 DEBUG [geotools.rendering] - Expanding rendering area by 4 pixels to consider stroke width
2014-05-06 17:12:32,137 DEBUG [geotools.rendering] - Querying layer http://www.test.org:wind_stations with bbox: ReferencedEnvelope[75.98650390625001 : 131.70349609375, 37.81123046875 : 56.98876953125]
2014-05-06 17:12:32,140 DEBUG [geotools.jdbc] - CREATE CONNECTION
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 2
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 14
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 3
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 21
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 4
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LogicFilter
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LiteralExpression
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LiteralExpression
2014-05-06 17:12:32,145 DEBUG [geotools.jdbc] - SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))
2014-05-06 17:12:38,531 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2014-05-06 17:12:38,531 DEBUG [geotools.rendering] - Style cache hit ratio: NaN , hits 0, requests 0
2014-05-06 17:12:38,531 INFO [geoserver.wms] -
Request: getMap
Filter = null
Buffer = 0
Format = image/png
Palette = null
Tiled = false
SRS = EPSG:4326
FeatureVersion = null
Styles = [StyleImpl[ name=point]]
Layers = [org.geoserver.wms.MapLayerInfo@anonymised.com]
MaxFeatures = null
Crs = GEOGCS[“WGS 84”,
DATUM[“World Geodetic System 1984”,
SPHEROID[“WGS 84”, 6378137.0, 298.257223563, AUTHORITY[“EPSG”,“7030”]],
AUTHORITY[“EPSG”,“6326”]],
PRIMEM[“Greenwich”, 0.0, AUTHORITY[“EPSG”,“8901”]],
UNIT[“degree”, 0.017453292519943295],
AXIS[“Geodetic longitude”, EAST],
AXIS[“Geodetic latitude”, NORTH],
AUTHORITY[“EPSG”,“4326”]]
Bbox = SRSEnvelope[76.21345703125 : 131.47654296875, 38.03818359375 : 56.76181640625]
RemoteOwsType = null
RemoteOwsURL = null
Env = {}
FormatOptions = {}
Angle = 0.0
CQLFilter = null
Elevation =
FeatureId = null
StartIndex = null
ViewParams = null
BgColor = java.awt.Color[r=255,g=255,b=255]
Transparent = false
SldBody = null
ValidateSchema = false
Sld = null
SldVersion = null
TilesOrigin = null
Filters = null
Exceptions = SE_XML
Height = 330
Width = 974
Time = [null]
Version = 1.1.1
RawKvp = {BBOX=76.21345703125,38.03818359375,131.47654296875,56.76181640625, VERSION=1.1.1, FORMAT=image/png, SERVICE=WMS, HEIGHT=330, REQUEST=GetMap, LAYERS=test:wind_stations, STYLES=, WIDTH=974, SRS=EPSG:4326}
BaseUrl = http://irena.masdar.ac.ae:8080/geoserver/
RequestCharset = UTF-8
Request = GetMap
Get = true
2014-05-06 17:12:38,535 DEBUG [wms.map] - Writing png image …
2014-05-06 17:12:38,535 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:38,535 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:38,645 DEBUG [wms.map] - Writing png image … done!
2014-05-06 17:12:38,645 DEBUG [geoserver.filters] - Not compressing output for mimetype: image/png
2014-05-06 17:12:38,645 DEBUG [filter.GeoServerSecurityContextPersistenceFilter$1] - SecurityContextHolder now cleared, as request processing completed
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,444 DEBUG [org.geoserver] - Thread 3937 locking in mode WRITE
2014-05-06 17:13:05,444 DEBUG [org.geoserver] - Thread 3937 got the lock in mode WRITE
2014-05-06 17:13:05,462 DEBUG [geoserver.filters] - Compressing output for mimetype: text/plain

···

On 05/06/2014 01:59 PM, Simone Giannecchini wrote:

Simple check:

- change geoserver logging level to verbose
- send a request
- extract SQL query from log file
- send the query to Postgis directly

If the query is correct/expected but slow when executed directly in
Postgis then I would probably rethink/review the table layout.
Otherwise we might want to figure out why GeoServer is not doing the
right thing.

Regards,
Simone Giannecchini
==
Meet us at GEO Business 2014! in London! Visit [http://goo.gl/fES3aK](http://goo.gl/fES3aK)
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://www.geo-solutions.it)
[http://twitter.com/geosolutions_it](http://twitter.com/geosolutions_it)

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

On Tue, May 6, 2014 at 10:21 AM, Jacinto Estima
[<jacinto.estima@anonymised.com>](mailto:jacinto.estima@anonymised.com) wrote:

Thanks Andrea,

I did what you suggested but the rendering is very very slow and sometimes
is even not showing anything.

Here is the query I am using to build the sqlview:
select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction,
b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

Are you seeing anything wrong with the query?
Do you think I would have good results using the app-schema?

Thanks a lot,
Jacinto

On 04/13/2014 04:28 PM, Andrea Aime wrote:

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima [<jacinto.estima@anonymised.com>](mailto:jacinto.estima@anonymised.com)
wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view
([http://docs.geoserver.org/stable/en/user/data/database/sqlview.html](http://docs.geoserver.org/stable/en/user/data/database/sqlview.html))
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I'm familiar with).

Cheers
Andrea

--
==
Meet us at GEO Business 2014! in London! Visit [http://goo.gl/fES3aK](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://www.geo-solutions.it)
[http://twitter.com/geosolutions_it](http://twitter.com/geosolutions_it)

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

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

Jacinto_Estima · May 8, 2014, 6:06am

Any idea/suggestion on the previous email?

If it doesn’t work that way I can think on two possible solutions:

develop a specific application schema for Geoserver considering that this is a case of complex features (having several measurements for each spatial location)
Use a different software that implements the SOS standard treating each measurement location as a sensor

Any comments regarding other experiences/issues on this would be much appreciated.

Thanks,
Jacinto

···

On 05/06/2014 05:35 PM, Jacinto Estima wrote:

Thank you very much Simone.

Bellow is the log file.
I ran the 2 select instructions highlighted below and got “2012-12-31 23:00:00” as a result for the first one and nothing for the second so I assume that something is wrong with the request. And I am also not getting the point of the where clause in the second request.

Thanks a lot for your help,
Jacinto

2014-05-06 17:12:12,000 INFO [geoserver.wms] -
Request: getMap
Filter = null
Buffer = 0
Format = image/png
Palette = null
Tiled = false
SRS = EPSG:4326
FeatureVersion = null
Styles = [StyleImpl[ name=point]]
Layers = [org.geoserver.wms.MapLayerInfo@anonymised.com]
MaxFeatures = null
Crs = GEOGCS[“WGS 84”,
DATUM[“World Geodetic System 1984”,
SPHEROID[“WGS 84”, 6378137.0, 298.257223563, AUTHORITY[“EPSG”,“7030”]],
AUTHORITY[“EPSG”,“6326”]],
PRIMEM[“Greenwich”, 0.0, AUTHORITY[“EPSG”,“8901”]],
UNIT[“degree”, 0.017453292519943295],
AXIS[“Geodetic longitude”, EAST],
AXIS[“Geodetic latitude”, NORTH],
AUTHORITY[“EPSG”,“4326”]]
Bbox = SRSEnvelope[76.21345703125 : 131.47654296875, 38.03818359375 : 56.76181640625]
RemoteOwsType = null
RemoteOwsURL = null
Env = {}
FormatOptions = {}
Angle = 0.0
CQLFilter = null
Elevation =
FeatureId = null
StartIndex = null
ViewParams = null
BgColor = java.awt.Color[r=255,g=255,b=255]
Transparent = false
SldBody = null
ValidateSchema = false
Sld = null
SldVersion = null
TilesOrigin = null
Filters = null
Exceptions = SE_XML
Height = 330
Width = 974
Time = [null]
Version = 1.1.1
RawKvp = {BBOX=76.21345703125,38.03818359375,131.47654296875,56.76181640625, VERSION=1.1.1, FORMAT=image/png, SERVICE=WMS, HEIGHT=330, REQUEST=GetMap, LAYERS=test:wind_stations, STYLES=, WIDTH=974, SRS=EPSG:4326}
BaseUrl = http://irena.masdar.ac.ae:8080/geoserver/
RequestCharset = UTF-8
Request = GetMap
Get = true
2014-05-06 17:12:12,005 DEBUG [wms.map] - Writing png image …
2014-05-06 17:12:12,005 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,005 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:12,113 DEBUG [wms.map] - Writing png image … done!
2014-05-06 17:12:12,114 DEBUG [geoserver.filters] - Not compressing output for mimetype: image/png
2014-05-06 17:12:12,114 DEBUG [filter.GeoServerSecurityContextPersistenceFilter$1] - SecurityContextHolder now cleared, as request processing completed
2014-05-06 17:12:12,136 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,136 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,137 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,137 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,138 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/test/wms]
2014-05-06 17:12:12,138 DEBUG [ows.OWSHandlerMapping] - Mapping [/test/wms] to HandlerExecutionChain with handler [org.geoserver.ows.Dispatcher@anonymised.com] and 1 interceptor
2014-05-06 17:12:12,140 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,140 INFO [geoserver.wms] -
Request: getServiceInfo
2014-05-06 17:12:12,145 DEBUG [geoserver.ows] - Getting layers and styles from LAYERS and STYLES
2014-05-06 17:12:12,145 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:12,145 DEBUG [geoserver.ows] - establishing point style for test:wind_stations
2014-05-06 17:12:12,150 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,150 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:12,151 DEBUG [geoserver.wms] - setting up map
2014-05-06 17:12:12,152 DEBUG [geotools.jdbc] - CREATE CONNECTION
2014-05-06 17:12:12,154 DEBUG [geotools.jdbc] - SELECT max(“measure_time”) FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable”
2014-05-06 17:12:32,131 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2014-05-06 17:12:32,132 DEBUG [wms.map] - setting up 974x330 image
2014-05-06 17:12:32,132 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,132 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,134 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[mongolia], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:32,135 DEBUG [geotools.rendering] - Computed scale denominator: 2.2557416347269792E7
2014-05-06 17:12:32,135 DEBUG [geotools.rendering] - Processing 1 stylers for http://www.test.org:wind_stations
2014-05-06 17:12:32,136 DEBUG [geotools.rendering] - creating rules for scale denominator - 22,557,416.347
2014-05-06 17:12:32,136 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,136 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,137 TRACE [geotools.styling] - ENTRY
2014-05-06 17:12:32,137 DEBUG [geotools.styling] - creating defaultMark
2014-05-06 17:12:32,137 DEBUG [geotools.rendering] - Expanding rendering area by 4 pixels to consider stroke width
2014-05-06 17:12:32,137 DEBUG [geotools.rendering] - Querying layer http://www.test.org:wind_stations with bbox: ReferencedEnvelope[75.98650390625001 : 131.70349609375, 37.81123046875 : 56.98876953125]
2014-05-06 17:12:32,140 DEBUG [geotools.jdbc] - CREATE CONNECTION
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 2
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 14
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 3
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 21
2014-05-06 17:12:32,143 TRACE [geotools.core] - ENTRY 4
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LogicFilter
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting SQL ComparisonFilter
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LiteralExpression
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting PropertyName
2014-05-06 17:12:32,144 DEBUG [geotools.filter] - exporting LiteralExpression
2014-05-06 17:12:32,145 DEBUG [geotools.jdbc] - SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))
2014-05-06 17:12:38,531 DEBUG [geotools.jdbc] - CLOSE CONNECTION
2014-05-06 17:12:38,531 DEBUG [geotools.rendering] - Style cache hit ratio: NaN , hits 0, requests 0
2014-05-06 17:12:38,531 INFO [geoserver.wms] -
Request: getMap
Filter = null
Buffer = 0
Format = image/png
Palette = null
Tiled = false
SRS = EPSG:4326
FeatureVersion = null
Styles = [StyleImpl[ name=point]]
Layers = [org.geoserver.wms.MapLayerInfo@anonymised.com]
MaxFeatures = null
Crs = GEOGCS[“WGS 84”,
DATUM[“World Geodetic System 1984”,
SPHEROID[“WGS 84”, 6378137.0, 298.257223563, AUTHORITY[“EPSG”,“7030”]],
AUTHORITY[“EPSG”,“6326”]],
PRIMEM[“Greenwich”, 0.0, AUTHORITY[“EPSG”,“8901”]],
UNIT[“degree”, 0.017453292519943295],
AXIS[“Geodetic longitude”, EAST],
AXIS[“Geodetic latitude”, NORTH],
AUTHORITY[“EPSG”,“4326”]]
Bbox = SRSEnvelope[76.21345703125 : 131.47654296875, 38.03818359375 : 56.76181640625]
RemoteOwsType = null
RemoteOwsURL = null
Env = {}
FormatOptions = {}
Angle = 0.0
CQLFilter = null
Elevation =
FeatureId = null
StartIndex = null
ViewParams = null
BgColor = java.awt.Color[r=255,g=255,b=255]
Transparent = false
SldBody = null
ValidateSchema = false
Sld = null
SldVersion = null
TilesOrigin = null
Filters = null
Exceptions = SE_XML
Height = 330
Width = 974
Time = [null]
Version = 1.1.1
RawKvp = {BBOX=76.21345703125,38.03818359375,131.47654296875,56.76181640625, VERSION=1.1.1, FORMAT=image/png, SERVICE=WMS, HEIGHT=330, REQUEST=GetMap, LAYERS=test:wind_stations, STYLES=, WIDTH=974, SRS=EPSG:4326}
BaseUrl = http://irena.masdar.ac.ae:8080/geoserver/
RequestCharset = UTF-8
Request = GetMap
Get = true
2014-05-06 17:12:38,535 DEBUG [wms.map] - Writing png image …
2014-05-06 17:12:38,535 DEBUG [config.impl] - Could not locate service of type interface org.geoserver.wms.WMSInfo in workspace WorkspaceInfoImpl[test], available services were [WCSInfoImpl[WCS], WFSInfoImpl[WFS], WMSInfoImpl[WMS]]
2014-05-06 17:12:38,535 DEBUG [geotools.styling] - number of fts set 1
2014-05-06 17:12:38,645 DEBUG [wms.map] - Writing png image … done!
2014-05-06 17:12:38,645 DEBUG [geoserver.filters] - Not compressing output for mimetype: image/png
2014-05-06 17:12:38,645 DEBUG [filter.GeoServerSecurityContextPersistenceFilter$1] - SecurityContextHolder now cleared, as request processing completed
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,443 TRACE [ows.OWSHandlerMapping] - No handler mapping found for [/web/]
2014-05-06 17:13:05,444 DEBUG [org.geoserver] - Thread 3937 locking in mode WRITE
2014-05-06 17:13:05,444 DEBUG [org.geoserver] - Thread 3937 got the lock in mode WRITE
2014-05-06 17:13:05,462 DEBUG [geoserver.filters] - Compressing output for mimetype: text/plain

On 05/06/2014 01:59 PM, Simone Giannecchini wrote:

Simple check:

- change geoserver logging level to verbose
- send a request
- extract SQL query from log file
- send the query to Postgis directly

If the query is correct/expected but slow when executed directly in
Postgis then I would probably rethink/review the table layout.
Otherwise we might want to figure out why GeoServer is not doing the
right thing.

Regards,
Simone Giannecchini
==
Meet us at GEO Business 2014! in London! Visit [http://goo.gl/fES3aK](http://goo.gl/fES3aK)
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://www.geo-solutions.it)
[http://twitter.com/geosolutions_it](http://twitter.com/geosolutions_it)

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

On Tue, May 6, 2014 at 10:21 AM, Jacinto Estima
[<jacinto.estima@anonymised.com>](mailto:jacinto.estima@anonymised.com) wrote:

Thanks Andrea,

I did what you suggested but the rendering is very very slow and sometimes
is even not showing anything.

Here is the query I am using to build the sqlview:
select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction,
b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

Are you seeing anything wrong with the query?
Do you think I would have good results using the app-schema?

Thanks a lot,
Jacinto

On 04/13/2014 04:28 PM, Andrea Aime wrote:

On Sun, Apr 13, 2014 at 1:21 PM, Jacinto Estima [<jacinto.estima@anonymised.com>](mailto:jacinto.estima@anonymised.com)
wrote:

Hi everyone,

I have a shapefile with measurement points and for each point a list of
time-series measurements. My question is what is the best way to publish
these data in a Geoserver? These data are stored in a PostGIS database
where I have one point table to represent the measurement stations and
another table that has all the measurements for each station (they are
related through a stationID). Apparently the only possibility is to use
complex features through application schemas.

I just wanted to check with the list if there are other
possibilities/solutions.

I would create a sql view
([http://docs.geoserver.org/stable/en/user/data/database/sqlview.html](http://docs.geoserver.org/stable/en/user/data/database/sqlview.html))
that joins the two, then publish it as a single layer,
and enable the time dimension in the layer

This will work fine as long as all the measurement stations are performing
their sampling at the same times (which is normally true, at least
in the ground weather station case I'm familiar with).

Cheers
Andrea

--
==
Meet us at GEO Business 2014! in London! Visit [http://goo.gl/fES3aK](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://www.geo-solutions.it)
[http://twitter.com/geosolutions_it](http://twitter.com/geosolutions_it)

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

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

Andrea_Aime4 · May 8, 2014, 6:13am

On Tue, May 6, 2014 at 3:35 PM, Jacinto Estima <jacinto.estima@anonymised.com>wrote:

2014-05-06 17:12:32,145 DEBUG [geotools.jdbc] -

*SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D("geom"),
0.04539062499999318)),'base64') as "geom" FROM (select a.id_number, a.geom,
b.id_station, b.measure_time, b.wind_direction, b.wind_speed from
test.mesurement_stations as a left join test.mesurements as b on
a.id_number=b.id_station order by b.measure_time ) as "vtable" WHERE
("measure_time" = '2012-12-31T19:00:00Z' AND "measure_time" IS NOT NULL
AND "geom" && ST_GeomFromText('POLYGON ((75.98650390625001 37.81123046875,
75.98650390625001 56.98876953125, 131.70349609375 56.98876953125,
131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))', 2))*
2014-05-06 17:12:38,531 DEBUG [geotools.jdbc] - CLOSE CONNECTION

Have you tried the query manually? Does it return anything? Can you fit it
to make it return stuff?

About performance, you have indexes both on the geometry column, and the
time one, yes?

Also, is all of your server setup to work in GMT (postgres, geoserver and
so on)?

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

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

Jacinto_Estima · May 8, 2014, 7:22am

Thank Andrea for your quick reply.

I played with the request directly through pgadmin and using it as it comes from the log doesn’t return anything. Then I figured out the issue. In the last where clause, the request is asking for “measure_time” = ‘2012-12-31T19:00:00Z’ and there are no measurements for T19. I have no clue why geoserver is adding this time to the request. Do you have any hint on how to solve it? can this be parametrized? Find bellow a screen-shot of the SQL view definition.

Thanks again,
Jacinto

···

On 05/08/2014 10:13 AM, Andrea Aime wrote:

On Tue, May 6, 2014 at 3:35 PM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

2014-05-06 17:12:32,145 DEBUG [geotools.jdbc] - SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a

left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))
2014-05-06 17:12:38,531 DEBUG [geotools.jdbc] - CLOSE CONNECTION

Have you tried the query manually? Does it return anything? Can you fit it to make it return stuff?

About performance, you have indexes both on the geometry column, and the time one, yes?

Also, is all of your server setup to work in GMT (postgres, geoserver and so on)?

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

Jacinto_Estima · May 8, 2014, 8:07am

Something else to add:

I checked again the log file and there are two queries:

to get the max time available from the data

SELECT max(“measure_time”) FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable”

to get all the points for that max time

SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))

If I query directly in postgis, I get “2012-12-31 23:00:00” so I conclude that for some reason Geoserver is using the wrong time for the second request (the result from the first request should be used instead). Am I thinking right?

Thanks,
Jacinto

···

On 05/08/2014 11:22 AM, Jacinto Estima wrote:

Thank Andrea for your quick reply.

I played with the request directly through pgadmin and using it as it comes from the log doesn’t return anything. Then I figured out the issue. In the last where clause, the request is asking for “measure_time” = ‘2012-12-31T19:00:00Z’ and there are no measurements for T19. I have no clue why geoserver is adding this time to the request. Do you have any hint on how to solve it? can this be parametrized? Find bellow a screen-shot of the SQL view definition.

Thanks again,
Jacinto

On 05/08/2014 10:13 AM, Andrea Aime wrote:

On Tue, May 6, 2014 at 3:35 PM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

2014-05-06 17:12:32,145 DEBUG [geotools.jdbc] - SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a

left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time

) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))
2014-05-06 17:12:38,531 DEBUG [geotools.jdbc] - CLOSE CONNECTION

Have you tried the query manually? Does it return anything? Can you fit it to make it return stuff?

About performance, you have indexes both on the geometry column, and the time one, yes?

Also, is all of your server setup to work in GMT (postgres, geoserver and so on)?

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

Andrea_Aime4 · May 8, 2014, 8:11am

On Thu, May 8, 2014 at 10:07 AM, Jacinto Estima <jacinto.estima@anonymised.com>wrote:

Something else to add:

I checked again the log file and there are two queries:

1. to get the max time available from the data

SELECT max("measure_time") FROM (select a.id_number, a.geom, b.id_station,
b.measure_time, b.wind_direction, b.wind_speed from
test.mesurement_stations as a

left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as "vtable"

2. to get all the points for that max time

SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D("geom"),
0.04539062499999318)),'base64') as "geom" FROM (select a.id_number, a.geom,
b.id_station, b.measure_time, b.wind_direction, b.wind_speed from
test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as "vtable" WHERE (*"measure_time" = '2012-12-31T19:00:00Z'* AND
"measure_time" IS NOT NULL AND "geom" && ST_GeomFromText('POLYGON
((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125,
131.70349609375 56.98876953125, 131.70349609375 37.81123046875,
75.98650390625001 37.81123046875))', 2))

If I query directly in postgis, I get "*2012-12-31 23:00:00*" so I
conclude that for some reason Geoserver is using the wrong time for the
second request (the result from the first request should be used instead).
Am I thinking right?

Quite likely, GeoServer does not have an internal good handling of
timezones, that's why I was suggesting to have
everything running in GMT (the issue you have there looks like a timezone
shift).

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

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

Jacinto_Estima · May 8, 2014, 8:26am

It makes sense Andrea. Thanks a lot. I’ll change the time zone to GMT and will get back to you with the results.

···

On 05/08/2014 12:11 PM, Andrea Aime wrote:

On Thu, May 8, 2014 at 10:07 AM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

Something else to add:

I checked again the log file and there are two queries:

to get the max time available from the data

SELECT max(“measure_time”) FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a

left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable”

to get all the points for that max time

SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))

If I query directly in postgis, I get “2012-12-31 23:00:00” so I conclude that for some reason Geoserver is using the wrong time for the second request (the result from the first request should be used instead). Am I thinking right?

Quite likely, GeoServer does not have an internal good handling of timezones, that’s why I was suggesting to have
everything running in GMT (the issue you have there looks like a timezone shift).

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

Jacinto_Estima · May 12, 2014, 5:41am

It woks perfect now.
Thanks again!

Cheers,
Jacinto

···

On 05/08/2014 12:26 PM, Jacinto Estima wrote:

It makes sense Andrea. Thanks a lot. I’ll change the time zone to GMT and will get back to you with the results.

On 05/08/2014 12:11 PM, Andrea Aime wrote:

On Thu, May 8, 2014 at 10:07 AM, Jacinto Estima <jacinto.estima@anonymised.com> wrote:

Something else to add:

I checked again the log file and there are two queries:

to get the max time available from the data

SELECT max(“measure_time”) FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a

left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable”

to get all the points for that max time

SELECT encode(ST_AsBinary(ST_Simplify(ST_Force_2D(“geom”), 0.04539062499999318)),‘base64’) as “geom” FROM (select a.id_number, a.geom, b.id_station, b.measure_time, b.wind_direction, b.wind_speed from test.mesurement_stations as a
left join test.mesurements as b
on a.id_number=b.id_station
order by b.measure_time
) as “vtable” WHERE (“measure_time” = ‘2012-12-31T19:00:00Z’ AND “measure_time” IS NOT NULL AND “geom” && ST_GeomFromText(‘POLYGON ((75.98650390625001 37.81123046875, 75.98650390625001 56.98876953125, 131.70349609375 56.98876953125, 131.70349609375 37.81123046875, 75.98650390625001 37.81123046875))’, 2))

If I query directly in postgis, I get “2012-12-31 23:00:00” so I conclude that for some reason Geoserver is using the wrong time for the second request (the result from the first request should be used instead). Am I thinking right?

Quite likely, GeoServer does not have an internal good handling of timezones, that’s why I was suggesting to have
everything running in GMT (the issue you have there looks like a timezone shift).

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

[Geoserver-users] geoserver time support for vector data

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Regards,
Simone Giannecchini

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

[Geoserver-users] geoserver time support for vector data

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

Regards, Simone Giannecchini

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

== Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK for more information.

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Regards,
Simone Giannecchini

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

==
Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.