- Performance to enrich your account data with geography data is much better than Bing Maps.
- With Google I can use the GUID, which is the key, in my table as the key. In BING Maps the GUID is not recognized, you need to use an Integer.
This example is based on an Exact Globe or Exact Synergy database in which the table Cicmpy exists. The Cicmpy table is used to store all account information. In case you want to update account data from another table, some minor extra changes need to be done. Please execute downloaded script: CreateCicmpyGeoUpdate.SQL This script will add some columns to the Cicmpy table and will create a table to update the geography data by the SSIS package.
- Download the CicmpyGeoUpdate.rar Extract the CicmpyGeodataGoogleMaps.dtsx package in folder on your server. The package need to be configured for your environment.
- Create a new SSIS project in Microsoft Visual Studio 2008.
- Add the CicmpyGeodataGoogleMaps.dtsx package to your project.
- In the solution explorer, right mouse click on SSIS packages, add existing package)
- Choose File System as package location and browse to the location where you have downloaded CicmpyGeodataGoogleMaps.dtsx
- Open the package by double clicking on the package name in the Solution Explorer.
- Configure the Connection Manager. The connection manager connection is used by the package to know in which database he needs to enrich the account data.
- Select GRAA1005-2.ExactGeoTest, right mouse, Edit.
- Specify your SQL Server and Database name
- Press Test Connection to test if you can connect to the database.
- Configure the package
- Open the dataflow task. Press on the Data Flow tab.
- Configure the OLE DB Source connection. Select OLE DB Source, right mouse click, Edit. Check if the correct OLE DB connection manager is selected.
- In case you are using another table for your account data you need to rewrite the SQL query. Change your table and columns but use the AS Address, AS ZIPCode for the column names of the result set. These columns names are used in the script to recognize the street, zip code etc.
- Install the Google Maps API key in the Script. Edit Script Component. Select Script Component, right mouse click, Edit.
- Press the Edit Script button.
- Open the GeoCode.vb and paste your Google Maps API key between the " "
- Save the script and build the project and close the project.
- Configure the OLE DB Destination. Check the configured OLE DB Connection Manager. Select 'Table or View fast load' as Data access mode and select CicmpyGeoUpdate table
- Save the package and you can start the package.
- The package will insert all geography data in the table CicmpyGeoUpdate with the GUID of the account as the key. Execute next script to update the account table Cicmpy with all geography data.
UPDATE Cicmpy
SET GeographyCol = Geography::Point(CGU.Lat, CGU.Long, 4326),
Lat=CGU.LAT,Long=CGU.Long,
Accuracy = CGU.Accuracy
FROM Cicmpy C
INNER JOIN CicmpyGeoUpdate CGU ON C.Cmp_wwn = CGU.cmp_wwn
WHERE CGU.Lat is not NULL - Everytime the package is executed, records are added to the CicmpyGeoUpdate table. After a succesfull update of the account data in step 6 you can truncate the table CicmpyGeoUpdate.
Truncate table CicmpyGeoUpdate
Enjoy using geography data. Later on I will post a blog about using geography data with SQL 2008 R2 Reporting Services.
5 comments:
Thanks for sharing this huge information. You know, I was also looking for such kind information to do my dissertation writing task. Believe me, I was looking for it for some days, but could not get it. thanks to you for providing. I am sure now I will be able to complete my dissertation writing task on my own.
Can you please tell me what is diablo. Honestly speaking, the name itself is very interesting but I do not what is diablo. I have even searched about it but sill doesn’t get it. I even wish to do more research but I can’t because I have to find master thesis help online service to complete my thesis writing task.https://dissertationsky.co.uk/special-education-dissertation-topics/
I must say that this is an excellent post, and I appreciate the information provided on "How to enrich your account data with latitude, longitude, and geography data?" You particularly noted one of the difficulties raised during the discussion of the 18 Years Scale. I'd like to read more of your arguments because they're so persuasive. But right now, I'm seeking for the marketing dissertation topics service to help me with a homework assignment. This is advantageous to me, as it allows me to do my assignment more rapidly. The vast content on the website can assist all students.
Consider using reputable geocoding services or APIs to supplement your account data with latitude, longitude, and geographic information. These solutions work smoothly with your data management systems, giving precise geographical information. For a full guide to optimising account data enrichment, look into resources accessible when you need them, such as obtaining professional help to "write my dissertation for me," assuring thorough and expertly created insights.
Streamlined and swift - that's the essence of effective venture capital for small business. It's about injecting capital efficiently to fuel growth and innovation, propelling budding enterprises towards success.
Post a Comment