I'm no expert on this but can't you limit the points to checking any
hotel whose lat is +- 25km north/south of your city, AND whose longitude
is also +- 25km of your city. It's crude but will probably eliminate
lots of points you should never be checking...
If you could index an approx lat and long for each hotel you could use
two indexes (or one combined) on these fields to filter the results
BEFORE applying the DISTANCE function.
You'll need to compute your approx upper and lower bound lat and long
for filtering.
I'm not sure if this approach works, but it may help?
John
sunithab@xxxxxxxxxxxxxx wrote:
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.
v_point is the city lat and long.(point(citylong citylat))
SELECT pr.property_id
, pr.property_type As property_type_id
, pr.property_name
,round (DISTANCE( v_point:: geometry,
POINTFromText('Point(' ||pr.long ||' ' ||
pr.lat||')')::geometry) *69.055) as CityToHotelDistance
FROM property.property pr
INNER JOIN place p ON (pr.place_id = p.place_id)
INNER JOIN placedetail pd ON (p.place_id = pd.place_id)
LEFT OUTER JOIN property.vw_property_price vwp ON
(vwp.property_id = pr.property_id)
WHERE DISTANCE( v_point :: geometry,
POINTFromText('Point(' ||pr.long ||' ' ||
pr.lat||')')::geometry) < .4 AND pr.place_id != p_place_id
AND (pr.status_type_id is null OR pr.status_type_id = 0)
ORDER BY DISTANCE( v_point :: geometry,
POINTFromText('Point(' ||pr.long ||' ' ||
pr.lat||')')::geometry)
offset 0 LIMIT 20;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
