To: pgsql-performance@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Re: application of KNN code to US zipcode searches?
Date: Thu, 17 Feb 2011 10:55:53 -0500
Lines: 24
Message-ID: <ijjge9$qtt$1@dough.gmane.org>
References: <ijjc1o$vgu$1@dough.gmane.org>
	<4D5CE118020000250003AB83@gw.wicourts.gov>
	<ijjecs$e8c$1@dough.gmane.org>
	<20110217152445.GV4116@tamriel.snowman.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
In-Reply-To: <20110217152445.GV4116@tamriel.snowman.net>

> PostGIS, geometry columns, and UTM..  I'm not sure where they are wrt
> adding KNN support, but it's something they've been anxious to have for
> a while, so I expect support will come quickly.

I've looked into this a little more.

One approach seems to be to project the lat/long pairs on to a flat
plane using the Albers projection (which would be a one-time
calculation), and then the current KNN point/distance calculations could
be used.

Here's a Perl module that references the Albers projection (although
it's not yet clear to me how to use it):

http://search.cpan.org/dist/PDL/

And a Wikipedia page on various calculation possibilities:
http://en.wikipedia.org/wiki/Geographical_distance#Flat-surface_formulae

Further suggestions welcome.

   Thanks,

    Mark