Top 10 nearest IP addresses from a location → Haversine function and geo coordinate
Using our IP geolocation listing in Snowflake or our IP geolocation database in your preferred database solution, you can get a list of IP addresses that is around a specific a location.
This operation utilizes the Haversine Formula that measures the distance between the input geographic coordinate provided with our IP geolocation’s IP address ranges’ geographic coordinates. You can even provide a parameter that limits the maximum location delta.
Snowflake platform has a native haversine
formula (doc) that can be used with our IP address to geolocation data available on the Snowflake marketplace
Random geographic coordinate input ⇒ 40.990, -100.713
// 40.990 → Input latitude
// -100.713 → Input longitude
SELECT
HAVERSINE(40.990, -100.713, loc.lat, loc.lng) as distance,
loc.start_ip,
loc.end_ip,
loc.city,
loc.region,
loc.country,
loc.postal,
loc.timezone
FROM public.ip_geolocation loc
ORDER BY distance
LIMIT 10;
-----------------
-- Explanation --
-----------------
-- The haversine is a snowflake native function
-- You can pass in the geographic coordinate to generate the closest IPs to that point
Result:
Service referenced
- IP to Geolocation data