SMS tables

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)