Mapnik PostGIS DataSource runtime error in python 2.7 for “Select * from table LIMIT 0”

[giantchen2012]

Hi, folks, I've encounter a problem similar to this issue, but my problem is more specific. I use Mapnik 2.2.0 to generate map, but it ends with runtime error, the error message is in chaos for my locale. And the SQL output in the command line is the same with isses/70. any help will be appreciated.

My code was like this:

# mapGenerator.py

import os, os.path, sys, tempfile

# NOTE: The following needs to be added to fix a problem with my path and
# Python3.  Remove to make this work generally.
sys.path.insert(0, "path\to\site-packages")
# End of fix.

import mapnik

def generateMap(tableName, minX, minY, maxX, maxY,
            mapWidth, mapHeight,
            hiliteExpr=None, points=None):

     extent = "{},{},{},{}".format(minX, minY, maxX, maxY)
    #extent = "%s,%s,%s,%s"%(minX, minY, maxX, maxY)
    #print(extent)
    map = mapnik.Map(mapWidth, mapHeight,
                     '+proj=longlat +datum=WGS84')

    layer = mapnik.Layer("Layer",'+init=epsg:4326')
    print(mapnik.inputpluginspath)

    #######create the datasource here################################################
    layer.datasource = mapnik.PostGIS(database="postgis_24_sample",
                              user="postgres",
                  password="mypassword",
                                  table="shorelines",
                                  extent="-180,-90,180,90",
                                  geometry_field="outline",
                                  srid=4326)
    ########################################################

    map.background = mapnik.Color("#8080a0")

    style = mapnik.Style()

    rule = mapnik.Rule()
    if hiliteExpr != None:
        rule.filter = mapnik.Filter(hiliteExpr)

    rule.symbols.append(mapnik.PolygonSymbolizer(
        mapnik.Color("#408000")))
    rule.symbols.append(mapnik.LineSymbolizer(
        mapnik.Stroke(mapnik.Color("#000000"), 0.1)))

    style.rules.append(rule)

    rule = mapnik.Rule()
    rule.set_else(True)

    rule.symbols.append(mapnik.PolygonSymbolizer(
        mapnik.Color("#a0a0a0")))
    rule.symbols.append(mapnik.LineSymbolizer(
        mapnik.Stroke(mapnik.Color("#404040"), 0.1)))

    style.rules.append(rule)

    map.append_style("Map Style", style)
    layer.styles.append("Map Style")
    map.layers.append(layer)

    if points != None:
        memoryDatasource = mapnik.MemoryDatasource()
        context = mapnik.Context()
        context.push("name")
        next_id = 1
        for long,lat,name in points:
            wkt = "POINT (%0.8f %0.8f)" % (long,lat)
            feature = mapnik.Feature(context, next_id)
            feature['name'] = name
            feature.add_geometries_from_wkt(wkt)
            next_id = next_id + 1
            memoryDatasource.add_feature(feature)

        layer = mapnik.Layer("Points")
        layer.datasource = memoryDatasource

        style = mapnik.Style()
        rule = mapnik.Rule()

        pointImgFile = os.path.join(os.path.dirname(__file__),
                                "point.png")

        shield = mapnik.ShieldSymbolizer(
                   mapnik.Expression('[name]'),
                   "DejaVu Sans Bold", 10,
                   mapnik.Color("#000000"),
                   mapnik.PathExpression(pointImgFile))
        shield.displacement = (0, 7)
        shield.unlock_image = True
        rule.symbols.append(shield)

        style.rules.append(rule)

        map.append_style("Point Style", style)
        layer.styles.append("Point Style")

        map.layers.append(layer)

    map.zoom_to_box(mapnik.Envelope(minX, minY, maxX, maxY))

    scriptDir = os.path.dirname(__file__)
    cacheDir = os.path.join(scriptDir, "..", "mapCache")
    if not os.path.exists(cacheDir):
        os.mkdir(cacheDir)
    fd,filename = tempfile.mkstemp(".png", dir=cacheDir)
    os.close(fd)

    mapnik.render_to_file(map, filename, "png")

    return "../mapCache/" + os.path.basename(filename)
