[postgis-users] extending a database with another table

[Jan Syryn]

Your question has nothing to do with PostGis problems, but is merely a
general DB design problem.

But because we are all nice guysz all together:

Don't add a table 'airports'.
Just extend station with a column stationtype_id(char 1) that points (fk) to
another table
'stationtype'.
Stationtype with id(char1) and stationtype_name(varchar(20))
Fill it with ('S' ; 'Station') and ('A', 'Airport')

Problem solved.

You want solar and climate for airports:
  SELECT climate.*, solar.* 
  FROM climate,solar,station
  WHERE climate.station_id = station.station_id
  AND solar.station_id = station.station_id
  AND station.stationtype_id='A'

Replace 'A' by 'S' for stations.

-----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: mardi 14 novembre 2006 21:49
To: PostGIS Discussion Users
Subject: [postgis-users] extending a database with another table

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)









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