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