Displaying raster tiles from OSM data stored in a postgis server

Question

I have been recently tasked with setting up a raster tile server that serves tiles from data pulled from a postgis store.

I have managed to get a vector tile server up and running but I'm stuck on the raster tile server bit. This is what the raw data looks like (an example row, it's basically a list of all the streets in Switzerland):

{ id: 1537021,
  osm_id: '317400638',
  osm_name: null,
  osm_meta: null,
  osm_source_id: '3236568222',
  osm_target_id: '3236568210',
  clazz: 72,
  flags: 6,
  source: 1216745,
  target: 1216746,
  km: 0.048946153,
  kmh: 10,
  cost: 0.004894615,
  reverse_cost: 0.004894615,
  x1: 8.1708598,
  y1: 47.4290024,
  x2: 8.1707774,
  y2: 47.4285814,
  geom_way: '0102000020E610000003000000F7668AEF7A572040E8F3F68CE9B64740AEC2C1397E572040DBAC5516E0B64740B5CAA7227057204042565CC1DBB64740' }

What would be the best approach to generate raster tiles given this data?

I managed to cook up vector geoJSON data via the following:

SELECT osm_name As properties, ST_ASGEOJSON(geom_way) as geometry 
FROM ch_2po_4pgr 
WHERE ST_Intersects(ch_2po_4pgr.geom_way, 
ST_SetSRID(ST_MakeBox2D(ST_Point($1, $2), ST_Point($3, $4)), 4326))

Most tutorials seem to deal with using Mapnik and a shapefile datasource but what I have is this postgis table :(

Answer 1

In your case I would recommend to set up a WMS service using mapserver or something equivalent, and then requesting tiles from the WMS endpoint.

http://mapserver.org/ogc/wms_server.html

This is a very flexible approach, as on the backend you do not need to worry about the exact TMS tiles definitions (indices and bboxes etc). Instead, let the frontend client worry about what part of your data belongs to each tile. For instance, leaflet can show a WMS layer directly:

http://leafletjs.com/reference.html#tilelayer-wms

The limitation of this approach are geometries at the edge, but that relates mainly to points (the point symbology of a point near the edge of a tile might be cut off, as it only gets drawn on the tile that contains the point). A singletile WMS layer could help in that case, see:

https://github.com/Leaflet/Leaflet/issues/558#issuecomment-61626718