The JDBC store database structure

The JDBC store uses a conventional relational structure, depicted in the following picture:

../../_images/dbschema.png

So a collection has its own primary search attributes, as well as:

  • Zero or more OGC links pointing to where the collection is published

  • Layer publishing information (for auto-generation of mosaic, layer and eventual coverage view in case the actual data resides locally)

  • One or more products

A product in turn is associated to:

  • A thumbnail image, in PNG or JPEG format

  • Zero or more OGC links pointing to where the product is published

The granule table is designed to contain per product file information in case there is a desire to publish the actual data from the same local GeoServer (but in general, OGC services might be missing or provided by a separate server).

Collections

The collection table currently looks as follows (check the SQL file in the installation instructions for a more up to date version of it):

create table collection (
  "id" serial primary key,
  "name" varchar,
  "primary" boolean,
  "htmlDescription" text,
  "footprint" geography(Polygon, 4326),
  "timeStart" timestamp,
  "timeEnd" timestamp,
  "productCqlFilter" varchar,
  "masked" boolean,
  "eoIdentifier" varchar unique,
  "eoProductType" varchar,
  "eoPlatform" varchar,
  "eoPlatformSerialIdentifier" varchar,
  "eoInstrument" varchar,
  "eoSensorType" varchar check ("eoSensorType" in ('OPTICAL', 'RADAR', 'ALTIMETRIC', 'ATMOSPHERIC', 'LIMB')),
  "eoCompositeType" varchar,
  "eoProcessingLevel" varchar,
  "eoOrbitType" varchar,
  "eoSpectralRange" varchar,
  "eoWavelength" int,
  "eoSecurityConstraints" boolean,
  "eoDissemination" varchar,
  "eoAcquisitionStation" varchar,
  "queryables" varchar[]
);

Most of the attributes should be rather self-explanatory to those familiar with OGC Earth Observation terminology. Each attribute prefixed by “eo” is exposed as a search attribute in OpenSearch, the structure can be modified by adding extra attributes and they will show up and made searchable.

Specific attributes notes:

  • A primary collection is normally linked to a particular satellite/sensor and contains its own products. Setting “primary” to false makes the collection “virtual” and the productCQLFilter field should be filled with a CQL filter that will collect all the products in the collection (warning, virtual collections are largerly untested at the moment)

  • The footprint field is used for spatial searches, while timeStart and timeEnd are used for temporal ones

  • The htmlDescription drives the generation of the visible part of the Atom OpenSearch response, see the dedicated section later to learn more about filling it

The collection_ogclink table contains the OGC links towards the services providing visualization and download access to the collection contents. See the “OGC links” section to gather more information about it.

Products

The product table currently looks as follows (check the SQL file in the installation instructions for a more up to date version of it):

-- the products and attributes describing them
create table product (
  "id" serial primary key,
  "htmlDescription" text,
  "footprint" geography(Polygon, 4326),
  "timeStart" timestamp,
  "timeEnd" timestamp,
  "originalPackageLocation" varchar,
  "originalPackageType" varchar,
  "thumbnailURL" varchar,
  "quicklookURL" varchar,
  "crs" varchar,
  "eoIdentifier" varchar unique,
  "eoParentIdentifier" varchar references collection("eoIdentifier") on delete cascade,
  "eoProductionStatus" varchar,
  "eoAcquisitionType" varchar check ("eoAcquisitionType" in ('NOMINAL', 'CALIBRATION', 'OTHER')),
  "eoOrbitNumber" int,
  "eoOrbitDirection" varchar check ("eoOrbitDirection" in ('ASCENDING', 'DESCENDING')),
  "eoTrack" int,
  "eoFrame" int,
  "eoSwathIdentifier" text,
  "optCloudCover" int check ("optCloudCover" between 0 and 100),
  "optSnowCover" int check ("optSnowCover" between 0 and 100),
  "eoProductQualityStatus" varchar check ("eoProductQualityStatus" in ('NOMINAL', 'DEGRADED')),
  "eoProductQualityDegradationStatus" varchar,
  "eoProcessorName" varchar,
  "eoProcessingCenter" varchar,
  "eoCreationDate" timestamp,
  "eoModificationDate" timestamp,
  "eoProcessingDate" timestamp,
  "eoSensorMode" varchar,
  "eoArchivingCenter" varchar,
  "eoProcessingMode" varchar,
  "eoAvailabilityTime" timestamp,
  "eoAcquisitionStation" varchar,
  "eoAcquisitionSubtype" varchar,
  "eoStartTimeFromAscendingNode" int,
  "eoCompletionTimeFromAscendingNode" int,
  "eoIlluminationAzimuthAngle" float,
  "eoIlluminationZenithAngle" float,
  "eoIlluminationElevationAngle" float,
  "sarPolarisationMode" varchar check ("sarPolarisationMode" in ('S', 'D', 'T', 'Q', 'UNDEFINED')),
  "sarPolarisationChannels" varchar check ("sarPolarisationChannels" in ('horizontal', 'vertical')),
  "sarAntennaLookDirection" varchar check ("sarAntennaLookDirection" in ('LEFT', 'RIGHT')),
  "sarMinimumIncidenceAngle" float,
  "sarMaximumIncidenceAngle" float,
  "sarDopplerFrequency" float,
  "sarIncidenceAngleVariation" float,
  "eoResolution" float
);

Notes on the attributes:

  • The footprint field is used for spatial searches, while timeStart and timeEnd are used for temporal ones

  • The htmlDescription drives the generation of the visible part of the Atom OpenSearch response, see the dedicated section later to learn more about filling it

  • The crs attribute is optional and is used only for automatic layer publishing for collections having heterogeneous CRS products. It must contain a “EPSG:XYWZ” expression (but the product footprint still need to be expressed in WGS84, east/north oriented).

  • The EO search attributes need to be filled according to the nature of the product, eo prefixes generic EOP attributes, opt optical ones, sar radar ones, atm altimetric, lmb limbic, ssp Synthesis and Systematic Product. New attributes can be added based on the above prefixes (at the time of writing only optical and sar attributes have been tested)

The product_thumb table contains the product thumbnail, in PNG or JPEG format, for display in the OpenSearch Atom output.

The product_ogclink table contains the OGC links towards the services providing visualization and download access to the collection contents. See the “OGC links” section to gather more information about it.

The granule table

The granule table can be filled with information about the actual raster files making up a certain product in order to publish the collection as a GeoServer image mosaic:

-- the granules table (might be abstract, and we can use partitioning)
create table granule (
  "gid" serial primary key,
  "product_id" int not null references product("id") on delete cascade,
  "band" varchar,
  "location" varchar not null,
  "the_geom" geometry(Polygon, 4326) not null
);

The granules associated to a product can have different topologies:

  • A single raster file containing all the information about the product

  • Multiple raster files splitting the products spatially in regular tiles

  • Multiple raster files splitting the product wavelenght wise

  • A mix of the two above

Notes about the columns:

  • The band column need to be filled only for products split in several files by bands, at the time of writing it needs to be a progressive integer starting from 1 (the module will hopefully allow more meaningful band names in the future)

  • The location is the absolute path of the file

  • The the_geom field is a polygon in WGS84, regardless of what the actual footprint of the file is. The polygon must represent the rectangular extend of the raster file, not its valid area (masking is to be treated separately, either with sidecar mask files or with NODATA pixels)

Previous: Configuring the OpenSearch module
Next: Automation with the administration REST API