finding nearest locations in GeoAlchemy and PostGIS

Question

I am trying to do a simple query where I find the nearest locations to a user (in my example, I am using airports). The database records look like this:

id: 2249
city: Osaka
country: Japan
location_type_id: 16
geolocation: SRID=4326;POINT(34.59629822 135.6029968)
name: Yao Airport

My database query looks like this:

@classmethod
  def find_nearest_locations(cls, data):
    self = cls(data)
    return db.session.query(LocationModel.location_type_id==16). \
      order_by(Comparator.distance_centroid(LocationModel.geolocation,
          func.Geometry(func.ST_GeographyFromText(self.__format_geolocation())))).limit(self.result_quantity)

Unfortunately my function keeps returning empty lists. I am not very familiar with GeoAlchemy as this is the first time I am using it. Any help would be greatly appriciated.

Thanks.

Answer 1

I was able to fix the issue. Long story short, since I am using Flask-SQLAlchemy and not the regular SQLAlchemy, I have to search the LocationModel and not db.session.query. The code looks like this.

@classmethod
  def find_nearest_locations(cls, data):
    self = cls(data)
    return LocationModel.query.filter(LocationModel.location_type_id==self.location_type_id). \
      order_by(Comparator.distance_centroid(LocationModel.geolocation,
          func.Geometry(func.ST_GeographyFromText(self.__format_geolocation())))).limit(self.result_quantity)

Answer 2

In Postgis, the coordinates must be expresses as longitude first, then latitude.

You will need to swap the coordinates in the input

geolocation: SRID=4326;POINT(34.59629822 135.6029968) should become geolocation: SRID=4326;POINT(135.6029968 34.59629822)