TileStach.vector PostGIS/ GeoJSON

[d.]

Hi all,

I have a problem with Tilestache. I got to set it up correctly (localhost:8080/example/previews.html and osm/preview.html work just fine). But when I want to set it up using GeoJSON or PostGIS it is just giving me blank tiles.

My tilestache.cfg looks like this:

{
  "cache":
  {
    "name": "Disk",
    "path": "/tmp/stache",
    "umask": "0000"
  },
  "layers":
  {
    "osm":
    {
        "provider": {"name": "proxy", "provider": "OPENSTREETMAP"},
        "png options": {"palette": "http://tilestache.org/example-palette-openstreetmap-mapnik.act"}
    },
    "example":
    {
        "provider": {"name": "mapnik", "mapfile": "examples/style.xml"},
        "projection": "spherical mercator"
    },
    "postgis":
    {
    "provider": {"name": "vector", "driver": "PostgreSQL","parameters":
                {"dbname": "webmap", "user": "postgres", "password": "xxxxx", "table": "tilestache"
                }
                }
    },
    "geojson":
    {
    "provider": {"name": "vector", "driver": "GeoJSON",
                 "parameters": {"file": "geojsontest.json"}
        }
    }
  }
}

And while asking for localhost:8080/postgis/preview.html the server calls
127.0.0.1 - - [20/Aug/2012 07:17:28] "GET /postgis/preview.html HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:28] "GET /postgis/preview.html HTTP/1.1" 200 -
127.0.0.1 - - [20/Aug/2012 07:17:28] "GET /postgis/10/167/396.png HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:28] "GET /postgis/10/167/396.png HTTP/1.1" 200 -
127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/166/397.png HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/166/397.png HTTP/1.1" 200 -
127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/167/394.png HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/167/394.png HTTP/1.1" 200 -
127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/167/397.png HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:29] "GET /postgis/10/167/397.png HTTP/1.1" 200 -
127.0.0.1 - - [20/Aug/2012 07:17:30] "GET /postgis/preview.html HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [20/Aug/2012 07:17:30] "GET /postgis/preview.html HTTP/1.1" 200 -

However there are no files created it the tmp/stache directory at all, not even one with 0kb.
The user login for postgis is alright, as i can access it from pgAdmin with the same user/password also the tablename is correct. The json file can be rendered correctly in polymaps itself, so no bad file format or something like that.

I am running tilestache on ubuntu 12.04 64bit. I am just wondering why the originial examples work and my basic tests fail.

Any help would be very much appreciated!
Thanks!
d.

[Michal Migurski]

Hi D,

The preview.html page is designed to preview image tiles, but the vector provider returns JSON data tiles. Can you try changing some of the requests below to say ".json" instead of ".png" on the end?

-mike.

----------------------------------------------------------------
michal migurski- mi...@stamen.com
415.558.1610

[d.]

Hi mike,

when I change the tilestache.cfg to

{
  "cache":
  {
    "name": "Disk",
    "path": "/tmp/stache",
    "umask": "0000"
  },
  "layers":
  {
    "osm":
    {
        "provider": {"name": "proxy", "provider": "OPENSTREETMAP"},
        "png options": {"palette": "http://tilestache.org/example-palette-openstreetmap-mapnik.act"}
    },
    "example":
    {
        "provider": {"name": "mapnik", "mapfile": "examples/style.xml"},
        "projection": "spherical mercator"
    },
    "postgis":
    {
    "provider": {"name": "vector", "driver": "PostgreSQL","parameters":

                {"dbname": "webmap", "user": "xxx", "password": "xxx", "table": "tilestache"
                }
                },
    "preview":  {"lat": 0,"lon": 0, "zoom": 5,"ext": "json"

            }
    },
    "geojson":
    {
    "provider": {"name": "vector", "driver": "GeoJSON",
                 "parameters": {"file": "geojsontest.json"}

        },
    "preview":  {"lat": 0,"lon": 0, "zoom": 5,"ext": "json"
            }
    }
  }
}

I have the same empty tile output in the webbrowser and can't find any tiles in the /tmp/stache folder. The terminal gives me here:
127.0.0.1 - - [21/Aug/2012 17:21:12] "GET /geojson/5/18/17.json HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:12] "GET /geojson/5/18/17.json HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:12] "GET /geojson/5/18/14.json HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:12] "GET /geojson/5/18/14.json HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/5/17/18.json HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/5/17/18.json HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/5/14/18.json HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/5/14/18.json HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/preview.html HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:13] "GET /geojson/preview.html HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:14] "GET /geojson/5/18/18.json HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:14] "GET /geojson/5/18/18.json HTTP/1.1" 200 -
127.0.0.1 - - [21/Aug/2012 17:21:14] "GET /geojson/preview.html HTTP/1.1" 200 -
INFO:werkzeug:127.0.0.1 - - [21/Aug/2012 17:21:14] "GET /geojson/preview.html HTTP/1.1" 200 -

so no error. Is it possible that tilestache is only serving tiles/jsons when I request them via polymaps?
Could you maybe post me a example.html for using polymaps with tilestache.vector postgis? a very basic one would probably help.

Any other thoughts are welcome.

[Todd Albert]

I am struggling with the same basic setup. I am using polymaps and overlaying a very large vector layer (600,000+ polygons!). 

I created a small subset of the polygons and put these into a .json file. This overlays on the map beautifully.

The entire dataset is too large and must be tiled. So I've turned to TileStache.

I've tried this several ways and have yet to find a working solution.

I've used a straight .json file and asked TileStache to tile it. No dice. Not even when using the smaller subset.

I've imported the data into my PostGIS database, but when I try to access the tiles, I am told that TileStache cannot connect to the database using my dbuser/pw. I have checked these settings multiple times. I can access the database using these settings, but TileStache cannot?

Finally, I used TileMill to create a (huge) .mbtiles file. TileStache is able to serve the mbtiles, but it is extremely slow and doesn't show every tile. This method, while it looks ok (not as nice as vectors), it does not suffice since I have data associated with each polygon that I need access to.

Any help on getting TileStache to work with my .json file (GeoJSON format) would be greatly appreciated!

