Received: from coopers.dsl.net (coopers.dsl.net [65.84.81.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f98DaOn64326 for ; Mon, 8 Oct 2001 09:36:24 -0400 (EDT) (envelope-from james@lrgmail.com) Received: from jameslt (65-85-235-195.client.dsl.net [65.85.235.195]) by coopers.dsl.net (Postfix) with SMTP id 7DE7A1FD41; Mon, 8 Oct 2001 09:22:49 -0400 (EDT) Message-ID: <002701c14fff$03a22fa0$1600000a@lrg.office> From: "James Orr" To: "Chris Ruprecht" , "pgsql" References: <001a01c14ff8$e0a3cf00$5dd26383@corp.compucom.com> Subject: Re: Search by longitude/latitude Date: Mon, 8 Oct 2001 09:42:02 -0400 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0024_01C14FDD.7BF1DEA0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200110/117 X-Sequence-Number: 5072 This is a multi-part message in MIME format. ------=_NextPart_000_0024_01C14FDD.7BF1DEA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Here is some perl code which does what you want. ----- Original Message ----- From: "Chris Ruprecht" To: "pgsql" Sent: Monday, October 08, 2001 8:58 AM Subject: [SQL] Search by longitude/latitude > Hi all, > > I need to implement "Find all hospitals in a 5 mile radius". Say I have all > the coordinates on them stored in a table with the fields longitude and > latitude. Has anybody some sample code for that? > > Best regards, > Chris > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ------=_NextPart_000_0024_01C14FDD.7BF1DEA0 Content-Type: application/octet-stream; name="distance" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="distance" #! /usr/bin/perl use DBI; my $dbh =3D DBI->connect($connectstring, $user, $password); my $getzip =3D $dbh->prepare(" SELECT longitude, latitude=20 FROM zip_codes=20 WHERE zip =3D ? "); my $getarea =3D $dbh->prepare(" SELECT DISTINCT zip, city, longitude, latitude=20 FROM zip_codes=20 WHERE longitude BETWEEN ? AND ? AND latitude BETWEEN ? AND ? "); print "Enter a ZIP code : "; my $zip =3D ; chomp $zip; $getzip->execute($zip); ($long, $lat) =3D @{ $getzip->fetchall_arrayref->[0] }; print "Longitude: $long\n"; print "Latitude: $lat\n"; print "Enter a radius (miles): "; my $miles =3D ; chomp $miles; my $long_d =3D $miles / (69.1 * cos($lat / 57.3)); my $long_1 =3D $long - $long_d; my $long_2 =3D $long + $long_d; my $lat_1 =3D $lat - ($miles / 69.1); my $lat_2 =3D $lat + ($miles / 69.1); printf "Longitudes: %.4f %.4f\n", $long_1, $long_2; printf "Latitudes: %.4f %.4f\n\n", $lat_1, $lat_2; $getarea->execute($long_1, $long_2, $lat_1, $lat_2); $getarea->bind_columns(\$zip, \$city, \$lo, \$la); while ($getarea->fetch) { my $dlat =3D 69.1 * ($la - $lat); my $dlong =3D 69.1 * ($lo - $long) * cos($lat / 57.3); my $dist =3D sqrt(($dlat ** 2) + ($dlong ** 2)); printf "$zip $city (%.3f miles)\n", $dist if ($dist <=3D $miles); } $dbh->disconnect(); ------=_NextPart_000_0024_01C14FDD.7BF1DEA0--