Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Highlighted
Tetranos
New 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
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
Tetranos
New 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

4 Replies
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
Tetranos
New 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

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
Tetranos
New 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