[postgis-users] day length calculation

[Kirk Wythers]

Thanks David. Thanks Brent. I had actually started with that same  
equation David. One question though (although perhaps it just shows  
my ignorance of geometry concept)... The metblogs equation takes  
latitude in degrees and converts it to radians with 0.65944. I see  
that you have dropped that conversion and use $1 to hold latitude and  
do the conversion in the equation.

My question is that I actually have two geometry columns in the sites  
table (one in degrees and one in meters). I assume that you are  
talking about using the Here is the structure:

met_data=# \d sites
               Table "public.sites"
      Column      |       Type        | Modifiers
-----------------+-------------------+-----------
station_id      | integer           | not null
name            | character varying |
longname        | character varying |
state           | character varying |
lat             | double precision  |
lon             | double precision  |
thepoint_lonlat | geometry          |
thepoint_meter  | geometry          | not null
Indexes:
     "sites_pkey" PRIMARY KEY, btree (station_id)
     "idx_sites_thepoint_lonlat" gist (thepoint_lonlat)
     "idx_sites_thepoint_meter" gist (thepoint_meter) CLUSTER
Check constraints:
     "enforce_dims_thepoint_lonlat" CHECK (ndims(thepoint_lonlat) = 2)
     "enforce_dims_thepoint_meter" CHECK (ndims(thepoint_meter) = 2)
     "enforce_geotype_thepoint_lonlat" CHECK (geometrytype 
(thepoint_lonlat) = 'POINT'::text OR thepoint_lonlat IS NULL)
     "enforce_geotype_thepoint_meter" CHECK (geometrytype 
(thepoint_meter) = 'POINT'::text OR thepoint_meter IS NULL)
     "enforce_srid_thepoint_lonlat" CHECK (srid(thepoint_lonlat) = 4269)
     "enforce_srid_thepoint_meter" CHECK (srid(thepoint_meter) = 32661)

So in your ussage:

>> so if you have a geometry column and a day of year column...
>> select daylength(Y(geom),dayofyear) from your table;

you would be talking about the geometry coumn "thepoint_lonlat" Correct?

Also, wouldn't the lat column work as well in your SELECT query?

Thanks for all the effort to pull that example together.

Kirk

On Jan 26, 2007, at 3:13 PM, Brent Wood wrote:

>
> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users