Distance calculation
От | |
---|---|
Тема | Distance calculation |
Дата | |
Msg-id | 001101c61b8f$e3211ff0$1301a8c0@sf.vagabond.com обсуждение исходный текст |
Ответы |
Re: Distance calculation
Re: Distance calculation Re: Distance calculation |
Список | pgsql-general |
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;
В списке pgsql-general по дате отправления: