Loading

Mapping IP Address Geocode Data

Дата публикации: Apr 13, 2024
Задача
How to map IP address geocoding data. 
Действия
While Tableau software does not include IP address geocoding data, many websites on the Internet do, such as IPInfoDB. This example uses the DB1.LITE Free IP Geolocation Database available from iPInfoDB and can be reviewed in the attached packaged workbook.

Most IP databases include columns for "starting IP range" and "ending IP range."  The values in these columns are typically eight digit integers. Your data likely has IP Addresses in a nine digit string of numbers separated by periods. For example, 1.23.456.789. To determine the location of the IP address, you will need to convert the IP address into an integer and determine within which IP range that address exists.

First, you will need to create two joins between your IP Address data and the IP Address database.

Step 1: Set up the join

  1. Connect to the IP Address data you want to map
  2. Drag the table containing your IP Address data into the connection pane
  3. Click Add to the right of Connections and connect to the IP Address database
  4. Drag the IP Address database table into the connection pane
  5. Click the join between your IP Address data table and the IP Address database table
  6. Select Left as the Join type
  7. Clear any automatic joins that have formed by clicking the red X to the right of the join.

Join 1: Convert the IP Address to an integer and compare the IP Address integer to the IP Address Start Range

  1. Click Add new join clause and select Create Join Calculation
  2. Use the following calculation:
    (INT(
    LEFT([IP Address],(FIND([IP Address], ".")))
    ))*16777216+
    (INT(
    LEFT(
    MID([IP Address],
    FIND([IP Address],".")+1),
    FIND(MID([IP Address],FIND([IP Address],".")+1),".")-1)
    ))*65536+
    (INT(
    MID([IP Address],
        FIND([IP Address], ".", FIND([IP Address], ".") +1)+1,
        (FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)) - (FIND([IP Address], ".", FIND([IP Address], ".") +1))-1
    )
    ))*256+
    (INT(
    RIGHT([IP Address],
        LEN([IP Address]) -
        FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)
    )
    ))
  3. Click OK
  4. Select >= as the join operator
  5. Select the IP range start field as the field to join on from the IP Address database

Join 2: Convert the IP Address to an integer and compare the IP Address integer to the IP Address End Range

  1. Click Add new join clause and select Create Join Calculation
  2. Use the following calculation:
    (INT(
    LEFT([IP Address],(FIND([IP Address], ".")))
    ))*16777216+
    (INT(
    LEFT(
    MID([IP Address],
    FIND([IP Address],".")+1),
    FIND(MID([IP Address],FIND([IP Address],".")+1),".")-1)
    ))*65536+
    (INT(
    MID([IP Address],
        FIND([IP Address], ".", FIND([IP Address], ".") +1)+1,
        (FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)) - (FIND([IP Address], ".", FIND([IP Address], ".") +1))-1
    )
    ))*256+
    (INT(
    RIGHT([IP Address],
        LEN([IP Address]) -
        FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)
    )
    ))
  3. Click OK
  4. Select <= as the join operator
  5. Select the IP range end field as the field to join on from the IP Address database

Step 2: Build the view

  1. Drag Country Name to the view. 
  2. If <number> unknown appears at the bottom of the view, Edit Locations to correct or filter out the unknown locations. In this example, choose Filter data. 
  3. Right-click and drag IP Address to color.
  4. When asked which field you want to drop, select CNTD(IP Address) and click OK. 
  5. Right-click and drag IP Address to label. 
  6. When asked which field you want, select CNTD(IP address) and click OK.
You now have a view mapping the number of IP Addresses in each country.
 
Дополнительные ресурсы
IP addresses are assigned to geographies, governments, corporations, and other entities as dictated by IANA.

For example, a city or province can choose from a range of addresses. The range includes a starting integer and an ending integer. Most IP address databases contain a "starting IP address integer" column. Each row of data contains an integer, and the end range is the next row.

Log files, such as a web log, Apache traffic log, or web analytics file, contain a valid IP address for the inbound request to the resource requested (such as a web page, iPhone app, ftp site, etc.).

For more examples of mapping IP address geocode data, see northwestcoder's IP address workbook on Tableau Public, or How to show IP.Address in the Tableau Community Forums. 

Note: The full range of assigned IP addresses that can be geocoded is in the millions. We don't recommend importing this volume of data into Tableau using the custom geocoding feature. Instead, connect to the data directly. For more information, see Mapping IP Address Geocode Data in the Tableau Community Forums.
Номер статьи базы знаний

001458025

Вложения

MapIPAddressExample.twbx

60 KB

IPAddresses.xlsx

40 KB

 
Загрузка
Salesforce Help | Article