I've got a staging database which stores the GEO location because the following structure :
I get the information from the vendor. The title isn't relevant. The information is available in a CSV file, the posts are
The truth that the information is available in the CSV file has a tendency to change, like the Postal Code, City Title, Region Title, or even the IP Range.
The way in which area of the application that handles the information import work the following: Remove Nations, Regions, Metropolitan areas, Postal Code etc. And Re populate the information in to the Database.
I want an easy method for this. Because once the application is live I'll be losing the secrets of the records that are within the database. but this posting takes a couple of minutes, meaning the applying wont have the ability to use GEO Location db. And so i cant really use remove and place.
I have to implement this such a manner that, I'll load all of the data into memory and match it just as the dwelling of after i browse the data in the list. i.e: Country Dictionary, Region Dictionary from DB and CSV file. and also the identify the alterations increase the database in a single transaction.
The issue is: How you can map them, to ensure that I'm able to identify changes. Quite simply, for instance : When the Country title changes :), I have to update by using regardless of the country title transformed to within the CSV file. Ok. But what about a lot more than 1 country title changes ? Same idea for regions, metropolitan areas, postal codes.
Yes, I store this ad a Tree Structure. Country may be the root node, Regions are first level children's, Metropolitan areas are seconds level and Postal codes would be the cherries.
I m sorry. It was kinda lengthy to describe. Appreciate time you required to see through this.
Execute a diff on CSV files and employ that to craft SQL which will update the database.
We've years of experience while using geolocation database. We're while using IP2Location from http://www.ip2location.com.
It's unlikely you are able to update the database by continuing to keep the beginning IP and Finish IP as index for reference purpose.
For instance, one Ip range might be splitted into multiple ranges within the next releases.
Therefore, we're posting each month update as different table. Therefore, we are able to execute a comparison lookup by utilizing two tables.
if you wish to do an update, you should use RedGate SQL Comparer (MS SQL). It's very good, provides you with the scripts. You will find also other tools which could do that kind of comparison. The steps could be
- set your database to single user mode, run the updates, release it from single user mode
Alternative: upload this right into a new table title, drop that old table and relabel this new table towards the original table title. Ofcourse you would need to handle any primany key foreign key associations too