On Thursday 12 February 2009 12:06:41 am Paolo Saudin wrote:
> Hi,
>
> I have 14 tables filled with meteorological data, one record per parameter
> per hour. The id field holds the parameter type (1=temperature, 2=humidity
> ...) My problem is that for short periods (maybe one week, one month) there
> are two stations with the same data, I mean the temperature of table1 is
> equal to the humidity of table3. I need to discover those cases.
Before I can start to answer this I need some clarification. How can temperature
and humidity be the same data?
>
> I could pick one record in the first station and then compare it with the
> ones in the other tables for all the parameters at that particular date. If
> two records are equals (it probably happens) I must then check the next one
> in the timeserie. If the second record is equal too, then probably the two
> series may be equals and I must raise an alert from my application. Is
> there a better and faster way to perform such a check ?
>
> -- tables
> CREATE TABLE table1
> (
> fulldate timestamp,
> id smallint NOT NULL,
> meanvalue real
> ) WITH (OIDS=FALSE);
> --.....................
> --.....................
> CREATE TABLE table14
> (
> fulldate timestamp,
> id smallint NOT NULL,
> meanvalue real
> ) WITH (OIDS=FALSE);
> --
> -- inserts
> insert into table1(select
> ('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
> 1::smallint, round(cast(random() as numeric), 1)::real
> from generate_series(0,1000) as s(a)
> );
> --
> insert into table2(select
> ('2009-01-01'::timestamp + interval '1 hour' * s.a)::timestamp,
> 1::smallint, round(cast(random() as numeric), 1)::real
> from generate_series(0,1000) as s(a)
> );
> --
> -- same data as table 1 -- MUST BE FOUND BY THE CKECK ROUTINE
> insert into table3(select fulldate, id, meanvalue from table1);
>
>
> Thank in advance,
> Paolo Saudin
--
Adrian Klaver
aklaver@comcast.net
