topic Re: Qlik Polygon Format in GeoAnalytics

Qlik Polygon Format

dhogan — Wed, 29 Jan 2025 15:35:54 GMT

Looking through this article - https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector-geoanalytics_reference-Dataset.htm

I see that I can pass polygon data to qlik for it to display. So out of SQL server I am using geometry.STAsText() to get something that looks like this: POLYGON ((lat long, lat long, lat long, lat long), (lat long, lat long, lat long, lat long))

Yet when I pass it to qlik and put it in the map dimension qlik yells at me that it doesn't know what I just gave it. According to the article above this format seems like it would work but since the format/layout details are lacking and there is no complete example I'm not sure what qlik is expecting. Can anyone help me out?

Re: Qlik Polygon Format

dhogan — Fri, 04 Jun 2021 17:50:39 GMT

In case this is helpful, here is the entire polygon string which qlik says cannot be found

POLYGON ((-78.74939599999999 38.206648, -78.655273 38.260427, -78.663145 38.277933, -78.368743 38.184073999999995, -78.341674 38.176898, -78.209384 38.131128, -78.25398899999999 38.062813, -78.306762999999989 38.006474, -78.491332 37.796971, -78.497714 37.7631, -78.643918 37.733084, -78.66886199999999 37.724751999999995, -78.682986 37.78206, -78.83887399999999 38.04737, -78.780879 38.081029, -78.74939599999999 38.206648), (-78.519333 38.010678, -78.46368 38.020451, -78.448796 38.058447, -78.466129999999993 38.045700000000004, -78.49503399999999 38.065337, -78.519333 38.010678))

I tested it at the link below and it works perfectly so it must be a formatting issue I'm not accounting for in what qlik expects.

https://clydedacruz.github.io/openstreetmap-wkt-playground/

I also tried putting it into the format mentioned on the qlik page which also did not work:

[[[-78.74939599999999,38.206648],[-78.655273,38.260427],[-78.663145,38.277933],[-78.368743,38.184073999999995],[-78.341674,38.176898],[-78.209384,38.131128],[-78.25398899999999,38.062813],[-78.306762999999989,38.006474],[-78.491332,37.796971],[-78.497714,37.7631],[-78.643918,37.733084],[-78.66886199999999,37.724751999999995],[-78.682986,37.78206],[-78.83887399999999,38.04737],[-78.780879,38.081029],[-78.74939599999999,38.206648]],[[-78.519333,38.010678],[-78.46368,38.020451],[-78.448796,38.058447],[-78.466129999999993,38.045700000000004],[-78.49503399999999,38.065337],[-78.519333,38.010678]]]

Re: Qlik Polygon Format

Patric_Nordstrom — Fri, 04 Jun 2021 20:04:53 GMT

STAsText() returns WKT which GeoOperations can read using the load() operation using intable as dataset.

Here's an example to read, first load to an inline table, then load with GeoOperations. The geometry field is renamed so we get a new field with Sense geoemtries.

LIB CONNECT TO 'Google_BigQuery_bps'; [sub]: SELECT `borough_name`, `borough_geom` FROM geo.sub; poly: Load * Extension GeoOperations.ScriptEval( 'Select borough_name, borough_geom as borough_poly from Load() ', sub);

Re: Qlik Polygon Format

dhogan — Wed, 09 Jun 2021 16:37:05 GMT

Thank you for the response. I tried this and got the following error:

'GeoOpeations.ScriptEval is not a registered SSE function'

How do I resolve this?

Re: Qlik Polygon Format

Patric_Nordstrom — Thu, 10 Jun 2021 08:55:16 GMT

Sounds like a license issue, what environment are you running?

GeoAnalytics is included for Qlik Enterprise customers on Qlik SaaS.

Thanks,

Patric

Re: Qlik Polygon Format

dhogan — Thu, 10 Jun 2021 15:46:05 GMT

My system admin reports the following:

I have setup the GeoExtensions that you should now see in Qlik
https://help.qlik.com/en-US/geoanalytics/Content/Qlik_GeoAnalytics/Home.htm
I also installed the GeoAnalyticsPlus connector for the dataset that is supposed to allow pulling from a local database. This uses Java and I have installed the Java SQL Server drivers.
https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoConnector/Content/connector/connector-geoanalytics_reference-Dataset.htm

However, when I re-run the data load editor with the code you provided previously I get the same exact error. I am really surprised at how difficult Qlik makes it to draw a simple polygon...

