Now Reading
SVG Photographs from Postgres

SVG Photographs from Postgres

2023-05-31 09:48:53

PostGIS excels at storing, manipulating and analyzing
geospatial knowledge. Sooner or later it is normally desired to transform uncooked spatial knowledge
right into a two-dimensional illustration to make the most of the integrative capabilities of
the human visible cortex. In different phrases, to see issues on a map.

PostGIS is a well-liked backend for mapping know-how, so there are a lot of choices
to select from to create maps. Knowledge might be rendered to a raster picture utilizing a
internet map server like GeoServer or
MapServer; it may be transformed to GeoJSON or vector
tiles through servers akin to
pg_featureserv and
pg_tileserv after which shipped to
a Internet browser for rendering by a library akin to
OpenLayers, MapLibre or
Leaflet; or a GIS utility akin to
QGIS can hook up with the database and create richly-styled
maps from spatial queries.

What these choices have in frequent is that they require exterior instruments which want
to be put in, configured and maintained in a separate surroundings. This could
introduce undesirable complexity to a geospatial structure.

This submit presents a easy method to generate maps completely throughout the database,
with no exterior infrastructure required.

SVG for the win

A good way to show vector knowledge is to make use of the Scalable Vector Graphic (SVG)
format. It offers wealthy performance for displaying and styling geometric
shapes. SVG is extensively supported by internet browsers and different instruments.

By together with CSS and Javascript it is potential so as to add superior styling, customized
popups, dynamic behaviour and interplay with different internet web page components.

Introducing pg-svg

Producing SVG “by hand” is troublesome. It requires detailed data of the
SVG specification, and establishing a fancy
textual content format in SQL is extremely error-prone. Whereas PostGIS has had the operate
ST_AsSVG for years, it
solely produces the SVG
path data attribute
worth. Far more is required to create a fully-styled SVG doc.

The PL/pgSQL library pg-svg solves this
downside! It makes it simple to transform PostGIS knowledge into styled SVG paperwork. The
library offers a easy API as a set of PL/pgSQL capabilities which permit
creating an SVG doc in a single SQL question. Better of all, this installs with
a set of capabilities, nothing else required!

Instance map of US excessive factors

One of the simplest ways to grasp how pg-svg works is to see an instance. We’ll create
an SVG map of the US exhibiting the very best level in every state. The map
has the next options:

  • All 50 states are proven, with Alaska and Hawaii remodeled to raised match the
  • States are labeled, and full of a gradient
  • Excessive factors are proven at their location by triangles whose shade and measurement
    point out the peak of the excessive level.
  • Tooltips present extra details about states and highpoints.

The ensuing map seems to be like this (to see tooltips open the
raw image):

The SQL question to generate the map is
It may be downloaded and run utilizing psql:

psql -A -t -o us-highpt.svg  < us-highpt-svg.sql

The SVG output us-highpt.svg might be considered in any internet browser.

The way it Works

Let’s break the question right down to see how the info is ready after which rendered to
SVG. A dataset of US states in geodetic coordinate system (WGS84, SRID = 4326)
is required. We used the Pure Earth states and provinces knowledge out there
It’s loaded right into a desk ne.admin_1_state_prov with the next command:

shp2pgsql -c -D -s 4326 -i -I ne_10m_admin_1_states_provinces.shp ne.admin_1_state_prov | psql

The question makes use of the SQL WITH assemble to arrange processing into easy,
modular steps. We’ll describe each in flip.

Choose US state options

First, the US state options are chosen from the Pure Earth boundaries
desk ne.admin_1_state_prov.

us_state AS (SELECT identify, abbrev, postal, geom
  FROM ne.admin_1_state_prov
  WHERE adm0_a3 = 'USA')

Make a US state map

Subsequent, the map is made extra compact by realigning the far-flung states of Alaska
and Hawaii.
That is achieved utilizing PostGIS
affine transformation functions.
The states are made extra proportionate utilizing
ST_Scale, and moved
nearer to the decrease 48 utilizing
ST_Translate. The
scaling is finished across the location of the state excessive level, to make it simple to
apply the identical transformation to the excessive level characteristic.

