IP Functions

Constraints

The relationships between IP addresses and regions provided by LTS are from third-party libraries. The data is updated periodically (about half a year) and may not reflect the correct relationships. LTS will be optimized to update the libraries more frequently to provide better experience.

A maximum of 5 million data records can be queried using the aggregate query of an IP function. Exceeding this limit will cause the query to time out.

Description

IP functions identify the country, state/province, city, and network carrier to which a target IP address belongs.

Syntax

SELECT count(*) AS PV, ip_to_province(client_ip) AS province GROUP BY province 

IP Function Statements

Examples

• ip_to_province
  Obtains the top 3 states/provinces with the most requests.

  1. Query and analysis statement

     SELECT count(*) AS PV, ip_to_province(client_ip) AS province GROUP BY province ORDER BY PV desc LIMIT 3

  2. Query and analysis result

     Table 2 Query and analysis result

     PV	province
     101	Guangdong
     83	Shanghai
     78	Shandong

• ip_to_country
  Obtains the top 3 countries or regions with the most requests.

  1. Query and analysis statement

     SELECT count(*) AS PV, ip_to_country(client_ip) AS county GROUP BY country ORDER BY PV desc LIMIT 3

  2. Query and analysis result

     Table 3 Query and analysis result

     PV	country
     100	China
     76	United States
     55	Canada

• ip_to_city
  Obtains the top 3 cities with the most requests.

  1. Query and analysis statement

     SELECT count(*) AS PV, ip_to_city(client_ip) AS city GROUP BY city ORDER BY PV desc LIMIT 3

  2. Query and analysis result

     Table 4 Query and analysis result

     PV	city
     109	Guangzhou
     89	Shanghai
     23	Xi'an

• ip_to_provider
  Obtains the top 3 carriers with the most requests.

  1. Query and analysis statement

     SELECT count(*) AS PV, ip_to_provider(client_ip) AS provider GROUP BY provider ORDER BY PV desc LIMIT 3

  2. Query and analysis result

     Table 5 Query and analysis result

     PV	provider
     115	Telecom
     65	att.com
     44	Unicom

• ip_to_geo

  Returns the longitude and latitude of an IP address.

  1. Query and analysis statement

     SELECT count(*) AS PV, ip_to_geo (client_ip) AS geo GROUP BY province ORDER BY PV desc LIMIT 3

  2. Query and analysis result

     Table 6

     PV	geo
     101	*, *
     83	47.369013, -68.326674
     78	32.715891, -117.161588