2.4 Setting Up the Spatial Studio Metadata Schema

You must set up a database schema to be the Spatial Studio schema, which will hold metadata used by the Spatial Studio application.

The Spatial Studio schema can be either an existing database schema in which Spatial Studio metadata will be added, or a newly created schema that will be used only for Spatial Studio. The recommended practice is to create a new database user that will be used only by Spatial Studio. The database user for the Spatial Studio schema must have the following privileges:

• CONNECT
• CREATE PROCEDURE
• CREATE SEQUENCE
• CREATE SESSION
• CREATE SYNONYM
• CREATE TABLE
• CREATE TRIGGER
• CREATE TYPE
• CREATE VIEW

Note:

The CREATE TRIGGER privilege is required only if you intend to store and display GeoRaster data in the schema.

For example, when connected as a user with DBA privileges:

SQL> create user SPATIAL_STUDIO identified by <password>;
SQL> grant connect, create procedure, create sequence, create session, create synonym, create table, create trigger, create type, create view to SPATIAL_STUDIO; 

When the Spatial Studio application starts running for the first time, it will generate a configuration file in a folder named .sgtech under the current user’s operating system home folder. The configuration file is named sgtech_config.json. This is where Spatial Studio application will be obtaining and storing the connection details in its metadata schema.

After the connection to the metadata schema is established, the Spatial Studio application will automatically check if the required tables and other database objects are already present, and if their versions are up to date with the latest Spatial Studio application software. If these tables are not found, or if the tables require migration, the Spatial Studio application will run the necessary SQL scripts to create or migrate them, all without any intervention from the user.

The Spatial Studio schema will contain the following tables and views:

• SGTECH_OBJECT: stores all the domain objects for Spatial Studio.
• SGTECH_SYS_TYPE: defines all the known types of domain objects of Spatial Studio.
• SGTECH_SYS_JSON_SCHEMA: stores JSON data schema.
• SGTECH_TASK_QUEUE: stores long-running jobs of Spatial Studio.
• SGTECH_TASK_BROKER: stores job brokers for Spatial Studio.

Note:

Do not add, delete, or edit any of the Spatial Studio tables or views or the data within them. They are automatically maintained by Spatial Studio, and should not be modified by users unless directed by Oracle Support.

Managing Metadata Schema when Running Multiple Instances of Spatial Studio

Spatial Studio prevents you from running multiple application instances each having its own sgtech_config.json file from using the same metadata schema. In case you want to run another instance, it is recommended that you copy the sgtech_config.json configuration file linked to the initial instance to the system where you want to start the new instance.

Otherwise, any attempt to connect to the same metadata schema from an instance running on a different system is prevented by Spatial Studio with the following warning:

The selected repository is already registered to another instance of Spatial Studio. Check the system logs for more details.

In such a scenario, see Recovery from Lost Master Key to resolve the issue.