| From: | <sunithab(at)travelpost(dot)com> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | Distance calculation |
| Date: | 2006-01-17 18:00:22 |
| Message-ID: | 001101c61b8f$e3211ff0$1301a8c0@sf.vagabond.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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;
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Fuhr | 2006-01-17 18:09:14 | Re: Distance calculation |
| Previous Message | Qingqing Zhou | 2006-01-17 17:56:13 | Re: Huge number of disk writes after migration to 8.1 |