Geoalchemy2 asks all users within X meters

Question

I have an application that takes an address bar, sends it to the Google Maps API and gets the lat / long coordinates. Then I want to show all users within X meters of this point (there lat / long is stored in my database), then I want the filter to be the result, to show only users with some pets

So i have my models

class User(UserMixin, Base): first_name = Column(Unicode) address = Column(Unicode) location = Column(Geometry('POINT')) pets = relationship('Pet', secondary=user_pets, backref='pets') class Pet(Base): __tablename__ = 'pets' id = Column(Integer, primary_key=True) name = Column(Unicode) user_pets = Table('user_pets', Base.metadata, Column('user_id', Integer, ForeignKey('users.id')), Column('pet_id', Integer, ForeignKey('pets.id')) ) 

I get my lat / long from the Google API and store it in my database, so from the address bar "London England" I get

 POINT (-0.1198244000000000 51.5112138999999871) 

this is stored in my database, for example:

 0101000000544843D7CFACBEBF5AE102756FC14940 

Now that everything is working fine, now, while reading the Geoalchemy2 docs, I cannot find the exmaple request to solve my problem.

What I want to convey is another set of lat / long coordinates for Geoalchemy2, and then return the next 10 users. While I ask about this, I also filter only users who have certain pets (this is not necessary for my job request, but I wanted to show that the request will really do in full).

I don’t like answering a question without offering an example query, but I really don’t know what functions I should use to achieve my desired result.

I assume that I will need to use "ST_DWithin" or "ST_DFullyWithin", but I cannot find a complete example of any of the functions. Thanks x.

So I have a work request

 distance = 10 address_string = "London, England" results = Geocoder.geocode(address_string) # load long[1], lat[0] into shapely center_point = Point(results.coordinates[1], results.coordinates[0]) print center_point # 'POINT (-0.1198244000000000 51.5112138999999871)' wkb_element = from_shape(center_point) users = DBSession.query(User).\ filter(func.ST_DWithin(User.location, wkb_element, distance)).all() 

What generates the following SQL

 2013-12-30 15:12:06,445 INFO [sqlalchemy.engine.base.Engine][Dummy-2] SELECT users.first_name AS users_first_name, users.last_name AS users_last_name, users.phone AS users_phone, users.address AS users_address, users.about AS users_about, ST_AsBinary(users.location) AS users_location, users.profile_image_id AS users_profile_image_id, users.searchable AS users_searchable, users.user_password AS users_user_password, users.registered_date AS users_registered_date, users.id AS users_id, users.last_login_date AS users_last_login_date, users.status AS users_status, users.user_name AS users_user_name, users.email AS users_email, users.security_code AS users_security_code FROM users WHERE ST_DWithin(users.location, ST_GeomFromWKB(%(ST_GeomFromWKB_1)s, %(ST_GeomFromWKB_2)s), %(param_1)s) 2013-12-30 15:12:06,445 INFO [sqlalchemy.engine.base.Engine][Dummy-2] {'ST_GeomFromWKB_1': <read-only buffer for 0x7f7d10258f70, size -1, offset 0 at 0x7f7d10258db0>, 'param_1': 10, 'ST_GeomFromWKB_2': -1} 

Now it always returns all my users, regardless of the distance variable, so I guess that something is wrong, right, but I can’t understand why.

Answer 1

Answer:

The units were in the radius of the degree, so I had to transfer the mile to degress to get the best (rough) rating. This does not have to be exact:

 d = 90 distance = d * 0.014472 #1 mile = 0.014472 degrees r1 = -0.1198244 r2 = 51.5112139 # load long[1], lat[0] into shapely center_point = Point(r1, r2) # 'POINT (-0.1198244000000000 51.5112138999999871)' wkb_element = from_shape(center_point) users = DBSession.query(User).\ filter(func.ST_DFullyWithin(User.location, wkb_element, distance)).all() 

Answer 2

Lat / Lon data is not suitable for distance calculations.

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the shape with a small ellipsoid of the earth) from 68,703 miles (110,567 km) at the equator to 69,407 (111,699 km) at the poles. This is convenient because every minute (1/60 degrees of a degree) is about one mile.

The longitude is the widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. 40 ° north or south, the distance between the degree of longitude is 53 miles (85 km).

You can use ST_Transform to convert your coordinates to another projection that uses meters or miles. They tend to be local as they project the Earth's sphere onto a rasterized plane. The British National Grid (SRID 27700) can suit your needs.