Storing GPS and mobile sensor data

Question

I am making an app to store GPS and mobile sensor data. My problem is how to store the data. The accelerometer data has a rapid frequency and the GPS data comes irregularly. Right now I've been storing it as a CSV file, but with this logic I get many redundant positions, because the sensor data is stored with a higher frequency

    id lat lon speed x y z time
    1  59  10  5     9 1 2 xxx
    2  59  10  5     5 4 3 xxx

The file will look something like this.

Is it possible to use a database logic with for example PostGIS or do anyone have a good solution for this?

Answer 1

You can model your problem as two incoming streams of messages that carry updates one on position and the other on activity for a given sensor.

One way of expressing that model in the database is as follows:

-- example schema for receiving fitness tracker data
create table devices (
    device_id uuid primary key,
    device_owner uuid, -- Foreign Key to a users table not included in this example
    device_data json --other dependent attributes
);

create table positions (
    device_id uuid references devices,
    received timestamptz, -- part of the pk for this table
    device_time timestamptz, -- timestamp from device not relied upon
    geom geometry('POINT',4326), -- postgis spatial data_type
    extradata json,
    primary key (device_id, received)
);

create table activity (
    device_id uuid references devices,
    received timestamptz,
    device_time timestamptz,
    activity json, -- whatever activity is provided by the device
    primary key (device_id, received)
);

You would then query the data using queries that interpolate the two time series over a given time interval. So that you would generate a path made up of line segments between location updates and assign derived positions to activity updates based on the times they came in.

You will note that in the table design above the received time is treated differently than the time reported by the sensor devices. This is because the received time is more likely to be monotonically increasing but your application might have different needs; in which case you probably want to add a composite index to query by device time as well as received time.