Experiments in converting ODC Datasets to PostGIS Geometries

odc_to_postgis.sql

-- Experiments with PostgreSQL, connecting to a remote server.

CREATE EXTENSION postgres_fdw;

DROP SERVER IF EXISTS collection2;
CREATE SERVER collection2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'datacube', host 'localhost');

DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER collection2;
CREATE USER MAPPING FOR dra547 SERVER collection2 OPTIONS (user 'dra547', password 'xxxxx');

CREATE SCHEMA old_collection;
IMPORT FOREIGN SCHEMA agdc
    LIMIT TO (dataset, dataset_location, dataset_source, dataset_type, metadata_type)
    FROM SERVER collection2 INTO old_collection;


SELECT name, id
from old_collection.dataset_type;


-- Experiments with PostgreSQL, converting ODC JSONB datasets into PostGIS geometries

CREATE EXTENSION postgis;


DROP TABLE IF EXISTS geom_datasets;
CREATE TABLE geom_datasets
(
    id               uuid PRIMARY KEY,
    dataset_type_ref smallint,
    geom             geography(POLYGON, 4326),
    archived         timestamp with time zone,
    added            timestamp with time zone,
    added_by         name
);


-- Extract and convert Geometries, assuming that they're all Australian Albers 3577
-- which is true for '_albers' products.

INSERT INTO geom_datasets
select id, dataset_type_ref,
               ST_Transform(
                       ST_SetSRID(
                               ST_GeomFromGeoJSON(metadata #> '{grid_spatial,projection,valid_data}'),
                               3577),
                       4326) as geom,
       archived, added, added_by
from old_collection.dataset
WHERE dataset_type_ref = (select id from old_collection.dataset_type where name = 'ls8_nbar_albers');




-- Experimental section, trying to use SQL to convert ODC CRS representation to PostGIS


select distinct (metadata#>'{grid_spatial,projection}')::jsonb - 'valid_data' - 'geo_ref_points' as crs
from old_collection.dataset
WHERE dataset_type_ref = (select id from old_collection.dataset_type; -- where name = 'ls8_nbar_scene');


select *
from public.spatial_ref_sys
where srtext ~ 'UTM'
and proj4text ~ 'zone=55' and proj4text ~ 'south';
-- {"zone": -56, "datum": "GDA94", "ellipsoid": "GRS80", "orientation": "NORTH_UP",
--  "map_projection": "UTM", "resampling_option": "CUBIC_CONVOLUTION"}
-