Also, Michal, is there a way to preview your vector layers using a method similar to previe.html?

Thanks,

Todd

[d.]

Hi Todd and Michael.

@ Todd: You got an error message when you tried to enter the PostGIS-DB? The strange thing for me is, that I do not get even though if I am entering the wrong connection parameters.

@ all:
I have now created a test-html with polymaps. I also added the "host" paramters to my tilestache.vector - PostGIS provided db.
I have still the same results with the preview, which does not wonder me, since tilestache.vescot delivers *.jsons.
The test.hml  + polymaps code looks like the following

geojsontest.js:
var po = org.polymaps;

var map = po.map()
    .container(document.getElementById("map").appendChild(po.svg("svg")))
    .zoom(1)
    .zoomRange([1, 10])
    .add(po.interact());

map.add(po.image()
    .url(po.url("http://{S}tile.cloudmade.com"
    + "/1a1b06b230af4efdbb989ea99e9841af"
    + "/20760/256/{Z}/{X}/{Y}.png")
    .hosts(["a.", "b.", "c.", ""])));

map.add(po.geoJson()
    .url("http://127.0.0.1:8080/postgis/{Z}/{X}/{Y}.json")
    .id("points")
    .on("load", load));

function load(e) {
  for (var i = 0; i < e.features.length; i++) {
    var feature = e.features[i];
    feature.element.appendChild(po.svg("circle")
        .appendChild(document.createTextNode(feature.data.id))
        .parentNode);

map.add(po.compass()
    .pan("none"));

}
}

geojsontest.html:
<!DOCTYPE html>
<html>
  <head>
    <title>GeoJSON Test</title>
    <script type="text/javascript" src="polymaps.js"></script>
    <style type="text/css">

@import url("example.css");
body    {
      background: #E6E6E6;
    }

.layer path    {
        fill: none;
        vector-effect: non-scaling-stroke;
        }

    </style>
  </head>
  <body id="map">
    <script type="text/javascript" src="geojsontest.js"></script>
  </body>
</html>

when I have the polymaps po.geojson object configured as a local geojson file, everything works perfectly, when set it up like abothe and try to get out tiles out of tilestache, der is no error in the terminal window i run tilestache in, but firebug gives me the following for each tile:

GET http://127.0.0.1:8080/postgis/1/0/1.json

200 OK

1.58s

polymaps.js (line 307)

GET http://127.0.0.1:8080/postgis/1/0/1.json

200 OK

1.58s

polymaps.js (line 307)

GET http://127.0.0.1:8080/postgis/1/1/1.json

200 OK

1.6s

polymaps.js (line 307)

GET http://127.0.0.1:8080/postgis/1/0/0.json

200 OK

1.95s

polymaps.js (line 307)

in specific for one:

Response Headers

Content-Length	5250
Content-Type	text/plain
Date	Wed, 22 Aug 2012 18:25:10 GMT
Server	Werkzeug/0.8.3 Python/2.7.3

Request Headers

Accept	text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding	gzip, deflate
Accept-Language	en-us,en;q=0.5
Connection	keep-alive
Host	127.0.0.1:8080
Origin	http://localhost
Referer	http://localhost/polymaps/test/GeoJSON/geojsontest.html
User-Agent	Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:14.0) Gecko/20100101 Firefox/14.0.1

and polymaps.js says on line 307
req.send(null);

Thats it. No vectors are beeing shown. Btw the SRID of my postgisdb is 4326 - do I maybe have to use 900913? As in another topic somebody wrote that is problem was the unmatching zoomlevels of the tiles and the layers -> how do I get (if that is possbile) the zoomlevel out of a postgis db?

any thoughts on this? I really want to get this going :/

[Michal Migurski]

Hi Todd,

I'll just answer a small part of your question since I'm traveling with limited email access for the moment. Can you paste here your postgres settings in the config file, alongside the psql command you run to connect to the database? If you are comfortable with Python, can you attempt to connect using the psycopg2 library that TileStache uses under the hood? Are you using Mapnik at all, and does it have trouble connecting? It would help to see more information to help troubleshoot the postgres connection.

-mike.

[Todd Albert]

Mike,

Thanks for the reply. Here are my settings in /etc/postgresql/9.1/main/postgresql.conf:

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                                                   h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/9.1/main'        # use data in another directory
                    # (change requires restart)
hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'    # host-based authentication file
                    # (change requires restart)
ident_file = '/etc/postgresql/9.1/main/pg_ident.conf'    # ident configuration file
                    # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/9.1-main.pid'        # write an extra PID file
                    # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'        # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all
                    # (change requires restart)
port = 5432                # (change requires restart)
max_connections = 100            # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3    # (change requires restart)
unix_socket_directory = '/var/run/postgresql'        # (change requires restart)
#unix_socket_group = ''            # (change requires restart)
#unix_socket_permissions = 0777        # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off                # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''            # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min        # 1s-600s
ssl = true                # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
                    # (change requires restart)
#ssl_renegotiation_limit = 512MB    # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres'        # (Kerberos only)
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0        # TCP_KEEPCNT;
                    # 0 selects the system default


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB            # min 128kB
                    # (change requires restart)
#temp_buffers = 8MB            # min 800kB
#max_prepared_transactions = 0        # zero disables the feature
                    # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB                # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB            # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000        # min 25
                    # (change requires restart)
#shared_preload_libraries = ''        # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms        # 0-100 milliseconds
#vacuum_cost_page_hit = 1        # 0-10000 credits
#vacuum_cost_page_miss = 10        # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms            # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0        # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1        # 1-1000. 0 disables prefetching


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#wal_level = minimal            # minimal, archive, or hot_standby
                    # (change requires restart)
#fsync = on                # turns forced synchronization on or off
#synchronous_commit = on        # synchronization level; on, off, or local
#wal_sync_method = fsync        # the default is the first option
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
#full_page_writes = on            # recover from partial page writes
#wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers
                    # (change requires restart)
#wal_writer_delay = 200ms        # 1-10000 milliseconds

#commit_delay = 0            # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min        # range 30s-1h
#checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s        # 0 disables

# - Archiving -

