2

I am new to geoalchemy/gis domain. I am looking to establish a local map server using postgis, flask and geoalchemy2. I have imported osm data into postgis and able to see it using qgis software. How can I serve mvt tiles using flask+geoalchemy2.

  1. I did reflection of the existing postgis db and the tables(eg osm_roads) is available to me in python.
  2. I have created ST_AsMVT aed ST_AsMVTGeom as as suggested by subclassing GenericQuery of geoalchemy2 https://geoalchemy-2.readthedocs.io/en/latest/spatial_functions.html#geoalchemy2.functions.GenericFunction
  3. How can I recreate the query like shown in http://www.pythonexample.com/snippet/python/flask_mvtpy_giohappy_python from pure geoalchemy2 ie not resorting to raw SQL? In other words how can I replace the psycopg2 calls with geoalchemy2 calls in the _create function of the snippet?

1 Answer 1

3

Here is an example that uses SQLAlchemy Core and GeoAlchemy2 to generate an ST_AsMVT- and ST_AsMVTGeom-based query:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.sql import functions, select, func, column
from sqlalchemy.dialects.postgresql import BYTEA
from geoalchemy2 import Geometry
from geoalchemy2.functions import GenericFunction
import mercantile


class ST_AsMVTGeom(GenericFunction):
    name = 'ST_AsMVTGeom'
    type = Geometry


class ST_AsMVT(functions.GenericFunction):
    type = BYTEA


metadata = MetaData()

arrond = Table('arrond', metadata,
               Column('gid', Integer, primary_key=True),
               Column('nom', String),
               Column('geom3857', Geometry('MultiPolygon', 3857))
               )

engine = create_engine('postgresql://localhost:5432/db', echo=True)

xmin, ymin, xmax, ymax = mercantile.xy_bounds(8, 5, 4)

subq = select([
    func.ST_AsMVTGeom(arrond.c.geom3857,
                      func.ST_MakeBox2D(func.ST_Point(xmin, ymin),
                                        func.ST_Point(xmax, ymax))).label('geom')
    ])
subq = subq.alias('q')

q = select([func.ST_AsMVT(column('q'), 'layer', 4096, 'geom')]).select_from(subq)

conn = engine.connect()

tile = conn.scalar(q)

with open('tile.mvt', 'wb') as f:
    f.write(tile)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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