Re: Qlik Polygon Format

Patric_Nordstrom — Fri, 11 Jun 2021 07:02:33 GMT

Ok, my bad I thought you were running Qlik SaaS. GeoOperations is only available there.

For Qlik Sense Windows use the Qlik GeoAnalytics Plus connector, here's how to connect to a local db:

https://help.qlik.com/en-US/geoanalytics/Content/Qlik_GeoAnalytics/GeoAnalytics-Connector/GeoAnalytics-Plus-connect-to-database.htm

Also attaching a mini tutorial loading and reading geo data from MS SQL Server.

Thanks,

Patric

Re: Qlik Polygon Format

dhogan — Fri, 11 Jun 2021 15:48:41 GMT

I appreciate the help but I'm still lost. I'm not in Qlik Windows. I'm in Qlik Sense Enterprise so this is all done through a browser. Further, the instructions: "In Qlik open the GeoAnalytics ‘Select Data’." is oh so unhelpful. I've found this a problem with many of your help sections. It assumes the user (me in this case) knows how to get to the place your instructions start from which is a poor assumption for instructions to make. Open GeoAnalytics where? I can't open any GeoAnalytics object, I can only drag them on to a sheet as I describe below.

In Qlik Sense Enterprise Edit Sheet I have 8 GeoAnalytics options but they don't seem to operate like a normal Qlik object. I add the GeoAnalytics Map but it doesn't have any dimension or field options to add to it. I can add an GeoAnalytics Area Layer but that also doesn't have dimension or field options and by default its just showing a random polygon with no map. All I want to do is get a polygon put on a map...how do I get that done? We are a Qlik Partner, can't someone set up a call to walk through this with me? The back and forth via this forum doesn't seem to be moving us forward.

Re: Qlik Polygon Format

Patric_Nordstrom — Mon, 14 Jun 2021 08:01:32 GMT

To get started with GeoAnalytics I can recommend https://community.qlik.com/t5/GeoAnalytics/New-to-GeoAnalytics-Read-this-first/td-p/1565699

The minimum steps to load data from ms sql server

Open Data Load Editor
Create Data connection... "Qlik GeoAnalytics Connector"
Choose "GeoAnalytics Plus"
Click "Select data"
Fill in the form

To render the data:

Add a map chart
Add an area layer, set location to the Geometry from the query

Thanks,

Patric

Re: Qlik Polygon Format

dhogan — Thu, 22 Jul 2021 17:39:23 GMT

So we've been stuck on your instructions.

Open Data Load Editor - YUP
Create Data connection... "Qlik GeoAnalytics Connector" - YUP
Choose "GeoAnalytics Plus" - YUP
Click "Select data" - no such thing, 'Create Connection - GeoAnalytics Connector' has two options: 1) GeoAnalytics server (ours is GeoAnalytics Plus and is shown in the drop down); 2) Connection Name. When we test connection we get "Could not contact the license service. Please contact support"
Fill in the form

So who in 'support' should I be contacting??

Re: Qlik Polygon Format

Patric_Nordstrom — Fri, 23 Jul 2021 09:47:51 GMT

The recommendation is to restart the sense server after installing GeoAnalytics Plus, that should solve the issue.

Thanks,

Patric

Re: Qlik Polygon Format

dhogan — Fri, 23 Jul 2021 16:06:41 GMT

response to that from our system admin: "yea i did that multiple times. after making sure the license was linked...."

what next....

Re: Qlik Polygon Format

Patric_Nordstrom — Sat, 24 Jul 2021 14:29:31 GMT

The Sense server needs internet access, at least to https://*.qlikcloud.com.

If still no connection the following may need to added to IdevioGeoAnalyticsConnector.exe.config

at C:\Program Files\Common Files\Qlik\Custom Data\QvIdevioConnector

Thanks,

Patric

Re: Qlik Polygon Format

dhogan — Mon, 26 Jul 2021 18:32:16 GMT

Alright we have the connector available but now we move on to another problem (see load and error below).

Any thoughts on why this error is occuring?

LOAD fips,

name,

poly,poly2;

[QLIKGeo]:

SELECT fips,

name,

poly,poly2

FROM dh.QLIKGeo;

LIB CONNECT TO 'QGA';

/* Generated by GeoAnalytics for operation Load ---------------------- */

[_inlineMap_]:

