How to render an SVG from PostGIS using ST_AsSVG

Question

Speaking with a cartographer from National Geographic last night, my interest was again piqued (peaked?) in an open-source map publishing toolset built on PostGIS and Inkscape (as per his workflow and how others use Illustrator with Avenza Map Publisher using ArcGIS data)

With the PostGIS ST_AsSVG command, I'm not sure how to work with the output as an SVG file. I can see in other discussions that SVG's can be exported somehow, but I'm not sure how that works exactly.

With that said, assuming it's perhaps just a matter of orienting the command to export a static SVG file - would/could it even be possible for Inkscape to read an SVG directly out of PostGIS?

Answer 1

The Postgis function ST_AsSVG simply converts geometries into their SVG point or path equivalents, and does not allow you to add any attributes/styling directly. For example, a query with a Point, Linestring and Polygon:

WITH shapes (geom, attribute) AS 
   (VALUES(
      (SELECT ST_MakePoint(0,0)),1),
      ((ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(10,10))), 2), 
      ((SELECT ST_MakeBox2d(ST_MakePoint(0,0), ST_MakePoint(10,10))), 3)
   )
SELECT ST_AsSVG(geom) from shapes;

returns:

 cx="0" cy="0"
 M 0 0 L 10 -10
 M 0 0 L 0 -10 10 -10 10 0 Z

which is a point, an unclosed linestring and a closed line, ie, a polygon. M means move to, L means line to and Z means close. There are other geometry types in SVG, but this is what ST_AsSVG returns (see docs for the Multi version of the same).

By itself, this isn't very helpful, as you are missing the path tags and any styling information, a full example of which would look something like:

 <path d="M 175 200 l 150 0" stroke="green" stroke-width="3" fill="none" />
 <path d="M 100 350 q 150 -300 300 0" stroke="blue" stroke-width="5" fill="none" />

See w3schools SVG Tutorial for more information.

So, if you want to actually create valid SVG using a Postgis query, you will need to add path tags and color, line, fill, etc formatting. This can be accomplished by using the concat operator and a case statement to set the formatting. For example,

WITH shapes (geom, attribute) AS (
  VALUES(
    (SELECT ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(10,10))), 2),
    ((SELECT ST_Envelope(ST_MakeBox2d(ST_MakePoint(0,0), ST_MakePoint(10,10)))), 3)
  ) 
  SELECT 
      concat(
         '<path d= "', 
         ST_AsSVG(geom,1), '" ', 
         CASE WHEN attribute = 0 THEN 'stroke="red" stroke-width="3" fill="none"' 
         ELSE 'stroke="black" stroke-width="2" fill="green"' END,
         ' />') 
 FROM shapes;

which returns:

<path d= "M 0 0 l 10 -10" stroke="black" stroke-width="2" fill="green" />
<path d= "M 0 0 l 0 -10 10 0 0 10 z" stroke="black" stroke-width="2" fill="green" />

Finally, if you wrap all of that in array_to_string and array_agg, and add the head and footer, ie, the <svg height="400" width="450"> and </svg>, you will end up with one block of SVG representing all your geometries, eg,

WITH shapes (geom, attribute) AS (
  VALUES(
    (SELECT ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(10,10))), 2),
    ((SELECT ST_Envelope(ST_MakeBox2d(ST_MakePoint(0,0), st_makepoint(10,10)))), 3)
  ),
  paths (svg) as (
     SELECT concat(
         '<path d= "', 
         ST_AsSVG(geom,1), '" ', 
         CASE WHEN attribute = 0 THEN 'stroke="red" stroke-width="3" fill="none"' 
         ELSE 'stroke="black" stroke-width="2" fill="green"' END,
          ' />') 
     FROM shapes
 )
 SELECT concat(
         '<svg height="400" width="450">',
         array_to_string(array_agg(svg),''),
         '</svg>')
 FROM paths;

which now returns the single record:

<svg height="400" width="450"><path d= "M 0 0 l 10 -10" stroke="black" stroke-width="2" fill="green" /><path d= "M 0 0 l 0 -10 10 0 0 10 z" stroke="black" stroke-width="2" fill="green" /></svg>

