Forum OpenACS Q&A: Re: gps to zipcode query: postgres or postgresql + postgis or how?

Ah, thank you, good point!

I was walking around tonight and got to thinking that perhaps the data is being stored all wrong. I want to know the closest zip code, but I don't care at all how far away I am from the center of that zip code.

Perhaps instead of storing X, Y, I should store X**2, Y**2, and then write my own distance function. Something like:

metric(zipx2, zipy2, x, y) {
   return (zipx2 - x**2) + (zipy2 - y**2)
}
This eliminates two multiplications and one square root for each measurement.

The question (that I will not be empirically answering until this thing becomes way more successful than it ever will) is if this function (as a pl/sql function) would be faster than the built-in point_distance function.

To further optimize the math, the lat/lon information can probably be represented by a Postgres INT2 representation (by scaling the actual values appropriately.) That should allow for all integer arithmetic to be used.