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