[postgis-users] extending a database with another table

[Kirk R. Wythers]

Thanks for your patience Jan.

krw

On Nov 15, 2006, at 8:01 AM, Jan Syryn wrote:

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