SMS tables
From ecology
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) |