#archive_mode = off        # allows archiving to be done
                # (change requires restart)
#archive_command = ''        # command to use to archive a logfile segment
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables


#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

# - Master Server -

# These settings are ignored on a standby server

#max_wal_senders = 0        # max number of walsender processes
                # (change requires restart)
#wal_sender_delay = 1s        # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0        # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0    # number of xacts by which cleanup is delayed
#replication_timeout = 60s    # in milliseconds; 0 disables
#synchronous_standby_names = ''    # standby servers that provide sync rep
                # comma-separated list of application_name
                # from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

#hot_standby = off            # "on" allows queries during recovery
                    # (change requires restart)
#max_standby_archive_delay = 30s    # max delay before canceling queries
                    # when reading WAL from archive;
                    # -1 allows indefinite delay
#max_standby_streaming_delay = 30s    # max delay before canceling queries
                    # when reading streaming WAL;
                    # -1 allows indefinite delay
#wal_receiver_status_interval = 10s    # send replies at least this often
                    # 0 disables
#hot_standby_feedback = off        # send info from standby to prevent
                    # query conflicts


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0            # same scale as above
#cpu_tuple_cost = 0.01            # same scale as above
#cpu_index_tuple_cost = 0.005        # same scale as above
#cpu_operator_cost = 0.0025        # same scale as above
#effective_cache_size = 128MB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5            # range 1-10
#geqo_pool_size = 0            # selects default based on effort
#geqo_generations = 0            # selects default based on effort
#geqo_selection_bias = 2.0        # range 1.5-2.0
#geqo_seed = 0.0            # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100    # range 1-10000
#constraint_exclusion = partition    # on, off, or partition
#cursor_tuple_fraction = 0.1        # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit
                    # JOIN clauses


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'        # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'        # directory where log files are written,
                    # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
#log_file_mode = 0600            # creation mode for log files,
                    # begin with 0 to use octal notation
#log_truncate_on_rotation = off        # If on, an existing log file with the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
#log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
#log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

#silent_mode = off            # Run server silently.
                    # DO NOT USE without syslog or
                    # logging_collector
                    # (change requires restart)


# - When to Log -

#client_min_messages = notice        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error

#log_min_messages = warning        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

#log_min_error_statement = error    # values in order of decreasing detail:
                     #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                     #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)

#log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this number
                    # of milliseconds


# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default        # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%t '            # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
#log_lock_waits = off            # log lock waits >= deadlock_timeout
#log_statement = 'none'            # none, ddl, mod, all
#log_temp_files = -1            # log temporary files equal or larger
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'


#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_functions = none            # none, pl, all
#track_activity_query_size = 1024     # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit


#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public'        # schema names
#default_tablespace = ''        # a tablespace name, '' uses the default
#temp_tablespaces = ''            # a list of tablespace names, '' uses
                    # only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0            # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex'            # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'

# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                    # abbreviations.  Currently, there are
                    #   Default
                    #   Australia
                    #   India
                    # You can create your own file in
                    # share/timezonesets/.
#extra_float_digits = 0            # min -15, max 3
#client_encoding = sql_ascii        # actually, defaults to database
                    # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'            # locale for system error message
                    # strings
lc_monetary = 'en_US.UTF-8'            # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'            # locale for number formatting
lc_time = 'en_US.UTF-8'                # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -

#dynamic_library_path = '$libdir'
#local_preload_libraries = ''


#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64        # min 10
                    # (change requires restart)
# Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64    # min 10
                    # (change requires restart)

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#array_nulls = on
#backslash_quote = safe_encoding    # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off


#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------

#exit_on_error = off                # terminate session on any error?
#restart_after_crash = on            # reinitialize after backend crash?


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = ''        # list of custom variable class names





My /etc/TileStache/tilestache.conf file is (note that I am trying this many different ways; osm and example work):

{
  "cache":
  {
    "name": "Disk",
    "path": "/tmp/stache"
  },

  "layers":
  {
    "parcels5":
    {
        "provider":
        {
            "class": "TileStache.Goodies.Providers.PostGeoJSON.Provider",
            "kwardgs":
            {
                "dsn": "dbname=gis user=gisuer",
                "query": "SELECT PARID, ZIP, PO_NAME, the_geom from parcels WHERE PARID && !bbox! AND PO_NAME IS NOT NULL",
                "id_column": "PARID", "geometry_column": "the_geom"
            }
        }
    },
    "parcels3":
    {
        "provider": {
            "name": "vector",
            "driver": "PostGIS",
            "parameters": {
                "dbname": "gis",
                "user": "gisuser",
                "password": "gogo",
                "table": "parcels"
             }
        },
        "preview": {
            "ext": "json"
        }
    },
    "parcels2":
    {
        "provider": {
            "name": "mbtiles",
            "tileset": "parcel-png8.mbtiles"
        }
    },
    "parcels":

    {
        "provider": {"name": "vector", "driver": "GeoJSON",

        "parameters": {"file": "test-parcels.json"},
        "properties": ["PARID"]}
    },
    "parcels4":
    {
        "provider": {"name": "vector", "driver": "shapefile",
                     "parameters": {"file": "Parcels_3.shp"},
                     "properties": ["PARID", "PO_NAME"]}
    },

    "osm":
    {
        "provider": {"name": "proxy", "provider": "OPENSTREETMAP"},
        "png options": {"palette": "http://tilestache.org/example-palette-openstreetmap-mapnik.act"}
    },
    "example":
    {
        "provider": {"name": "mapnik", "mapfile": "examples/style.xml"},
        "projection": "spherical mercator"
    }
  }
}

[Todd Albert]

I seemed to have honed in on the problem.

My apache error logs show that there is a problem when I try to use vectors in TileStache. The script is hanging up in TileStache/Vector/__init__.py, line 378, which has the command:

     driver = ogr.GetDriverByName(str(driver_name))

The error that is returned is that 'ogr' is not defined.

I have no trouble accessing ogr from bash, but I tried within python and ogr is unreachable. The command "from osgeo import ogr" within python yields the error:

File "<stdin>", line 1, in <module>

ImportError: No module named osgeo

