Re: Distance calculation

sunithab(at)travelpost(dot)com schrieb:
> Hi
>
> I have a latiude and longitude for a city and latitude, longitude
> foreach hotel in hotels table.
>
> I have to reitreive 20 hotels nearby to that city in 25 miles. The
> below is the query I am using to check the distance. But the query is
> slow because of distance calulation on fly and order by distance.
>
> Can anybody help me how can I improve performance by refining lat and
> long data.

Actually I was using cube and earth datatype from contrib directory.

Instead of saving latiude/longitude I had earth (basically
a 0-dimensonal cube so you have 3-coordinates measured from
center of the earth in meters (you can also calculate in archaic
miles measurement by changing the constant for the earth radius -
see the files in contrib)

You can create an index on the earth-column (coordinates).

Next I was using cube_enlarge(earth_coordinates,radius,3) to get a
cube which covers nearest cities using the index:

cube_enlarge(start.coordinates,radius,3) @ cities.coordinates

now since you rules out a lot points far away you can fine scan
using earth_distance:

AND earth_distance(start.coordinates,cities.coordinates) < radius

this is really fast since only a couple of cities are outside
the circle (actually sphere) but inside the cube.

HTH
Tino