sunithab@travelpost.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