adapting this to other datasources

[jaketangosierra]

Hi,

I'm not sure if this is the right place to ask this question, but I haven't been able to find other resources in this space, so I figured I'd ask here.

I'm trying to adapt this and the mapzen TileStache fork to a differently formatted dataset (a subset of OSM data for now, but extending into other things in the future). What are the assumptions of what gets returned from the SQL queries for each zoom level? I've read through the various .pgsql files, and tried to replicate them for my own dataset, but it looks like there's some assumptions being made in the VecTiles code for the .mapbox extensions. Are these assumptions enumerated someplace?

If not, could they be documented? I'd prefer not to figure them out myself, but can if needed.

Thanks

[rmarianski]

Hey,

This is a fine place to ask. I'm surprised to hear you say that there are assumptions made specifically for the mapbox format. Do you mind expanding on some of the specifics of what didn't work when you tried?

There are some inconsistencies at the moment with how the formatters handle ids. And in particular with the mapbox format, if the geometry is a multipolygon, we clone that feature and create a separate one for each polygon in the multipolygon. But otherwise, I don't think that there are other assumptions baked into the queries for each format.

We have been moving to a system where some transformations happen at the python level, and not in sql. Those are also meant to be format agnostic.

[jaketangosierra]

In one of the pgsql files, I'm doing very simple joins:

SELECT location.id as id, allowed, linestring as geometry FROM ways, location WHERE location_type like 'osm_way' and osm_id=ways.id (linestring && !bbox!);

but when I go to get a tile, I get errors like:

Traceback (most recent call last):
File "/usr/local/lib/python2.7/site-packages/TileStache/init.py", line 311, in requestHandler2
status_code, headers, content = layer.getTileResponse(coord, extension)
File "/usr/local/lib/python2.7/site-packages/TileStache/Core.py", line 431, in getTileResponse
tile.save(buff, format, **save_kwargs)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 427, in save
tile = layer.provider.renderTile(width, height, layer.projection.srs, self.coord)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 209, in renderTile
self.columns[query] = query_columns(self.dbinfo, self.srid, query, bounds)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 467, in query_columns
db.execute(query + '\n LIMIT 0')
File "/usr/local/lib/python2.7/site-packages/psycopg2/extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)
ProgrammingError: syntax error at or near "LIMIT"
LINE 2: LIMIT 0
^

This seems like it's expecting "output" from the SQL file in a certain way (either as a sub-query, or certain projections coming in, I'm not quite sure). Do the geometries returned need to be in a specific projection? In looking at the commands provided in this repo, it looks like there are size limitations being imposed, is this assumption required?

Maybe I'm mis-interpreting these errors, and if so, do you have a different suggestion?

[rmarianski]

Try removing the semicolon at the end of your query to see if that helps.

It initially does a query to figure out what the columns need to be. It could just be that the semicolon is separating out the limit into a separate query for the db, and that's why it's blowing up.

[jaketangosierra]

It does not:

Traceback (most recent call last):
File "/usr/local/lib/python2.7/site-packages/TileStache/init.py", line 311, in requestHandler2
status_code, headers, content = layer.getTileResponse(coord, extension)
File "/usr/local/lib/python2.7/site-packages/TileStache/Core.py", line 431, in getTileResponse
tile.save(buff, format, **save_kwargs)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 427, in save
tile = layer.provider.renderTile(width, height, layer.projection.srs, self.coord)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 209, in renderTile
self.columns[query] = query_columns(self.dbinfo, self.srid, query, bounds)
File "/usr/local/lib/python2.7/site-packages/TileStache/Goodies/VecTiles/server.py", line 467, in query_columns
db.execute(query + '\n LIMIT 0')
File "/usr/local/lib/python2.7/site-packages/psycopg2/extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)
ProgrammingError: syntax error at or near "LIMIT"
LINE 2: LIMIT 0
^

[rmarianski]

Can you try putting in a breakpoint or printing out the query that it's trying to run? That might reveal what's going on.

I think it's going to break later on because it expects the columns to be named __geometry__ and __id__, but that shouldn't affect the error you're seeing in the stacktrace.

[jaketangosierra]

eek, those columns are named that way, but the markdown interpreter make the underscores translate into bold instead.

The problem, after doing some digging, is that the command

