[postgis-users] data base structure for holding climate data

[Obe, Regina]

I would go with the single timestamp function datatype. Note - I think
you want timestamp instead of date if you plan to do anything with hours
minutes and seconds, but otherwise date would be fine. 

It's a lot more flexible when you are trying to aggregate data or
compare data over month, years, weeks, days, minutes etc. since you can
then easily use the built-in postgres date functions.  

I'm assuming also you will do the same for lat lon - store them as
postgis geom point types to take advantage of the postgis spatial
functions.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
R. Wythers
Sent: Monday, October 02, 2006 11:11 AM
To: PostGIS Discussion Users
Subject: [postgis-users] data base structure for holding climate data

I am curious as to the consensus approach to handling dates in postgis?
I have gathered up a fairly large collection of climate data. The date
fields however, came parsed in the form (year,month,day). I want to set
up a postgres database to hold this data with a location structure along
the lines of:

mn_climate=# \d sites
                 Table "public.sites"
      Column     |         Type          | Modifiers
----------------+-----------------------+-----------
site_id        | integer               |
site_shortname | character varying(8)  |
site_name      | character varying(50) |
lat            | numeric               |
lon            | numeric               |
utm_e          | numeric               |
utm_n          | numeric               |
Indexes:
     "sites_site_id_key" UNIQUE, btree (site_id)


And tie this "site" table to a second "clim" table that holds the actual
meteorological data. However, I am inclined to concatenate the year,
month, day data into a single "date" data type of the form year-
month-day-hour-minute-second (yyyy-mm-dd-hh-mm-ss). Does this make sense
to you all? If anyone can re-direct me before I do something dumb in way
I set this up, I would appreciate it.

Thanks,

Kirk
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.