<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.5730.11" name=GENERATOR></HEAD>
<BODY
style="WORD-WRAP: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space">
<DIV dir=ltr align=left>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=752473911-14022007>I'm a
bit confused - so does solar have a station_id or not or do you get by joining
with the weather? Sorry the USING syntax has always confused me reading it
so I try to avoid it even though not using it makes my statements a bit
longer. I'm going to assume that solar has a
station_id.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=752473911-14022007>The
join would get pretty hairy since you need it twice, so I would create one view
that joins the two together to serve as your solar. It also occurred to me
that you can save quite a bit of speed (to compensate for the lack of closest
index feature that Paul mentioned) by doing a UNION instead. I'm assuming
that we already know that if a solar's station id matches the site, its the
closest so no need to bother the exhaustive min distance search for those.
So the revised query unions those records with solars on the site with those
that don't have a solar on the site.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=752473911-14022007>CREATE
VIEW vwsolar AS</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007> <FONT face=Arial color=#0000ff
size=2>s.date, s.year, s.month, s.day, s.doy, s.rad, s.dss, s.par,
sites.thepoint_meter</FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007> FROM sites INNER JOIN solar s ON
sites.station_id = s.station_id</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007>----Then your final query becomes (using a
union)</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>SELECT w.obs_id,
sites.station_id, sites.longname, sites.lat, sites.lon, sites.thepoint_meter,
</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>w.date, w.year, w.month, w.day,
w.doy, w.precip, w.tmin, w.tmax, w.tmean, w.snowfall, w.snowdepth, </FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>s.date, s.year, s.month, s.day,
s.doy, s.rad, s.dss, s.par</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>FROM sites <SPAN
class=752473911-14022007>INNER JOIN solar s ON sites.station_id = s.station_id
</SPAN>LEFT OUTER JOIN weather w <SPAN class=752473911-14022007>ON
w.station_id = sites.station_id</SPAN></FONT></DIV>
<DIV><SPAN class=752473911-14022007><FONT face=Arial color=#0000ff
size=2>UNION</FONT></SPAN></DIV>
<DIV><SPAN class=752473911-14022007>
<DIV><FONT face=Arial color=#0000ff size=2>SELECT w.obs_id,
sites.station_id, sites.longname, sites.lat, sites.lon, sites.thepoint_meter,
</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>w.date, w.year, w.month, w.day,
w.doy, w.precip, w.tmin, w.tmax, w.tmean, w.snowfall, w.snowdepth, </FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>s.date, s.year, s.month, s.day,
s.doy, s.rad, s.dss, s.par</FONT></DIV>
<DIV><FONT color=#0000ff><FONT face=Arial><FONT size=2>FROM sites <SPAN
class=752473911-14022007>INNER</SPAN></FONT></FONT><SPAN
class=752473911-14022007><FONT face=Arial><FONT size=2> JOIN <FONT
color=#000000> </FONT></FONT></FONT>
<DIV dir=ltr><FONT face=Arial size=2>(SELECT <SPAN
class=752473911-14022007>s2.</SPAN>station_id, min(distance(s<SPAN
class=752473911-14022007>2</SPAN>.thepoint_meter,
sol.thepoint_meter)) As mindist</FONT></DIV>
<DIV dir=ltr><FONT face=Arial><FONT size=2>FROM station s<SPAN
class=752473911-14022007>2</SPAN><SPAN class=752473911-14022007> LEFT
JOIN solar nosols ON s2.station_id = nosols.station_id</SPAN> <SPAN
class=752473911-14022007>CROSS JOIN vwsolar sol</SPAN></FONT></FONT></DIV>
<DIV dir=ltr><FONT face=Arial><FONT size=2><SPAN class=752473911-14022007>WHERE
nosols.station_id IS NULL</SPAN></FONT></FONT></DIV>
<DIV dir=ltr><FONT face=Arial><FONT size=2>GROUP BY s<SPAN
class=752473911-14022007>2</SPAN>.station_id) <SPAN
class=752473911-14022007>solmin</SPAN></FONT></FONT></DIV></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=752473911-14022007> ON sites.station_id =
solmin.station_id INNER JOIN vwsolar s ON distance(sites.thepoint_meter,
s.thepoint_meter) = solmin.mindistance </SPAN>LEFT JOIN weather
w <SPAN class=752473911-14022007>ON w.station_id =
sites.station_id</SPAN>)</FONT></DIV></SPAN></DIV>
<DIV><SPAN class=752473911-14022007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Kirk
Wythers<BR><B>Sent:</B> Tuesday, February 13, 2007 7:45 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] joining "nearest"
data<BR></FONT><BR></DIV>
<DIV></DIV><BR>
<DIV>
<DIV>On Feb 13, 2007, at 5:01 PM, Obe, Regina wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<P style="MARGIN: 0px"><FONT style="FONT: 12px Helvetica" face=Helvetica
size=3>SELECT station_id, distance(s.thepoint_meter, sol.thepoint_meter)<SPAN
class=Apple-converted-space> </SPAN>As dist</FONT></P>
<P style="MARGIN: 0px"><FONT style="FONT: 12px Helvetica" face=Helvetica
size=3>FROM station s, solar sol</FONT></P>
<P style="MARGIN: 0px"><FONT style="FONT: 12px Helvetica" face=Helvetica
size=3>(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.<SPAN class=Apple-converted-space> </SPAN>If you don't you'd have
to do another alias join with station to get the point
location.).</FONT></P></BLOCKQUOTE></DIV><BR>
<DIV>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.</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>I have simplified my select with aliases (so it's shorter), removed the
nextval bit, and now have:</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>SELECT w.obs_id, sites.station_id, sites.longname, sites.lat,
sites.lon, sites.thepoint_meter, </DIV>
<DIV>w.date, w.year, w.month, w.day, w.doy, w.precip, w.tmin, w.tmax, w.tmean,
w.snowfall, w.snowdepth, </DIV>
<DIV>s.date, s.year, s.month, s.day, s.doy, s.rad, s.dss, s.par</DIV>
<DIV>FROM (sites LEFT OUTER JOIN weather w USING (station_id)) </DIV>
<DIV>FULL OUTER JOIN solar s USING (obs_id) </DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>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):</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>FROM sites LEFT OUTER JOIN weather w USING (station_id)</DIV>
<DIV>FROM sites LEFT OUTER JOIN solar s USING (station_id)</DIV>
<DIV>FROM w FULL OUTER JOIN s USING (obs_id)</DIV>
<DIV><BR class=khtml-block-placeholder></DIV>
<DIV>Sorry for making this such a struggle. </DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P><br>
<P><STRONG><br>
The substance of this message, including any attachments, may be<br>
confidential, legally privileged and/or exempt from disclosure<br>
pursuant to Massachusetts law. It is intended solely for the<br>
addressee. If you received this in error, please contact the sender<br>
and delete the material from any computer.<br>
</STRONG></P></BODY></HTML>