[postgis-users] joining "nearest" data

[Kirk Wythers]

On Feb 13, 2007, at 9:20 AM, Obe, Regina wrote:

> What't the nextval('climate_id_seq') in your view for?

nextval was actually a left over from earlier attempts to create a  
workable primary key. The better solution was creating obs_id  
(observation_id by combining station_id and date resulting in a  
unique value since each site had only one observation date)

> Also wouldn't
> all your weather data have sites - seems like it should be sites LEFT
> OUTER JOIN weather.

You are correct.

>
> Here is my thought on how to get the nearest solar using a subselect
> that finds the mindistance between a station and a solar point.   
> You may
> get more than one record if there are more solar locations equidistant
> from your station in close proximity to station.
>
>
> FROM (weather LEFT OUTER JOIN sites USING (station_id))
> 	LEFT JOIN
> 	(s.station_id, min(distance(s.thepoint_meter,
> sol.thepoint_meter) as mindist FROM solar sol, sites s GROUP BY
> s.station_id) solmin
> 	ON solmin.station_id = sites.station_id  AND (solar.station_id =
> solmin.station_id or distance(station.thepoint_meter,
> solar.thepoint_meter) = solmin.mindist  )

I am having trouble reading my way through this. It looks like you  
are using sol as an alias for solar and s for sites. Correct? Also,  
how are you using GROUP BY? And can you explain solmin and mindist in  
a little more detail for the weak of mind? Where did they come from?