Database Schema - irtlab/lost GitHub Wiki

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. The shapes represent service areas of the downstream LoST servers. The shapes are referenced in the shape table.

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

Resolvers use the tables in the resolver schema. These tables represent the data used by LoST resolvers to interpret queries submitted by applications. Those queries refer to geographic objects by name.

The table feature contains geographic features (e.g., buildings) downloaded from OpenStreetMap. This table references the shape table. The objects in this table are hierarchically organized, i.e., have a parent-child relationship.

create table feature (
    id             uuid        primary key default public.uuid_generate_v4(),
    type           feature_t,
    name           text        not null,
    parent         uuid        references feature(id),
    vertical_range text,
    indoor         bool        default 't',
    shape          uuid        references public.shape(id) on delete set null,
    control_points text[],
    created        timestamptz not null default current_timestamp,
    image          text,
    transform      text,
    attrs          jsonb       not null default '{}'::jsonb
);

The control_point table contains coordinates of reference control points in raster images representing floor plans.

create table control_point (
    id          uuid                   primary key default public.uuid_generate_v4(),
    coordinates public.geometry(point)
);

The coordinate_tranform table describes a coordinate transformation that orients a floor plan raster image in the world so that it could be overlaid over an OpenStreetMap map.

create table coordinate_transform (
    id            uuid  primary key default public.uuid_generate_v4(),
    control_links jsonb not null
);

The raster_image table contains metadata about raster images (floorplans) uploaded to the resolver.

create table raster_image (
    id          uuid        primary key default public.uuid_generate_v4(),
    name        text,
    file_name   text        not null,
    width       integer     check (width > 0) not null,
    height      integer     check (height > 0) not null,
    size        integer     check (size > 0) not null,
    storage_ref text        not null,
    created     timestamptz not null default current_timestamp,
    updated     timestamptz not null default current_timestamp
);