MESONET RI.NOSA.SPATIAL

From NGDC Wiki
Jump to: navigation, search

MESONET Longitude Maximum

select 'MESONET' dataset, 'Longitude' parameter, 'Maximum' statistic, to_char(min(gdate),'DD-MON-YY') segment_start,
to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LONMAX - lag(LONMAX, 1) over (order by gdate)) >.2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LONMAX <>0))
group by segment_counter
order by segment_counter nulls first;

DATASET PARAMETER STATIST SEGMENT_S SEGMENT_E     LATMIN     LATMAX     LONMIN     LONMAX NUMBER_OF_GRANULES
------- --------- ------- --------- --------- ---------- ---------- ---------- ---------- ------------------
MESONET Longitude Maximum 26-APR-05 08-AUG-05  -44.48917    87.1161    -177.36      176.2                 96
MESONET Longitude Maximum 09-AUG-05 16-NOV-05   -45.8379     71.323    -177.36   176.4905                 94
MESONET Longitude Maximum 17-NOV-05 23-NOV-05   -45.8379     71.323    -177.36   176.7528                  7
MESONET Longitude Maximum 24-NOV-05 19-MAY-06   -45.9863     71.323    -177.36     177.65                164
MESONET Longitude Maximum 20-MAY-06 29-NOV-07      -63.4     71.323    -177.36   178.0102                166

5 rows selected.

MESONET Longitude Minimum

select 'MESONET' dataset, 'Longitude' parameter, 'Minimum' statistic, to_char(min(gdate),'DD-MON-YY') segment_start,
to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LONMIN - lag(LONMIN, 1) over (order by gdate)) >2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LONMIN <>0))
group by segment_counter
order by segment_counter nulls first;

DATASET PARAMETER STATIST SEGMENT_S SEGMENT_E     LATMIN     LATMAX     LONMIN     LONMAX NUMBER_OF_GRANULES
------- --------- ------- --------- --------- ---------- ---------- ---------- ---------- ------------------
MESONET Longitude Minimum 26-APR-05 26-MAY-05  -44.48917     71.323    -170.72      176.2                 28
MESONET Longitude Minimum 27-MAY-05 29-NOV-07      -63.4    87.1161    -177.36   178.0102                499

2 rows selected.

MESONET Latitude Maximum

select 'MESONET' dataset, 'Latitude' parameter, 'Maximum' statistic, to_char(min(gdate),'DD-MON-YY') segment_start,
to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LATMAX - lag(LATMAX, 1) over (order by gdate)) >1 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LATMAX <>0))
group by segment_counter
order by segment_counter nulls first;

DATASET PARAMETE STATIST SEGMENT_S SEGMENT_E     LATMIN     LATMAX     LONMIN     LONMAX NUMBER_OF_GRANULES
------- -------- ------- --------- --------- ---------- ---------- ---------- ---------- ------------------
MESONET Latitude Maximum 26-APR-05 10-JUL-05  -44.48917     71.323    -177.36      176.2                 71
MESONET Latitude Maximum 12-JUL-05 21-JUL-05  -44.48917    87.1161    -177.36      176.2                 10
MESONET Latitude Maximum 22-JUL-05 29-NOV-07      -63.4     71.323    -177.36   178.0102                446

3 rows selected.

MESONET Latitude Minimum

select 'MESONET' dataset, 'Latitude' parameter, 'Minimum' statistic, to_char(min(gdate),'DD-MON-YY') segment_start,
to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LATMIN - lag(LATMIN, 1) over (order by gdate)) >2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LATMIN <>0))
group by segment_counter
order by segment_counter nulls first;

DATASET PARAMETE STATIST SEGMENT_S SEGMENT_E     LATMIN     LATMAX     LONMIN     LONMAX NUMBER_OF_GRANULES
------- -------- ------- --------- --------- ---------- ---------- ---------- ---------- ------------------
MESONET Latitude Minimum 26-APR-05 28-JUN-06   -45.9863    87.1161    -177.36   178.0102                401
MESONET Latitude Minimum 29-JUN-06 29-NOV-07      -63.4     71.323    -177.36   178.0102                126

2 rows selected.

