Tool Recommendation to create Interaction Map with

2019-02-19 13:04发布

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?

2条回答
smile是对你的礼貌
2楼-- · 2019-02-19 13:30

In my opinion in Qliksense it is easiest.

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

data model

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

add map

Add new calculated dimension:

calculated dimension

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

and that's all. Ready:

solution

查看更多
做个烂人
3楼-- · 2019-02-19 13:36

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

enter image description here

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)

enter image description here

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:

enter image description here

and

enter image description here

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

查看更多
登录 后发表回答