Working in IT Operations is really fantastic and interesting but you need to update yourself on daily basis. Your innovative skills nourishes everyday and you will feel that there are endless possibilities to work with IT equipments. I know this post will not be very much interesting for most of the readers of IT Operationz but few people might find this post very much interesting. Perhaps someone out there would be looking for some comprehensive guide to load data in SQL from NAVTEQ. I know that NAVTEQ provide a guide to prepare and load the data but that’s a very lengthy guide to extract, prepare and load the data in SQL server 2005 database. It took me two weeks to extract, prepare and load the data into SQL server 2005 database, although the size of the data was huge but the preparation took most of the time. I was given this task again to load the new data from NAVTEQ in SQL server 2005 and I did it just in three days because loading data took more than two days and the complete size of the databases may exceed 900GB (I loaded two databases for complete Q1 for Europe). However I would like to talk about one database over here. How I did this, see the steps given as under:
Step 1: Download the data from NAVTEQ website with high speed (as much as provided by your service provider) Place the data at your hard drive somewhere (For hardware requirements please see the NAVTEQ guide) (one thing for the hard drive please one TB hard drive to save yourself from any inconvenience)
Step 2: Extract all files to the ..MSSQL2005loaderfiles folder (Delete the compressed files after extraction) (Under loaderfiles there are several other folders like admin, rdfcore, etc) (Extract the files to the respective folders)
Step 3: You must verify the files before loading to the database to save your self from a big disaster
How to Verify?
Open a CMD window to ..MSSQL2005loaderfiles and navigate to the location where the
RDF data files were copied and execute the following command:
fsum -c MD5loaderfiles.txt > checksum.txt
Step 4: create a SQL server database, command is given as under:
Open a CMD window to ..MSSQL2005 and enter the following command:
CreateDB.bat [SQLServerName] [DBName] [DBAdminName] [DBAdminPassword]
Step 5: Prepare the data by using the under commands:
These examples of command lines show each of the possible data options listed within the brackets of the previous description, as well as the absolute path to the loader files folder, which is shown here as H:MSSQL2005loaderfiles. Your real Path will reflect your environment. (H:MSSQL2005loaderfiles replace with your real path and please take care the space, if you have any space between folder names use “” on the both ends of the path)
Prepare_CoreRDF_Admin.bat H:MSSQL2005loaderfiles
Prepare_Traffic.bat H:MSSQL2005loaderfiles
Prepare_Voice.bat H:MSSQL2005loaderfiles
Prepare_WKT.bat H:MSSQL2005loaderfiles
Prepare_ExtendedListings.bat H:MSSQL2005loaderfiles
Prepare_Midpoint.bat H:MSSQL2005loaderfiles
Prepare_PointAddress.bat H:MSSQL2005loaderfiles
Prepare_ADAS.bat H:MSSQL2005loaderfiles
Prepare_SC.bat H:MSSQL2005loaderfiles
Prepare_Truck.bat H:MSSQL2005loaderfiles
Prepare_VUL.bat H:MSSQL2005loaderfiles
Prepare_DistanceMarkers.bat H:MSSQL2005loaderfiles
Step 6: Load the RDF core data into the SQL Server 2005 database, load indexes, constraints
and create the data views.
Open a CMD window and navigate to the ..MSSQL2005 folder type following commands:
Example is given as under:
These examples of command lines show each of the possible data options to be loaded, as well as the SQL Server 2005 name, the database name, the Database Admin Name, and the Database Admin Password.
Note: Load_CoreRDF must be loaded before extensions due to referential integrity constraints.
Load_CoreRDF_Admin.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_Traffic.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_Voice.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_WKT.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_ExtendedListings.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_Midpoint.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_PointAddress.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_ADAS.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_SC.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_Truck.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_VUL.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Load_DistanceMarkers.bat NAVTEQ RDF_CORE rdfadmin rdfpswd
Note:
NAVTEQ = Database Server Name
RDF_CORE = Database Name
rdfadmin = SQL server user name
rdfpswd = SQL server user name’s password
The load tables process may reflect all the following steps for CoreRDF_Admin, Traffic,
Voice, and Extended Listings data:
1. Creating Tables
2. Loading Data
3. Creating Indexes
4. Creating Constraints
5. Verifying Installed Data
Completion of the entire process is indicated by a message of “process complete.”
Check the associated log files which were written to the MSSQL2005 folder.
Good Job amd Good Writing