So, apparently, GDAL/OGR is installed properly and python is installed properly, but either the bindings are not installed properly or their is a config-file or path not setup correctly.

Anyone have any suggestions here?

[Todd Albert]

Ok, I've gotten Python talking to GDAL now thanks to:

http://blog.mackerron.com/2012/06/01/postgis-2-ubuntu-12-04/

Then Python wouldn't talk to PostGIS / PostgreSQL because psycopg2 wasn't intalled correctly. I simply did:

http://www.ehow.com/how_8614778_install-psycopg2-ubuntu-pip.html

Now the commands:

    from osgeo import ogr

and

    import psycopg2

both work in python.

So why am I still unable to connect to the database?!

[Todd Albert]

Making progress…

Now I got python and postgresql talking. Now I need to get TileStache working. Well, it works… it will serve tiles from direct data sources, but not (yet) the database.

Do I have something wrong in my tilestache.cfg?

Here's the important bit:

…

"parcels2":

{

    "provider": {"name": "vector", "driver": "postgis",

…

[Todd Albert]

Still no luck on this, Mike. I am able to produce a .geojson tile from a .json source, but still not from my database.

I've assured that everything is properly installed, the database is working, and everything seems to be setup correctly, but… when I request a single tile from the database, I still get a 500 Internal Server Error. A look at the apache error log shows:

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] mod_python (pid=1484, interpreter='localhost', phase='PythonHandler', handler='TileStache::modpythonHandler'): Application error

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] ServerName: 'localhost'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] DocumentRoot: '/var/www'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] URI: '/tiles/tiles.py/parcels2/16/18185/27794.geojson'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] Location: None

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] Directory: '/var/www/tiles/'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] Filename: '/var/www/tiles/tiles.py'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] PathInfo: '/parcels2/16/18185/27794.geojson'

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] Traceback (most recent call last):

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/lib/python2.7/dist-packages/mod_python/importer.py", line 1537, in HandlerDispatch\n    default=default_handler, arg=req, silent=hlist.silent)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/lib/python2.7/dist-packages/mod_python/importer.py", line 1229, in _process_target\n    result = _execute_target(config, req, object, arg)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/lib/python2.7/dist-packages/mod_python/importer.py", line 1128, in _execute_target\n    result = object(arg)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/__init__.py", line 462, in modpythonHandler\n    mimetype, content = requestHandler(config_path, path_info, query_string)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/__init__.py", line 287, in requestHandler\n    mimetype, content = getTile(layer, coord, extension)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/__init__.py", line 96, in getTile\n    tile = layer.render(coord, format)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/Core.py", line 384, in render\n    tile = provider.renderTile(width, height, srs, coord)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/Vector/__init__.py", line 561, in renderTile\n    layer, ds = _open_layer(self.driver, self.parameters, self.layer.config.dirpath)

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1]   File "/usr/local/lib/python2.7/dist-packages/TileStache/Vector/__init__.py", line 436, in _open_layer\n    datasource = driver.Open(str(source_name))

[Fri Aug 24 10:21:11 2012] [error] [client 192.168.37.1] AttributeError: 'NoneType' object has no attribute 'Open'

If I am defining PostgreSQL as my provider, I am not sure why the script is even executing line 436.

Please, please, please help me out here. I need to get this running by Monday.

Desperately,

Todd

[Michal Migurski]

Hi Todd,

You're at least doing everything right. Line 436 is reached because the gdal datasource is defined here:
https://github.com/migurski/TileStache/blob/master/TileStache/Vector/__init__.py#L383

Gdal wants a string like "PG:dbname='foo' user='blah'" in order to make a connection to the database. What happens if you execute this minimal bit of Python?

from osgeo import ogr
driver = ogr.GetDriverByName('PostgreSQL')
src_name = "PG:dbname='gis' user='gisuser' password='gogo' table='parcels'"
datasource = driver.Open(src_name)
assert datasource

I'm still traveling, but I'll be on email later tonight (UK time).

-mike.

[Todd Albert]

Mike,

