[postgis-users] joining "nearest" data

[Kirk Wythers]

postgis-ers,

I have a tricky query to figure out (well... tricky for me anyway). I  
have a climate database that consists of three tables:

table1 - site and geometry data
table2 - temperature and precip data
table3 - soar radiation data

I have constructed a join that combines these tables into a single  
view. The trouble is that there are some sites that contain only  
solar radiation data, and some sites that contain only temp and  
precip data. In some cases sites with solar data are very close to  
the temp and precip sites (less than a km away), but in other cases,  
the nearest solar site may be 10s of km away.

I would like to be able to create a join where the "nearest" solar  
data is joined to the temp and precip data.

How difficult would this be?

Here is the join statement that seems to grab everything but the  
sites that contain only solar data:

SELECT nextval( 'climate_id_seq' ), sites.station_id, sites.longname,  
sites.lat, sites.lon, sites.thepoint_meter,
weather.date, weather.year, weather.month, weather.day, weather.doy,  
weather.precip, weather.tmin, weather.tmax,
weather.tmean, weather.snowfall, weather.snowdepth, solar.date,  
solar.year, solar.month, solar.day, solar.doy,
solar.rad, solar.dss, solar.par
FROM (weather LEFT OUTER JOIN sites USING (station_id)) FULL OUTER  
JOIN solar USING (obs_id)

Thanks,

Kirk