Import Uniform data into Earthlight
In this short guide we will describe the steps that need to be taken to migrate data from Uniform database into Earthlight. Because Uniform stores geometry in ESRI proprietary format Earthlight cannot read data straight from the database and instead need to collate business data from the database table with geometries from a dataset in open standard format, namely Shapefile.
- First, you have to localize the table in Uniform database which you want to import into Earthlight. Make note of the connection string to this database and table since it will be required later on.
- Now you have to export the geometries from the table. We don’t know if you can pick the columns being exported or if the export procedure automatically includes all fields in the Shapefile. If the first is true please, in addition to the geometry column, pick a unique column which will allow StatMap DataPump to identify a record when comparing it with the database table (usually this is the KEYVAL column).
- Now it’s time to prepare the Collation script. You will need the location of the Shapefile, the connection string to the Uniform database and list of table fields (columns) which you want to import into Earthlight.
Uniform specific Collation script merges the business attributes read directly from the Uniform database with geometries from the previously exported Shapefile.
- Connection string to the MasterTable – this should always point to the database
- List of business attribute fields from MasterTable which will be imported into Earthlight
- Connection string to the DetailTable – this will always be the Shapefile where the geometries are stored
- The common field – this is the name of the column which will be used to identify records in both sources and collate them
- List of the columns from DetailTable which will be imported into Earthlight – in Uniform scenario it only lists the GEOMETRY column
Once you have the .collation file prepared you can prepare the other files.
First is the .script file:
- In the Source section enter the full path to the .collation file.
- In Destination enter the name of the table in Earthlight database.
Next is the .destination file:
This file contains the connection string to the Earthlight database without the table name= entry. Please note that connection string should be entered as a single line. In the screenshot above the Notepad has line wrapping enabled to make it more readable.
The last file is the batch file which will execute the .script file via StatMap DataPump and can be added to the Windows Task Scheduler to be run on a regular basis.
This is a standard script execution batch file. First you provide the path to the DataPump.exe program. Next comes the path to the .script file. The last part is optional but it is useful if the script is executed on a daily basis to be able to determine if the import process was successful. Quotation marks around any section are required if the path contains whitespace characters. Please note that all this should be entered as a single line. In the screenshot above the Notepad has line wrapping enabled to make it more readable.
The final result of such collation script will be a table in Earthlight specific database consisting of all records and their chosen business attributes from the MasterTable and geometries from the Shapefile dataset defined in the DetailsTable which DataPump was able to match to the records based on the DetailColumnName and MasterColumnName values.