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?

migrated from scicomp.stackexchange.com Jul 26 '13 at 14:18

This question came from our site for scientists using computers to solve scientific problems.

  • 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 '13 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 '13 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 '13 at 16:56
  • possible duplicate of IP Geocoding Data Sources and/or APIs – philshem Apr 7 '15 at 12:04
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.

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy