[postgis-users] query question (probably pitifully simple)

[Brent Wood]

Hi Gerry,

If your table with timestamped observations includes an id field for the
weather station which is also in the weather station you can create a view on
the join of the two tables. 

Summat like:

create view v_weather as
select <list of attrs from both tables that you want>,
from weather_stations s,
     weather_data d
where s.station_id=d.station_id;

the s & d become aliases for the full table names and can be used in the
attribute list, eg: s.station_id, s.geom, d.rainfall, ....

It might also be useful to include d.oid in the attr list, and to create a
unique index on this as well if you are using something like QGIS which uses
the Postgres oids. 

You can then treat v_weather just like a table. Make sure that the attribute
you are joining on (station_id) is uniquely indexed in the weather station
table, so weather data can only ever link to a single station, and that a
normal index is applied to the station_id in the data table, otherwise the join
will be very slow. 

Hope this makes sense...

  Brent



--- Gerry Creager N5JXS <gerry.creager at tamu.edu> wrote:

> If I have a spatially enabled table (weather station sites) with 
> point-geometry and an appropriate srid, and a "regular" PostgreSQL table 
> of timestamped weather observations, can I perform a query on the two 
> tables, effectively doing a join, to get the surface data for display 
> in, say, Mapserver?
> 
> Or, is this a better question to the mapserver list?
> 
> Thanks,
> Gerry
> -- 
> Gerry Creager -- gerry.creager at tamu.edu
> Texas Mesonet -- AATLT, Texas A&M University	
> Cell: 979.229.5301 Office: 979.458.4020 FAX: 979.847.8578
> Page: 979.228.0173
> Office: 903A Eller Bldg, TAMU, College Station, TX 77843
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>