4

I have loaded into my db the cities1000.txt available here. I am attempting to create a query using GeoAlchemy to return the nearest city when I pass in Lat and Long. I currently have the query setup using ST_ClosestPoint, but it returns the same city no matter what lat/lon values I pass in. Here is the query I have written so far:

q = db.session.query(cities1000,
                     func.ST_ClosestPoint(cities1000.c.geom,
                                          func.Geometry(func.ST_GeographyFromText(
                                              'POINT({} {})'.format(lon, lat))))).first()

Is ST_ClosestPoint what i'm looking for? Performance is a concern.

5

For performance reasons, it is better to use distance_centroid rather than ST_Distance in your query:

from geoalchemy2.comparator import Comparator
q = db.session.query(cities1000.c.name, 
                    cities1000.c.stateRegion, 
                    cities1000.c.country).\
    order_by(
    Comparator.distance_centroid(cities1000.c.geom,
                 func.Geometry(func.ST_GeographyFromText(
                     'POINT({} {})'.format(lon, lat))))).limit(1).first()

ST_Distance forces the database to calculate the distance between the query Point and every city in your table of candidate features, then sort them all and take the first result. Whereas the PostGIS <-> operator that translates into distance_centroid in geoalchemy2 will do an index based Nearest Neighbour (NN) search. An index search partitions the space into multiple boxes (an R-Tree data structure) which considerably speeds up the nearest neighbor search for large databases.

To better understand how R-Tree and K-NN search work, you can read this great post on the Mapbox blog.

4

I was unable to get this query working with ST_ClosestPoint, but the following seems to work with ST_Distance:

q = db.session.query(cities1000.c.name, 
                        cities1000.c.stateRegion, 
                        cities1000.c.country).\
    order_by(
    func.ST_Distance(cities1000.c.geom,
                     func.Geometry(func.ST_GeographyFromText(
                         'POINT({} {})'.format(lon, lat))))).limit(1).first()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.