# [postgis-users] day length calculation

Brent Wood pcreso at pcreso.com
Fri Jan 26 13:13:26 PST 2007

```Hi David,

That's much more elegant than my way :-)

Note that the geom in these statements is assumed to be a point, so for
polygons it would expand to someting like centroid(geom).

Cheers,

Brent

--- David William Bitner <david.bitner at gmail.com> wrote:

> CREATE OR REPLACE FUNCTION daylength(real, int)
>   RETURNS double precision AS
> 'select 24*(ACOS(1-(1-TAN(radians(\$1))*TAN(.409088 * COS(.0172024 *
> int))))/PI());'
>   LANGUAGE 'sql' VOLATILE;
>
> usage:
> select daylength(latitude,dayofyear)
>
> so if you have a geometry column and a day of year column...
> select daylength(Y(geom),dayofyear) from your table;
>
> If you want it to just take a date instead of dayofyear, you could use this:
>
> CREATE OR REPLACE FUNCTION daylength(real, date)
>   RETURNS double precision AS
> 'select 24*(ACOS(1-(1-TAN(radians(\$1))*TAN(.409088 * COS(.0172024 *
> extract(doy from \$2)))))/PI());'
>   LANGUAGE 'sql' VOLATILE;
>
> I pulled these equations from here:
> http://dc.metblogs.com/archives/2006/12/the_longest_nig.phtml
>
>
> David
>
> On 1/26/07, 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?
> >
> > Thanks,
> >
> > Kirk
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
> --
> ************************************
> David William Bitner
> > _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

```