WIP version of openmaptiles/postserve that has better concurrency

pg_mbtileserve.py

###
# NOTE: This is super WIP and is intended to demonstrate one method of improving concurrency.
###
import io
import os

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, scoped_session, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy import text
from sqlalchemy import event

import mercantile
import pyproj
import yaml
import sys
import itertools
import decimal
import traceback


def GetTM2Source(file):
    with open(file,'r') as stream:
        tm2source = yaml.load(stream)
    return tm2source


def GeneratePrepared(layers):
    queries = []
    for layer in layers['Layer']:
        buffer_size = layer['properties']['buffer-size']

        geom = f'ST_AsMVTGeom(geometry,!bbox!,4096,{buffer_size},true)'

        layer_query = layer['Datasource']['table'].strip()    # Remove leading and trailing whitespace
        layer_query = layer_query[1:-6]       # Remove enough characters to remove first and last () and "AS t"
        layer_query = layer_query.replace("geometry", f'{geom} AS mvtgeometry')

        base_query = f"SELECT ST_ASMVT('{layer['id']}', 4096, 'mvtgeometry', tile) FROM ({layer_query} WHERE {geom} IS NOT NULL) AS tile"

        queries.append(base_query.replace("!bbox!","$1").replace("!scale_denominator!","$2").replace("!pixel_width!","$3").replace("!pixel_height!","$4"))

    prepared = f'PREPARE gettile(geometry, numeric, numeric, numeric) AS {" UNION ALL ".join(queries)};'

    # print(prepared)
    return(prepared)


layers = GetTM2Source('openmaptiles/build/openmaptiles.tm2source/data.yml')
prepared = GeneratePrepared(layers)

engine = create_engine('postgresql://'+os.getenv('POSTGRES_USER','openmaptiles')+':'+os.getenv('POSTGRES_PASSWORD','openmaptiles')+'@'+os.getenv('POSTGRES_HOST','localhost')+':'+os.getenv('POSTGRES_PORT','32770')+'/'+os.getenv('POSTGRES_DB','openmaptiles'), pool_size=16, max_overflow=0, pool_pre_ping=True)
# session_factory = sessionmaker(bind=engine)
# Session = scoped_session(session_factory)


@event.listens_for(engine, 'connect')
def receive_connect(dbapi_connection, connection_record):
    with dbapi_connection.cursor() as c:
        c.execute(prepared)

def bounds(zoom,x,y):
    inProj = pyproj.Proj(init='epsg:4326')
    outProj = pyproj.Proj(init='epsg:3857')
    lnglatbbox = mercantile.bounds(x,y,zoom)
    ws = (pyproj.transform(inProj,outProj,lnglatbbox[0],lnglatbbox[1]))
    en = (pyproj.transform(inProj,outProj,lnglatbbox[2],lnglatbbox[3]))
    return {'w':ws[0],'s':ws[1],'e':en[0],'n':en[1]}

def zoom_to_scale_denom(zoom):                      # For !scale_denominator!
    # From https://github.com/openstreetmap/mapnik-stylesheets/blob/master/zoom-to-scale.txt
    map_width_in_metres = 40075016.68557849
    tile_width_in_pixels = 256.0
    standardized_pixel_size = 0.00028
    map_width_in_pixels = tile_width_in_pixels*(2.0**zoom)
    return str(map_width_in_metres/(map_width_in_pixels * standardized_pixel_size))

def replace_tokens(query,s,w,n,e,scale_denom):
    return query.replace("!bbox!","ST_MakeBox2D(ST_Point("+w+", "+s+"), ST_Point("+e+", "+n+"))").replace("!scale_denominator!",scale_denom).replace("!pixel_width!","256").replace("!pixel_height!","256")

def get_mvt(zoom,x,y):
    # Sanitize the inputs
    sani_zoom, sani_x, sani_y = float(zoom), float(x), float(y)
    del zoom,x,y

    session = engine.connect()
    
    scale_denom = zoom_to_scale_denom(sani_zoom)
    tilebounds = bounds(sani_zoom,sani_x,sani_y)
    s,w,n,e = str(tilebounds['s']),str(tilebounds['w']),str(tilebounds['n']),str(tilebounds['e'])
    final_query = "EXECUTE gettile(!bbox!, !scale_denominator!, !pixel_width!, !pixel_height!);"
    sent_query = replace_tokens(final_query,s,w,n,e,scale_denom)
    try:
        response = list(session.execute(sent_query))
    except:
        print('Failed', sani_zoom, sani_x, sani_y)
        raise

    # print(sent_query)
    layers = filter(None,list(itertools.chain.from_iterable(response)))
    final_tile = b''
    
    for layer in layers:
        final_tile = final_tile + io.BytesIO(layer).getvalue()
        
    return final_tile

Thanks for posting! Just FYI, the actual SQL is now generated by openmaptiles-tools, and it is by far cleaner because it knows the original layer data. See mvt tiles by postgis. I have rewritten postserve code to new server.py, but it might benefit from some of your multithreaded Python magic.