Creating a Table
Spectrum Spatial™ Manager allows you to create a named table for a file-based (TAB and shapefile) or JDBC database datasource. These instructions explain how to create a single, non-spatial named table or one with a datasource that has a spatial column.
To create a named table in Spectrum Spatial™ Manager:
-
From the Create drop-down located in the toolbar, select
Table.
Note: This is disabled if you do not have sufficient permissions.The Create Table page appears.
-
In the Choose a Datasource section, select a connection
from the Connection list.
Information about the connection appears to the right, including its location in the repository, and its source folder (where the data it points to is located) for a file-based connection or its URL for a JDBC database connection.
When creating a named table for an Oracle, PostGIS, or SQL Server connection, there is a choice of:
- Seeing a datasource list and picking an existing database table or database view from the database or
- Entering your own database specific query
-
From the Datasource list, select the source of the data
for the named table.
Note: You can only select a GeoPackage table as a datasource if you are running Spectrum Spatial on Windows or Linux (CentOS).Information about the datasource appears to the right.
If you choose to enter your own database query, enter the query into the text area. It will be validated when you try to save the named table.
-
In the Settings section:
-
Choose whether to change the Volatile flag or use the default.
The Volatile flag is enabled by default. This setting is appropriate for data sources that are frequently updated. Spectrum Spatial checks with the data source to see if the table schema can change, and if so, empties the cache and reloads the table, although at some expense to performance. Disable the Volatile setting only for tables that you know are not going to change over time or change infrequently. Disabling volatility aids in data access performance, especially for batch operations such as map tile generation. See Data Source Volatility for more information.
- Choose whether to set the table to be read-only. This is applicable only to MapInfo native TAB and extended native TAB (NativeX) files in a Windows operating system. For more information, see MapInfo Native TAB.
- Choose whether to enable spatial aggregation (applicable only to MS SQL Server data sources). For more information, see Support for Spatial Aggregation.
-
Choose whether to change the Volatile flag or use the default.
- If you selected a shapefile as a datasource, the Additional Info section appears. Character Set and Coordinate System are required fields. The Character Set field is populated with the value read from the shapefile's .dbf or .cpg file. If the shapefile does not have a character set, then the field defaults to a character set of ISO-8859-1. The Coordinate System field is populated with the value from the shapefile's .prj file. If the coordinate system is missing or cannot be understood, then the field defaults to a coordinate system of epsg:4326. By default, Spectrum Spatial creates a spatial index for your shapefile to improve performance of spatial queries. Disable this setting if you are concerned about performance loss due to the initial creation of the spatial index file.
-
The Column Hints fields are optional and are shown when
you choose a JDBC connection. Following are the supported input fields:
-
Primary Key Column: Specify the Primary Key in the text box. It
is required if the table is not able to identify the primary key
automatically. In that case, the user can come back and specify the
primary key. The Primary Key column text box is applicable only if a
JDBC connection was selected.
The following fields will be displayed only for Database Query and if the Connection is of Oracle, SQL Server, or PostGIS.
-
Geometry Column: Specify the name of the geometry column which
is present in the SELECT clause of the SQL.
For SQL Server, if the geometry column is not specified, then the geometry will be returned as binary.
- Coordinate System: Pick the coordinate system of the geometry stored in the table. This value is mandatory if you have specified the Geometry Column.
-
Table SRID: SRID is applicable only for Oracle connection.
Specify the SRID in the following conditions:
SRID of the EPSG code from selected Coordinate System SRID from ALL_SDO_GEOM_METADATA Table Both are same Table SRID Text Box entry Yes Yes Yes Leave blank Yes No Not applicabale Leave blank Yes Yes No Enter the SRID from ALL_SDO_GEOM_METADATA No Yes Not applicabale Enter the SRID from ALL_SDO_GEOM_METADATA No No Not applicabale Enter 0 - Specify the table SRID from the Oracle
ALL_SDO_GEOM_METADATA table if
- It is different from the SRID of EPSG code chosen under coordinate system or
- There is no EPSG code listed for the chosen coordinate system
- Specify 0 if
- There is no EPSG code for the chosen coordinate system and
- There is no SRID in the Oracle ALL_SDO_GEOM_METADATA table
- Leave blank if
- The SRID of EPSG Code for the chosen coordinate system and the SRID in the Oracle ALL_SDO_GEOM_METADATA table have the same value or
- There is no SRID specified in the Oracle ALL_SDO_GEOM_METADATA table
Obtaing SRID: You can obtain SRID in one of the following ways:
- Method 1: Obtain the SRID from the ALL_SDO_GEOM_METADATA table using the query (SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE OWNER= <OWNER_NAME> AND TABLE_NAME= <TABLE_NAME>).
- Method 2: Obtain the SRID from EPSG code using the query (SELECT SDO_CS.MAP_EPSG_SRID_TO_ORACLE(<EPSG_CODE>) FROM DUAL)
- Specify the table SRID from the Oracle
ALL_SDO_GEOM_METADATA table if
-
Primary Key Column: Specify the Primary Key in the text box. It
is required if the table is not able to identify the primary key
automatically. In that case, the user can come back and specify the
primary key. The Primary Key column text box is applicable only if a
JDBC connection was selected.
- In the Repository Info section, the New Table Name field uses the name of the chosen datasource as the default. You can accept the default name or change it. Spectrum Spatial™ Manager will warn you if another named table exists with that name. Names are case sensitive and cannot contain the following characters: / \ : [ ] | * { } "
- The Repository Folder field defaults to the location you were at in the repository when you selected Create > Table. You can accept the default path or change it.
-
Click Create.
The details page for the new named table appears.