Thread: Distance calculation

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;

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

On Tue, Jan 17, 2006 at 10:00:22AM -0800, sunithab@travelpost.com wrote:
> 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.

You appear to be using PostGIS but the query you posted doesn't use
any of PostGIS's indexable operators.  Have you read the "Using
PostGIS" documentation, in particular the parts that discuss creating
and using indexes on geometry columns?

http://postgis.refractions.net/docs/ch04.html

Your data looks like it has separate lat/lon columns when it should
have a geometry column with a GiST index.

If you're using PostGIS then you might want to subscribe to the
postgis-users mailing list:

http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Michael Fuhr

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