[postgis-users] joining "nearest" data

[Kirk Wythers]

On Feb 14, 2007, at 6:20 AM, Obe, Regina wrote:

> SELECT w.obs_id,  sites.station_id, sites.longname, sites.lat,  
> sites.lon, sites.thepoint_meter,
> w.date, w.year, w.month, w.day, w.doy, w.precip, w.tmin, w.tmax,  
> w.tmean, w.snowfall, w.snowdepth,
> s.date, s.year, s.month, s.day, s.doy, s.rad, s.dss, s.par
> FROM sites INNER JOIN solar s ON sites.station_id = s.station_id  
> LEFT OUTER JOIN weather w ON w.station_id = sites.station_id
> UNION
> SELECT w.obs_id,  sites.station_id, sites.longname, sites.lat,  
> sites.lon, sites.thepoint_meter,
> w.date, w.year, w.month, w.day, w.doy, w.precip, w.tmin, w.tmax,  
> w.tmean, w.snowfall, w.snowdepth,
> s.date, s.year, s.month, s.day, s.doy, s.rad, s.dss, s.par
> FROM sites INNER JOIN
> (SELECT s2.station_id, min(distance(s2.thepoint_meter,  
> sol.thepoint_meter))  As mindist
> FROM station s2 LEFT JOIN solar nosols ON s2.station_id =  
> nosols.station_id CROSS JOIN vwsolar sol

Regina, did you mean FROM sites s2 LEFT JOIN here? There is no  
"station" table, nor have we aliased one


> WHERE nosols.station_id IS NULL
> GROUP BY s2.station_id) solmin
>     ON sites.station_id = solmin.station_id INNER JOIN vwsolar s ON  
> distance(sites.thepoint_meter, s.thepoint_meter) =  
> solmin.mindistance LEFT JOIN weather w ON w.station_id =  
> sites.station_id)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070214/8dfda369/attachment.html>