SMS tables

From ecology
Revision as of 11:28, 11 May 2015 by Stefan verhoeven (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

The eEcology SMS reciever (https://github.com/NLeSC/eEcology-SMS-reciever) service recieves SMS messages as http POST requests and stores them in the eEcology database. The SMS messages are converted to http POST requests by the SMSSync Android app (http://smssync.ushahidi.com/).

The messages are stored in the PostgreSQL server at db.e-ecology.sara.nl in the eecology database and sms schema. The data can be inserted by the eEcology SMS reciever service. The data can be selected by users with the gps_read role.

Data is stored in 3 tables:

sms.raw_message table

Data received from sending party.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Auto incrementing
message_id uuid No No unique ID of the SMS
sent_from character varying No No number that sent the SMS
body character varying No No Body of the SMS
sent_to character varying No No the phone number registered on the SIM card otherwise it's the value set on the app as device ID
gateway_id character varying No No the unique id set on the device to be used by the server to identify which device is communicating with it
sent_timestamp timestamp with time zone No No the timestamp the SMS was sent

sms.message table

The parsed message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Foreign key sms.raw_message.id
device_info_serial integer No No Tracker identifier
date_time timestamp with time zone No No Datetime the message was recieved
battery_voltage real No No
memory_usage real No No
debug_info character varying No No

sms.position table

The positions parsed form the message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Foreign key sms.message.id
device_info_serial integer No No Tracker identifier
date_time timestamp with time zone Yes Yes Timestamp at which location was taken
lon double precision No No Longitude
lat double precision No No Latitude
location geometry(1107456) No No PostGIS location as a Point(lon, lat)