Received: from mx1.relaypoint.net (ns2.generalbroadband.com [64.32.62.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f98FXmn89224 for ; Mon, 8 Oct 2001 11:33:48 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from [206.19.64.3] (HELO jecw2k1) by mx1.relaypoint.net (CommuniGate Pro SMTP 3.4.8) with SMTP id 624385; Mon, 08 Oct 2001 08:33:39 -0700 Message-ID: <00a701c1500e$9e34a8e0$6cdc10ac@jecw2k1> From: "Joe Conway" To: "Chris Ruprecht" , "pgsql" References: <001a01c14ff8$e0a3cf00$5dd26383@corp.compucom.com> Subject: Re: Search by longitude/latitude Date: Mon, 8 Oct 2001 08:33:45 -0700 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_00A4_01C14FD3.F17EB030" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200110/120 X-Sequence-Number: 5075 This is a multi-part message in MIME format. ------=_NextPart_000_00A4_01C14FD3.F17EB030 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > 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 > Here's a plpgsql function that will do what you need. You might also look at the earthdistance code in contrib if you'd rather have a C function. HTH, Joe ------=_NextPart_000_00A4_01C14FD3.F17EB030 Content-Type: application/octet-stream; name="geodist.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="geodist.sql" DROP FUNCTION geodist(float8,float8,float8,float8); CREATE FUNCTION geodist(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 :=3D 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(si= n(radians(lon2)/2 - radians(lon1)/2),2)))); return dist; END; ' LANGUAGE 'plpgsql'; ------=_NextPart_000_00A4_01C14FD3.F17EB030--