bitner

#!/bin/bash
psql <<EOD
    SET SEARCH_PATH to public, data;
    DROP VIEW IF EXISTS items_as_ndjson;
    CREATE OR REPLACE VIEW items_as_ndjson AS
    SELECT
        date_trunc('month', datetime)::date as month,
        datetime,
        jsonb_build_object(
            'id', items.id,

bitner

SELECT
    jsonb_build_object(
        'id', items.id,
        'datetime', items.datetime,
        'collection', items.collection,
        'geometry', public.st_asgeojson(items.geometry)::jsonb,
        'properties', items.properties,
        'assets', items.assets,
        'links', items.links
    ) AS content

bitner

CREATE OR REPLACE FUNCTION landsat(
    -- mandatory parameters
    xmin float,
    ymin float,
    xmax float,
    ymax float,
    epsg integer
)
RETURNS bytea AS $$
WITH bounds AS (

bitner

POST https://planetarycomputer.microsoft.com/api/stac/v1/search HTTP/1.1
content-type: application/json

{
    "bbox":[-59.69, -34.74, -58.24, -33.65],
    "datetime":"2017-01-01/2020-12-31",
    "collections":["sentinel-2-l2a"],
    "limit":5
}

bitner

#!/bin/bash
TMP_TABLE_RANDOM=$(tr -dc a-z </dev/urandom | head -c 20)
TABLE="pgstac.tmp_items_$TMP_TABLE_RANDOM"
psql <<EOD
CREATE UNLOGGED TABLE $TABLE (
    -- id text GENERATED ALWAYS AS (content->>'id') STORED,
    -- partition text GENERATED ALWAYS AS (items_partition_name(stac_datetime(content))) STORED,
    content jsonb
);
EOD

bitner

‎‎​

bitner

MATCH (n:DataProduct) WHERE id(n) = 6737 CALL apoc.path.expand(n, ">", "*", 1, 4) YIELD path WITH collect(path) as paths CALL apoc.convert.toTree(paths) yield value RETURN value

bitner

CREATE OR REPLACE FUNCTION xyzsearch_asmvtrows(
    IN _x int,
    IN _y int,
    IN _z int,
    IN queryhash text,
    IN fields jsonb DEFAULT NULL,
    IN _scanlimit int DEFAULT 10000,
    IN _limit int DEFAULT 100,
    IN _timelimit interval DEFAULT '5 seconds'::interval,
    IN exitwhenfull boolean DEFAULT TRUE, -- Return as soon as the passed in geometry is full covered

bitner

bitner

-- PostgreSQL
select 
  row_number() over (order by date_utc asc, location, parameter), 
  location, 
  value, 
  parameter, 
  country, 
  city, 
  date_utc 
from measurements