This article will help you to setup your spatial warehouse project and configure the warehouse task in order to automate tasks and create history from your objects in your SQL Server database.
- Ultimate edition of the desktop product
- Microsoft SQL Server / SQL Server Management Studio
- XY or GSA Server
Step 1: Setting up an SQL Server database
Note about using SQL Server for your Spatial Warehouse
SQL Server is not the only database you can use in combination with Spatial Warehouse.
Oracle and PostGIS are also supported databases when using the Spatial Warehouse. Configuring Oracle or PostGIS is very similar to SQL server, following the steps for the SQL server database should also suffice to configure an Oracle or PostGIS database for your Spatial Warehouse.
First we have to start SQL Server Management Studio and create a new database for this project.
When starting SQL Management Studio, you will be prompted to login, which most likely will be windows authentication if you are working locally. Otherwise, you can use specific server credentials to log in.
When you are logged in, right-click on databases and create a new database.
The default settings are fine, we name the database ‘Demo Warehouse’ and click ‘OK’.
After that, click on the new database in the menu and select “New Query” and type ‘create schema History’ and ‘create schema Staging’.
Now there will be two added schemas in the database, History and Staging.
Step 2: Setting up the warehouse data source in the projectfile
Start Spatial Workshop and add a new data source and select Spatial Warehouse Database
Select the database type (in this case an SQL Server database) and enter the credentials
Enter the database details.
For the staging settings, you should leave the Tablespace/Filegroup box empty.
At the staging schema you can select the staging scheme that you created earlier.
The version settings can be left on the default settings:
Model: All Models use All History Tables.
Mode: Select time automatically.
Click on OK and complete adding the feature source.
Next, click on the warehouse feature source again and select “configure”.
Go to the management tab, click on ‘manage models’ and create a model for your warehouse. You can enter the name, EPSG code and description according to your data source.
When the model is created, click on OK and close the management settings. (You can check the batches settings, but currently it will be empty).
After that, configure the business collection to suit your needs.
In this example, we used a shapefile and an excel sheet to create a business collection on municipalities with the population numbers for each municipality. To demonstrate the differences shown when an excel number gets changed in the source data and the warehouse task runs.
When the business collection is configured, please make sure there is an index on the unique key value.
We are now finished configuring the project file. Let’s move on to the Lite web client and XY server to set up the server and warehouse task.
Step 3: Setting up the XYserver
For detailed instructions on how to install the XY or GSA Server and Lite web client, please visit the article: https://support.spatial-eye.com/hc/en-us/articles/360001371392-Installing-the-Lite-web-client
When the XY server is installed and the licenses are set, please check the XYserver.config file to make sure the path is set correctly.
When you open the XYserver config file, you can see the “startupProject path”” , which in our case is ="D:\Projecten\20180521_SpatialEye\3_Spatial Warehouse\Projectfile\Spatial Warehouse\Spatial Warehouse.seproject"
Also make sure the default port is set correctly, according to how you installed your SSL certificate for your webserver. You can use a self-signed certificate for this.
For more info on the SSL / HTTPS configuration, please visit https://support.spatial-eye.com/hc/en-us/articles/115010019388-Server-HTTPS-Configuration
In this example we use port 8080 for our server. When the config file is configured, start the XY or GSA server.
If the settings are configured correctly, the server will start and you will see the confirmation that server started.
The XYserver is now running on the local address.
Note about using server as a service
When you have the correct licenses, you can also install the XY or GSA server as a windows service. This is preferred for server environments as it’s easier to maintain long running applications and it has many other advantages.
To install the XY or GSA server as a windows server, use the command prompt to navigate to the XYserver folder. Here you can type “xyserver /h” for additional helpful commands.
To install it as a service, type “xyserver /install /name “[name]” /serviceport [port number].
Step 4: configuring the warehouse task in the management client.
When the server is up and running, go the server management client to configure the warehouse task.
The the server manager is available on the endpoint address: [computer name]:[port]/management.
For this example we are running the server locally, so it will be
Go to the last tab on the left bottom, which is “Tasks” to configure the Warehouse task.
When you click “Add” in the upper right, to add a new task, select the ‘Spatial Warehouse Builder’ task.
There are other types of tasks as well, and the Start Type can be configured here (once, daily, weekly, etcetera).
Now you can configure the warehouse task, according to your project file.
Warehouse Target: This is the name of the feature source.
Model ID: The number you configured in the “Manage Models” tab. The first one will be usually 1.
Select Recent Project: Select the most recent project store, leave this on ‘false’.
Project Store: The location of the project store.
Project: The name of the projectfile
Force Full Sync: Do a full sync instead of table differences only. Set to ‘true’.
Timestamp: You can use this for custom timestamps.
Force Timestamp on all tasks: This overrides the timestamp on all tables.
Max concurrent SQL: Maximum SQL script. Default =4.
Max concurrent Staging: Maximum staging tasks. Default =4.
Max staging record failures: Maxium failure records per collection. Default = 10.
Switch logfile: Create a new logfile when a task starts. Default = false.
According to the project file that we set up, the settings will be configured as shown in the screenshot above.
Now run the task by pressing “Start”. In the logging you can follow the progress of the task.
When the task has finished, you can inspect the results in the projectfile in the “batch management” setting.
The state should say ‘Finished’ if everything has been processed smoothly.
After that, setting up the warehouse is completed.
Let’s change some source data, for example the population of Amsterdam, in our Excel sheet in order to look at differences between batches. After you changed some population numbers in the Excel Sheet, run the task again.
Now, open a new project file and add the Spatial Warehouse feature source.
Make sure you select the previously created Model number.
Create the business collection of the source table and select “Collection Differences”. You will see the collections and the amount of records that are inserted, updated and deleted. You can filter between batches, or timeperiods, depending on what information you want to see.
If you click on the blue text, the result list will show what changed in the record.
In the red you will see the results of the previous batch, that is now historicized.
In the green you see the current values in the table. You will see the population of Amsterdam has been updated with a new value!
Now you can use this business collection to control, analyze and further build upon the automated calculated differences over time with the Spatial Warehouse task.