Message-ID: <3D80E8A5.1080107@propertykey.com>
Date: Thu, 12 Sep 2002 14:19:01 -0500
From: Jeff Hoffmann <jeff@propertykey.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Tim Perdue <tim@perdue.net>, pgsql-general@postgresql.org
Subject: Re: [SQL] Latitude / Longitude
References: <20020912124426.A27570@mail.perdue.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tim Perdue wrote:
> Hi Folks -
> 
> I'm working on a global weather database for a client and have hit an issue
> which I'm sure has been solved before. Unfortunately, the site search doesn't
> pull up anything.
> 
> Essentially, I've got two tables, one with city/county/lat/long and another
> with lat/long/weather data.
> 
> None of the lat/longs in the two tables match up directly, so I can't do a
> simple join of the two tables. I need to join on closest proximity on the
> lat/long fields.
> 
> Any suggestions? It seems to me this will be pretty expensive on CPU resources
> unless there's a really elegant trick uncovered.

I guess that all depends on how you define expensive on CPU resources. 
I think the way I would do it is to define an sql function.  For 
example, assume the following table structure:

create table a (city text, city_location point, city_weather text);
create table b (weather text, weather_location point);

Create a function something like:

create function closest_weather (point) returns text as
'select b.weather from b order by $1 <-> b.weather_location limit 1;'
language sql;

Then you could do something like:

update a set city_weather = closest_weather(city_location);

And if you had the lat/long as seperate numbers, just cast them as a 
point (i.e., point(long,lat) will return a point type).  It's going to 
do a table scan for each city it updates, but that may or may not be a 
problem for you.

-- 

Jeff Hoffmann
PropertyKey.com