Disolve and Convert - An Example
When developing this process and writing documentation, I worked through an example. The document below was the process I went through to create custom polygons that were an aggregate of zip codes while also loosely documenting the process for later moving in to the Knowledge Base.
All steps in this are just set as an example and should not be used as documentation. Instead, refer to other articles in the KB under MS SQL Server.
Process
1. Install SQL Server 2014 express with tools
2. Install DLL. Documentation on what we are doing can be found here but it may be easier to simply execute the code below on the database you want to install the .dll.
- Select Database-->New Query and run the code below. You will need to update the location of the downloaded encodedPolyline.dll
CREATE ASSEMBLY QlikMaps
from 'C:\Users\pvinton\Documents\Analytics8\QlikMaps\SQL Server EP Builder\ClassLibrary2\bin\Debug\encodedPolyline.dll'
WITH PERMISSION_SET = SAFE;
CREATE FUNCTION EncodePolyline(@x [geometry], @SimplifyFeature [float], @SimplifyBranches [float], @PruneSmallBranches [float], @FillHoles [bit])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME QlikMaps.UserDefinedFunctions.EncodePolyline;3. Import .shp files using ogr2ogr and executing the script from your windows command window.
Shapes can be imported many different ways. ogr2ogr is one method. If you have QGIS installed, ogr2ogr is already installed. If not, I recommend installing QGIS anyway since it's a great viewer while working to build custom shapes. To make the following script work:
- Change the folder location of ogr2ogr. It may be in your QGIS bin folder. Here it is C:\Program Files\PostgreSQL\9.5\bin\ogr2ogr.exe
- Change your server name. Here is is localhost\SQLEXPRESS
- Change the name of your database. Here it is shp_northamerica
- Change the name and location of your .shp file. Here it is c:\Users\Trey\Desktop\Shp Files\State\Unsimplified\Source\State.shp
- provide the schema and name of you want the table to be. Here it is unitedstates.unsimplified_state
"C:\Program Files\PostgreSQL\9.5\bin\ogr2ogr.exe" -overwrite -f MSSQLSpatial "MSSQL:server=localhost\SQLEXPRESS;database=shp_northamerica;trusted_connection=yes" -t_srs EPSG:4326 -lco "GEOM_NAME=geom" "c:\Users\Trey\Desktop\Shp Files\Zip\Unsimplified\Source\Zip.shp" -nln unitedstates.unsimplified_zip4. Import mapping file. Great instructions are here.
5. In order to run the EncodedPolyline .dll procedure you must Enable the execution of user code in the .Net framework. See here. I simply turn it on during the load.
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;And then turn it off after at the end of my scipt
EXEC sp_configure 'clr enabled' , '0';
RECONFIGURE;
6. To put it together the following script:
- enables clr execution.
SQL EXEC sp_configure 'clr enabled' , '1';
SQL RECONFIGURE;- Joins and dissolves the loaded files
SQL select cluster,
geometry::UnionAggregate(geom) as geom
into #tt1
from qm_shapes.dbo."CHEP_Clusters_FY17" c
inner join qm_shapes.dbo.unsimplified_zip z
on (c.Zip = z.zip)
group by cluster;- Converts them to a QlikMaps format using the loaded .dll
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
#tt1;- disables clr execution
EXEC sp_configure 'clr enabled' , '0';
RECONFIGURE;
Put the pieces together for the following
SQL EXEC sp_configure 'clr enabled' , '1';
SQL RECONFIGURE;
SQL select cluster,
geometry::UnionAggregate(geom) as geom
into #tt1
from qm_shapes.dbo."CHEP_Clusters_FY17" c
inner join qm_shapes.dbo.unsimplified_zip z
on (c.Zip = z.zip)
group by cluster;
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
#tt1;
SQL EXEC sp_configure 'clr enabled' , '0';
SQL RECONFIGURE;