TL;DR, other options

At which point you are probably thinking, TL;DR, and it is true this is a lot of work, and the case statements to make the stroke color, width, etc, could get very long and unwieldy.

There are other options. Geoserver (with a Postgis/vector data source) supports SVG as an output format and you can use SLDs or CartoCSS within Geoserver to style them up, then just request a WMS with SVG as output format and you are done. As @Scro also said, you can use tools like TileMill to do the styling for you (not tested). So, after a very long answer, I am basically suggesting that you should not use ST_AsSVG directly for anything other than very quick and dirty SVGs.

Answer 2

If you are open to exporting data from within QGIS, rather than from PostGIS, you could use the Simple SVG plugin. This plugin will export the active layers from within QGIS, attempting to maintain the symbology. I have uses this with some relatively good success. Additionally, you can use QGIS composer to start your maps and use their SVG export function. This is not the same as the PostGIS query, but if the goal is to get data from QGIS to Inkscape, then this will work.

Answer 3

Now another option for rendering SVG from PostGIS data natively in the database is to use pg-svg. This is a library of PostgreSQL functions which supports converting all PostGIS geometry to SVG shapes. It also allows adding styling and producing full SVG documents.

Answer 4

Through some very clumsy fumbling, I stumbled upon a more complete ( / generalized ) solution that I hope might be helpful to fellow travelers on the Google / Stack Exchange hunt.

WITH
    query AS (SELECT wkb_geometry AS geometry FROM districts ),
    q AS (SELECT
        ST_XMin(ST_Collect(geometry)) as x_min,
        ST_XMax(ST_Collect(geometry)) as x_max,
        ST_YMin(ST_Collect(geometry)) as y_min,
        ST_YMax(ST_Collect(geometry)) as y_max,
        ARRAY_TO_STRING(ARRAY_AGG(CONCAT('<path d="', ST_AsSVG(geometry), '" ', 'fill="green"', ' />')),'') as svg FROM query )
SELECT
    CONCAT('<svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" height="400" width="400" viewBox="',
        CONCAT_WS(' ', q.x_min, -1 * q.y_max, q.x_max-q.x_min, q.y_max-q.y_min), '">', q.svg, '</svg>') FROM q

Drop your query inside of that first query AS ( .... ) CTE, and Bob's your uncle.

Answer 5

Here's the trouble with rendering SVGs from geospatial data: decimal places. @bahoo's answer got me most of the way there, but the features that would show on the screen were rounded to the nearest 100th, which looked TERRIBLE.

These steps work for showing one feature in isolation, and not on a map, because the coordinates will be heavily changed in the process.

There are 3 steps:

• Move the geometry to the canvas origin (0, 0)
• Scale the geometry up
• Round the decimals to the nearest 0.001 (optional)

    WITH polygons AS (
        SELECT
            polygon_features.geom as "geometry",
            ST_Scale(ST_Translate(polygon_features.geom, -1 * ST_XMin(ST_Collect(polygon_features.geom)), -1 * ST_YMin(ST_Collect(polygon_features.geom))), 5000, 5000) as "geom"
        FROM polygon_features
        GROUP BY polygon_features.geom
    ),
    q as (
        SELECT
            ST_AsText(polygons.geometry) as geom,
            ST_XMin(ST_Collect(polygons.geom)) as x_min,
            ST_XMax(ST_Collect(polygons.geom)) as x_max,
            ST_YMin(ST_Collect(polygons.geom)) as y_min,
            ST_YMax(ST_Collect(polygons.geom)) as y_max,
            ARRAY_TO_STRING(ARRAY_AGG(CONCAT('<path d="', ST_AsSVG(polygons.geom), '" ', 'fill="green"', ' />')),'') as svg
        FROM polygons
        GROUP BY geometry
    )
    SELECT
        CONCAT('<svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" height="500" width="500" viewBox="',
            CONCAT_WS(' ', q.x_min, -1 * q.y_max, q.x_max-q.x_min, q.y_max-q.y_min), '">', q.svg, '</svg>') as "svg",
        q.geom as "geom"
    FROM q

I've left the WKT there as geom with svg so you can see that side-by-side.