[postgis-users] joining "nearest" data

[Kirk Wythers]

On Feb 13, 2007, at 5:01 PM, Obe, Regina wrote:

> SELECT station_id, distance(s.thepoint_meter, sol.thepoint_meter)   
> As dist
> FROM station s, solar sol
> (you are right I'm aliasing station s and sol for solar and I made  
> the possibly incorrect assumption that you have a thepoint_meter in  
> your solar table.  If you don't you'd have to do another alias join  
> with station to get the point location.).

I'm still struggling with this part Regina. thepoint_meter is not in  
the solar table. geometry is in the sites table only (there is no  
station table, station_id is a column name). So I will need to do a  
third join (sites on solar). If I follow you correctly.

I have simplified my select with aliases (so it's shorter), removed  
the nextval bit, and now have:

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 LEFT OUTER JOIN weather w USING (station_id))
FULL OUTER JOIN solar s USING (obs_id)


However, I am confused on part where I need to do an alias join of  
the sites.thepoint_meter with solar. I think what I need is these  
three joins. (I was getting lost in all the parentheses):

FROM sites LEFT OUTER JOIN weather w USING (station_id)
FROM sites LEFT OUTER JOIN solar s USING (station_id)
FROM w FULL OUTER JOIN s USING (obs_id)

Sorry for making this such a struggle. 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070213/23fdf168/attachment.html>