pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLatitude / Longitude
8+ messages / 7 participants
[nested] [flat]
* Latitude / Longitude
@ 2002-09-12 17:44 Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Re: [SQL] Latitude / Longitude Larry Rosenman <ler@lerctr.org>
2002-09-12 19:19 ` Re: [SQL] Latitude / Longitude Jeff Hoffmann <jeff@propertykey.com>
2002-09-12 20:54 ` Re: Latitude / Longitude Joe Conway <mail@joeconway.com>
0 siblings, 3 replies; 8+ messages in thread
From: Tim Perdue @ 2002-09-12 17:44 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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.
Tim
--
Founder - PHPBuilder.com / Geocrawler.com / SourceForge
GPG Public Key: http://www.perdue.net/personal/pgp.php
Perdue, Inc. / Immortal LLC
515-554-9520
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
@ 2002-09-12 17:49 ` Larry Rosenman <ler@lerctr.org>
2002-09-12 19:46 ` Re: [SQL] Latitude / Longitude Bruno Wolff III <bruno@wolff.to>
2002-09-12 19:49 ` Re: [SQL] Latitude / Longitude Tim Perdue <tim@perdue.net>
2 siblings, 2 replies; 8+ messages in thread
From: Larry Rosenman @ 2002-09-12 17:49 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-general@postgresql.org; pgsql-sql
Look at contrib/earthdistance, I **think** it does what you need.
LER
On Thu, 2002-09-12 at 12:44, 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.
>
> Tim
>
> --
> Founder - PHPBuilder.com / Geocrawler.com / SourceForge
> GPG Public Key: http://www.perdue.net/personal/pgp.php
> Perdue, Inc. / Immortal LLC
> 515-554-9520
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Re: [SQL] Latitude / Longitude Larry Rosenman <ler@lerctr.org>
@ 2002-09-12 19:46 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2002-09-12 19:46 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: Tim Perdue <tim@perdue.net>; pgsql-general@postgresql.org; pgsql-sql
On Thu, Sep 12, 2002 at 12:49:21 -0500,
Larry Rosenman <ler@lerctr.org> wrote:
> Look at contrib/earthdistance, I **think** it does what you need.
There isn't an index for the distance operator. You can use boxes to
limit the candidates if there is a small bound on how far apart points
can be before they are no longer candidates for a match even if they
are a nearest neighbor pair.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Re: [SQL] Latitude / Longitude Larry Rosenman <ler@lerctr.org>
@ 2002-09-12 19:49 ` Tim Perdue <tim@perdue.net>
2002-09-12 22:10 ` Re: [SQL] Latitude / Longitude James Orr <james@orrwhat.net>
2002-09-13 15:19 ` Re: [SQL] Latitude / Longitude Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 2 replies; 8+ messages in thread
From: Tim Perdue @ 2002-09-12 19:49 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Thu, Sep 12, 2002 at 12:49:21PM -0500, Larry Rosenman wrote:
> Look at contrib/earthdistance, I **think** it does what you need.
Thanks, yeah this starts to get me into the realm of what I need. It still
has to run every possible permutation to figure out which one is closest for
each postalcode. What I'll do is run that once and build a 3rd table which can
be used to join the other two together using a view.
Thanks,
Tim
--
Founder - PHPBuilder.com / Geocrawler.com / SourceForge
GPG Public Key: http://www.perdue.net/personal/pgp.php
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Re: [SQL] Latitude / Longitude Larry Rosenman <ler@lerctr.org>
2002-09-12 19:49 ` Re: [SQL] Latitude / Longitude Tim Perdue <tim@perdue.net>
@ 2002-09-12 22:10 ` James Orr <james@orrwhat.net>
1 sibling, 0 replies; 8+ messages in thread
From: James Orr @ 2002-09-12 22:10 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Thursday 12 September 2002 15:49, Tim Perdue wrote:
> On Thu, Sep 12, 2002 at 12:49:21PM -0500, Larry Rosenman wrote:
> > Look at contrib/earthdistance, I **think** it does what you need.
>
> Thanks, yeah this starts to get me into the realm of what I need. It still
> has to run every possible permutation to figure out which one is closest
> for each postalcode. What I'll do is run that once and build a 3rd table
> which can be used to join the other two together using a view.
If you are looking for everything within a certain radius of a zip code, what
I have done in the past is calculate the highest/lowest longitude and
latitude and then select anything that falls between them and only check
those ones to see if they fall within the radius.
That's saves a lot of time over checking the exact distance for every zip
code.
- James
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Re: [SQL] Latitude / Longitude Larry Rosenman <ler@lerctr.org>
2002-09-12 19:49 ` Re: [SQL] Latitude / Longitude Tim Perdue <tim@perdue.net>
@ 2002-09-13 15:19 ` Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jan Wieck @ 2002-09-13 15:19 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: Larry Rosenman <ler@lerctr.org>; pgsql-general@postgresql.org; pgsql-sql
Tim Perdue wrote:
>
> On Thu, Sep 12, 2002 at 12:49:21PM -0500, Larry Rosenman wrote:
> > Look at contrib/earthdistance, I **think** it does what you need.
>
> Thanks, yeah this starts to get me into the realm of what I need. It still
> has to run every possible permutation to figure out which one is closest for
> each postalcode. What I'll do is run that once and build a 3rd table which can
> be used to join the other two together using a view.
That third table should be maintained by triggers and
constraints, no?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being
right. #
# Let's break this rule - forgive
me. #
#==================================================
JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
@ 2002-09-12 19:19 ` Jeff Hoffmann <jeff@propertykey.com>
2 siblings, 0 replies; 8+ messages in thread
From: Jeff Hoffmann @ 2002-09-12 19:19 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Latitude / Longitude
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
@ 2002-09-12 20:54 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 8+ messages in thread
From: Joe Conway @ 2002-09-12 20:54 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-general@postgresql.org; 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 see you've gotten some other help, but in case you're interested, I'll give
you another alternative. Here's a plpgsql function I wrote a while ago based
on the Haversine formula:
CREATE FUNCTION "zipdist" (float8,float8,float8,float8 ) RETURNS float8 AS '
DECLARE
lat1 ALIAS FOR $1;
lon1 ALIAS FOR $2;
lat2 ALIAS FOR $3;
lon2 ALIAS FOR $4;
dist float8;
BEGIN
dist := 0.621 * 6371.2 * 2 *
atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 -
radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
pow(sin(radians(lon2)/2 - radians(lon1)/2),2))),sqrt(abs(1 -
pow(sin(radians(lat2)/2 - radians(lat1)/2),2) + cos(radians(lat1)) *
cos(radians(lat2)) * pow(sin(radians(lon2)/2 - radians(lon1)/2),2))));
return dist;
END;
' LANGUAGE 'plpgsql';
I used the following PHP code to start looking for a match in a small circle,
and then expand it if no matches were found:
$dist = INIT_DIST;
$cnt = 0;
$cntr = 0;
do {
if ((! $zip == "") && (! $dist <= 0)) {
$sql = get_zip_sql($lon1d,$lat1d,$dist,$numtoshow);
$rs = connexec($conn,$sql);
$rsf = rsfetchrs($rs);
$dist *= 2;
$cntr++;
} else {
$cntr = 10;
}
} while (count($rsf) < $numadvisorstoshow && $cntr < 10);
Hopefully you get the idea.
As was suggested, you can narrow the results using a box to make the query
perform better, and then sort by distance to get the closest alternative.
Here's the related part of get_zip_sql():
function get_zip_sql($lon1d,$lat1d,$dist,$numtoshow)
{
$sql = "
SELECT DISTINCT <fields>
FROM tbl_a AS a
,tbl_d AS d
,tbl_a_zipcodes AS az
,tbl_zipcodes as z
WHERE
abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
and zipdist($lat1d,$lon1d,lat,long) <= $dist
and z.zip = az.zipcode
<other criteria>
ORDER BY
LIMIT $numtoshow;
";
return $sql;
}
The "X * 60 * 1.15078" converts differences in degrees lat/long into rough
distances in miles. The zipdist function returns a more-or-less exact distance
using the Haversine formula.
Hope this helps. Let me know if you want/need more explanation of any of this.
Joe
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-09-13 15:19 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-12 17:44 Latitude / Longitude Tim Perdue <tim@perdue.net>
2002-09-12 17:49 ` Larry Rosenman <ler@lerctr.org>
2002-09-12 19:46 ` Bruno Wolff III <bruno@wolff.to>
2002-09-12 19:49 ` Tim Perdue <tim@perdue.net>
2002-09-12 22:10 ` James Orr <james@orrwhat.net>
2002-09-13 15:19 ` Jan Wieck <JanWieck@Yahoo.com>
2002-09-12 19:19 ` Jeff Hoffmann <jeff@propertykey.com>
2002-09-12 20:54 ` Joe Conway <mail@joeconway.com>
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