resto-database-model/03_resto_target_model.sql
--
-- resto dedicated target model
--
--
-- Target tables are sored under {DATABASE_TARGET_SCHEMA} schema (default is resto)
--
CREATE SCHEMA IF NOT EXISTS __DATABASE_TARGET_SCHEMA__;
--
-- collections table list all resto collections
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.collection (
-- Unique id for collection
"id" TEXT PRIMARY KEY,
-- Collection version
version TEXT,
-- Model used to ingest collection metadata
model TEXT,
-- Model lineage
lineage TEXT[],
-- A default license attached to this collection.
-- Every feature within this collection will inherit from the collection's license
licenseid TEXT,
-- Visibility - group visibility (only user within this group can see collection)
visibility INTEGER,
-- Owner of the collection. References __DATABASE_COMMON_SCHEMA__.user.id
owner BIGINT,
-- Timestamp of collection creation
created TIMESTAMP,
-- Start time of the collection
startdate TIMESTAMP,
-- Completion time of the collection
completiondate TIMESTAMP,
-- Spatial extent of the collection
bbox GEOMETRY(GEOMETRY, 4326),
-- [STAC] Providers
providers JSON,
-- [STAC] Additional properties
properties JSON,
-- [STAC] Static links
links JSON,
-- [STAC] Collection assets
assets JSON,
-- [STAC] Keywords
keywords TEXT[]
);
--
-- osdescriptions table describe all RESTo collections
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.osdescription (
-- Reference __DATABASE_TARGET_SCHEMA__.collection.id
collection TEXT REFERENCES __DATABASE_TARGET_SCHEMA__.collection (id) ON DELETE CASCADE,
-- OpenSearch description lang
lang TEXT,
-- Contains a brief human-readable title that identifies this search engine.
shortname TEXT,
-- Contains an extended human-readable title that identifies this search engine.
longname TEXT,
-- Contains a human-readable text description of the search engine.
description TEXT,
-- Contains a set of words that are used as keywords to identify and categorize this search content. Tags must be a single word and are delimited by the space character
tags TEXT,
-- Contains the human-readable name or identifier of the creator or maintainer of the description document.
Developer TEXT,
-- Contains an email address at which the maintainer of the description document can be reached.
contact TEXT,
-- Defines a search query that can be performed by search clients. Please see the OpenSearch Query element specification for more information.
query TEXT,
-- Contains a list of all sources or entities that should be credited for the content contained in the search feed
attribution TEXT
);
--
-- Collection aliases
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.collection_alias (
-- Alternate name to this collection
alias TEXT PRIMARY KEY,
-- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.collection.id
collection TEXT REFERENCES __DATABASE_TARGET_SCHEMA__.collection (id) ON DELETE CASCADE
);
--
-- Features table - handle every metadata
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature (
-- [INDEXED] UUID v5 based on productidentifier
"id" UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),
-- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.collection.id - A feature is within one and only one collection
collection TEXT NOT NULL,
-- Vendor identifier (for migration)
productidentifier TEXT,
-- A title for this feature
title TEXT,
-- A description for this feature
description TEXT,
-- Start of validity timestamp for this feature
startdate TIMESTAMP,
-- End of validity timestamp for this feature. If equal to started then feature is "instantaneous"
completiondate TIMESTAMP,
-- [INDEXED] Visibility - only user within a group with the same name as visibility can see feature
visibility INTEGER,
-- [INDEXED] Owner of the feature. Reference __DATABASE_COMMON_SCHEMA__.user.id
owner BIGINT,
-- [INDEXED] Open value
status INTEGER,
-- Number of likes for this feature. Used by Social add-on
likes INTEGER,
-- Number of comments for this feature. Used by Social add-on
comments INTEGER,
-- Metadata. You can put whatever you want.
-- Field indexation is based on collection type
--
-- Example for a satellite imagery
-- {
-- authority:
-- productType:
-- processingLevel:
-- platform:
-- instrument:
-- resolution:
-- sensorMode:
-- orbitNumber:
-- }
metadata JSON,
-- Assets array contains download, metadata original file, etc.
--
-- {
-- download:{
-- "title":"Download link",
-- "href":"http://localhost/image123.tif",
-- "realPath":"file://data/images/image123.tif",
-- "size": 1234567,
-- "integrity": "sha384-oqVuAfXRKap...7f86",
-- "type":"application/tif"
-- },
-- metadata:{
-- "rel":"alternate",
-- "title":"Metadata link",
-- "href":"http://localhost/image123.xml",
-- "realPath":"file://data/images/image123.xml",
-- "type":"application/xml"
-- }
-- }
--
assets JSON,
-- Links array contains related url
--
-- [
-- {"rel": "acquisition", "href": "http://cool-sat.com/catalog/acquisitions/20160503_56"}
-- {"rel": "something", "title":"SIVolcano", "href": "http://volcano.si.edu/volcano.cfm?vn=233020"}
-- ]
--
links JSON,
-- Timestamp of creation for this feature metadata
created TIMESTAMP,
-- Timestamp of update for this feature metadata
updated TIMESTAMP,
-- Keywords computed by Tag add-on
keywords JSON,
--
-- List of hashtags (without prefix # !)
--
-- Two kind of hashtags:
-- * hashtags without {RestoConstants::TAG_SEPARATOR} hashtags *provided* by user from description
-- * hashtags with {RestoConstants::TAG_SEPARATOR} hashtags *computed* by Tag add-on (depend on collection)
hashtags TEXT[],
-- Original geometry as provided during feature insertion
-- It is set to NULL if equals to geom (see below)
geometry GEOMETRY(GEOMETRY, 4326),
-- Centroid computed from geometry
centroid GEOMETRY(POINT, 4326),
-- Result of ST_SplitDateLine(geometry)
-- Guarantee a valid geometry in database even if input geometry crosses -180/180 meridian of crosses North or South pole
-- If input geometry does not cross one of this case, then input geometry is not
-- modified and geom equals geomety.
geom GEOMETRY(GEOMETRY, 4326),
-- [INDEXED] Hashtags
--
-- List of normalized hashtags (without prefix # !)
--
normalized_hashtags TEXT[],
-- [INDEXED] Start date unique iterator
startdate_idx BIGINT,
-- [INDEXED] Created date unique iterator
created_idx BIGINT
);
--
-- Feature geometry is splitted into smaller part and indexed
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.geometry_part (
-- Feature identifier
"id" UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Feature's collection
collection TEXT NOT NULL,
-- Part iterator
part_num INTEGER,
-- [INDEXED] Geometry part
geom GEOMETRY(GEOMETRY, 4326),
-- Primary key based on unique identifier
PRIMARY KEY (id, part_num)
);
--
-- Relation between features
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.relation (
-- Reference feature id master
id1 UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Reference feature id slave
id2 UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Relation type: -1 (id1 is the parent of id2 - "hasSample"), 1 (id1 is the child of id1 - "isSampleOf")
relation INTEGER NOT NULL,
-- Relation type
type TEXT,
-- Relation creation date
created TIMESTAMP,
-- Primary key based on unique identifier
PRIMARY KEY (id1, id2)
);
--
-- Features content common to all features belonging to LandCoverModel (based on itag)
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_landcover (
-- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
"id" UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Collection id
collection TEXT NOT NULL,
-- Percentage of cultivated area
cultivated NUMERIC,
-- Percentage of desert area
desert NUMERIC,
-- Percentage of flooded area
flooded NUMERIC,
-- Percentage of forest area
forest NUMERIC,
-- Percentage of herbaceous area
herbaceous NUMERIC,
-- Percentage of ice area
ice NUMERIC,
-- Percentage of urban area
urban NUMERIC,
-- Percentage of water area
water NUMERIC,
-- Population estimation (in number of people)
population NUMERIC,
-- Population estimation density (in peopler per square kilometers)
population_density NUMERIC
);
--
-- Features specific properties based on SatelliteModel
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_satellite (
-- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
"id" UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Collection id
collection TEXT NOT NULL,
-- Image resolution in meters
resolution NUMERIC
);
--
-- Features specific properties based on OpticalModel
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_optical (
-- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
"id" UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,
-- Collection id
collection TEXT NOT NULL,
-- Percentage of snow in area
snowcover NUMERIC,
-- Percentage of cloud area
cloudcover NUMERIC
);
--
-- Facets table
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.facet (
-- Identifier for the facet (unique in combination with collection id)
id TEXT NOT NULL,
-- Collection id attached to the facet
collection TEXT NOT NULL,
-- Facet value
value TEXT,
-- Facet type (i.e. catalog, hashtag, region, state, location, etc.)
type TEXT,
-- Parent identifier (i.e. 'europe' for facet 'france')
pid TEXT NOT NULL,
-- Set to 1 if facet is a terminal leaf, 0 otherwise (used for STAC)
isleaf INTEGER,
-- Number of appearance of this facet within the collection
counter INTEGER,
-- Facet date of creation
created TIMESTAMP DEFAULT now(),
-- Owner of the facet i.e. first user to create it
owner BIGINT,
-- Description
description TEXT,
-- The id, pid, collection pair should be unique
PRIMARY KEY (id, pid, collection)
);
-- --------------------- INDEXES ---------------------------
-- [TABLE __DATABASE_TARGET_SCHEMA__.collection]
CREATE UNIQUE INDEX IF NOT EXISTS idx_id_collection on __DATABASE_TARGET_SCHEMA__.collection (public.normalize(id));
CREATE INDEX IF NOT EXISTS idx_lineage_collection ON __DATABASE_TARGET_SCHEMA__.collection USING GIN (lineage);
CREATE INDEX IF NOT EXISTS idx_visibility_collection ON __DATABASE_TARGET_SCHEMA__.collection (visibility);
CREATE INDEX IF NOT EXISTS idx_created_collection ON __DATABASE_TARGET_SCHEMA__.collection (created);
CREATE INDEX IF NOT EXISTS idx_keywords_collection ON __DATABASE_TARGET_SCHEMA__.collection USING GIN (keywords);
-- [TABLE __DATABASE_TARGET_SCHEMA__.osdescription]
ALTER TABLE __DATABASE_TARGET_SCHEMA__.osdescription DROP CONSTRAINT IF EXISTS cl_collection;
ALTER TABLE ONLY __DATABASE_TARGET_SCHEMA__.osdescription ADD CONSTRAINT cl_collection UNIQUE(collection, lang);
CREATE INDEX IF NOT EXISTS idx_collection_osdescription ON __DATABASE_TARGET_SCHEMA__.osdescription (collection);
-- CREATE INDEX IF NOT EXISTS idx_lang_osdescription ON __DATABASE_TARGET_SCHEMA__.osdescription (lang);
-- [TABLE __DATABASE_TARGET_SCHEMA__.facet]
CREATE INDEX IF NOT EXISTS idx_id_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(id));
CREATE INDEX IF NOT EXISTS idx_pid_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(pid));
CREATE INDEX IF NOT EXISTS idx_type_facet ON __DATABASE_TARGET_SCHEMA__.facet (type);
CREATE INDEX IF NOT EXISTS idx_collection_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(collection));
CREATE INDEX IF NOT EXISTS idx_value_facet ON __DATABASE_TARGET_SCHEMA__.facet USING GIN (public.normalize(value) gin_trgm_ops);
-- [TABLE __DATABASE_TARGET_SCHEMA__.feature]
CREATE INDEX IF NOT EXISTS idx_collection_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (collection);
CREATE INDEX IF NOT EXISTS idx_startdateidx_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (startdate_idx);
CREATE INDEX IF NOT EXISTS idx_createdidx_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (created_idx);
CREATE INDEX IF NOT EXISTS idx_owner_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (owner) WHERE owner IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_visibility_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (visibility);
CREATE INDEX IF NOT EXISTS idx_status_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (status);
CREATE INDEX IF NOT EXISTS idx_centroid_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIST (centroid);
CREATE INDEX IF NOT EXISTS idx_nhashtags_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIN (normalized_hashtags) WHERE normalized_hashtags IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_geom_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIST (geom);
-- [TABLE __DATABASE_TARGET_SCHEMA__.geometry_part]
CREATE INDEX IF NOT EXISTS idx_id_geometry_part ON __DATABASE_TARGET_SCHEMA__.geometry_part USING HASH (id);
CREATE INDEX IF NOT EXISTS idx_geom_geometry_part ON __DATABASE_TARGET_SCHEMA__.geometry_part USING GIST (geom);
-- [TABLE __DATABASE_TARGET_SCHEMA__.feature_landcover]
CREATE INDEX IF NOT EXISTS idx_cultivated_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (cultivated);
CREATE INDEX IF NOT EXISTS idx_desert_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (desert);
CREATE INDEX IF NOT EXISTS idx_flooded_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (flooded);
CREATE INDEX IF NOT EXISTS idx_forest_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (forest);
CREATE INDEX IF NOT EXISTS idx_herbaceous_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (herbaceous);
CREATE INDEX IF NOT EXISTS idx_ice_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (ice);
CREATE INDEX IF NOT EXISTS idx_urban_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (urban);
CREATE INDEX IF NOT EXISTS idx_water_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (water);
-- [TABLE __DATABASE_TARGET_SCHEMA__.feature_optical]
CREATE INDEX IF NOT EXISTS idx_cloudcover_m_optical ON __DATABASE_TARGET_SCHEMA__.feature_optical USING btree (cloudcover);