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?

  • I believe you should at the least choose the criteria that defines what is valid data and what is redundant data. Will this be interval based, or triggered by a value change? On the other hand, looking at the data sample you provided, it looks like your position data does not have the necessary resolution to record movement (unless you're travelling at light speed) since 1 degree latitude at the equator equals roughly 111km. – Techie_Gus Jan 23 '17 at 18:18
  • Yes my explanation was a bit unclear. The sample data was just a rough example. The position data has a higher precision (Geolocation for mobile) and so does the rest of the data. Most of the data will be accepted as valid. The problem is the different frequencies in the data capture. I'll get more x y z than lat and lon. So I need a good way to store the data. – Eirik Aabøe Jan 23 '17 at 18:24

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.