Re: [SQL] Latitude / Longitude

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