{"id":264,"date":"2014-09-26T13:26:07","date_gmt":"2014-09-26T18:26:07","guid":{"rendered":"http:\/\/cartometric.com\/blog\/?p=264"},"modified":"2014-09-26T13:33:41","modified_gmt":"2014-09-26T18:33:41","slug":"create-utfgrid-tiles-from-postgis-tables","status":"publish","type":"post","link":"https:\/\/elrobis.com\/blog\/2014\/09\/26\/create-utfgrid-tiles-from-postgis-tables\/","title":{"rendered":"Create UTFGrid Tiles from PostGIS Tables"},"content":{"rendered":"

I assume you’re where I was about a week ago. That is, you’ve heard of<\/a> UTFGrid<\/a>, and now you want to render your own UTFGrid tiles. Perhaps you found yourself looking at this thread<\/a> over at GIS.SE, but you don’t want to jump into TileStache just now. Anyway if you’ve got a working installation of GDAL\/OGR<\/a> and Mapnik 2+<\/a>, complete with Python bindings, I’ll show you what worked for me..<\/p>\n

Because this is merely an adaptation of Matthew Perry’s original solution, I highly recommend considering his original blog entry on the topic<\/a>, complete with discussion points and caveats, before proceeding further!<\/p>\n

Once you’re ready, head over to GitHub<\/a> and download some code, specifically globalmaptiles.py<\/code><\/a>, create_tile_shp.py<\/code><\/a>, and create_utfgrids.py<\/code><\/a>. [1] You don’t actually need create_tile_shp.py<\/code>, but I snagged all three. To keep things simple put these files in the same folder.<\/p>\n

Next, in that same directory, create a new Python file, I called mine createUtfgridsFromPG.py<\/code>.<\/p>\n

Where create_utfgrids.py<\/code> works entirely on shapefiles,\u00a0createUtfgridsFromPG.py<\/code> accepts an OGR PostgreSQL<\/a> connection string in place of a shapefile path. Fortunately the original OGR code didn’t change, but to use the Mapnik PostGIS driver I had to iterate over the PostgreSQL connection string and store the connection parameters so I could supply them a differently to Mapnik.<\/p>\n

Finally, copy the following code and paste it into your new file. It’s basically the same as create_utfgrids.py<\/code>, but I changed shppath<\/code> to pgconn<\/code> and added some code to use a mapnik.PostGIS()<\/code> datasource<\/a> in place of a mapnik.Shapefile()<\/code> datasource<\/a>.<\/p>\n

If you’re curious, the comment # ELR 2014.9.26:<\/code> flags the few places I changed the original code.<\/p>\n

createUtfgridsFromPG.py<\/code><\/h1>\n
#!\/usr\/bin\/env python\r\n# -*- coding: utf-8  -*-\r\n\"\"\"\r\ncreate_utfgrids.py\r\nAuthor: Matthew Perry\r\nLicense: BSD\r\n\r\nCreates utfgrid .json tiles for the given polygon shapefile\r\n\r\nThx to Dane Springmeyer for the utfgrid spec and mapnik rendering code\r\nand to  Klokan Petr P\u0159idal for his MapTiler code\r\n(http:\/\/www.maptiler.org\/google-maps-coordinates-tile-bounds-projection\/)\r\n\r\n\"\"\"\r\nimport globalmaptiles\r\nimport mapnik\r\nimport ogr\r\nimport os\r\nfrom optparse import OptionParser, OptionError\r\ntry:\r\n    import simplejson as json\r\nexcept ImportError:\r\n    import json\r\n\r\ndef create_utfgrids(pgconn, minzoom, maxzoom, outdir, fields=None, layernum=0):\r\n\r\n    # ELR 2014.9.26:\r\n    # Original implementation pushed in a shapefile path.\r\n    #ds = ogr.Open(shppath)\r\n    ds = ogr.Open(pgconn)\r\n\r\n    # ELR 2014.9.26:\r\n    # Iterate over the PostgreSQL connection string and pull out values we need\r\n    # to use Mapnik's PostGIS datasource constructor.\r\n    pgConnARR = pgconn[3:].split(' ')\r\n    for kvPair in pgConnARR:\r\n        if kvPair.split('=')[0] == \"host\":\r\n            nikHost = kvPair.split('=')[1]\r\n        if kvPair.split('=')[0] == \"port\":\r\n            nikPort = kvPair.split('=')[1]\r\n        if kvPair.split('=')[0] == \"user\":\r\n            nikUser = kvPair.split('=')[1]\r\n        if kvPair.split('=')[0] == \"password\":\r\n            nikPass = kvPair.split('=')[1]\r\n        if kvPair.split('=')[0] == \"dbname\":\r\n            nikDB = kvPair.split('=')[1]\r\n        if kvPair.split('=')[0] == \"tables\":\r\n            nikTable = kvPair.split('=')[1]\r\n\r\n    print\r\n    print \"WARNING:\"\r\n    print \" This script assumes a polygon shapefile in spherical mercator projection.\"\r\n    print \" If any of these assumptions are not true, don't count on the results!\"\r\n    # TODO confirm polygons\r\n    # TODO confirm mercator\r\n    # TODO get layernum from command line\r\n    layer = ds.GetLayer(layernum)\r\n    bbox = layer.GetExtent()\r\n    print \"\"\r\n    print str(bbox)\r\n\r\n    mercator = globalmaptiles.GlobalMercator()\r\n\r\n    m = mapnik.Map(256,256)\r\n\r\n    # Since grids are `rendered` they need a style\r\n    s = mapnik.Style()\r\n    r = mapnik.Rule()\r\n    polygon_symbolizer = mapnik.PolygonSymbolizer(mapnik.Color('#f2eff9'))\r\n    r.symbols.append(polygon_symbolizer)\r\n    line_symbolizer = mapnik.LineSymbolizer(mapnik.Color('rgb(50%,50%,50%)'),0.1)\r\n    r.symbols.append(line_symbolizer)\r\n    s.rules.append(r)\r\n    m.append_style('My Style',s)\r\n\r\n    print \"\"\r\n    # ELR 2014.9.26:\r\n    # Original implementation using shapefile..\r\n    #ds = mapnik.Shapefile(file=shppath)\r\n\r\n    # ELR 2014.9.26:\r\n    # Parameterized PostGIS implementation..\r\n    ds = mapnik.PostGIS(host=nikHost,port=nikPort,user=nikUser,password=nikPass,dbname=nikDB,table=nikTable)\r\n\r\n    mlayer = mapnik.Layer('poly')\r\n    mlayer.datasource = ds\r\n    mlayer.styles.append('My Style')\r\n    m.layers.append(mlayer)\r\n\r\n    print \"\"\r\n    if fields is None:\r\n        fields = mlayer.datasource.fields()\r\n        print \"Fields were NONE. Using..\"\r\n        print fields\r\n    else:\r\n        print \"Fields are USER PROVIDED. Using..\"\r\n        print fields\r\n    print \"\"\r\n\r\n    for tz in range(minzoom, maxzoom+1):\r\n        print \" * Processing Zoom Level %s\" % tz\r\n        tminx, tminy = mercator.MetersToTile( bbox[0], bbox[2], tz)\r\n        tmaxx, tmaxy = mercator.MetersToTile( bbox[1], bbox[3], tz)\r\n        for ty in range(tminy, tmaxy+1):\r\n            for tx in range(tminx, tmaxx+1):\r\n                output = os.path.join(outdir, str(tz), str(tx))\r\n                if not os.path.exists(output):\r\n                    os.makedirs(output)\r\n\r\n                # Use top origin tile scheme (like OSM or GMaps)\r\n                # TODO support option for TMS bottom origin scheme (ie opt to not invert)\r\n                ymax = 1 << tz;\r\n                invert_ty = ymax - ty - 1;\r\n\r\n                tilefilename = os.path.join(output, \"%s.json\" % invert_ty) # ty for TMS bottom origin\r\n                tilebounds = mercator.TileBounds( tx, ty, tz)\r\n                #print tilefilename, tilebounds\r\n\r\n                box = mapnik.Box2d(*tilebounds)\r\n                m.zoom_to_box(box)\r\n                grid = mapnik.Grid(m.width,m.height)\r\n                mapnik.render_layer(m,grid,layer=0,fields=fields)\r\n                utfgrid = grid.encode('utf',resolution=4)\r\n                with open(tilefilename, 'w') as file:\r\n                    file.write(json.dumps(utfgrid))\r\n\r\nif __name__ == \"__main__\":\r\n    usage = \"usage: %prog [options] shapefile minzoom maxzoom output_directory\"\r\n    parser = OptionParser(usage)\r\n    parser.add_option(\"-f\", '--fields', dest=\"fields\", help=\"Comma-seperated list of fields; default is all\")\r\n    (options, args) = parser.parse_args()\r\n\r\n    if len(args) != 4:\r\n        parser.error(\"Incorrect number of arguments\")\r\n\r\n    pgconn = args[0]\r\n    minzoom, maxzoom = int(args[1]), int(args[2])\r\n    outdir = args[3]\r\n\r\n    if os.path.exists(outdir):\r\n        parser.error(\"output directory exists already\")\r\n\r\n    if options.fields:\r\n        fields = options.fields.split(\",\")\r\n    else:\r\n        fields = None\r\n\r\n    create_utfgrids(pgconn, minzoom, maxzoom, outdir, fields)<\/pre>\n
Usage..<\/h1>\n
Once you’ve prepared createUtfgridsFromPG.py<\/code>, you can call it from the command line like this..<\/code><\/p>\n
C:\\xDev\\utfgrids\\createUtfgridsFromPG.py \"PG:host=127.0.0.1 port=5432 user=postgres dbname=gis password=passw0rd tables=parcels_pmerc\" 12 16 \"C:\/xGIS\/tiles\/utf\" -f tms,owner_name
\n<\/code><\/p>\n