These steps rely on using an iShare Studio instance whose Data Share database is your Spatial Data Warehouse e.g.
You need to have installed the Astun Core PostgreSQL functions - see the section on Installation of Astun Core functions. |
First you need to grant permission for SDW Admin staff to write to the public schema in your SDW. This can be done either via pgAdmin III or by creating a Stored Procedure Task in Studio using the
astun_wkf_executecmd function (from v6.0.0 this is called wkf_executecmd) with the cmdtoexecute parameter similar to the following:
|
Now you can use the following steps:
Create a Stored Procedure Task to drop the table in the new schema using the astun_wkf_executecmd function with the cmdtoexecute parameter similar to the following:
|
Create another Stored Procedure Task to move the table from the public to the new schema. This is done using the astun.sdw_move_table function. From v6.0.0 select astun from the Function filter and pick sdw_move_table.
This has the following parameters:
schemasource e.g. public
schemadestination e.g. myschema
tablename e.g. mytable
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.
From the Postgresql bin folder (C:\Program Files (x86)\Postgresql\9.5\bin) run pg_dump.exe -host <host> -port <port> -table <table> -U <user> -f <path/file.sql>. <iShareData_database_name> This will dump the table to a file.
pg_dump.exe --host localhost --port 5433 --username postgres --file "C:\export\schools.sql" --table education.school "iShareDataWorkshop" |
From the same folder run psql.exe -U <user> -f <path/file.sql> <sdw_database_name> to load the file into the SDW database.
psql.exe -U user_admin --port 5434 -f "c:\import\schools.sql" sdw |
Use an admin user to import the data. |