I tried executing ogrinfo PG:"dbname:'gis' user:'gisuser'"
(or whatever the proper command is - I'm on my phone at the moment) from bash and it failed because OGR was not installed with the PG driver.

So, I rebuilt GDAL with Python, Geos, and PostgreSQL, all configured properly (I think), and reinstalled. I also reinstalled PostgreSQL and PostGIS.

Still no dice. Still no driver.

So, it's obviously not a TileStache bug. It's that I can't seem to get GDAL/OGR installed with the required PG driver.

Any thoughts or suggestions?

Thanks,
Todd

P.S. safe travels!

[Michal Migurski]

Looking back to your previous posts, I see that you used this to install the ogr bindings:
http://blog.mackerron.com/2012/06/01/postgis-2-ubuntu-12-04/

My experience with Postgis 2.0 is very limited, and I can see that the instructions you followed include postgres in the gdal build step. Did you see any errors go by when you did that, about missing postgres-anything?

Maybe a more blunt question: do you actually need postgis 2.0, or would it be enough to use the older postgis 1.5 which is well-supported in Ubuntu package managers? I've only installed Postgis 2.0 once myself, and I remember it being a multiple-hours-long affair that I only put myself through because I needed the offset curves function. Maybe you can get by without it?

I'll fire up a 12.04 machine and try to retrace your steps.

-mike.

[Michal Migurski]

Hi Todd,

I followed George MacKerron's instructions on a clean Ubuntu 12.04 install, with some minor differences:

- I have skipped PostGIS 2.0 since it's not really the part I'm testing
- I modified /etc/postgresql/9.1/main/pg_hba.conf, to set all methods to "trust" (so passwords, etc. wouldn't be needed)

...and this works:
from sys import path
path.append('/opt/gdal/lib/python2.7/site-packages') # needed due to /opt prefix from build

from osgeo import ogr
driver = ogr.GetDriverByName('PostgreSQL')

src_name = "PG:dbname='postgres' user='postgres'"

datasource = driver.Open(src_name)
assert datasource

So I've at least gotten a working PG driver this way. Does the sys.path line above look like something you've done as well? Might you be importing from the wrong osgeo package? What about the edit to pg_hba.conf? I typically set everything to "trust" when I work with Postgres on my own systems, might that be an important difference?

-mike.

[Todd Albert]

Hi Mike,

Ok, this sounds promising…

I tried George MacKerron's instructions again, but without the prefix to simplify things. I skipped PostGIS. I will probably need it later on, but I don't think I need 2.0 for any reason.

I got everything installed and then ran, line-by-line in Python:

>>>  from osgeo import ogr

>>>  driver=ogr.GetDriverByName('PostgreSQL')

>>>  src_name="PG:dbname='gis' user='gisuser'"

>>>  datasource=driver.Open(src_name)

And then I got the error:

   NameError: name 'driver' is not defined

I assume this means that PostgreSQL is not a valid driver.

So I don't think this solved the problem.

I even tried the "from sys import path" line first.

I did edit pg_hba.conf to set everything to trust.

I don't think I am importing from the wrong osgeo package, because before I rebuilt it, the line "from osgeo import ogr" wasn't working. And I uninstalled the package before rebuilding it.

At bash, the command ogrinfo -- formats does not list a PG, PostgreSQL, or PostGIS driver. The closest thing I see is DXF or PGDump. So I am back to thinking that even though I am configuring gdal with PostgreSQL (--with-pg=/usr/lib/postgresql/9.1/bin/pg_config), it doesn't seem to be installing that driver. Ugh!

So I'm still stuck here… but I feel like I am SO close!!  Any other ideas??

Thanks so much for all of the help!!

-Todd

[Todd Albert]

Mike, you're really haloing me out here!

Ok, so far this works (see below). But I have not yet installed mapnik, apache, and tilestache, nor have I imported any data into my database. But the PG driver is installed in OGR and working from Python.

As for the rest, I'll keep you posted…

# from a clean install of Ubuntu-Server
sudo aptitude update
sudo aptitude upgrade

# install prerequisites
sudo aptitude install build-essential checkinstall postgresql \
postgresql-server-dev-9.1 libjson0-dev libxml2-dev libproj-dev \
python2.7-dev swig

cd ~
mkdir -p src

# download and compile geos
cd ~/src/
wget http://download.osgeo.org/geos/geos-3.3.5.tar.bz2
tar -xvjf geos-3.3.5.tar.bz2
cd geos-3.3.5
./configure --enable-python
make -j2
sudo checkinstall # uninstall with: dpkg -r geos

# download and compile gdal
cd ~/src/
wget http://download.osgeo.org/gdal/gdal-1.9.1.tar.gz
tar -xvzf gdal-1.9.1.tar.gz
cd gdal-1.9.1/
./configure --with-geos=/usr/local/bin/geos-config \
--with-pg=/usr/lib/postgresql/9.1/bin/pg_config --with-python
make -j2
sudo checkinstall # uninstall with: dpkg -r gdal

sudo ldconfig

sudo -u postgres -iH

# create a user within the database
createuser -SdRP gisuser

# create a database
createdb -E UTF8 -O gisuser gis
exit

# configure access to our database

sudo nano /etc/postgresql/9.1/main/pg_hba.conf

# Scroll to bottom and change the words ident and md5 to “trust”

sudo /etc/init.d/postgresql start

[Michal Migurski]

On Aug 26, 2012, at 1:26 AM, Todd Albert wrote:

> Hi Mike,
>
> Ok, this sounds promising…
>
> I tried George MacKerron's instructions again, but without the prefix to simplify things. I skipped PostGIS. I will probably need it later on, but I don't think I need 2.0 for any reason.
>
> I got everything installed and then ran, line-by-line in Python:
>
> >>> from osgeo import ogr
> >>> driver=ogr.GetDriverByName('PostgreSQL')
> >>> src_name="PG:dbname='gis' user='gisuser'"
> >>> datasource=driver.Open(src_name)
>
> And then I got the error:
>
> NameError: name 'driver' is not defined
>
> I assume this means that PostgreSQL is not a valid driver.

This is actually really weird. A NameError is raised when a variable doesn't exist, which shouldn't be true no matter what the return value of ogr.GetDriverByName is. It could be None or False or whatever, and you still should not be seeing a NameError from these four lines of Python *no matter what*.

-mike.

[Todd Albert]

Below is a detailed list of what I've done (most recently).

Results:

+ PG-driver is installed

+ Apache is serving me web pages

– I cannot SFTP anything into /var/www

– TileStache tests all return 500 Internal Server Error

So close… but yet so far…

cd /

sudo ldconfig

sudo -u postgres -iH

# create a user within the database

createuser -SdRP gisuser

# create a database

createdb -E UTF8 -O gisuser gis

exit

# configure access to our database

sudo nano /etc/postgresql/9.1/main/pg_hba.conf

# Scroll to bottom and change the words ident and md5 to “trust”

# restart the PostgreSQL Server

sudo /etc/init.d/postgresql start

# install Apache, MySQL, and PHP

sudo tasksel install lamp-server

# ensure add-apt-repository is installed

sudo apt-get install -y python-software-properties

# install Mapnik 2.0

sudo add-apt-repository ppa:mapnik/v2.0.2

sudo apt-get update

sudo apt-get install libmapnik mapnik-utils python-mapnik

# Now to install TileStache. First some dependencies...

# install mod_python which TileStache will use to communicate

# with the web server

sudo apt-get install libapache2-mod-python

# restart the web server

sudo /etc/init.d/apache2 restart

# install packages Curl and Git

sudo apt-get install curl

sudo apt-get install git-core

# install some required python tools and libraries

sudo apt-get install python-setuptools

sudo aptitude install python-dev

sudo apt-get install libjpeg8 libjpeg62-dev libfreetype6 libfreetype6-dev

# install PIP to easily install python modules

sudo curl -O https://raw.github.com/pypa/pip/master/contrib/get-pip.py

sudo python get-pip.py

# install the required python modules

sudo pip install -U werkzeug

sudo pip install -U simplejson

sudo pip install -U modestmaps

# PIL has some quirks in Ubuntu 11.10 so we need to do some quick fixes

sudo ln -s /usr/lib/x86_64-linux-gnu/libjpeg.so /usr/lib

sudo ln -s /usr/lib/x86_64-linux-gnu/libfreetype.so /usr/lib

sudo ln -s /usr/lib/x86_64-linux-gnu/libz.so /usr/lib

sudo pip install -U pil

# download TileStache from GitHub

sudo git clone https://github.com/migurski/TileStache.git

# run the install script

cd TileStache/

sudo python setup.py install

# add the mod_python configuration to tell our web server

# which URLs to have TileStache process

sudo nano /etc/apache2/httpd.conf

# and add this:

<Directory /var/www/tiles>

  AddHandler mod_python .py

  PythonHandler TileStache::modpythonHandler

  PythonOption config /TileStache/tilestache.cfg

</Directory>

# will direct any web traffic to the “tiles” folder

# containing the file extension “.py” to TileStache

# need to add a tiles directory to the web directory

sudo mkdir /var/www/tiles

# install OpenSSH

sudo apt-get install openssh-client

sudo apt-get install openssh-server

# to allow established connections to continue to function

sudo iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# to allow SSH traffic

sudo iptables -A INPUT -p tcp --dport 22 -j ACCEPT

# to allow HTTP traffic

sudo iptables -A INPUT -p tcp --dport 80 -j ACCEPT

# to allow HTTPS traffic

sudo iptables -A INPUT -p tcp --dport 443 -j ACCEPT

# to allow remote database connections

sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

# drop all remaining traffic

sudo iptables -A INPUT -j DROP

# save all the rules to a file

sudo iptables-save > /etc/iptables.rules

# add the rules to the network adapter by editing the interfaces file

sudo nano /etc/network/interfaces

# just after iface eth0 inet static, add the following

# and make sure it is indented like the other lines

pre-up iptables-restore < /etc/iptables.rules

# reboot

sudo reboot

# if you don't know your ip address or server address, get your ip

ifconfig | grep inet

# Testing Your Server

# in a browser, go to:

 http://YOUR.IP.ADDRESS/tiles/tiles.py/osm/preview.html

# get an individual tile:

 http://YOUR.IP.ADDRESS/tiles/tiles.py/osm/16/18184/27794.png

# test that tileStache and Mapnik are working together:

 http://YOUR.IP.ADDRESS/tiles/tiles.py/example/preview.html

[Todd Albert]

Mike,

Several more problems solved. Another problem encountered.

I figured out one issue from my apache error.log:

[Sat Aug 25 21:55:08 2012] [error] [client 192.168.37.1] AttributeError: module '/usr/local/lib/python2.7/dist-packages/TileStache/__init__.pyc' contains no 'ModpythonHandler'

It seems that I had accidentally capitalized the M in what should have been 'modpythonHandler' in my httpd.conf file!

I've now managed to get TileStache working again with .json files.

At first, it is still was not able to connect to the database. I tweaked some permissions and modified the database, and now I can connect to the database from Python using the script you provided. But since I didn't modify any prefixes, I just used:

>>> from osgeo import ogr
>>> driver = ogr.GetDriverByName('PostgreSQL')

>>> src_name = "PG:dbname='gis' user='gisuser'"

>>> datasource = driver.Open(src_name)
>>> assert datasource

And no errors! Hallelujah!!

So, I fired up my browser and pointed to a vector tile that I've defined:

    http://192.168.37.131/tiles/tiles.py/parcels2/16/18185/27794.geojson

And I get the error:

  Known unknown!
  Couldn't get a layer from data source PG:dbname='gis' user='gisuser'

So, do I perhaps now have my database setup incorrectly? I can generate a .geojson tile from a .json file, but so far have not been able to from the database. I imported my data into the database from a .shp file. The same .shp file that I used to create the .json file that is working.

Two steps forward, one step back… but I feel like I am really close. Still…

Thanks,

Todd

[Todd Albert]

How can I assure that my data are formatted / stored properly in the database?

Spatial queries seem to work.

Is there a special format that TileStache is expecting?

–Todd

[Todd Albert]

Grr…

I went back and recreated the database, redid my shp2pgsql command with the original shapefile, and re-imported the data into PostGIS.

I still get the error:

Known unknown!
Couldn't get a layer from data source PG:dbname='gis' user='gisuser'

Nothing valuable appears in the apache logs this time…

–Todd

[Todd Albert]

Mike,

Ok, I'm making good progress here…

I had to go into the PostGIS database and manually insert my SRID into the geometry_columns table. It had my SRID as -1 instead of 2236. I updated that. 

Now when I visit http://192.168.37.131/tiles/tiles.py/parcels2/16/18185/27794.geojson it actually serves a tile instead of an error!

But, just when I thought I had things going well, I look at the .geojson file that it server and I see:

{"type": "FeatureCollection", "features": []}

It's empty!

So, I looked at my geography_columns table and it is empty. It has columns, but there are 0 rows.

Do I need the geography_columns to be defined for my table?

What am I doing wrong here?!

[Todd Albert]

Here's what I tried –

I reprojected the data into Google's Spherical Mercator projection (SRID=900913)

   sudo ogr2ogr re_Parcels_3.shp Parcels_3.shp -t_srs EPSG:900913

I verified that this worked

   ogrinfo re_Parcels_3.shp -al -so

...

Layer SRS WKT:

PROJCS["Google_Maps_Global_Mercator",

    GEOGCS["GCS_WGS_1984",

        DATUM["WGS_1984",

            SPHEROID["WGS_1984",6378137,298.257223563]],

        PRIMEM["Greenwich",0],

        UNIT["Degree",0.017453292519943295]],

    PROJECTION["Mercator_2SP"],

    PARAMETER["standard_parallel_1",0],

    PARAMETER["latitude_of_origin",0],

    PARAMETER["central_meridian",0],

    PARAMETER["false_easting",0],

    PARAMETER["false_northing",0],

    UNIT["Meter",1]]

...

I then imported this data into the database

   shp2pgsql re_Parcels_3.shp > re_Parcels_3.sql

   psql -d gis -U gisuser -f re_Parcels_3.sql

And updated the geometry_columns table to associate the new SRID with the new table.

Of course I had to update my tilestache.cfg to point to the new table.

And still, I'm getting blank tiles.

   {"type": "FeatureCollection", "features": []}

When I point to the layer that points directly to the .json sample file, I get a bunch of features

{"type": "FeatureCollection", "features": [{"geometry": {"type": "Polygon", "coordinates": [[[-80.107558, 26.338201], [-80.10732, 26.338201], [-80.10732, 26.337898], [-80.107558, 26.337898], [-80.107558, 26.338201]]]}, "type": "Feature", "properties": {"PARID": "06424725140260280"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.111183, 26.340753], [-80.110847, 26.340758], [-80.110843, 26.340552], [-80.111179, 26.340546], [-80.111183, 26.340753]]]}, "type": "Feature", "properties": {"PARID": "06424725220550240"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.11199, 26.339854], [-80.111654, 26.339859], [-80.11165, 26.339653], [-80.111986, 26.339648], [-80.11199, 26.339854]]]}, "type": "Feature", "properties": {"PARID": "06424725220540190"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.108286, 26.339245], [-80.108051, 26.339246], [-80.10805, 26.338943], [-80.108285, 26.338943], [-80.108286, 26.339245]]]}, "type": "Feature", "properties": {"PARID": "06424725140250120"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.111491, 26.339303], [-80.111155, 26.339309], [-80.111151, 26.339102], [-80.111487, 26.339097], [-80.111491, 26.339303]]]}, "type": "Feature", "properties": {"PARID": "06424725220550120"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.107223, 26.339686], [-80.106994, 26.339686], [-80.106994, 26.339384], [-80.107223, 26.339384], [-80.107223, 26.339686]]]}, "type": "Feature", "properties": {"PARID": "06424725140240190"}}, {"geometry": {"type": "Polygon", "coordinates": [[[-80.106935, 26.337761], [-80.10681152467893, 26.337761], [-80.10681152467893, 26.337729968444485], [-80.106935, 26.337729968444485], [-80.106935, …………

and on and on for pages…

I am running out of time to figure this out. We're on the threshold of switching to a commercial solution which is much less flexible.

Please help!

Desperately,

Todd

On Sunday, August 26, 2012 2:34:35 PM UTC-4, Todd Albert wrote:

Mike,

Ok, I'm making good progress here…

I had to go into the PostGIS database and manually insert my SRID into the geometry_columns table. It had my SRID as -1 instead of 2236. I updated that. 

Now when I visit http://192.168.37.131/tiles/tiles.py/parcels2/16/18185/27794.geojson it actually serves a tile instead of an error!

But, just when I thought I had things going well, I look at the .geojson file that it server and I see:

{"type": "FeatureCollection", "features": []}

It's empty!

So, I looked at my geography_columns table and it is empty. It has columns, but there are 0 rows.

Do I need the geography_columns to be defined for my table?

What am I doing wrong here?!

[Michal Migurski]

Well, my ability to help with your own database is fairly limited, but here's what I'd look out for:

1. The vector provider will try to figure out your layer's projection, but if it can't for some reason it will default back to plain geographic 4326, so to be on the safe side keep your database unprojected and in lat, lon:
https://github.com/migurski/TileStache/blob/master/TileStache/Vector/__init__.py#L487

2. I'm pretty sure you don't need anything in geography_columns, it's a new PostGIS 2.0-ism and I've never used it, but as I've said my experience with 2.0 is limited.

3. Can you make normal spatial queries of your table using psql itself for a given tile? You seem to have data in Florida, can you query for features within a known tile bounding box like 14/4546/6948 (http://teczno.com/s/yjh)?
southwest: 26.33280 -80.11230 (geographic)
northeast: 26.35249 -80.09033 (geographic)
upper-left: -8918061 3042804 (spherical mercator)
lower-right: -8915615 3040358 (spherical mercator)

4. If you need to see inside whatever Tilestache is doing, you can turn on logging output and modify the source code of TileStache/Vector/__init__.py to insert your own logging statements and watch your web server's log, wherever that goes:
http://tilestache.org/doc/#logging

# sample log code
import logging; logging.debug('hello world')

Hope this helps!

-mike.

[Todd Albert]

Mike,

You're a lifesaver. I roughly followed these instructions to convert and import the shapefile to PostGIS in the WGS 4326 format.

The only other stumbling block was one field that was imported was a date field (that I didn't need). Ogr got hung up on it so I just removed the column from the database and smooth sailing from there!

Thank you so so much for your help!!!

You are my hero!

Enjoy the UK

–Todd

Todd Albert
Lead Software Developer
Mad Valorem, LLC
http://www.MadValorem.com/
to...@madvalorem.com

[Michal Migurski]

Awesome!

Thrilled this worked out. Super curious what the competing commercial product was, too.

-mike.

[Todd Albert]

We were looking at Arc Online, CartoDB.com, GisCloud.com, or MapBox.com as potential back-up plans, but a custom solution was really best for us. We couldn't do exactly what we want with any of those solutions.

Thanks again! And keep your eyes on http://www.MadValorem.com/ in the future (~December 1, 2012) to see this in action!

–Todd

Todd Albert
Lead Software Developer
Mad Valorem, LLC
http://www.MadValorem.com/
to...@madvalorem.com

[d.]

Hi all, I am still struggeling with this.

I've rebuild gdal/ogr and geos (both with python support). But when I try mikes script:

from osgeo import ogr
driver = ogr.GetDriverByName('PostgreSQL')

src_name = "PG:host=127.0.0.1 dbname='webmap user=postgres password=xxx"

datasource = driver.Open(src_name)
assert datasource

the last line gives me the error:

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

AssertionError

So I can't get python talk to my db I think - what am I doing wrong?

[Michal Migurski]

Your src_name looks wrong, with that lone single-quote. Is this exactly the code you are using?

-mike.

[Todd Albert]

d.

Did you verify that the PG drivers are installed and working? "ogrinfo --formats"

Also, did you correct the stray ' that you have in the src_name line?

Hang in there… it may be a rough ride, but you can do this!

–Todd

[d.]

Hi Todd,

ogrinfo --formats gives me:
Supported Formats:
  -> "ESRI Shapefile" (read/write)
  -> "MapInfo File" (read/write)
  -> "UK .NTF" (readonly)
  -> "SDTS" (readonly)
  -> "TIGER" (read/write)
  -> "S57" (read/write)
  -> "DGN" (read/write)
  -> "VRT" (readonly)
  -> "REC" (readonly)
  -> "Memory" (read/write)
  -> "BNA" (read/write)
  -> "CSV" (read/write)
  -> "GML" (read/write)
  -> "GPX" (read/write)
  -> "KML" (read/write)
  -> "GeoJSON" (read/write)
  -> "GMT" (read/write)
  -> "SQLite" (read/write)
  -> "PostgreSQL" (read/write)
  -> "PCIDSK" (read/write)
  -> "XPlane" (readonly)
  -> "AVCBin" (readonly)
  -> "AVCE00" (readonly)
  -> "DXF" (read/write)
  -> "Geoconcept" (read/write)
  -> "GeoRSS" (read/write)
  -> "GPSTrackMaker" (read/write)
  -> "VFK" (readonly)
  -> "PGDump" (read/write)
  -> "GPSBabel" (read/write)
  -> "SUA" (readonly)
  -> "OpenAir" (readonly)
  -> "PDS" (readonly)
  -> "WFS" (readonly)
  -> "HTF" (readonly)
  -> "AeronavFAA" (readonly)
  -> "EDIGEO" (readonly)
  -> "GFT" (read/write)
  -> "SVG" (readonly)
  -> "CouchDB" (read/write)
  -> "Idrisi" (readonly)
  -> "ARCGEN" (readonly)
  -> "SEGUKOOA" (readonly)
  -> "SEGY" (readonly)

So I thinks, thats it ok? I can query my db and tables with that tool and for instance I get:
ogrinfo PG:"host=127.0.0.1 user=postgres password=xxx  dbname=webmap port=5432" tilestache -summary

INFO: Open of `PG:host=127.0.0.1 user=postgres password=irmeltier  dbname=webmap port=5432'
      using driver `PostgreSQL' successful.

Layer name: tilestache
Geometry: Point
Feature Count: 11
Extent: (-2.267578, -122.260000) - (37.800000, 2.244141)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
FID Column = id
Geometry Column = geom
name: String (20.0)

Right now I've tried to rebuild python, geos and gdal again, with the effect that nothing works right now. I've tried to build geos -enable-python but got an error, because pathon was not build with -enable-shared. but when I try that, "make" yells, that it fails to build multiple modules.
but without proper python no geos, no gdal.

anybody has ideas what to do on that python problem?

sometimes i hate this stuff

[Todd Albert]

Yes, it's good that it lists "PostgreSQL" as a driver in OGR.

Since you're able to query the database, you're definitely past many of the hurdles I had along the way.

Have you assured that the data in your database is formatted to SRID: 4326 as Mike had suggested? (ogr2ogr can reproject shapefiles for you)

Did you update your geometry_columns table to not the correct SRID for the table of data you're trying to display?

–Todd

[d.]

sorry for the late response.

To bring everything run I've resetuped my system.
PostGIs is version 1.5 with a table where the srid is 4326

ogrinfo is able to connect to the db and gives me

INFO: Open of `PG:host=127.0.0.1 user=postgres password=*** dbname=postgistest port=5432'
      using driver `PostgreSQL' successful.

Layer name: points
Geometry: Point
Feature Count: 35
Extent: (-126.400000, -45.320000) - (126.400000, 45.320000)

Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.01745329251994328,

        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
FID Column = id

Geometry Column = point
name: String (20.0)

I am also able to run the 5 line python script above with no error. So I think my gdal/ogr, postgis/ python setup is alright. I've created a small polymaps testscript:
var po = org.polymaps;

var map = po.map()
    .container(document.getElementById("map").appendChild(po.svg("svg")))
    .center({lat: 0, lon: 0})
    .zoomRange([1, 10])
    .zoom(5)
    .add(po.interact());

map.add(po.image()
    .url(po.url("http://{S}tile.cloudmade.com"
    + "/1a1b06b230af4efdbb989ea99e9841af"
    + "/20760/256/{Z}/{X}/{Y}.png")
    .hosts(["a.", "b.", "c.", ""])));

map.add(po.geoJson()
    .url("jsontest.json")
    .id("points"))

map.add(po.compass()
    .pan("none"));

which gives me a world map with some points - direct rendered from polymaps - perfect so far. No I have my tilestache.cfg file:
{
  "cache":
  {
    "name": "Test",
    "path": "/tmp/stache",
    "umask": "0000"
  },
  "layers":
  {
    "jsontest":
    {  "provider": {"name": "vector", "driver": "GeoJSON",
                   "parameters": {"file": "jsontest.json"},
           "properties": ["points"]}
             }
    }
}

where the jsontest.json file is in the same folder as the cfg file. I think my problem is that I don't now how to request the tiles correct in polymaps or how to configure tilestache to serve them correctly, because when I change the following line in my polymaps-script:
map.add(po.geoJson()
    .url("jsontest.json")
    .id("points"))

to
map.add(po.geoJson()
    .url("http://localhost/jsontest/{Z}/{X}/{Y}.json")
    .id("points"))

then firebug tells me:

GET http://localhost:8080/jsontest/5/13/16.json

200 OK

1.2s

polymaps.js (line 307)

for all tiles but the response is empty I think my mistake is that I do not serve/request the tiles correctly or am I wrong? Can somebody please explain how to configurate tile serving and a proper requesting? Thank you so much! D.

[d.]

sorry, the script URI for the tiles is   
.url("http://localhost:8080/jsontest/{Z}/{X}/{Y}.json")

but still no result.

[Todd Albert]

Have you tried bringing up a single tile from TileStache in your browser?

Also, TileStache will serve .geojson, not .json. It may be as simple as just adding the geo.

[namec Piccolo]

Hello everyone,

I hope tode found a solution.

Currently I am stuck with a similar issue.

I have installed properly tilestache and pgsql db. But when trying to render tiles. It doesn't shows all the tiles !!!!!

I have tiles shown but also blank tiles ??? Can any one help me