1. Ian Mayo
  2. gnd

Wiki

Clone wiki

gnd / Architectural Design in PostGIS

Design Basics

Define a range of "abstract" schemas, and record which of these are supported by each "concrete" real-world data formats.

Major Data Categories:

  • Tabular Data (Users, Inventory etc.)
  • Sensor Data (Time Stamped measures of Temperature, GPS, Pressure, Light, Speed, etc)
  • Route Data (2d, 3d, 6d)
  • Course Data (Time Stamped 2d, 3d, 6d)

Tabular Data

Collection of general purpose tables to store information related to users, inventory of vehicle parts, or a list of drivers.

Sensor Data

Time stamped readings from various sensors including Temperature, GPS, Pressure, Light, Speed, etc.

DM_Sensor_Data_1

Note: Based on the assumption that, sensors will have very limited number of attributes.

Another option for storing sensor readings is to populate main table with common attributes, and to maintain a separate table for storing readings from different sensor types including Temperature, Speed, Light, Pressure and others.

DM_Sensor_Data_2

Route Data

Series of location values, with no time-value. Could be the route of a pipeline, a road, a contour on a mountain. A route can be 2d, 3d or 6d. There could be the following two solutions to store Route information.

Option # 1 : ==== To have a single generic table having redundant attributes. The available attributes will be populated for each type of Route (2d, 2d or 6d) and rest of the attributes will be left empty.

DM_Route_Data_1

  • For 2d route data, attributes Location Z axis, Pitch, Roll and Yaw will left empty.
  • For 3d route data, attributes Pitch, Roll and Yaw will left empty.
  • For 6d route data, all the attributes will be available.

Cons: This solution will end up in a single huge table with most of the columns having empty values.

Option # 2 : ==== To have 3 separate physical tables, one for each type of Route (2d, 3d, or 6d)

DM_Route_Data_2

  • Route Data 2d table will be populated with 2d components from all type of vehicle routes including values from Watch based GPS, GPS with altitudes and airplane black box.
  • Route Data 3d table will be populated with 3d components from all type of vehicle routes including values from GPS with altitudes and airplane black box.
  • Route Data 6d table will be populated with 6d components from airplane black box.

Course Data

Time stamped route data. Could be the course of car, plane or any other vehicle.

Updated