Postgis Geoalchemy2

Docker

https://github.com/thomaxxl/safrs-postgis/

Implementation

Database creation based on http://www.jennifergd.com/post/7/:

• Install packages

sudo apt-get install postgis

• Configure database

psql testgis

CREATE EXTENSION postgis;

CREATE TABLE cities (
    point_id SERIAL PRIMARY KEY,
    location VARCHAR(30),
    latitude FLOAT,
    longitude FLOAT,
    geo geometry(POINT)
);

• SQL to insert a city

INSERT INTO cities (location, latitude, longitude, geo)
VALUES ('San Bruno', 37.6305, -122.4111, 'POINT(-122.4111 37.6305)');

App with SAFRS Model

import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
from geoalchemy2 import Geometry
from flask_sqlalchemy import SQLAlchemy
from safrs import SAFRSBase, SAFRSAPI
from safrs.json_encoder import SAFRSJSONEncoder
from geoalchemy2.shape import from_shape, to_shape
from shapely.geometry import shape
from shapely import geometry
from geoalchemy2.elements import _SpatialElement

app = Flask(__name__)
db = SQLAlchemy()

class GeoJSONEncoder(SAFRSJSONEncoder):
    """
        json encode geometry shapes
    """
    def default(self, obj, **kwargs):
        if isinstance(obj, _SpatialElement):
            result = geometry.mapping(to_shape(obj))
            return result

        return super().default(obj, **kwargs)


class DocumentedColumn(db.Column):
    """
        The class attributes are used for the swagger
    """

    description = "Geo column description"
    swagger_type = "json"
    swagger_format = "json"
    sample = {"coordinates": [-122.43129, 37.773972], "type": "Point"}


class City(SAFRSBase, db.Model):
    """
        A city, including its geospatial data
    """

    __tablename__ = "cities"

    point_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    location = db.Column(db.String(30),default="Gotham City")
    geo = DocumentedColumn(Geometry(geometry_type="POINT", srid=25833, dimension=2))

    def __init__(self, *args, **kwargs):
        # convert the json to geometry database type
        geo = kwargs.get("geo")
        kwargs["geo"] = str(to_shape(from_shape(geometry.shape(geo))))
        print(kwargs["geo"])
        super().__init__(*args, **kwargs)

    def __repr__(self):
        return f"<City {self.location}"

    __str__ = __repr__

    def get_cities_within_radius(self, radius):
        """Return all cities within a given radius (in meters) of this city."""
        return City.query.filter(func.ST_Distance_Sphere(City.geo, self.geo) < radius).all()


def connect_to_db(app):
    """Connect the database to Flask app."""

    app.config["SQLALCHEMY_DATABASE_URI"] = "postgres:///testgis"
    app.config["SQLALCHEMY_ECHO"] = False
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.app = app
    db.init_app(app)


def create_api(app, HOST="localhost", PORT=5000, API_PREFIX=""):
    api = SAFRSAPI(app, host=HOST, port=PORT, prefix=API_PREFIX)
    app.json_encoder = GeoJSONEncoder
    api.expose_object(City)
    print("Starting API: http://{}:{}/{}".format(HOST, PORT, API_PREFIX))


if __name__ == "__main__":

    connect_to_db(app)
    db.create_all()
    host = "192.168.235.136"
    with app.app_context():
        create_api(app, host, 5555)
        for city in City.query.all():
            pass  # print(city, city.location)

    app.run(host=host, port=5555)

Now cities can be added like this

    geo = {
          "coordinates": [
            -122.4111,
            37.6305
          ],
          "type": "Point"
        }
    test_city = City(location='Test City', latitude=38.01, longitude=-122.4111, geo=geo)
    db.session.add(test_city)
    db.session.commit()

Then a HTTP GET returns

{
  "data": {
    "attributes": {
      "geo": {
        "coordinates": [
          -122.4111,
          37.6305
        ],
        "type": "Point"
      },
      "location": "Test City",
      "point_id": 3
    },
    "id": "3",
    "links": {
      "self": "http://192.168.235.136:1237/cities/3/"
    },
    "relationships": {},
    "type": "City"
  },
  "included": [],
  "jsonapi": {
    "version": "1.0"
  },
  "links": {
    "related": "http://192.168.235.136:1237/cities/3/?fields%5BCity%5D=point_id%2Clocation%2Cgeo",
    "self": "http://192.168.235.136:1237/cities/3/"
  },
  "meta": {
    "count": 1,
    "instance_meta": {},
    "limit": 250
  }
}