IP found in API/Website but not in IPinfo Plus BQ db

[alexscmp]

Hi I would like to ask about the completeness of the data available in the IPinfo plus db.

Previously we had subscribed to the API to try and batch retrieve data for specific IPs and we were able to get results. Recently we have switched to try the IPinfo Plus database accessible through BigQuery.

When we tried to cross check whether the IPs we had gotten data for previously through the API was also present in the Plus db we were only able to match < 10% of the records to the Plus db.

For example, the IP here can be found through the website and previously through the API, but it cannot be found in the BigQuery database

119.236.68.116 | Hong Kong, AS4760, & VPN Not Detected - IPinfo.io

Could you kindly clarify what are the limitations of the Plus data and is there a criterion for it to be present there? Thanks

[Abdullah]

Hi Alex,

Thank you for posting in our community. I believe you are part of our enterprise user group. Our solutions architects and partnership team can organize a session with you to walk through our Google Cloud Platform integration.

There are certain operational nuances between our database (and our database in GCP) that I need to explain more clearly. You brought an interesting point forward.

Thank you for raising the question. I will enhance our documentation based on your feedback.

---

Core issue: How to look up IP addresses in our GCP database: Use the UDF

Our GCP documentation is listed here: IPinfo on Google Cloud Platform - BigQuery | IPinfo.io

UDF to be used: plus

IP to Lookup: 119.236.68.116

Query you should run

SELECT plus.plus('119.236.68.116') as plus

Result from BigQuery:

[{
  "plus": {
    "network": "119.236.68.112/28",
    "city": "Hong Kong",
    "region": null,
    "region_code": null,
    "country": "Hong Kong",
    "country_code": "HK",
    "continent": "Asia",
    "continent_code": "AS",
    "latitude": "22.27832",
    "longitude": "114.17469",
    "timezone": "Asia/Hong_Kong",
    "postal_code": "999077",
    "dma_code": null,
    "geoname_id": "1819729",
    "radius": "200",
    "asn": "AS4760",
    "as_name": "HKT Limited",
    "as_domain": "netvigator.com",
    "as_type": "isp",
    "carrier_name": null,
    "mcc": null,
    "mnc": null,
    "as_changed": "2021-05-01",
    "geo_changed": "2025-11-16",
    "is_anonymous": "true",
    "is_anycast": "false",
    "is_hosting": "false",
    "is_mobile": "false",
    "is_satellite": "false",
    "is_proxy": "false",
    "is_relay": "false",
    "is_tor": "false",
    "is_vpn": "true",
    "privacy_name": null
  }
}]

Deeper explanation

Understanding the difference between the database and API service

Reference post: Understanding Range Aggregation in IPinfo's IP Databases (this is a bit longer read but goes into a lot of details)

In an IP address database, the index column consists of a mix of individual IPs and network ranges. Within those network ranges belong individual IP addresses. Individual IP addresses are not listed in separate rows labeled “IPs”; they are listed within network ranges in the network column.

For example, your target IP address you want to look up belongs inside the network: 119.236.68.112/28 (notice here).

If you expand: 119.236.68.112/28 to its individual IP address, you get this:

ipinfo prips 119.236.68.112/28

119.236.68.112
119.236.68.113
119.236.68.114
119.236.68.115
119.236.68.116 <----------------------
119.236.68.117
119.236.68.118
119.236.68.119
119.236.68.120
119.236.68.121
119.236.68.122
119.236.68.123
119.236.68.124
119.236.68.125
119.236.68.126
119.236.68.127

As the target IP address you are looking for is included in the network range, queries like this will return nothing.

SELECT *
FROM plus.plus
WHERE network='119.236.68.116'

You have to use our packaged UDFs.

Understanding the UDF

So, the network column includes these IP ranges (119.236.68.112/28) and within them are billions of IP addresses! So, you have to find a way in SQL to go into each of these network columns one at a time at look if the target IP address is inside the ranges.

This is a complex operation and to find the best path forward, we have developed sophisticated algorithms and packaged them inside the Query Functions (UDF or TVF). This makes query operations super simple.

If you need to look up a single IP address, you run the following query:

SELECT plus.plus('119.236.68.116') as plus

Or if you need to look up a ton of IP addresses from a GCP table, you run this command:

SELECT
 ip,
 plus.plus(ip) as plus
FROM
 plus.ip_sample;

---

Let me know what you think, please.

[alexscmp]

Thanks Abdullah, this was very helpful

The UDF was the trick I needed

[Abdullah]

Thank you very much, Alex. Our GCP/BQ integration is an incredibly powerful solution, and we would like you and your team to continue investing in it. There are a ton of advantages to using it.

If you come across any blockers, points of friction or queries or thoughts. Do not hesitate to reach out to us. I am at your disposal. Cheers!

— Abdullah | DevRel, IPinfo