Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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,

2 Solutions

Accepted Solutions
Highlighted
Employee
Employee

Re: Display a Postgis geom on Qlik Sense

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

Highlighted
Contributor II
Contributor II

Re: Display a Postgis geom on Qlik Sense

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

6 Replies
Highlighted
Employee
Employee

Re: Display a Postgis geom on Qlik Sense

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
Highlighted
Contributor II
Contributor II

Re: Display a Postgis geom on Qlik Sense

 

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

Highlighted
Employee
Employee

Re: Display a Postgis geom on Qlik Sense

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

Highlighted
Contributor II
Contributor II

Re: Display a Postgis geom on Qlik Sense

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

Highlighted
Partner
Partner

Re: Display a Postgis geom on Qlik Sense

Hi Patrick,

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

Thank you

David   

Highlighted
Contributor II
Contributor II

Re: Display a Postgis geom on Qlik Sense

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...