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

Collapse
3: Re: gps to zipcode query: postgres or postgresql + postgis or how? (response to 1)
Posted by russ m on 07/22/03 08:01 AM
It may also be worth trying changing Jerry's inner select to something like this 

select code, point_distance(loc, :X, :Y) as dist
  from zip
 where @ (:X - loc[0]) < x_delta_max
   and @ (:Y - loc[1]) < y_delta_max
 order by dist
 limit 1

to avoid doing an exact distance calculation unless the zipcode you're checking is relatively close to the point you're testing. [xy]_delta_max should be tuned depending on the maximum distance between centers of adjacent zipcode areas in the US.

This may of course have no noticable effect on performance, but it's an obvious first optimisation.

Collapse
4: Re: gps to zipcode query: postgres or postgresql + postgis or how? (response to 3)
Posted by Jerry Asher on 07/22/03 10:22 AM
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.