*UPDATE:* I went to the logs and found this. Looks like Geoserver doesn’t
support the tstzrange datatype.
2017-10-10 20:38:03,371 WARN [geotools.jdbc] - Could not find mapping
for 'date_range', ignoring the column and setting the feature type
read only
2017-10-10 20:38:57,543 WARN [geotools.jdbc] - Could not find mapping
for 'active_dates', ignoring the column and setting the feature type
read only
On Tue, Oct 10, 2017 at 2:50 PM, Gavin Medley <[email protected]> wrote:
> Hi all,
>
> I am using Geoserver to make a view (Layer) from my PostGIS database using
> the query below. Unfortunately, I can’t get the tstzrange field to
> appear. Is there a limitation here that I’m not seeing? I don’t get an
> error but the field just doesn’t appear in the “Attributes” table after
> clicking “Refresh.” The query works fine in PGAdmin and in the CLI. What am
> I missing?
>
> WITH
> metric_types AS
> (SELECT station, station_type, tstzrange(min(lower(duration)),
> max(upper(duration))) AS active_dates
> FROM (
> select metric_station.name as station,
> metric_type.name as station_type,
> duration,
> ROW_NUMBER() OVER (PARTITION BY metric_station_id, metric_type_id ORDER
> BY lower(duration) DESC) AS first_row,
> ROW_NUMBER() OVER (PARTITION BY metric_station_id, metric_type_id ORDER
> BY lower(duration) ASC) as last_row
> FROM metrics.active_station_status
> JOIN metric_type ON metric_type_id = metric_type.id
> JOIN metric_station ON metric_station_id = metric_station.id)
> AS station_sub
> WHERE first_row = 1 or last_row = 1
> GROUP BY station, station_type)
> SELECT
> t3sstations.idstn,
> metric_types.station,
> t3sstations.stnname,
> t3sstations.stnnickname,
> ST_SETSRID(ST_MAKEPOINT(t3sstations.stnlong, t3sstations.stnlat), 4326)
> AS lonlat_geom,
> t3sstations.stnlong,
> t3sstations.stnlat,
> t3sstations.stnelev,
> stationtype.label,
> t3sstations.stninsdate,
> metric_types.station_type AS sensor_type,
> metric_types.active_dates
> FROM
> t3sstations
> INNER JOIN metric_types ON t3sstations.fourcharid=metric_types.station
> LEFT JOIN stationtype ON stationtype.id = t3sstations.idstationtype
>
>
> --
> Gavin Medley
> Software Engineer, UNAVCO
> 6350 Nautilus Drive
> Boulder, CO 80301-5394
> Mobile: 505-819-1270 <(505)%20819-1270>
> [email protected]
>
>
>
--
Gavin Medley
Software Engineer, UNAVCO
6350 Nautilus Drive
Boulder, CO 80301-5394
Mobile: 505-819-1270
[email protected]
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
