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

Obe, Regina robe.dnd at cityofboston.gov
Tue Oct 3 06:52:02 PDT 2006


If you read them in as simple xy coordinates, you have a processing
overhead everytime you do a spatial query because it has to convert

'POINT(....)' to a postgis point geometry to utilize the spatial
functions.

Also if you don't store them as postgis geometries, you can't take
advantage of gist indexing and clustered indexing which would improve
spatial queries significantly.

Thanks,
Regina

 

-----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: Tuesday, October 03, 2006 9:17 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] data base structure for holding climate
data


On Oct 2, 2006, at 10:22 AM, Obe, Regina wrote:

> 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.

Obe, How is storing lat lon as geom points different from reading them
into postgis as simple xy coordinates?

Thanks,

Kirk


>
> -----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.
>
> _______________________________________________
> 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



More information about the postgis-users mailing list