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

[klchandrakanth]

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.

[richgalloway · Accepted solution]

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

[klchandrakanth]

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

[klchandrakanth]

@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

[richgalloway]

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

[klchandrakanth]

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

[richgalloway]

That looks good.

[klchandrakanth]

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

[klchandrakanth]

Found it.

globallimit=0 fixed the issue with "OTHER".

globallimit, Controls the number of pies in the pie-chart. All other split-by values will be grouped under "OTHER".Setting globallimit=0 will remove all limits and all columns will be rendered

[klchandrakanth]

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

[DalJeanis]

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_Boun...

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.

[klchandrakanth]

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

[richgalloway]

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

[klchandrakanth]

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?

[klchandrakanth]

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

[klchandrakanth]

@niketnilay or @richgalloway, any help ?

[niketn]

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

[klchandrakanth]

This is for area lookup in Brazil

[richgalloway]

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.

[klchandrakanth]

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

[richgalloway]

What have you tried so far?