dostSensor

Stores DOST Sensor information taken from its Web Service into PostgreSQL/PostGIS database

Things to do:

• Create a PostgreSQL Database.

 createdb flood

• Add PostGIS extension

 psql flood
create extension postgis;
create extension postgis_topology;

• Create the Sensor tables

 create table asg (
id serial,
name text,
time timestamp,
value float,
water_level_change float,
time_difference float,
lon float,
lat float,
the_geom geometry('POINT',4326),
PRIMARY KEY ( name,time )
);

create table td (
id serial,
name text,
time timestamp,
sunrise timestamp,
sunset timestamp,
moonrise timestamp,
moonset timestamp,
value float,
water_level_change float,
time_difference float,
lon float,
lat float,
the_geom geometry('POINT',4326),
PRIMARY KEY ( name,time )
);

 create table arg (
id serial,
name text,
time timestamp,
value float,
lon float,
lat float,
the_geom geometry('POINT',4326),
PRIMARY KEY ( name,time )
);

 create table aws (
id serial,
name text,
time timestamp,
temp float,
humi float,
pres float,
rain float,
lon float,
lat float,
the_geom geometry('POINT',4326),
PRIMARY KEY ( name,time )
);

• Create view tables to access only the latest data

 create view asg_latest as select a.* from asg as a inner join (select name,max(time) as time from asg group by name) as b on a.name = b.name and a.time = b.time order by a.time desc;

 create view arg_latest as select a.* from arg as a inner join (select name,max(time) as time from arg group by name) as b on a.name = b.name and a.time = b.time order by a.time desc; 

 create view aws_latest as select a.* from aws as a inner join (select name,max(time) as time from aws group by name) as b on a.name = b.name and a.time = b.time order by a.time desc;

 create view td_latest as select a.* from td as a inner join (select name,max(time) as time from td group by name) as b on a.name = b.name and a.time = b.time order by a.time desc; 

• Compile and create a Runnable jar file ( in Eclipse ).

• Run the jar file that will populate the sensor table

 java -jar dostSensor.jar http://noah.dost.gov.ph/latest/download/station_all

• Create a Cron entry that will execute the program above and populate the sensor table at specific times.

• View the latest data in QGIS by opening the *_latest tables.