From: Jeff Hoffmann <jeff@propertykey.com>
To: Tim Perdue <tim@perdue.net>
To: pgsql-general@postgresql.org
Subject: Re: [SQL] Latitude / Longitude
Date: Thu, 12 Sep 2002 14:19:01 -0500
Message-ID: <3D80E8A5.1080107@propertykey.com> (raw)
References: <20020912124426.A27570@mail.perdue.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff@propertykey.com, tim@perdue.net, pgsql-general@postgresql.org
Subject: Re: [SQL] Latitude / Longitude
In-Reply-To: <3D80E8A5.1080107@propertykey.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox