[postgis-users] joining "nearest" data

[Obe, Regina]

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.
 
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
 
----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)
 
 
________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kirk
Wythers
Sent: Tuesday, February 13, 2007 7:45 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] joining "nearest" data



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. 



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070214/26a6408d/attachment.html>