Loading a Non-spatial table into the SDW V1.5

Owned by Kim Stimpson
Last updated: 29 Sept, 2022
2 min read

iShare Studio where Data Share database is your SDW

These steps rely on using an iShare Studio instance whose Data Share database is your Spatial Data Warehouse e.g.

  • Studio ETL
  • Studio on an internal SDW Server.

Steps:

  • Open Studio
  • Right click on the Data Share Connections node and select Add Datasource. Please follow the instructions for Adding a new Datasource in the on-line Help for the type of datasource you wish to connect to.
  • Once you have created the Data Share Connection you can either right click on it and select Synchronise to populate the SDW or, if you want to schedule this, create a Workflow Job and Task. This copies the data to the public schema in your SDW.
  • You now need to move this to a different schema within your SDW.
  • Check that your have a Workflow Connection to your SDW and if not create one.

  • Create a Stored Procedure Task to drop the table in the new schema using the at_wkf_executecmd function (from v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter) with the cmdtoexecute parameter similar to the following:

    DROP TABLE IF EXISTS schema_name.table_name;

  • Create another Stored Procedure Task to move the table from the public to the new schema. This is done using the at_sdw_move_nonspatial_table function e.g.

iShare Studio where Data Share database is not your SDW

The easiest way of migrating non-spatial data into the SDW is to use Studio to define a Data Share Connection. A Data Share Connection in Studio will create a connection to numerous different sources. Syncing a Data Share Connection will migrate the data to a public table of the same name in the public schema of the local iShareData database. PostgreSQL commands are then used to move the table to the SDW database.

  • Open Studio
  • Right click on the Data Share Connections node and select Add Datasource. Please follow the instructions for Adding a new Datasource in the on-line Help for the type of datasource you wish to connect to.
  • Once you have created the Data Share Connection right click on it and select Synchronise.
  • From the Postgresql bin folder (C:\Program Files (x86)\Postgresql\9.0\bin) run pg_dump.exe -table <table> -U <user> <iShareData_database_name> -f <path/file.sql>. This will dump the table to a file.
  • From the same folder run psql.exe -U <user> -f <path/file.sql> <sdw_database_name> to load the file into the SDW database.
  • Create a Workflow Stored Procedure Task.
  • Select at_sdw_update_schema_ownership_and_privileges from Astun.
  • Select the schemaname parameter and enter the name of the schema where you uploaded the table into the Edit parameter 'schemaname' box e.g.

  • Run task.