Configuring Local Dataset (using a VRT file)

This article is only relevant for Pozi Enterprise clients who are hosting datasets within their own network for internal use only.

Introduction

Datasets which are hosted internally are usually made available to Pozi as shape files, tab files, or directly out of an internal database such as SQL Server.

While it is common for Pozi to directly apply styling to these layers within the site configuration, it is also possible for the client to have some control over the styling through the use of VRT files.

In essence, the VRT file reads the dataset and applies some styling, and Pozi reads from the VRT file (rather than from the underlying dataset).

The VRT File

In its simplest form, a VRT file will look something like this:

<OGRVRTDataSource> 
  <OGRVRTLayer name="StreetTrees"> 
    <SrcDataSource>MSSQL:server=GISServer;database=GISDatabase</SrcDataSource> 
    <SrcLayer>vw_ASSET_StreetTrees</SrcLayer> 
  </OGRVRTLayer> 
</OGRVRTDataSource>

In this example, the VRT file is reading a database view directly from a SQL Server database. It is not modifying anything so in this case the output from the VRT file will be the same as the original dataset.

In the next example there is a <SrcSQL> tag with a select statement, which selects all the attributes from the dataset pozi.assetic_trees, but also adds a new attribute which it has called "marker-color", and which has been assigned the value #00FF00.

The outcome is that when Pozi reads this VRT file it will recognise the attribute "marker-color" and will be able to use it for styling.

<OGRVRTDataSource>
  <OGRVRTWarpedLayer>
    <OGRVRTLayer name="assetic_trees">
      <SrcDataSource>MSSQL:server=GISServer;database=GISServer</SrcDataSource>
      <SrcSQL dialect="sqlite">
        select
          *,
          '#00FF00' as "marker-color"
        from pozi.assetic_trees
      </SrcSQL>
    </OGRVRTLayer>
    <TargetSRS>EPSG:4326</TargetSRS>
  </OGRVRTWarpedLayer>
</OGRVRTDataSource>

The net outcome in this example is that the client has been able to specify the marker colour of a point dataset, and has also specified the output projection (EPSG:4326). This was possible without needing to contact Groundtruth.

A more complex usage of styling using a CASE statement:

<OGRVRTDataSource>
     <OGRVRTLayer name="assetic_trees">
       <SrcDataSource>MSSQL:server=GISServer;database=GISServer</SrcDataSource>
       <SrcSQL dialect="sqlite">
         select           *,
	  CASE
		WHEN [radius] < 103 THEN '#0000ff'
		WHEN [radius] < 154 and [radius] > 102 THEN '#d7191c'
		WHEN [radius] < 237 and [radius] > 153 THEN '#fec981'
		WHEN [radius] < 343 and [radius] > 236 THEN '#c4e687'
		ELSE '#1a9641'
	END as "marker-color",
        from pozi.assetic_trees
       </SrcSQL>
     </OGRVRTLayer>
 </OGRVRTDataSource>

The net outcome in this example is that the client has been able to specify a variable marker colour based on the value of the attribute "radius". Note that because this is embedded SQL you cannot use the Greater Than and Less Than symbols, but rather you must use escape codes. So when the above code was typed, the '<' symbol was actually type as < (it has been translated back to the Less Than symbol by the page formatter). See below under "Usage of Special Characters" for a broader explanation.

Styling Options

This is a list of supported field names for vector styling:

Point style

"marker-color": eg #0000FF (default)
"marker-size": eg 1,2,3,etc,small,large
"marker-symbol": circle (default)|square|diamond|triangle|hexagon|star|heptagon
"marker-opacity": eg 0.25 (default)

Line style

"stroke": eg #0000FF (default)
"stroke-opacity": eg 1 (default)
"stroke-width": eg 2 (default)

Polygon style

"stroke": eg #0000FF (default)
"stroke-opacity": eg 1 (default)
"stroke-width": eg 2 (default)
"fill": eg #0000FF (default)
"fill-opacity": eg 0.25 (default)

Note: field aliases such as "fill-opacity" should be enclosed in double quotes (") to avoid syntax errors, especially for field names containing dashes.

Usage of Special Characters

Sometimes fields will have special characters in them, eg. '&' (ampersand), which will need to be escaped when embedded in SQL.

First find the ASCII code for the special character, in the case of the ampersand the ASCII code is 38, then replace the special character with the following:

&

The & is the escape character (so is always &), the #38 is the ASCII code for the special character (in this case ampersand), and the ; is the terminator.

Other examples are < for the less than symbol (<), and > for the greater than symbol (>).

See https://theasciicode.com.ar/ for a complete list of codes.

Note that some codes have a corresponding name which can be used instead, eg. Less Than can be escaped as < instead of &#60. This can sometimes make the escape coding more readable. See https://www.freeformatter.com/html-entities.html for a mapping of codes to names.

The need to escape special characters will usually only be necessary when referencing attribute values within a CASE statement in the SQL.

Spatial Operations

The source dataset's geometry can be queried and manipulated using Spatialite operators. See https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html for a list of available operators.

<SrcSql dialect="sqlite">
    select
        ass_num_char,
        ownername as "label",
        ST_PointOnSurface(geometry) as geometry
    from "propertymp_ratepayerowner"
</SrcSql>

Summary

The fundamental idea behind using VRT files is to provide the client with more direct control over the way their internal datasets are exposed to Pozi.

Footnote: Although this article has focused on using VRT files to provide some styling control for Pozi clients, VRT files can also be used more extensively to create dynamically manipulated dataset combinations such as, for example, to extract data from multiple database tables via table joins constructed within the VRT file.

For more information about the VRT file format, see https://www.gdal.org/drv_vrt.html.