Database Schema

The database schema is created by the SQL script in scripts/sql/00-lost.sql. The schema is common for LoST servers and resolvers. Server-specific objects are stored in the schema "server". Resolver-specific objects are stored in the schema "resolver".

Common Tables

The table shape stores geometric objects, i.e., boundary polygons downloaded from OpenStreetMap. The objects are geographically indexed with a GIN index. This table is used by LoST servers and resolvers.

create table shape (
    id         uuid                                primary key default uuid_generate_v4(),
    uri        text                                not null,
    geometries public.geometry(GeometryCollection) not null,
    created    timestamptz                         not null default current_timestamp,
    updated    timestamptz                         not null default current_timestamp,
    attrs      jsonb                               not null default '{}'::jsonb
);

create        index shape_geom_idx  on shape using gist(geometries);
create unique index shape_uri_idx   on shape using btree(uri);
create        index shape_attrs_idx on shape using gin(attrs);

Server tables

The main LoST server table is called mapping. This table maps shapes to downstream LoST server URIs stored in an attribute called "uri" in the attrs column.

create table mapping (
    id      serial       primary key,
    shape   uuid         references public.shape(id) on delete set null,
    srv     text         not null,
    created timestamptz  not null default current_timestamp,
    updated timestamptz  not null default current_timestamp,
    attrs   jsonb        not null default '{}'::jsonb
);

Resolver Tables