0

I have a database with about two million entries (!), relating IP ranges to locations. Unfortunately, a lot of the data is overlapping, so that every IP produces multiple hits (about two or three. Of these, only one is within an acceptable range of error. I have tried with about 10 IPs, and it seems the last one is always the most accurate, but I would like to try with more, about a thousand before making such a general rule and weeding out stuff by it.

Is there anywhere I can get such a massive number of accurate IP addresses, complete with location information?

Is there a better way of cleaning this data?

Improve this question
4
  • 2
    This doesn't seem especially like it relates to open data. I reckon you'd do better with your request for data wrangling help on Stack Overflow. If you're after a source for IP/location data then this is potentially the right place, but the question would need an edit.
    – Snubian
    Jul 27, 2013 at 5:37
  • There are a lot of things that can wrong with GeoIP -- IP blocks get reassigned, so can change over time. You also have delegation, so a larger block might be assigned to one location and a subnet that's somewhere else. (quite common within companies with multiple branches that have an older class B)
    – Joe
    Jul 30, 2013 at 16:55
  • Oh ... and this would probably be more on-topic if you were asking about accurate GeoIP databases that you could use to verify / validate your data.
    – Joe
    Jul 30, 2013 at 16:56
  • possible duplicate of IP Geocoding Data Sources and/or APIs
    – philshem
    Apr 7, 2015 at 12:04

2 Answers 2

Reset to default
2

You might want to take a look at https://github.com/open-city/dedupe. I haven't used it, but looks like it should solve your problem.

Improve this answer
2

assuming you do want to simply take the final entry for each geography, perhaps import your data set into any SQL database and use the LAST command like this

SELECT LAST( ip_column ) , geography_name FROM tablename GROUP BY geography_name

click here for a more thorough explanation

SQL databases should be able to handle two million records without a problem, even on an older laptop :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.