Tool Recommendation to create Interaction Map with multiple dimensions

Question

I have knowledge in QLik/Sense, Big Query, Google Data Studio ( and related )

I need to create a map with multiple points and each point will display data from the database (point related)

The table will look for example like this:

+------------+---------------------------+-----------+---------+-------+
| Store Name | Store Location (Long/Lat) | Product   | Ordered | Sold  |
+============+===========================+===========+=========+=======+
| Store 1    | XXX,XXX                   | Product 1 | True    | False |
+------------+---------------------------+-----------+---------+-------+
| Store 2    | YYY,YYY                   | Product 1 | True    | True  |
+------------+---------------------------+-----------+---------+-------+
| Store 2    | YYY,YYY                   | Product 2 | False   | True  |
+------------+---------------------------+-----------+---------+-------+

So when you hove over with mouse on the map at store "Store 2", You will see pop up box with only:

+------------+----------+-----------+
| Product 1  | True     | True      |
+------------+----------+-----------+
| Product 2  | False    | True      | 
+------------+----------+-----------+

And Store 1:

+------------+----------+-----------+
| Product 1  | True     | False     |
+------------+----------+-----------+

I have tried:

Google Data studio (limit one dimension for example store name and that it)
Qlik Limit to 1 dimension
Google maps, better but I can display only 1 product, not a list of products

Any suggestions, what can be used?

@MikhailBerlyant Do you know the way moving BigQuery Geo Viz Tool to webview/datastudio any external way? — J.Doe, Feb 01 '19 at 13:12

score 2 · Answer 1 · answered Jan 31 '19 at 22:26

Any suggestions, what can be used?

As a workaround - you can consider doing some extra work before visualizing your data like in an example below (BigQuery Standard SQL)

#standardSQL
WITH `project.dataset.table` AS (
    SELECT "Maria's Tortillas" Store, '-118.379096984863,33.9593620300293' Location, 'machaka breakfast burritos' Product, TRUE Ordered, FALSE Sold UNION ALL
    SELECT "Maria's Tortillas", '-118.379096984863,33.9593620300293', 'chile relleno plate', TRUE, TRUE UNION ALL
    SELECT "Anthony's Gift Shop", '-118.371124267578,33.9462585449219', 'LA commemorative trinkets', FALSE, TRUE 
)
SELECT 
    ANY_VALUE(ST_GEOGFROMTEXT(CONCAT('POINT(', REPLACE(location, ',', ' '), ')'))) Store_Location,
    Store Store_Name, 
    STRING_AGG(CONCAT(Product,' | ',CAST(Ordered AS STRING),' | ',CAST(Sold AS STRING)), ' *** ') Products
FROM `project.dataset.table`
GROUP BY Store

If you run above in BigQuery Geo Viz Tool - you will get something like below

To “spice” the visualization a little, you can add some extras:

#standardSQL
WITH `project.dataset.table` AS (
    SELECT "Maria's Tortillas" Store, '-118.379096984863,33.9593620300293' Location, 'machaka breakfast burritos' Product, TRUE Ordered, FALSE Sold UNION ALL
    SELECT "Maria's Tortillas", '-118.379096984863,33.9593620300293', 'chile relleno plate', TRUE, TRUE UNION ALL
    SELECT "Anthony's Gift Shop", '-118.371124267578,33.9462585449219', 'LA commemorative trinkets', FALSE, TRUE 
)
SELECT 
    ANY_VALUE(ST_GEOGFROMTEXT(CONCAT('POINT(', REPLACE(location, ',', ' '), ')'))) Store_Location,
    Store Store_Name, 
    CONCAT(
        '<table cellpadding="5" style="border-style:solid; border-width:1px;border-collapse:collapse;">',
        STRING_AGG(CONCAT('<tr>',td,Product,'</td>',td,CAST(Ordered AS STRING),'</td>',td,CAST(Sold AS STRING),'</td></tr>')),
        '</table>'
    ) Products
FROM `project.dataset.table`, UNNEST(['<td style="border-style:solid;border-width:1px">']) td
GROUP BY Store

So, the result will be little bit better formatted (as in below pic)

You might be able to adopt this “technic” unless you will find the tool that handles all your needs on its own

Meantime, have in mind that different tools will treat such workaround in a different way – for example, if you run above two scripts within Goliath – you will have respectively:

and

Also, in Goliath, Geo Visualization is in-built feature, so you can interactively build geo visualizations with multiple layers and combining results from multiple queries, etc. without leaving BigQuery Tool

Disclosure: I am an author, product owner and am leading development of Potens.io Suite of Tools (which Goliath is a part of) - which is also clearly stated in my SO profile

Thank you, will have a try and will let you know. But I did not know that this tool exists!!! — J.Doe, Feb 01 '19 at 08:26
Amasing tool )) I have managed to do what I need with BigQuery Geo Viz Tool, by any chance do you know to move project from BigQuery Geo Viz Tool TO Webview/Data Studio or something that will be visible for other people?? — J.Doe, Feb 01 '19 at 11:04
@J.Doe - unfortunately, currently, BigQuery GIS visualizations are not supported by Data Studio. But Goliath supports downloading visualization as an image for further sharing — Mikhail Berlyant, Feb 01 '19 at 14:33

score 1 · Accepted Answer · answered Feb 01 '19 at 16:10

1

In my opinion in Qliksense it is easiest.

First just load Store and Product table and create simple data model:

Then add new chart (Map) and set location fields:

Add new calculated dimension:

=Product & ', Ordered: ' & Ordered & ', Sold: ' & Sold

and that's all. Ready:

answered Feb 01 '19 at 16:10

Hubert Dudek

1,666
1
13
21

Can pictures be added on pop up box? – J.Doe Feb 04 '19 at 09:39
1

haven't' tested that but theoretically yes as images can be imported inside qliksense. I will test it today or tomorrow and will let you know. – Hubert Dudek Feb 04 '19 at 10:12
Hi I haven't found easy and nice solution for map + images. When you need table with images is easy. For map it will require probably to write own extension, you can also take a look at https://developer.qlik.com/ – Hubert Dudek Feb 07 '19 at 17:17

Tool Recommendation to create Interaction Map with multiple dimensions

2 Answers2

Linked