Geoalchemy2 query all users within X meteres

Question

I have an app that takes an address string, sends it to Google Maps API and gets lat/long co-ordinates, I then want to show the all users within X meteres of this point (there lat/long is stored in my database), I then want to filter the result to only show users with certain pets

So first off, 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 Google API and store it in my database, so from the address string "London England" I get

POINT (-0.1198244000000000 51.5112138999999871)

this stores in my database like:

0101000000544843D7CFACBEBF5AE102756FC14940

Now that all works fine, now reading the Geoalchemy2 docs I cant seem to find an exmaple query to resolve my problem.

What I want to pass is another set of lat/long co-ordinates to Geoalchemy2 and then return the nearest say 10 users. Whilst querying this I will also filter only users that have certain pets (this isn't essential for my query to work, but I wanted to show what the query will actually do in its entirety).

I don't really like to answer a question without providing a sample query, but I really don't know what functions I should be using to achieve my required result.

I am guessing I will need to use "ST_DWithin" or "ST_DFullyWithin" but I cannot find a full example of either function. Thank's.

So I know have a working query

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()

Which 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 this always returns all my users, regardless of the distance variable, so I am guessing something is not quite, right, but I cannot work out why.

Answer 1

Answer:

The units was in a degree radius, so I had to convert miles to degress to get the best (rough) estimate. It doesn't need 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 particularly fit for distance calculations.

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

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

You can use ST_Transform to transform your coordinates to a different projection that uses meters or miles. These do tend to be local since they are projecting the earth's sphere onto a rasterized plane. The British national grid (SRID 27700) may be suited to your needs.