[postgis-users] joining "nearest" data

[Kirk Wythers]

Thanks Regina,


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

> I'm a bit confused - so does solar have a station_id or not or do  
> you get by joining with the weather?

Yes solar does contain station_id

> 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.
>
> 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.
>
>
> CREATE VIEW vwsolar AS
>     s.date, s.year, s.month, s.day, s.doy, s.rad, s.dss, s.par,  
> sites.thepoint_meter
>     FROM sites INNER JOIN solar s ON sites.station_id = s.station_id

This makes sense. You are creating a solar view that contains your  
geometry data so that you can calculated mindist

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.

Speed is probably going to be an issue because I have over 7 million  
rows in the weather table.

>
> ----Then your final query becomes (using a 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 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
> 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/a96cd949/attachment.html>