Distance between geolocations in a query

[Ruud Schroen]

Hi,

I'm developing a portal for students where they can search for places they can rent.

It features an option where you select a location and a maximum allowed distance.

The locations you can choose from have a hard-coded geolocation. The records in for example db.places also have a geolocation based on the address, postcal code, etc.

Now when I fetch the records, i only want those records which have a distance equal to or below the given distance.

I found a way to calculate the distance in kilometers between latitude and longitude points, but I'm afraid that if i have to check each record, my site will get really slow when there are alot of records.

Anyone got some tips?

[Oscar Fonts]

Hi Ruud,

> when I fetch the records, i only want those records which have a distance equal to or below the given distance.

The usual technique in the geospatial world is to first select the points from the containing rectangle (bounding box, or BBOX), which is a much simpler calculation that helps discard most of the unwanted points. Then calculate the distance against the points inside that BBOX. Something like:

SELECT * FROM table
WHERE

  LAT >= :min_lat

  AND LAT <= :max_lat
  AND LON >= :min_lon

  AND LON <= :max_lon

HAVING (distance_calculation) <= :radius

The HAVING condition is only checked against the rows satisfying the WHERE conditions, so it's a good way to refine your selection with an expensive calculation.

Obviously you'll want to index LAT and LON fields as well.

Details on formulae for bbox and distance calculations:
http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

--

Oscar Fonts
www.geomati.co

[Ruud Schroen]

I found a Python version of the link you posted:

>>> loc = GeoLocation.from_degrees(51.441642, 5.469722)

>>> distance = 1000  # 1 kilometer

>>> SW_loc, NE_loc = loc.bounding_locations(distance)

>>> print SW_loc

>>> print NE_loc

>>> print loc.distance_to(SW_loc)

>>> print loc.distance_to(NE_loc)

This function should create a box.

Now this is the output in my terminal:

(42.4584deg, -9.0379deg) = (0.741040rad, -0.157741rad)

(60.4248deg, 19.9773deg) = (1.054613rad, 0.348670rad)

1483.52131402

1342.64671093

Also, I saw this post.

My guess is that I should do the following:
Get the logged in users geo information (lat and long)
Get the distance away that I want to search around that person and calculate the lat and logitudes of that area...
Find if the lat and log of the items.lat and items.long in my database are within the regions of the users.lat and users.log and the (areas.lat and areas.long)* for for four directions.

What values do i need to use in my query?

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/ahO2ydF7yK0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Massimo Di Pierro]

This thread seems to ignore the fact that web2py_postgresql support GIS:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=st_distance

Given:

from gluon.dal import geoPoint

db.define_table('point', Field('name'), Field('loc','geometry()'))

query = db.point.loc.st_distance(geoPoint(-1,2))<10

rows = db(query).select(db.point.name)

[Oscar Fonts]

This thread seems to ignore the fact that web2py_postgresql support GIS.

Good point.

To calculate distances in meters over positions in degreees, define a "geography" field instead of a "geometry" one: http://postgis.refractions.net/docs/ST_Distance.html

The BBOX optimization still applies, unless you used "ST_DWithin": http://postgis.refractions.net/documentation/manual-2.0/ST_DWithin.html

Oscar-

[Ruud Schroen]

This doesn't seem to be working properly or I'm doing something wrong..

query = db.providers.geolocation.st_distance(geoPoint(51.340245, 5.530007))<5

providers = db(query).select()

 
I currently have one record. It's geometry field holds this value: POINT(51.247427 5.89289)

I'm using a hardcoded geoPoint to test it. It is AT LEAST 5 kilometers away from the geoPoint in the record. However.. it is still showing up in the results. What am I doing wrong?

EDIT: This is the distance returned by st_distance: 0.37456541860267917, kind of a strange number..

[Ruud Schroen]

Nevermind.. I was using a geometry field instead of a geography one, so it was showing the difference in degrees rather than meters.