Spatial Engine SQLServer

A spatial engine provides access to a service that can perform spatial operations, one of the available spatial engines is for SQLServer 2008.

Namespace

urn:com.cohga.server.spatial.geotools#1.0

Tags

spatialengine

Properties

Name	Type	Required	Description
id	string	yes	Unique identifier
dbtype	'sqlserver'	yes	The name of the driver to use, in this case sqlserver
host	string	yes	The hostname or ip address of the SQL Server server
port	number	yes	The port number that the SQL Server instance is running on
database	string	no	The database to be connected to
instance	string	no	The SQLServer instance to be connected to
schema	string	no	The schema used when connecting to the database
user	string	yes	The userid used when connecting to the database
passwd	string	yes	The password used when connecting to the database
namespace	string	no	The namespace prefix
minconnections	integer	no	The minimum number of pooled connection. Default 1
maxconnections	integer	no	The maximum number of open connections. Default 10
validateconnections	boolean	no	Check connection is alive before using it. Default false
fetchsize	integer	no	The number of records read with each iteraction with the dbms. Default 1000
connectiontimeout	integer	no	The number of seconds the connection pool will wait before timing out attempting to get a new connection. Default 20 (seconds)
primarykeymetadatatable	string	no	The optional table containing primary key structure and sequence associations. Can be expressed as 'schema.name' or just 'schema' (if the table name is 'GT_PK_METADATA'). Default none
maxopenpreparedstatements	integer	no	Maximum number of prepared statements kept open and cached for each connection in the pool. Set to 0 to have unbounded caching, to -1 to disable caching. Default 50

Sub-tags

None

Content

None

Examples

Basic SQL Server connection

<spatial:spatialengine id="sqlserver">
  <dbtype>sqlserver</dbtype>
  <host>sqlhost</host>
  <port>1434</port>
  <user>gis</user>
  <passwd>hak0rz</passwd>
  <schema>GIS</schema>
  <minconnection>2</minconnections>
  <validateconnections>true</validateconnections>
</spatial:spatialengine>

SQL create statement for gt_pk_metadata table

CREATE TABLE gt_pk_metadata (
  table_schema varchar(255),
  table_name varchar(255),
  pk_column varchar(255),
  pk_column_idx int,
  pk_policy varchar(255),
  pk_sequence varchar(255)
)

pk_policy can be 'assigned', 'sequence' or 'autogenerated' depending upon how the key column value is generated. The pk_sequence value only need to be set if the policy is 'sequence'.
'sequence' means that the value for the column is generated using a database sequence, and the 'pk_sequence' value must be set.
'autogenerated' means that the value for the column is generated by the database using another method.
'assigned' means that the value for the column is determined by the current maximum value +1, if the column is an integral type, or as a random string if the column is textual.

Column	Description
table_schema	Name of the database schema in which the table is located.
table_name	Name of the table to be published
pk_column	Name of a column used to form the feature IDs
pk_column_idx	Index of the column in a multi-column key. In case multi column keys are needed multiple records with the same table schema and table name will be used.
pk_policy	The new value generation policy, used in case a new feature needs to be added in the table ('assigned', 'sequence' or 'autogenerated').
pk_sequence	The name of the database sequence to be used when generating a new value for the pk_column.