When using a custom function, the SQL Server must be notified that the function is safe to use. This can be done by including the following in your Qlik load script. CLR can be enabled system wide by your database administrator or, at the beginning of a script and disabled at the end of the script.

To enable:

SQL EXEC sp_configure 'clr enabled' , '1';
SQL RECONFIGURE;

To disable at the end of your script:

SQL EXEC sp_configure 'clr enabled' , '0';
SQL RECONFIGURE;

The polyline encoded converter can be used inside any SQL command run on the MS SQL Server, regardless of whether it is started from the Qlik Script or the SQL Server.

For example, the following code is executed inside the QlikView Script Editor.

The function:

1. Reads the geometry field 'geom' from the source .shp file stored in the qm_shapes database and converts it to polyline encoded format using the encodedpolylinefunction() function.

2. Finds the center lat and center lng of each boundary being created using native MS SQL functions.

SQL select
  cluster,
  qm_shapes.dbo.encodepolyline(geom, 1, 0, 0, 1) as clusterBoundary,
  geom.STCentroid().STY as clusterCenterLat,
  geom.STCentroid().STX as clusterCenterLng
from qm_shapes.dbo."Clusters_FY17";

The EncodedPolyline() function has the following parameters:

EncodedPolyline( Geometry, Simplify Feature, Simplify Branches, Prune Small Branches, Fill Holes)

1. The Geometry is a field in the .shp file that represents the geographical boundaries of the polygon. The geometry field is typically called 'geom' but can be different depending on the source of the data.

2. The Simplify Feature parameter allows for simplification of the entire feature. '0' is off. '1' is standard. <1 is less aggressive simplification. >1 is more aggressive simplification.

3. The Simplify Branches parameter allows for simplification of all the parts of a feature individually. '0' is off. '1' is standard. <1 is less aggressive simplification. >1 is more aggressive simplification.

4. The Prune Small Branches parameter allows for removing small portions of a polygon. (Think Islands.) '0' is off. '1' is standard. <1 is less aggressive simplification. >1 is more aggressive simplification.

5. The Fill Holes parameter allows for filling holes in polygons. (Think large lakes or reservations.) '1' is on. '0' is off.