sunithab@xxxxxxxxxxxxxx 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
