topic Re: How to implement a cluster map based on telephone dialing codes? in Dashboards & Visualizations

How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Wed, 07 Mar 2018 14:51:13 GMT

Hi Team,

We are trying to implement cluster map based on telephone dialing codes. need your help in implementing.
How to create maps and how to map the volume against the corresponding area and how to show the volume numbers in the corresponding area in the graph?

Your help is appreciated.

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Wed, 07 Mar 2018 16:37:01 GMT

What have you tried so far?

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Wed, 07 Mar 2018 16:50:50 GMT

I am not sure where to start. All i have is telephone area codes. Do i need to have a lookup created with telephone area codes with latitude and longitude? To proceed further?

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Wed, 07 Mar 2018 18:09:55 GMT

Yes, you will need a means to convert area codes into coordinates. A lookup table should work well. Depending on your data, you would then use the geom or geostats command to plot it on a map.

Re: How to implement a cluster map based on telephone dialing codes?

niketn — Wed, 07 Mar 2018 19:00:24 GMT

@klchandrakanth, is this dialing code lookup required only for US or anywhere in World?

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Wed, 07 Mar 2018 20:13:27 GMT

This is for area lookup in Brazil

Re: How to implement a cluster map based on telephone dialing codes?

DalJeanis — Wed, 07 Mar 2018 20:13:27 GMT

For US and for Canada, it looks like there are ESRI files (or similar) available for that from at least a couple of places.

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=United_States_Telephone_Area_Code_Boundaries

You may have another issue, though. Area codes are no longer geographically distinct; most of them in the most populous locations are overlays, so you may have three different area codes that represent identical geography. You need to identify what the purpose of your map is before you decide on representation.

I suspect that you probably should go with plotting all overlays to the exact same coordinates, and aggregating the results as a single pie chart. Presumably, if you are presenting a geographical representation, then you are trying to see where your calls are coming from.

Check your underlying data and see if your assumptions are correct or if there is a more specific field, though. I make calls from my Dallas 469 number while I am on site in Florida or Kansas or San Jose.

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Wed, 07 Mar 2018 20:13:55 GMT

Thank you for your quick response. I will try and get back to you.

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Wed, 07 Mar 2018 21:59:39 GMT

Thanks for the details. I am trying this in Brazil.

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Wed, 07 Mar 2018 22:38:59 GMT

The same caution applies to Brazil as most states there have more than one area code.

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 04:51:46 GMT

I have created a sample lookup table with telephone area code, state,lat and long.
with below command i am able to see the coordinates.

| inputlookup DDDLookupSample.csv | geostats count by DDD_Code latfield=Latitude longfield=Longitude

However, not able to map my query result to the map. Any help is appreciated.
my base query that is giving the count of the area codes with below.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | stats count by PhoneDDD

PhoneDDD count
11 6860
12 216
13 98
15 446

My CSV is like this.

DDD_Code States Latitude Longitude
11 Sao Paulo -23.65283405 -46.52781661
68 Acre -9.070003236 -68.66997929
82 Alagoas -9.48000405 -35.83996769
96 Amapa -0.039598369 -51.17998743
92 Amazonas -3.289580873 -60.6199797

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 13:18:27 GMT

@niketnilay or @richgalloway, any help ?

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Thu, 08 Mar 2018 13:55:53 GMT

Combine your two searches and you should have it.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | lookup DDDLookupSample.csv DDD_Code as PhoneDDD | geostats latfield=Latitude longfield=Longitude count by DDD_Code

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 14:00:25 GMT

Perfect. Thanks. below command worked. Thanks for your quick help. you are awesome.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | lookup DDDLookupSample.csv DDD_Code as PhoneDDD | geostats latfield=Latitude longfield=Longitude count by PhoneDDD

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Tue, 29 Sep 2020 18:21:02 GMT

@richgalloway, why i am getting few results in OTHER ?

geobin latitude longitude 11 12 16 19 21 22 24 27 31 32 34 35 37 OTHER
bin_id_zl_0_y_2_x_2 -27.93652 -52.55455 907 79 315 116 124
bin_id_zl_0_y_2_x_3 -22.56003 -44.16995 17830 1716 1384

bin_id_zl_0_y_3_x_2 -3.21593 -47.30641 2327

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Thu, 08 Mar 2018 15:50:50 GMT

I assume you have data with DDD codes not in your lookup file.

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 16:16:02 GMT

I am guessing issue with multiple DDD vs states.
Can I use multiple DDD codes like below.

54,Rio Grande Do Sul,-30.88004148,-55.53000615
55,Rio Grande Do Sul,-30.88004148,-55.53000615
56,Rio Grande Do Sul,-30.88004148,-55.53000615
57,Rio Grande Do Sul,-30.88004148,-55.53000615
58,Rio Grande Do Sul,-30.88004148,-55.53000615
59,Rio Grande Do Sul,-30.88004148,-55.53000615
69,Rondonia,-11.64002724,-61.20999536
95,Roraima,1.816231505,-61.12767481
47,Santa Catarina,-27.23003172,-52.03001306
48,Santa Catarina,-27.23003172,-52.03001306
49,Santa Catarina,-27.23003172,-52.03001306
11,Sao Paulo,-23.65283405,-46.52781661
12,Sao Paulo,-23.65283405,-46.52781661
13,Sao Paulo,-23.65283405,-46.52781661
14,Sao Paulo,-23.65283405,-46.52781661
15,Sao Paulo,-23.65283405,-46.52781661

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 18:47:05 GMT

@richgalloway, any help ? i am stuck and not getting any information what are the causes for OTHER results.

Re: How to implement a cluster map based on telephone dialing codes?

richgalloway — Thu, 08 Mar 2018 22:41:41 GMT

That looks good.

Re: How to implement a cluster map based on telephone dialing codes?

klchandrakanth — Thu, 08 Mar 2018 22:52:06 GMT

Not sure what is the issue, even if i do count by ddd instead of states, still i see OTHER. i think its not issue with look up. Any light on the issue is. Much appreciated. Thanks for your help so far @richgalloway