db.execute(query + '\n LIMIT 0;')
(or at https://github.com/mapzen/TileStache/blob/integration-1/TileStache/Goodies/VecTiles/server.py#L467)

is appending

'\n LIMIT 0;'
to a query string that already has a semi-colon (not from my query *.pgsql files).

If I print the same thing it's trying to execute before it doing so, I see

SELECT location.id as id, allowed, linestring as geometry FROM ways, location WHERE location_type like 'osm_way' and osm_id=ways.id (linestring && ST_SetSRID(ST_MakeBox2D(ST_MakePoint(-10385651.907163, 5606197.402548), ST_MakePoint(-10380759.937353, 5611089.372358)), 900913));
LIMIT 0;

[rmarianski]

Ah, understood about the underscores around the property names. I've had that happen to me too. :)

That semicolon getting inserted is odd, because we're not encountering that on our end. Not sure where it's coming from. Maybe as a quick fix what we can do in the columns query is trim the query for whitespace, and then semicolons. Something like:

query = query.strip().rstrip(';')
db.execute(query + '\n LIMIT 0')

[jaketangosierra]

So, I'm stupid, I'd edited the config file, and I was pointing to the wrong place. The semi-colon was my fault.

That said, upon fixing it, I'm getting tiles returned, but they're blank (or at least not rendering the way I'm expecting them to).

The last time I setup TileStache (using the core version), it took a while to render tiles, and I'm expecting this one to take a bit too. There's no errors, and I'm getting successful tile requests. Is there something else I should be looking for?

[rmarianski]

I'd suggest logging the actual queries that the database sees. Maybe the bounding box part of the query isn't right.

[jaketangosierra]

I've double-checked all the requirements listed in the comments, and ensured that my geometries were in spherical mercator. When I do this, and manually run the logged query, I get geometries returned. I also log the query at https://github.com/mapzen/TileStache/blob/integration-1/TileStache/Goodies/VecTiles/server.py#L544, which seems to be doing the same thing. If I run it manually, it seems to work properly, but when I load the map (using Leaflet.MapboxVectorTIle), I'm seeing tiles (approximately 31 bytes over the wire) that don't show any geometries. However, when I point to the mapzen end-point, I see plenty of geometries using the same technique.

It seems like there's something in my queries or configuration (or perhaps a non-obvious bug) that is causing these queries to return nothing. I've put my tilestache.cfg below, to make sure there's nothing weird going on here.

{
    "cache": {
        "name": "Test",
        "path": "cache",
        "umask": "0000",
        "dirs": "quadtile",
        "gzip": ["txt", "text", "json", "xml", "topojson", "geojson", "oscimap"]
    },
    "layers": {
        "all": {
          "allowed origin": "*",
          "provider": {
            "class": "TileStache.Goodies.VecTiles:MultiProvider",
            "kwargs": {
              "names": [
                "buildings",
                "pois"
              ],
              "ignore_cached_sublayers": true
            }
          }
        },
        "buildings": {
          "allowed origin": "*",
          "provider": {
            "class": "TileStache.Goodies.VecTiles:Provider",
            "kwargs": {
              "clip": false,
              "dbinfo": {
                "host": "localhost",
                "port": 5432,
                "user": "username",
                "database": "db"
              },
              "queries": [
                null, null, null, null,
                null, null, null, null,
                "queries/buildings-z8.pgsql"
              ],
              "geometry_types": ["Polygon", "MultiPolygon"]
            }
          }
        },
        "pois": {
          "allowed origin": "*",
          "provider": {
            "class": "TileStache.Goodies.VecTiles:Provider",
            "kwargs": {
              "dbinfo": {
                "host": "localhost",
                "port": 5432,
                "user": "username",
                "database": "db"
              },
              "queries": [
                null, null, null, null,
                null, null, null, null,
                "queries/pois-z8.pgsql"
              ],
              "geometry_types": ["Point", "MultiPoint"]
            }
          }
        }
    }
}

[rmarianski]

The config looks right to me. The only thing I can think of is the geometry_types, but those look like they are set appropriately for the layer. You can try to get rid of that part of the config on the off chance that it has an impact.

Otherwise, I'd recommend trying to see if the get_features function actually ends up returning any features. You can try to put in some debug prints or stick a breakpoint in there to see what ends up happening. That can be found here:

https://github.com/mapzen/TileStache/blob/808151317f981c605d8e837aa1eb2dd18036eb97/TileStache/Goodies/VecTiles/server.py#L471-L508

If that returns data for your layers, then the next place I would look is to see if something in the formatter itself is causing it to drop the features.

[bcamper]

@jtsmn can you also try hitting the GeoJSON endpoint (.json) for a sample of your tiles and comparing to the MVT ones? This will help narrow down a possible issue with the MVT formatter. Thanks!

[jaketangosierra]

Alright, so if I print the output from get_features(), I do see features being printed to the console.

However, even hitting a URL that I know just printed features to the console with the GeoJSON endpoint does not work, as I get:

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

Which looks similar to the .mapbox endpoint, although I can't fully read that in the browser debug console.

EDIT: Upon further inspection, my POIs layer is working fine, it's only the buildings layer that's breaking, which suggests there's a disconnect between my query and the renderer. I'll keep digging and report back if I find anything.

[jaketangosierra]

The problem ended up being that the geometry_types declaration was wrong, for my dataset. The OSM data I imported has the ways table as defaulting to LineStrings, not polygons.

Thus, for the buildings layer, I needed it to read:

"geometry_types": ["LineString", "Polygon", "MultiPolygon"]

(or remove the geometry_types key altogether.

I'm going to close this. Sorry for your time being spent around a stupid issue, but I hope this helps someone in the future.

[rmarianski]

No worries, I'm glad that you were able to figure it out.

[bcamper]

Thanks - your pain will hopefully help us to help others ;)