<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">Thanks Regina,<DIV><BR class="khtml-block-placeholder"></DIV><DIV><BR><DIV><DIV>On Feb 14, 2007, at 6:20 AM, Obe, Regina wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite"> <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? <BR></SPAN></FONT></DIV></DIV></BLOCKQUOTE><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Yes solar does contain station_id</DIV><BR><BLOCKQUOTE type="cite"><DIV dir="ltr" align="left"><DIV><FONT face="Arial" color="#0000ff" size="2"><SPAN class="752473911-14022007">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></BLOCKQUOTE><BLOCKQUOTE type="cite"><DIV dir="ltr" align="left"><DIV><BR class="khtml-block-placeholder"></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></BLOCKQUOTE><DIV><BR class="khtml-block-placeholder"></DIV><DIV>This makes sense. You are creating a solar view that contains your geometry data so that you can calculated mindist </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>As an aside, what would be faster, doing the query from views or tables? I ask because I could create two new tables weather2 (weather + thepoint_meter) and solar2 (solar + thepoint_meter). Then do your suggested UNION from there. </DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Speed is probably going to be an issue because I have over 7 million rows in the weather table. </DIV><BR><BLOCKQUOTE type="cite"><DIV dir="ltr" align="left"> <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></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>