From: | Jeff Hoffmann <jeff(at)propertykey(dot)com> |
---|---|
To: | Tim Perdue <tim(at)perdue(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [SQL] Latitude / Longitude |
Date: | 2002-09-12 19:19:01 |
Message-ID: | 3D80E8A5.1080107@propertykey.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Bulmer | 2002-09-12 19:22:27 | Re: describe table query? |
Previous Message | Larry Rosenman | 2002-09-12 17:49:21 | Re: [SQL] Latitude / Longitude |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-09-12 19:34:07 | Re: LIMIT within UNION? |
Previous Message | Andrew Perrin | 2002-09-12 19:11:45 | LIMIT within UNION? |