Help required for st_asMVT in geoalchemy2

[jeetu]

Ref: https://gis.stackexchange.com/questions/291418/st-asmvt-in-geoalchemy2

Sometime back, I have posted this question on gis.stack... 

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 functions 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 https://blog.jawg.io/how-to-make-mvt-with-postgis/,  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?

[jeetu]

Ref2: http://www.pythonexample.com/snippet/python/flask_mvtpy_giohappy_python

I am trying to understand how to create similar queries using Geoalchemy2 but not able to do so. Can somebody help me with these?

[Eric Lemoine]

On Wed, Aug 8, 2018 at 4:40 PM, jeetu <alind...@gmail.com> wrote:
> Ref2:
> http://www.pythonexample.com/snippet/python/flask_mvtpy_giohappy_python
>
> I am trying to understand how to create similar queries using Geoalchemy2
> but not able to do so. Can somebody help me with these?

Hi Jeetu

Below is an example that seems to work for me. Tell me how that goes for you.




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/formation', 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)




--
Eric

[alind sharma]

Worked lke a charm for me.

Thanks.

Also learned along the way that mercantile.tile (lng=__, lat=__, zoom=__) gives z,y,z which are normally used in the {x} {y} {z} as in xy_bounds here.

Thanks and Regards,

--
You received this message because you are subscribed to the Google Groups "GeoAlchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to geoalchemy+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jeetu]

Hi Eric,

I will try it out today when I get access to my computer. My primary use case is to serve osm data consume using leaflet on the web.

Thanks a lot for helping me out.