[postgis-users] joining "nearest" data

[Obe, Regina]

What't the nextval('climate_id_seq') in your view for?  Also wouldn't
all your weather data have sites - seems like it should be sites LEFT
OUTER JOIN weather.

Here is my thought on how to get the nearest solar using a subselect
that finds the mindistance between a station and a solar point.  You may
get more than one record if there are more solar locations equidistant
from your station in close proximity to station.

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))  
	LEFT JOIN
	(s.station_id, min(distance(s.thepoint_meter,
sol.thepoint_meter) as mindist FROM solar sol, sites s GROUP BY
s.station_id) solmin 
	ON solmin.station_id = sites.station_id  AND (solar.station_id =
solmin.station_id or distance(station.thepoint_meter,
solar.thepoint_meter) = solmin.mindist  )  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
Wythers
Sent: Tuesday, February 13, 2007 9:06 AM
To: PostGIS Users Discussion
Subject: [postgis-users] joining "nearest" data

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

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.