mapping LOAD * inline [

_char_, _utf_

"'", '\u0027'

'"', '\u0022'

"[", '\u005b'

"/", '\u002f'

"*", '\u002a'

";", '\u003b'

"}", '\u007d'

"{", '\u007b'

"`", '\u0060'

"´", '\u00b4'

" ", '\u0009'

];

IF FieldNumber('fips', 'QLIKGeo') = 0 THEN

call InvalidInlineData('The field fips in QLIKGeo is not available');

END IF

IF FieldNumber('name', 'QLIKGeo') = 0 THEN

call InvalidInlineData('The field name in QLIKGeo is not available');

END IF

IF FieldNumber('poly2', 'QLIKGeo') = 0 THEN

call InvalidInlineData('The field poly in QLIKGeo is not available');

END IF

Let [TESTGEOInlineTable] = 'fips' & Chr(9) & 'name' & Chr(9) & 'poly2';

Let numRows = NoOfRows('QLIKGeo');

Let chunkSize = 1000;

Let chunks = numRows/chunkSize;

For n = 0 to chunks

Let chunkText = '';

Let chunk = n*chunkSize;

For i = 0 To chunkSize-1

Let row = '';

Let rowNr = chunk+i;

Exit for when rowNr >= numRows;

For Each f In 'fips', 'name', 'poly2'

row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'QLIKGeo'));

chunkText = chunkText & Chr(10) & Mid('$(row)', 2);

[TESTGEOInlineTable] = [TESTGEOInlineTable] & chunkText;

chunkText=''

[TESTGEO]:

SQL SELECT [fips], [name], [poly] FROM Load(dataset='TESTGEO')

DATASOURCE TESTGEO INLINE tableName='QLIKGeo', tableFields='fips,name,poly2', geometryType='WKT', loadDistinct='NO', suffix='', crs='Auto' {$(TESTGEOInlineTable)}

;

tag field [fips] with '$primarykey';

[TESTGEOInlineTable] = '';

/* End GeoAnalytics operation Load ----------------------------------- */

Started loading data

Connected

Connected QLIKGeo << QueryResult Lines fetched: 1

Connected _inlineMap_ << ad5826a7-b157-47fd-8e19-e196eda36853 Lines fetched: 11

The following error occurred:

Connector reply error: QVX_SYNTAX_ERROR: Failed to process query: Failed to create dataset TESTGEO: org.locationtech.jts.io.ParseException: Expected word but found End-of-Stream (line 1)

The error occurred here:

[TESTGEO]: SQL SELECT [fips], [name], [poly] FROM Load(dataset='TESTGEO') DATASOURCE TESTGEO INLINE tableName='QLIKGeo', tableFields='fips,name,poly2', geometryType='WKT', loadDistinct='NO', suffix='', crs='Auto' {fips name poly2 51003 Albemarle \u005b\u005b\u005b-78.74939599999999,38.206648],\u005b-78.655273,38.260427],\u005b-78.663145,38.277933],\u005b-78.368743,38.184073999999995],\u005b-78.341674,38.176898],\u005b-78.209384,38.131128],\u005b-78.25398899999999,38.062813],\u005b-78.306762999999989,38.006474],\u005b-78.491332,37.796971],\u005b-78.497714,37.7631],\u005b-78.643918,37.733084],\u005b-78.66886199999999,37.724751999999995],\u005b-78.682986,37.78206],\u005b-78.83887399999999,38.04737],\u005b-78.780879,38.081029],\u005b-78.74939599999999,38.206648]],\u005b\u005b-78.519333,38.010678],\u005b-78.46368,38.020451],\u005b-78.448796,38.058447],\u005b-78.466129999999993,38.045700000000004],\u005b-78.49503399999999,38.065337],\u005b-78.519333,38.010678]]]}

Data has not been loaded. Please correct the error and try loading again.

Re: Qlik Polygon Format

Patric_Nordstrom — Tue, 27 Jul 2021 07:35:53 GMT

From the code it doesn't look like the query returns WKT. If it's SQL server the query has be phrased something like this to return WKT:

SELECT fips, geometry.STAsText() as poly

GeoAnalytics connector examples: https://community.qlik.com/t5/Qlik-Sense-Documents/Connector-Examples-Qlik-GeoAnalytics/ta-p/1548148

MS SQL Server example: https://community.qlik.com/t5/Qlik-Sense-Documents/MS-SQL-Server-and-Qlik-GeoAnalytics-QGA/ta-p/1521142

Thanks,

Patric