generateMap("countries", -180, -90, 180, 90,
                  600, 400,
                  hiliteExpr="[id]=40", points=None)

This piece of code is example code from Chapter 9 in Python Geospatial Development 3rd edition written by Erik Westra, published in 2016 by Packet

[giantchen2012]

OK, I finally get the exact error Postgis Plugin: ERROR: relation "***" does not exist

but it's still hard to resolve, i'm not in the situation as others https://github.com/mapnik/mapnik/issues/3898

[talaj]

Can you please post the exact SQL query and error message?

[giantchen2012]

I just init the postgis datasource as shown in the code, and the SQL：select * from ‘mytable’ limit 0. it's a statement in postgis_datasource.cpp:

        std::ostringstream s;
        s << "SELECT * FROM " << populate_tokens(table_) << " LIMIT 0";

        shared_ptr<ResultSet> rs = conn->executeQuery(s.str());

[giantchen2012]

and more, I build my mapnik 3.0.20 with libpq 9.4.5 with VS 2017 and toolset v141. But my postgresql version is 9.6, should I build it with the libpq.dll under postgresql 9.6?

[giantchen2012]

this is what I did in my command line:

Python 3.6.3 (v3.6.3:2c5fed8, Oct  3 2017, 18:11:49) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mapnik
>>> mapnik.PostGIS(database="postgis_24_sample",user="postgres",password="the-password",table="shorelines",extent="-180,-90,180,90",geometry_field="outline",srid=4326)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "dir\to\mapnik\__init__.py", line 458, in PostGIS
    return CreateDatasource(keywords)
RuntimeError: Postgis Plugin: 错误:  关系 "shorelines" 不存在
LINE 1: SELECT * FROM shorelines LIMIT 0
                      ^
in executeQuery Full sql was: 'SELECT * FROM shorelines LIMIT 0'

[giantchen2012]

Well, and I try to load from xml, it works fine. the code:

import mapnik
m = mapnik.Map(256,256)
mapnik.load_map(m, "path/to/file.xml")
m.zoom_all()
mapnik.render_to_file(m, "the_image.png")

and my xml

<Map background-color="blue" srs="+proj=lcc +ellps=GRS80 +lat_0=49 +lon_0=-95 +lat+1=49 +lat_2=77 +datum=NAD83 +units=m +no_defs">
  <Style name="My Style">
    <Rule>
      <PolygonSymbolizer fill="#f2eff9" />
      <LineSymbolizer stroke="rgb(50%,50%,50%)" stroke-width="0.1" />
    </Rule>
  </Style>
  <Layer name="world" srs="+proj=lcc +ellps=GRS80 +lat_0=49 +lon_0=-95 +lat+1=49 +lat_2=77 +datum=NAD83 +units=m +no_defs">
    <StyleName>My Style</StyleName>
    <Datasource>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="host">localhost</Parameter>
      <Parameter name="dbname">postgis_24_sample</Parameter>
      <Parameter name="user">postgres</Parameter>
      <Parameter name="password">my password</Parameter>
      <Parameter name="table">"shorelines"</Parameter>
      <Parameter name="estimate_extent">true</Parameter>
    </Datasource>
  </Layer>
</Map>

[talaj]

I don't understand why do you have quotations marks in

<Parameter name="table">"shorelines"</Parameter>

Does it work also without the quotation marks?

[giantchen2012]

Ok, i find some thread from this article, so I add the quotation marks. And now, my question is: Why mapnik python cant create the datasource directly by code, but works correctly loading xml map?

[talaj]

I've tried to call mapnik.PostGIS() as you did and had to use dbname="..." instead of database="...".

[giantchen2012]

Great appreciation!! @talaj , i've tried what you adviced, yep, it works fine. So the problem is that I used the wrong param's name.