Received: from smtp-1.attica.net.nz (pop3-1.attica.net.nz [202.180.66.199]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6T4TRf35533 for ; Sun, 29 Jul 2001 00:29:27 -0400 (EDT) (envelope-from markir@slingshot.co.nz) Received: (qmail 22716 invoked from network); 29 Jul 2001 04:29:20 -0000 Received: from 202-180-68-154.nas1.wgn.attica.net.nz (HELO spikey.slithery.org) (202.180.68.154) by pop3-1.attica.net.nz with SMTP; 29 Jul 2001 04:29:20 -0000 Content-Type: text/plain; charset="iso-8859-1" From: Mark kirkwood To: Ryan Littrell Subject: Re: performance issue with distance function Date: Sun, 29 Jul 2001 16:30:13 +1200 X-Mailer: KMail [version 1.2] Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Message-Id: <01072916301301.01095@spikey.slithery.org> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/374 X-Sequence-Number: 4188 Hi Ryan, There is a bit of a strange way around the distance overhead issue : Create another table with structure like (lat1,long1,zip1,lat2,long2,zip2,distance) and precalculate the distance for each possibility. This means n*(n-1) rows if you have n location rows. You would then include this table in your query and use distance like you wanted to initially ( should work fast provided you index it on lat1,long1,distance) The calculation overhead of distance is then removed from your query ( at the expense of some disk space ). The insert of each new location requires n calculations of distance - you could perform this in the background I guess ! regards Mark