0

I'm building a small tile service to present postgis data on the fly to a leaflet map.

for testing I am using the data set ne_110m_admin_0_countries

The first problem is the extent of the tiles returned, many are error'ing in predictable patterns. For example on zoom 0 no tiles below the equator are drawn.

zoom 0

between zoom 0 and 3 New Zealand doesn't exist

zoom 3

and also at zoom 3 nothing appears below 41 degrees south, however this behaviour does not exist reflected in the norther hemisphere.

zoom 3 again I'm using the following to serve up the tiles on a flask server:

@app.route('/tiles/test/<int:z>/<int:x>/<int:y>', methods=['GET'])
@login_required
def tiles(z=0, x=0, y=0):

    tile = get_tile(z, x, y, 'ne_110m_admin_0_countries')
    if tile != None:
        response = make_response(tile)
        response.headers['Content-Type'] = "application/octet-stream"
        return response
    else:
        return ('', 502)

def get_tile(z,x,y,TableName):
    cursor = db.connection.cursor()

    query= """WITH webmercator(envelope) AS (
              SELECT ST_TileEnvelope({},{},{})   -- z, x, y
            ),
            wgs84(envelope) AS (
              SELECT ST_Transform((SELECT envelope FROM webmercator), 4326)
            ),
            geometries(id, geom) AS (
              SELECT id, ST_Transform(geom, 3857)
              FROM  {}
              WHERE geom && (SELECT envelope FROM wgs84)   -- do not read objects outside the tile
            )
            SELECT ST_AsMVT(tile) FROM (
              SELECT id, ST_AsMVTGeom(geom, (SELECT envelope FROM webmercator))
              FROM geometries
            ) AS tile""".format(z, x, y, TableName) 
    try:
        cursor.execute(query)#,(z,x,y)) 
        tile = bytes(cursor.fetchone()[0])
        cursor.close()
        return tile
    except:
        cursor.execute("rollback")

The time zones from natural earth also show weird issues when zooming in out. For example at zoom level 4 the longitude extent of the tiles is up to 158 degrees east/west, at zoom 3 its 135 degrees, and at zoom 2 it's restricted even more to only 90 degrees east west

Any suggestions on why the zoom might be effecting the tiles shown?

Stay safe all.

4
  • You have not begun an explicit transaction, so there is no need to rollback. Anyway, you should log errors.
    – CL.
    Apr 10 '20 at 11:35
  • @CL. so i've changed the last line to cursor.close() , good point. As for when an error, I get the following "psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block" . Not particularly informative unfortunately . Apr 10 '20 at 14:11
  • That sounds as if some other part of your code begins a transaction, and does not close it.
    – CL.
    Apr 10 '20 at 14:22
  • so I've managed to log the errors and the above repeated errors seem to be kicked off with "transform: tolerance condition error (-20)" . Any suggestions? Thanks once again for your help. Tom Apr 10 '20 at 21:53
1

That self-explaining error message "tolerance condition error (-20)" is obviously thrown by ST_Transform when it is not able to transform some coordinate. In this case, the problem is that the Web Mercator projection is not able to show data near the poles.

You can either change the data in the table, by snipping off any coordinates that would lie beyond the edge of the world (about ±85°):

UPDATE ne_110m_admin_0_countries
SET geom = ST_Intersection(geom, ST_MakeEnvelope(-180, -85, 180, 85, 4326))
WHERE geom && ST_MakeEnvelope(-180, -90, 180, -85, 4326)
   OR geom && ST_MakeEnvelope(-180,  85, 180,  90, 4326);

Or you could do the same clipping whenever you read data from the table:

...
SELECT id, ST_Transform(ST_Intersection(...), 3857)
FROM ...

The second problem is how transactions are handled. Python's DB-API specification mandates that transactions are automatically opened, but must be manually closed (which is silly). You could commit the transaction after each request, but it is a better idea to simply set autocommit immediately after opening the connection so that errors in one tile do not affect all following requests.

5
  • All understood @CL. . Would you mind incorporating it in to the initial query so I may test it? Sorry to ask but i'm still learning SQL. I'll change the Flask App to autocommit. Apr 11 '20 at 10:21
  • thankyou for the update. So I incorporated ST_Intersection and the SQL no longer sends an error message (without need for autocommit). However there are still several tiles that seem not to be generating correctly. For example at zoom level 2, Australia, Singapore and Japan and the areas in between are not drawn, however the tiles to the east, west, north and south are drawn fine. The ne_10m_time_zones data set from natural earth continues to be cropped inwards from it's eastern and western edges. Any further suggestions? Thanks once again for your help. Apr 12 '20 at 9:03
  • No error is logged?
    – CL.
    Apr 12 '20 at 9:49
  • No errors logged! I've checked the log for the flask app and i see the GET for all z/x/y for zoom level 1 and the tilles are produced and sent back with no errors, however the tiles seem to have dropped data somewhere, very odd. Apr 12 '20 at 10:25
  • So just to check I ran the original SQL query from the previous Question you answered here and that does present all the data in the tiles, albeit completely distorted. Apr 12 '20 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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