[postgis-users] extending a database with another table
Kirk R. Wythers
kwythers at umn.edu
Tue Nov 14 12:49:22 PST 2006
I am looking for suggestions on database design.
I have a simple three table database that holds climate data. The
first table (stations) holds station id, and geoposition information.
The second table (climate) holds temp and precip information, and the
third table (solar) holds solar radiation data. The column station_id
acts as a foreign key to link the tables.
My trouble is that I need to add a forth table (airports) that in the
same manner as "stations" in that it contains a station_id number, on
which data lines from "solar" and "climate" key off of. The goal here
is to be able to differentiate solar data from airports and solar
data from the stations.
Below are the three current tables (stations, climate, and solar).
Any ideas would be most appreciated.
met_data=# \d stations
Table "public.stations"
Column | Type | Modifiers
-----------------+-------------------+-----------
station_id | integer | not null
station_name | character varying |
state | character varying |
lat | double precision |
lon | double precision |
the_geom_lonlat | geometry |
the_geom_meters | geometry |
Indexes:
"stations_pkey" PRIMARY KEY, btree (station_id)
"stations_station_id_key" UNIQUE, btree (station_id)
Check constraints:
"enforce_dims_the_geom_lonlat" CHECK (ndims(the_geom_lonlat) = 2)
"enforce_dims_the_geom_meters" CHECK (ndims(the_geom_meters) = 2)
"enforce_geotype_the_geom_lonlat" CHECK (geometrytype
(the_geom_lonlat) = 'POINT'::text OR the_geom_lonlat IS NULL)
"enforce_geotype_the_geom_meters" CHECK (geometrytype
(the_geom_meters) = 'POINT'::text OR the_geom_meters IS NULL)
"enforce_srid_the_geom_lonlat" CHECK (srid(the_geom_lonlat) = 4269)
"enforce_srid_the_geom_meters" CHECK (srid(the_geom_meters) =
32661)
met_data=# \d climate
Table "public.climate"
Column | Type | Modifiers
------------+----------+-----------
station_id | integer |
year | smallint |
month | smallint |
day | smallint |
doy | smallint |
date | date |
precip | real |
tmin | real |
tmax | real |
Foreign-key constraints:
"climatefk" FOREIGN KEY (station_id) REFERENCES stations
(station_id)
met_data=# \d solar
Table "public.solar"
Column | Type | Modifiers
------------+----------+-----------
station_id | integer |
year | smallint |
month | smallint |
day | smallint |
doy | smallint |
date | date |
solar | real |
Foreign-key constraints:
"solarfk" FOREIGN KEY (station_id) REFERENCES stations(station_id)
More information about the postgis-users
mailing list