[postgis-users] day length calculation

[Brent Wood]

--- Kirk Wythers <kwythers at umn.edu> wrote:

> I am looking for ideas to add a daylength calculation to a postgis  
> database. The database in question has a table named "sites", which  
> contains lat, lon, and geometry info for a number of locations. A  
> second table contains climate data including a day_of_the_year column.
> 
> I would like to add the capability to calculate the length of each  
> day (could be done as a view or another table). The equation for day  
> length calculation takes as input day_of_the_year and latitude, does  
> some trig (uses acos, tan, cos, radians, and pi) and spits out an  
> estimate of the day length in hours.
> 
> How would folks recommend I approach this problem?

Hi Kirk,

You will need to create a site_day_length table, with an entry for every site
for every day. It could be implemented as a view, your call on that. For
occasional lookups a view will probably be fine, for frequent use, an indexed
table will be faster. The columns would be site_id & day_of_year as foreign
keys 
and day_length.

Postgres supports all the cos, tan, atan operators & even pi as a constant, so
you take the Y of your site centroid (assuming sites are polygons) transformed
to degrees (SRID=4326) & converted to radians, the rest is just writing the
equation using SQL operators.

eg; this should return the latitude in radians for the centroid of each site
     (if you want to speed things up a tiny bit, you could substitute 0.0174533
for pi/180):

select ((Y(centroid(transform(site,4326)))*pi)/180) from sites;

As your day of year is in a different table, you'll need to join the two
tables.

I believe this is an unusual case where there won't be a where clause
constraining the join, you'll get no of sites x 365 records.

create table demo as
select distinct 
       s.site_id, 
       ((Y(centroid(transform(s.site,4326)))*pi)/180) as lat_rad,
       c.day_of_year
from sites s, 
     climate c;

You could normalise this further, by having day_length keyed on latitude
instead of site_id, and adding the latitude as an explicit column in the sites
table (as sites of the same latitude will have the same day_length). Sightly
tider in terms of database design, but I think living with the duplicates &
keying on site_id is more user friendly.

The distinct is there because you climate table presumeably has more than one
year in it, so there would otherwise be a record output for every
site/day/year, you only want one for site/day.

I'll leave the SQL to calculate hours up to you :-)

HTH,

  Brent