Qlik Community

GeoAnalytics

Discussion Board for collaboration regarding Qlik GeoAnalytics.

cancel
Showing results for 
Search instead for 
Did you mean: 
Tetranos
Contributor II
Contributor II

Display a Postgis geom on Qlik Sense

Hi all,

I've already posted that question on "Qlik Sense \ Qlik Sense App Development" but I'm not sure that was the good forum for it.

Therefore I try here...hope this will not be a problem (otherwise sorry).

 

According to the documentation "Qlik Sense can use: Fields containing geodata in GeoJSON, LineString, or MultiLineString formats.".

I'm trying to display a Qlik Map based on a Postgis column.

My Postgis column is Linestring ZM ESPG:2154. I'm creating a view on it to :

- transform it to ESPG:4326

- force it to 2D

- convert it to GeoJSON

 

The query looks like this : ST_AsGeoJSon(ST_Force2D(ST_Transform(geom, 4326)))

And returns something like that : {"type":"LineString","coordinates":[[4.29371849296831,44.0363076802448],[4.29399312738311,44.036478158726]]}

Then I load the view in my Qlik App, add a map, add a layer to the map and finally select the view column as the location....but nothing is displayed.

 

Regards,

Labels (2)
2 Solutions

Accepted Solutions
Patric_Nordstrom
Employee
Employee

Yes, it's variation of the same topic. In your case strip the headers with something like this:

=replace(replace(geom,'{"type":"LineString","coordinates":',''),'}','')

But I recommend using QGA connector Load instead. It's easier and more stable.
Thanks,
Patric

View solution in original post

Tetranos
Contributor II
Contributor II
Author

Yes !!!

Instead of your "replace(replace(" method, I've updated my view using json capabilities of PostgreSQL like that :

ST_AsGeoJSon(ST_Force2D(ST_Transform(geom, 4326)))::json->>'coordinates'

Now the view only returns the coordinates part and Qlik Sense display it well.

 

I'll take a closer look to QGA.

Thanks

View solution in original post

13 Replies
Patric_Nordstrom
Employee
Employee

Hi,
The best way is to use the QGA connector "Load" operation and load the geometries in GeoJSON or WKT format. For WKT use ST_AsText instead of ST_AsGeoJSon. The QGA connector can also do the coordinate transform.

It is technically possible to do some pruning of the GeoJSON and only keeping the geometry, the "coordinates" field and then display directly. The Sense internal geometry format is similar to GeoJSON.

Thanks,
Patric
Tetranos
Contributor II
Contributor II
Author

 

Hi Patric,

Thanks for your help.

You mean that without QGA I need to "adapt" my geom so that native Sense can display it?

I've find a post where you talk about "transform the WKT directly to Sense geometries" here:

https://community.qlik.com/t5/Qlik-GeoAnalytics-Discussions/WKT-Area-Layer/m-p/1444797

That's it?

Regards

Patric_Nordstrom
Employee
Employee

Yes, it's variation of the same topic. In your case strip the headers with something like this:

=replace(replace(geom,'{"type":"LineString","coordinates":',''),'}','')

But I recommend using QGA connector Load instead. It's easier and more stable.
Thanks,
Patric
Tetranos
Contributor II
Contributor II
Author

Yes !!!

Instead of your "replace(replace(" method, I've updated my view using json capabilities of PostgreSQL like that :

ST_AsGeoJSon(ST_Force2D(ST_Transform(geom, 4326)))::json->>'coordinates'

Now the view only returns the coordinates part and Qlik Sense display it well.

 

I'll take a closer look to QGA.

Thanks

david38
Partner
Partner

Hi Patrick,

Could it be possible to have an example of a script, loading data shape from a POSTGIS connexion?

Thank you

David   

Tetranos
Contributor II
Contributor II
Author

Hi David,

I've created two PG functions so that I can know easily create views that I load using the Qlik wizard.

 

Function 1: To transform a Geom to WGS84

create or replace function f.TransformerEnWGS84(_Geom geometry) returns geometry as $$
select ST_Transform(_Geom, 4326);
$$ language sql;

 

Function 2: To extract the coordinates part of the JSON that Qlik waits

create or replace function f.TransformerEnGeomQlik(_Geom Geometry) returns text as $$
select ST_AsGeoJSON(TransformerEnWGS84(ST_Force2D(_Geom)))::json->>'coordinates';
$$ language sql;

 

View sample:

create view v.LinRoutierD30_4Qlik as
select TransformerEnGeomQlik(t.Geom) as Geom
from m.Troncon t

 

Hope that helps...

WernerDC
Creator
Creator

Hi guys.

 

Can someone help me with my Geom point please. I will not be using QGA.

This is what my data looks like.

I want to use that Point field. How can i get that Qlik friendly?

 

Kind regards

Patric_Nordstrom
Employee
Employee

WernerDC
Creator
Creator

Hi @Patric_Nordstrom 

Ive seen this post but to be quite honest, I have no idea where to even start.

 

How Do I start?