topic Re: Display map data from SQL Spatial? in GeoAnalytics

Display map data from SQL Spatial?

Anonymous — Fri, 14 Dec 2018 15:55:14 GMT

Hello all,

I'm wondering if it is possible, in either Qlik Sense or using GeoAnalytics, to display map data directly from SQL?

I have read the following - https://community.qlik.com/t5/Qlik-GeoAnalytics-Discussions/Geometry-datatype-in-Geoanalytics/td-p/8122 - but this appears to be for a Postgres database, as well as a Geometry datatype (I am using Geography).

I have attempted and am unsuccessful in displaying both Geography and Geometry datatypes, as well I have not been successful in attempting to display the WKT data after converting the SQL binary shape field either. This particular SQL Server version is 2008 R2.

Any thoughts or recommendations?

Re: Display map data from SQL Spatial?

Patric_Nordstrom — Fri, 14 Dec 2018 16:21:13 GMT

Hi,

Yes, that is possible, In MS SQL STAsText produces WKT that can be read by QGA.

ex: select top(2) zip4, GEOM.STAsText() as wkt from dk_zip4 order by zip4;

Check out my slides here:

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

Re: Display map data from SQL Spatial?

Anonymous — Fri, 14 Dec 2018 16:34:09 GMT

Thanks very much, that is helpful. A couple things below..

I am currently working in Qlik Sense - In the data load editor, I attempt to use:

LOAD OBJECTID,

SHAPE,

etc

Despite the fact that intellisense brings up "SHAPE", when I try to load the data I get the error message "Field 'Shape' not found"

There is no issue when I add Shape.STAsText() as wkt to my SELECT statement though.

After taking a look at your slides, it looks like I require GeoAnalytics to load SQL data. Is that the case? Thanks again!

Re: Display map data from SQL Spatial?

Patric_Nordstrom — Fri, 14 Dec 2018 16:45:57 GMT

Hi, yes sorry for being unclear, QGA is required.

Re: Display map data from SQL Spatial?

Anonymous — Fri, 14 Dec 2018 16:51:26 GMT

Thanks so much! You've saved me a lot of time and possible frustration.

Re: Display map data from SQL Spatial?

auzzie4185 — Wed, 02 Jan 2019 19:57:03 GMT

Hi,

This is a bit of a long shot... but if you have control of your database you might be able to use the Latitude and Longitude in the original non-GA map tool. There are LOTS of "variables" so this is my best guess for your situation. I also think it will only work for points, not polygons and lines.

Option 1:

If your database table already has Latitude and Longitude you can use the GeoMakePoint function in the load script. It will only work if you have data in WGS 1984.

Ex. GeoMakePoint(Latitude, Longitude) as GeoInc

Ex. GeoMakePoint(Latitude/1000000, Longitude/-1000000) as GeoInc if your Lat and Long don't have any decimal points or negative numbers.

Option 2:

1. Create a view of the table that has the SHAPE Field. If you can do the conversion on the database side and create the view with the " sde.st_astext(SHAPE) as geo". Then read the view into Qlik Sense as a Geo Point.

Hope that helps. If you have any questions or details please let me know! I might be able to help you if your in a different spatial format. But I am using Oracle and can't create a view in our database. So I would have to talk you through YOUR Case.

-Auzzie

Re: Display map data from SQL Spatial?

christms — Fri, 28 Jun 2019 02:40:31 GMT

Hi,

I am new to GeoAnalytics and I am having some difficulty plotting the map reading from my ODBC - SHAPE attribute. Do you have any tutorial or videos on the solution for the suggested solution below?

Thanks in advance.