,us_map AS (SELECT identify, abbrev, postal,
    -- rework AK and HI to make them match map
    CASE WHEN identify="Alaska" THEN
        ST_Intersection( ST_GeometryN(geom,1), 'SRID=4326;POLYGON ((-141 80, -141 50, -170 50, -170 80, -141 80))'),
        'POINT(0.5 0.75)', 'POINT(-151.007222 63.069444)'::geometry), 18, -17)
    WHEN identify="Hawaii" THEN
        ST_Intersection(geom, 'SRID=4326;POLYGON ((-161 23, -154 23, -154 18, -161 18, -161 23))'),
        'POINT(3 3)', 'POINT(-155.468333 19.821028)'::geometry), 32, 10)
    ELSE geom END AS geom
  FROM us_state)

Excessive Factors of US states

Knowledge for the very best level in every state is supplied as an inline desk of

,high_pt(identify, state, hgt_m, hgt_ft, lon, lat) AS (VALUES
 ('Denali',              'AK', 6198, 20320,  -151.007222,63.069444)
,('Mount Whitney',       'CA', 4421, 14505,  -118.292,36.578583)
,('Britton Hill',        'FL',  105,   345,  -86.281944,30.988333)

Put together Excessive Level symbols

The subsequent question does a number of issues:

  • interprets the lon and lat location for Alaska and Hawaii excessive factors to
    match the transformation utilized to the state geometry
  • computes the symHeight attribute for the peak of the excessive level triangle
  • assigns a fill shade worth to every excessive level based mostly on the peak
  • makes use of ORDER BY to kind the excessive factors by latitude, in order that their symbols
    overlap appropriately when rendered
,highpt_shape AS (SELECT identify, state, hgt_ft,
    -- translate excessive factors to match shifted states
    CASE WHEN state="AK" THEN lon + 18
      WHEN state="HI" THEN lon + 32
      ELSE lon END AS lon,
    CASE WHEN state="AK" THEN lat - 17
      WHEN state="HI" THEN lat + 10
      ELSE lat END AS lat,
    (2.0 * hgt_ft) / 15000.0 + 0.5 AS symHeight,
    CASE WHEN hgt_ft > 14000 THEN '#ffffff'
         WHEN hgt_ft >  7000 THEN '#aaaaaa'
         WHEN hgt_ft >  5000 THEN '#ff8800'
         WHEN hgt_ft >  2000 THEN '#ffff44'
         WHEN hgt_ft >  1000 THEN '#aaffaa'
                             ELSE '#558800'
    END AS clr
  FROM high_pt ORDER BY lat DESC)

Generate SVG components

The earlier queries remodeled the uncooked knowledge right into a kind appropriate for
Now we get to see pg-svg in motion! The subsequent question generates the SVG textual content for
every output picture aspect, as separate information in a end result set known as shapes.

The SVG components are generated within the order by which they’re drawn – states and
labels first, with high-point symbols on high. Let’s break it down:

SVG for states

The primary subquery produces SVG shapes from the state geometries. The
svgShape operate produces an SVG
form aspect for any PostGIS geometry. It additionally offers elective parameters
supporting different capabilities of SVG. Right here title specifies that the state identify
is displayed as a tooltip, and fashion specifies the styling of the form.
Styling in SVG might be supplied utilizing properties outlined within the
Cascaded Style Sheets (CSS)
specification. pg-svg offers the
svgStyle operate to make it simple
to specify the names and values of CSS styling properties.

Word that the fill property worth is a URL as an alternative of a shade specifier. This
refers to an SVG gradient fill which is outlined later.

The state geometry can be included within the subquery end result set, for causes
which will likely be mentioned beneath.

,shapes AS (
  -- State shapes
  SELECT geom, svgShape( geom,
    title => identify,
    fashion => svgStyle(  'stroke', '#ffffff',
                        'stroke-width', 0.1::textual content,
                        'fill', 'url(#state)',
                        'stroke-linejoin', 'spherical' ) )
    svg FROM us_map

SVG for state labels

Labels for state abbreviations are positioned on the level produced by the
ST_PointOnSurface operate. (Alternatively, ST_MaximumInscribedCircle may
be used.) The SVG is generated by the
svgText operate, utilizing the
specified styling.

See Also

  -- State names
  SELECT NULL, svgText( ST_PointOnSurface( geom ), abbrev,
    fashion => svgStyle(  'fill', '#6666ff', 'text-anchor', 'center', 'font', '0.8px sans-serif' ) )
    svg FROM us_map

SVG for top level symbols

The excessive level options are displayed as triangular symbols. We use the
handy svgPolygon operate
with a easy array of ordinates specifying a triangle based mostly on the excessive level
location, with peak given by the beforehand computed svgHeight column. The
title is supplied for a tooltip, and the styling makes use of the computed clr
attribute because the fill.

  -- Excessive level triangles
  SELECT NULL, svgPolygon( ARRAY[ lon-0.5, -lat, lon+0.5, -lat, lon, -lat-symHeight ],
    title => identify || ' ' || state || ' - ' || hgt_ft || ' ft',
    fashion => svgStyle(  'stroke', '#000000',
                        'stroke-width', 0.1::textual content,
                        'fill', clr  ) )
    svg FROM highpt_shape

Produce closing SVG picture

The generated form components should be wrapped in an <svg> doc aspect.
That is dealt with by the svgDoc

The viewable extent of the SVG knowledge must be supplied by the viewbox
parameter. The commonest case is to show all the rendered knowledge. A simple
method to decide that is to use the PostGIS ST_Exrtent mixture operate to
the enter knowledge (this is the reason we included the geom column in addition to the svg
textual content column). We are able to embody a border by enlarging the extent utilizing the
ST_Expand operate. The operate
svgViewBox converts the PostGIS
geometry for the extent into SVG format.

We additionally embody a definition for an SVG
linear gradient
for use because the fill fashion for the state options.

SELECT svgDoc( array_agg( svg ),
    viewbox => svgViewbox( ST_Expand( ST_Extent(geom), 2)),
    def => svgLinearGradient('state', '#8080ff', '#c0c0ff')
  ) AS svg FROM shapes;

The output from svgDoc is a textual content worth which can be utilized wherever that SVG
is supported.

Extra to Discover

We have proven how the pg-svg SQL operate library permits you to simply generate map
photographs from PostGIS knowledge proper within the database. This can be utilized as a easy
ad-hoc means of visualizing spatial knowledge. Or, it could possibly be embedded in a bigger
system to automate repetitive map era workflows.

Though SVG is a pure match for vector knowledge, there could also be conditions the place
producing a map as a bitmap (raster) picture is smart.
For a means of producing raster maps proper within the database see this PostGIS Day
2022 presentation. This is able to be
particularly interesting the place the map is displaying knowledge saved utilizing
PostGIS raster data.
It could even be potential to mix vector and raster knowledge right into a hybrid
SVG/picture output.

Though we have focussed on creating maps of geospatial knowledge, SVG is commonly used
for creating other forms of graphics. For examples of utilizing it to create
geometric and mathematical designs see the pg-svg
demo folder. This is an
picture of a Lissajous knot generated by
this SQL.

Lissajous Knot

You could possibly even use pg-svg to generate charts of non-spatial knowledge (though
this is able to be higher dealt with by a extra task-specific API).

Tell us in case you discover pg-svg helpful, or in case you have concepts for bettering it!

Source Link

What's Your Reaction?
In Love
Not Sure
View Comments (0)

Leave a Reply

Your email address will not be published.

2022 Blinking Robots.
WordPress by Doejo

Scroll To Top