OSMC

From NGDCWiki

Jump to: navigation, search

Contents

DIF Views into OSMC

See Service Home Page for NDBC Service Information.

GetCapabilities

The SOS getCapabilities request returns a list of available stations with locations, available time periods, sensors and observed parameters. The sections included below show the queries required for the NDBC and OSMC database. There are a couple of problems:

  1. The present OSMC database design does not include sensor information. This information might be extracted through a link or a query against the NDBC.SENSOR table.
  2. It is difficult to identify NDBC platforms in the OSMC database because the vast majority of the platform "owners" are unknown. Not sure why this is.

Query (NDBC):

select lower(p.name) as name
, p.description
, X(p.location) as lon
, Y(p.location) as lat
, o.shortname as owner
, osv.shortTypeName as shortstname
, osv.sensorNumber as sensornumber
, osv.sensorType as stname
, date_format(osv.firstObsDate,'%Y-%m-%dT%H:%iZ') as firstObsDate
, date_format(osv.lastObsDate,'%Y-%m-%dT%H:%iZ') as lastObsDate 
from    platform p
left join organization o on p.organizationId = o.id 
inner join sensor s on p.id = s.platformId 
inner join observationSummaryView osv on s.id = osv.sensorId 
order by p.name, osv.sensorType;

Response (NDBC):

