[postgis-users] joining "nearest" data

[Obe, Regina]

Yes sorry for the typo.  Oh I kind of see what you are doing now with the observe id after rereading what you had wrote.  So the query may be a little trickier  than I had proposed because I realize now that solars are observation points.
 
Do you always take these observations from the same sites -- e.g. for some days would you have solar data for a particular site and then for a different day no solar data for that site - but solar data for a different site or the sites are always consistent?  Sorry if that's  a stupid question - I'm not too familiar with how this kind of data is collected.  If the sites you get solar data change from day to day, then you might have to do a compound key join in the mindist query with date and station_id.
 
Regarding your question about views.  Yes they are almost always slower than caching the data in a table.  I usually try the view out first though since it provides more flexibility and see how slow it is and then resort to a static table if it is too slow.  Sometimes the speed difference is not that noticeable especially if you have a clustered index on the station_id field.  Sometimes I think the view can actually be faster because in this case your station id table is probably much smaller than your weather solar data so the number of geometries you'd be going thru would be smaller and the planner would probably come out with a better plan.  If the geometries are repeated in your solar/weather table, then it may perceive those geometries as being different when they are the same so may actually be doing more processing work when doing distance checks.

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Kirk Wythers
Sent: Wed 2/14/2007 3:12 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] joining "nearest" data



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


	
	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


Regina, did you mean FROM sites s2 LEFT JOIN here? There is no "station" table, nor have we aliased one



	
	
	
	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)





-----------------------------------------
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 --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 8787 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070214/46cf016f/attachment.bin>