MESONET Union All

select dataset, parameter, statistic, segment_start, segment_end, latmin, latmax, lonmin, lonmax, number_of_granules
from (select 'MESONET' dataset, 'Longitude' parameter, 'Maximum' statistic,
to_char(min(gdate),'DD-MON-YY')   segment_start, to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LONMAX - lag(LONMAX, 1) over (order by gdate)) >.2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LONMAX <>0))
group by segment_counter
order by segment_counter nulls first)
union all
select dataset, parameter, statistic, segment_start, segment_end, latmin, latmax, lonmin, lonmax, number_of_granules
from (select 'MESONET' dataset, 'Longitude' parameter, 'Minimum' statistic,
to_char(min(gdate),'DD-MON-YY') segment_start, to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LONMIN - lag(LONMIN, 1) over (order by gdate)) >2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LONMIN <>0))
group by segment_counter
order by segment_counter nulls first)
union all
select dataset, parameter, statistic, segment_start, segment_end, latmin, latmax, lonmin, lonmax, number_of_granules
from (select 'MESONET' dataset, 'Latitude' parameter, 'Maximum' statistic,
to_char(min(gdate),'DD-MON-YY')  segment_start, to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LATMAX - lag(LATMAX, 1) over (order by gdate)) >1 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LATMAX <>0))
group by segment_counter
order by segment_counter nulls first)
union all
select dataset, parameter, statistic, segment_start, segment_end, latmin, latmax, lonmin, lonmax, number_of_granules
from (select 'MESONET' dataset, 'Latitude' parameter, 'Minimum' statistic,
to_char(min(gdate),'DD-MON-YY') segment_start, to_char(max(gdate),'DD-MON-YY') segment_end,
min(LATMIN) LATMIN, max(LATMAX) LATMAX, min(LONMIN) LONMIN, max(LONMAX) LONMAX,
count(*) number_of_granules
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
sum(segment_start) over (order by gdate) segment_counter
from (select granule, gdate, LATMIN, LATMAX, LONMIN, LONMAX,
case when abs(LATMIN - lag(LATMIN, 1) over (order by gdate)) >2 then 1 else null end segment_start
from ri.nosa_spatial
where granule = 'MESONET_TSQP' and LATMIN <>0))
group by segment_counter
order by segment_counter nulls first);

DATASET PARAMETER STATIST SEGMENT_S SEGMENT_E     LATMIN     LATMAX     LONMIN     LONMAX NUMBER_OF_GRANULES
------- --------- ------- --------- --------- ---------- ---------- ---------- ---------- ------------------
MESONET Longitude Maximum 26-APR-05 08-AUG-05  -44.48917    87.1161    -177.36      176.2                 96
MESONET Longitude Maximum 09-AUG-05 16-NOV-05   -45.8379     71.323    -177.36   176.4905                 94
MESONET Longitude Maximum 17-NOV-05 23-NOV-05   -45.8379     71.323    -177.36   176.7528                  7
MESONET Longitude Maximum 24-NOV-05 19-MAY-06   -45.9863     71.323    -177.36     177.65                164
MESONET Longitude Maximum 20-MAY-06 29-NOV-07      -63.4     71.323    -177.36   178.0102                166
MESONET Longitude Minimum 26-APR-05 26-MAY-05  -44.48917     71.323    -170.72      176.2                 28
MESONET Longitude Minimum 27-MAY-05 29-NOV-07      -63.4    87.1161    -177.36   178.0102                499
MESONET Latitude  Maximum 26-APR-05 10-JUL-05  -44.48917     71.323    -177.36      176.2                 71
MESONET Latitude  Maximum 12-JUL-05 21-JUL-05  -44.48917    87.1161    -177.36      176.2                 10
MESONET Latitude  Maximum 22-JUL-05 29-NOV-07      -63.4     71.323    -177.36   178.0102                446
MESONET Latitude  Minimum 26-APR-05 28-JUN-06   -45.9863    87.1161    -177.36   178.0102                401
MESONET Latitude  Minimum 29-JUN-06 29-NOV-07      -63.4     71.323    -177.36   178.0102                126

12 rows selected.



Spatial_Extents_Queries