The dynamic part of the getCapabilities response is a list of stations in this XML (see http://sdf.ndbc.noaa.gov/sos/server.php?request=GetCapabilities&service=SOS):

<ObservationOffering gml:id="offering-41033">
   <gml:name>urn:x-noaa:def:station:ndbc::41033</gml:name>
   <gml:srsName>EPSG:4326</gml:srsName>
   <gml:boundedBy>
      <gml:Envelope srsName="EPSG:4326">
         <gml:lowerCorner>32.28 -80.41</gml:lowerCorner>
         <gml:upperCorner>32.28 -80.41</gml:upperCorner>
      </gml:Envelope>
   </gml:boundedBy>
   <time>
      <gml:TimePeriod>
         <gml:beginPosition>2007-11-02T00:00Z</gml:beginPosition>
         <gml:endPosition>2008-07-25T16:00Z</gml:endPosition>
      </gml:TimePeriod>
   </time>
   <procedure xlink:href="urn:x-noaa:def:sensor:ndbc::41033:adcp0"/>
   <procedure xlink:href="urn:x-noaa:def:sensor:ndbc::41033:anemometer1"/>
   <observedProperty xlink:href="http://www.csc.noaa.gov/ioos/schema/IOOS-DIF/IOOS/0.6.1/dictionaries/phenomenaDictionary.xml#Currents"/>
   <observedProperty xlink:href="http://www.csc.noaa.gov/ioos/schema/IOOS-DIF/IOOS/0.6.1/dictionaries/phenomenaDictionary.xml#Winds"/>
   <featureOfInterest xlink:href="urn:x-noaa:def:station:ndbc::41033"/>
   <responseFormat>text/xml;schema="ioos/0.6.1"</responseFormat>
   <responseFormat>application/ioos+xml;version=0.6.1</responseFormat>
   <resultModel>om:Observation</resultModel>
   <responseMode>inline</responseMode>
</ObservationOffering>

Query (OSMC):

select PLATFORM.PLATFORM_CODE as NAME
,      PLATFORM.DESCRIPTION
,      PARAMETER.NAME as PARAMETER_NAME
,      MIN(ORGANIZATION.NAME) as OWNER
,      to_char(min(obs.OBSERVATION_DATE),'YYYY-MM-DD"T"HH24:MI"Z"') as firstObsDate
,      to_char(max(obs.OBSERVATION_DATE),'YYYY-MM-DD"T"HH24:MI"Z"') as lastObsDate
,      min(obs.SHAPE.SDO_POINT.X) LONGITUDE
,      min(obs.SHAPE.SDO_POINT.Y) LATITUDE
from   OSMCREV.PLATFORM
,      OSMCREV.ORGANIZATION
,      OSMCREV.OBSERVATION_VALUE
,      OSMCREV.OBSERVATION obs
,      OSMCREV.PARAMETER
where  OSMCREV.PLATFORM.ORGANIZATION_ID = OSMCREV.ORGANIZATION.ID
and    OSMCREV.PLATFORM.ID = obs.PLATFORM_ID
and    obs.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.PARAMETER.ID = OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
group by PLATFORM.PLATFORM_CODE
,      PLATFORM.DESCRIPTION
,      PARAMETER.NAME
;

DescribeSensor

Query (NDBC):

select lower(p.name) as name
, X(p.location) as lon
, Y(p.location) as lat
, osv.shortTypeName as shortstname
, osv.sensorNumber as sensornumber 
from platform p inner join sensor s on p.id = s.platformId 
inner join observationSummaryView osv on s.id = osv.sensorId 
where lower(p.name) = '51003' 
and osv.shortTypeName = 'anemometer' 
and osv.sensorNumber = 1 
order by p.name, osv.sensorType;

Response (NDBC):

DescribeSensor for urn:x-noaa:def:sensor:ndbc::41012:adcp0

<sml:Sensor>
<!--
 This is a PROTOTYPE service.  The information in this response is NOT complete. 
-->
<sml:identification>
<sml:IdentifierList/>
</sml:identification>
<sml:inputs>
<sml:InputList/>
</sml:inputs>
<sml:outputs>
<sml:OutputList/>
</sml:outputs>
<sml:positions>
<sml:PositionList>
<sml:position name="southwestCorner">
<swe:GeoLocation>
<swe:latitude>
<swe:Quantity>30.04</swe:Quantity>
</swe:latitude>
<swe:longitude>
<swe:Quantity>-80.55</swe:Quantity>
</swe:longitude>
</swe:GeoLocation>
</sml:position>
<sml:position name="southeastCorner">
<swe:GeoLocation>
<swe:latitude>
<swe:Quantity>30.04</swe:Quantity>
</swe:latitude>
<swe:longitude>
<swe:Quantity>-80.55</swe:Quantity>
</swe:longitude>
</swe:GeoLocation>
</sml:position>
<sml:position name="northeastCorner">
<swe:GeoLocation>
<swe:latitude>
<swe:Quantity>30.04</swe:Quantity>
</swe:latitude>
<swe:longitude>
<swe:Quantity>-80.55</swe:Quantity>
</swe:longitude>
</swe:GeoLocation>
</sml:position>
<sml:position name="northwestCorner">
<swe:GeoLocation>
<swe:latitude>
<swe:Quantity>30.04</swe:Quantity>
</swe:latitude>
<swe:longitude>
<swe:Quantity>-80.55</swe:Quantity>
</swe:longitude>
</swe:GeoLocation>
</sml:position>
</sml:PositionList>
</sml:positions>
</sml:Sensor>

Query (OSMC):

select PLATFORM.PLATFORM_CODE as NAME
,      min(obs.SHAPE.SDO_POINT.X) LONGITUDE
,      min(obs.SHAPE.SDO_POINT.Y) LATITUDE
,      min(PLATFORM.PLATFORM_CODE||':'||PARAMETER.NAME) as shortstname
,      min(PLATFORM.PLATFORM_CODE||':'||PARAMETER.NAME) as sensornumber
from   OSMCREV.PLATFORM
,      OSMCREV.ORGANIZATION
,      OSMCREV.OBSERVATION_VALUE
,      OSMCREV.OBSERVATION obs
,      OSMCREV.PARAMETER
where  OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 5
and    OSMCREV.PLATFORM.ORGANIZATION_ID = 1
and    OSMCREV.PLATFORM.ORGANIZATION_ID = OSMCREV.ORGANIZATION.ID
and    OSMCREV.PLATFORM.ID = obs.PLATFORM_ID
and    obs.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.PARAMETER.ID = OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
group by PLATFORM.PLATFORM_CODE
,      PLATFORM.DESCRIPTION
,      PARAMETER.NAME
;

GetObservation

Queries (NDBC):

The first query selects information about the station and the time period available for a particular sensor type (parameter).

select osv.platform_id
,      upper(osv.platform) as name
,      p.platformTypeId
,      p.description
,      X(osv.location) as lon
,      Y(osv.location) as lat
,      o.shortname as owner
,      osv.sensorType as stname
,      osv.shortTypeName as shortstname
,      osv.sensorNumber as sensornumber
,      date_format(osv.firstObsDate,'%Y-%m-%dT%H:%i') as firstObsDate
,      date_format(osv.lastObsDate,'%Y-%m-%dT%H:%i') as lastObsDate 
from   observationSummaryView osv left join platform p on osv.platform_id = p.id 
left   join organization o on p.organizationId = o.id 
inner  join sensor s on osv.platform_id = s.platformId 
and    osv.sensorId = s.id inner join sensorType st on st.id = s.sensorTypeId 
where  osv.platform = '51003' 
and    lower(osv.sensorType) = 'winds';

The second query gets the actual observation values:

select s.id as sensorid
,      s.sensorTypeId as sensortype
,      st.dataType as stname
,      date_format(winds.observationDate,'%Y-%m-%dT%H:%i:%sZ') as observationDate
,      winds.windDirection as wdir
,      winds.windSpeed as wspd
,      winds.windGust as gust 
from   sensor s inner join winds on s.id = winds.sensorId 
       inner join sensorType st on st.id = s.sensorTypeId 
where  s.platformId = 78 
and    winds.observationDate = '2008-09-24T19:50' 
order by winds.observationDate;

Response (NDBC):

See Service Home Page for examples

Query (OSMC):

This query returns the date range over which a particular station has observations for a particular parameter (in this case winds). At this point the database models diverge a bit, since the OSMC model has all observations in a single table. The same behavior can be achieved by specifying the station, parameter and datasource:

--- retrieve the min and max observation date for
--- OSMCREV.OBSERVATION.PLATFORM_ID = 122
--- OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 5 (WINDSPD)
--- OSMCREV.OBSERVATION.DATASOURCE_ID = 1       (NDBC)
select OSMCREV.OBSERVATION.PLATFORM_ID
,      OSMCREV.OBSERVATION.DATASOURCE_ID
,      OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
,      MIN(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) MIN_DATE
,      MAX(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) MAX_DATE
from   OSMCREV.OBSERVATION
,      OSMCREV.OBSERVATION_VALUE
where  OSMCREV.OBSERVATION.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.OBSERVATION.PLATFORM_ID = 122
and    OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 5
and    OSMCREV.OBSERVATION.DATASOURCE_ID = 1  
group by OBSERVATION.PLATFORM_ID, OBSERVATION.DATASOURCE_ID, OBSERVATION_VALUE.PARAMETER_ID
;

Result:
122	1	5	24-OCT-08	01-NOV-08

Removing the specification of PARAMETER_ID gives the same information for all parameters:

--- retrieve the min and max observation date for all parameters
--- OSMCREV.OBSERVATION.PLATFORM_ID = 122
--- OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 5 (WINDSPD)
--- OSMCREV.OBSERVATION.DATASOURCE_ID = 1       (NDBC)
select OSMCREV.OBSERVATION.PLATFORM_ID
,      OSMCREV.OBSERVATION.DATASOURCE_ID
,      OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
,      MIN(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) MIN_DATE
,      MAX(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) MAX_DATE
from   OSMCREV.OBSERVATION
,      OSMCREV.OBSERVATION_VALUE
where  OSMCREV.OBSERVATION.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.OBSERVATION.PLATFORM_ID = 122
and    OSMCREV.OBSERVATION.DATASOURCE_ID = 1  
group by OBSERVATION.PLATFORM_ID, OBSERVATION.DATASOURCE_ID, OBSERVATION_VALUE.PARAMETER_ID
;

Result:
Stat DS   PARAM         START            END
122	1	1	24-OCT-08	01-NOV-08
122	1	3	24-OCT-08	01-NOV-08
122	1	4	24-OCT-08	27-OCT-08
122	1	5	24-OCT-08	01-NOV-08
122	1	6	24-OCT-08	01-NOV-08
122	1	7	24-OCT-08	01-NOV-08

Now get the actual values for a single parameter:

select OSMCREV.OBSERVATION.PLATFORM_ID
,      OSMCREV.OBSERVATION.DATASOURCE_ID
,      OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE
,      OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
,      OSMCREV.OBSERVATION_VALUE.VALUE
from   OSMCREV.OBSERVATION
,      OSMCREV.OBSERVATION_VALUE
where  OSMCREV.OBSERVATION.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.OBSERVATION.PLATFORM_ID = 122
and    OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 5
and    OSMCREV.OBSERVATION.DATASOURCE_ID = 1  
order by OSMCREV.OBSERVATION.OBSERVATION_DATE
;

Result:
122	1	26-OCT-08	5	12.869747855900007
122	1	26-OCT-08	5	15.443697427080009
122	1	27-OCT-08	5	18.017646998260012
122	1	27-OCT-08	5	19.04722682673201
122	1	30-OCT-08	5	13.899327684372007
122	1	31-OCT-08	5	10.295798284720005
122	1	01-NOV-08	5	7.7218487135400045
122	1	01-NOV-08	5	2.5739495711800013
122	1	01-NOV-08	5	0
122	1	03-NOV-08	5	8.751428542012006

And the same query can be done in terms of Platform_Code:

select OSMCREV.PLATFORM.PLATFORM_CODE
,      OSMCREV.OBSERVATION_VALUE.PARAMETER_ID
,      OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE
,      OSMCREV.OBSERVATION_VALUE.VALUE
from   OSMCREV.OBSERVATION
,      OSMCREV.OBSERVATION_VALUE
,      OSMCREV.PLATFORM
where  OSMCREV.OBSERVATION.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    OSMCREV.OBSERVATION.PLATFORM_ID = OSMCREV.PLATFORM.ID
and    OSMCREV.OBSERVATION.DATASOURCE_ID = 1
and    OSMCREV.PLATFORM.PLATFORM_CODE = '44004'
and    OSMCREV.OBSERVATION_VALUE.PARAMETER_ID = 11
order by OBSERVATION.PLATFORM_ID
,     OBSERVATION.DATASOURCE_ID
,     OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE
,     OBSERVATION_VALUE.PARAMETER_ID
;

with results like:
44004	11	27-OCT-08	2.1
44004	11	27-OCT-08	1.9000000000000001
44004	11	27-OCT-08	1.9000000000000001
44004	11	27-OCT-08	1.9000000000000001
44004	11	27-OCT-08	1.7000000000000002
44004	11	27-OCT-08	1.8
44004	11	27-OCT-08	1.6
44004	11	27-OCT-08	1.6
44004	11	27-OCT-08	1.5

Query Optimization

These queries were tested by Mike Garcia at NDBC during early November 2008 and showed very poor performance.

GetCapabilities

GetCapabilities explains what type of data is offered at what geographic locations and time frames. It also tells the clients how to retrieve this data. The database must provide a list of stations (names, locations, owners,etc), sensors installed, measurement types and start/stop dates. The OSMC query was terminated after 80 minutes with no response.

The getCapabilities query is slow because the observation table must be queried in order to determine the location of the station and the parameters it measures. These are very large tables.

One solution to this could be to build a view to specifically support the capabilities request:

create materialized view capabilities
build immediate
refresh on demand as
select MIN(OSMCREV.PLATFORM.PLATFORM_CODE) PLATFORM_CODE
,      MIN(OSMCREV.PLATFORM_TYPE_ASSOCIATION.PLATFORM_TYPE_ID) PLATFORM_TYPE
,      MIN(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) START_DATE
,      MAX(OSMCREV.OBSERVATION_VALUE.OBSERVATION_DATE) END_DATE
,      min(obs.SHAPE.SDO_POINT.X) MIN_LONGITUDE
,      min(obs.SHAPE.SDO_POINT.Y) MIN_LATITUDE
,      max(obs.SHAPE.SDO_POINT.X) MAX_LONGITUDE
,      max(obs.SHAPE.SDO_POINT.Y) MAX_LATITUDE
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,1,1,0))SLP
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,2,1,0))SST
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,3,1,0))ATMP
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,4,1,0))DEWPOINT
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,5,1,0))WINDSPD
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,6,1,0))CLOUDS
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,7,1,0))PRECIP
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,8,1,0))ZTMP
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,9,1,0))ZSAL
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,10,1,0))WVHT
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,11,1,0))LOCATION
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,12,1,0))PCO2_SW
,      SUM(DECODE(OSMCREV.OBSERVATION_VALUE.PARAMETER_ID,13,1,0))SSS
from   OSMCREV.PLATFORM
,      OSMCREV.OBSERVATION obs
,      OSMCREV.OBSERVATION_VALUE
,      OSMCREV.PLATFORM_TYPE_ASSOCIATION
where  obs.ID = OSMCREV.OBSERVATION_VALUE.OBSERVATION_ID
and    obs.PLATFORM_ID = OSMCREV.PLATFORM.ID
and    OSMCREV.PLATFORM.ID = OSMCREV.PLATFORM_TYPE_ASSOCIATION.PLATFORM_ID
and    obs.DATASOURCE_ID = 1
group by obs.PLATFORM_ID
;

OSMC Query Optimization

Personal tools