SVG Photographs from Postgres
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
map - 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
here.
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
here.
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_Translate(ST_Scale(
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_Translate(ST_Scale(
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
values:
,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
andlat
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
image - 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
rendering.
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.
UNION ALL
-- 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.
UNION ALL
-- 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
operate.
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.
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!