[postgis-users] day length calculation
Brent Wood
pcreso at pcreso.com
Fri Jan 26 12:56:13 PST 2007
--- 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
More information about the postgis-users
mailing list