Page Comparison

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

Warning
Weave does not currently support the SQL Server `geography` column type.

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

host
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

SQL Server instance to be connected to
integratedsecurity	boolean	no	Login as current windows user account. Works only in windows. Ignores user and password settings.
url	string	no	JDBC URL connection string, full URL that completely replaces the URL constructed from the settings listed above (host, port, database, instance and integratedsecurity)
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

minConnections

integer

no

The minimum number of pooled connection. Default 1

maxconnections

maxConnections

integer

no

The maximum number of open connections. Default 10

validateconnections

validateConnections

boolean

no

Check connection is alive before using it. Default false

fetchsize

fetchSize

integer

no

The number of records read with each

iteraction

interaction with the

dbms

DBMS. Default 1000

connectiontimeout

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

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

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

geometrymetadatatable

geometryMetadataTable	string	no	The options table containing geometry column metadata information.
useNativeGeometrySerialization	boolean	no	Use native SQL Server serialization, or WKB serialization. Default is false.
forceSpatialIndexUsageViaHints	boolean	no	When enabled, spatial filters will be accompanied by a WITH INDEX sql hint forcing the usage of the spatial index. Default is false.
tableHints	string	no	These table hints will be added to every select query.
useNativePaging	boolean	no	Use native paging for SQL queries. For some sets of data, native paging can have a performance impact.
trustServerCertificate	boolean	no	If true (or not set) the connection URL will include the TrustServerCertificate=true flag. Available since 2.6.9

Sub-tags

None

Content

None

Examples

Basic SQL Server connection

Code Block

xml

language	xml
title	Basic SQL Server connection

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

SQL Server connection using a URL

title

Code Block

language	xml

<spatial:spatialengine id="sqlserver">
  <dbtype>sqlserver</dbtype>
  <url>jdbc:sqlserver://sqlhost:1434;DatabaseName=INTERNAL</url>
  <user>gis</user>
  <passwd>ENCSMAAFGJGAMGESBUK</passwd>
  <schema>GIS</schema>

Code Block

sql

  <minconnection>2</minconnection>
  <validateconnections>true</validateconnections>
</spatial:spatialengine>

Note
Note: Because of a bug in the `org.geotools.sqlserver` plugin you still need to include the <host> tag when using the URL connection type, even though it will be ignored. Weave 2.6.9.5 and 2.6.10, which include version 13.4.8.20231123 of the above plugin, resolves that issue.

Row Identifiers

Weave needs to be able to uniquely identify each row in a particular table, normally it would do this using the primary key of the table, but sometimes a primary key has not been specified so this information must be provided another way.

This can be done by creating a primary key metadata table and populating it with a row for each table that will be exposed to Weave and then setting the primarykeymetadatatable attribute for the spatial engine.

SQL create statement for gt_pk_metadata table

Code Block

language	sql

CREATE TABLE gtGT_pkPK_metadataMETADATA (
  tableTABLE_schemaSCHEMA varchar(255) NOT NULL,
  tableTABLE_nameNAME varchar(255) NOT NULL,
  pkPK_columnCOLUMN varchar(255) NOT NULL,
  pkPK_columnCOLUMN_idxIDX int NOT NULL,
  pkPK_policyPOLICY varchar(255) NOT NULL,
  pkPK_sequenceSEQUENCE varchar(255),
   UNIQUE(TABLE_SCHEMA, TABLE_NAME, PK_COLUMN)

...

,
   CHECK(PK_POLICY IN ('ASSIGNED','

...

SEQUENCE'

...

, '

...

AUTOGENERATED') )
)

Primary Key Metadata

If the primary key for a table can not be determined by Weave directly from the database then the information can be provided by creating a table or directly embedding the information in the spatial engine configuration.

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'). Where: '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.
pk_sequence	The name of the database sequence to be used when generating a new value for the pk_column. The pk_sequence value only need to be set if the pk_policy is 'sequence'.

Using primary key metadata configuration

You can also specify the above information directly in the spatial engine configuration.

Name	Description
name	The table name
schema	The optional table schema
column	The column in the the table that contains the unique id
policy	How the key is generated
sequence	The name of the database sequence to use if the policy is 'sequence'

Code Block

language	xml

<spatial:spatialengine id="sqlserver">
  <dbtype>sqlserver</dbtype>
  <host>sqlhost</host>
  <port>1434</port>
  <user>gis</user>
  <passwd>hak0rz</passwd>
  <schema>GIS</schema>
  <minconnection>2</minconnection>
  <validateconnections>true</validateconnections>
  <primarykeymetadata>
    <table schema="dbo" name="park" column="gid" policy="autogenerated"/>
    <table schema="dbo" name="property" column="park_id" type="autogenerated"/>
    <table schema="dbo" name="road" column="objectid" type="autogenerated"/>
  </primarykeymetadata>
</spatial:spatialengine>

Using the geometry metadata table

The SQL server data store can determine the geometry type and native SRID of a particular column only by data inspection, by looking at the first row in the table. Of course this is error prone, and works only if there is data in the table. The administrator can address the above issue by manually creating a geometry metadata table describing each geometry column. Its presence is indicated via the SQL Server datastore connection parameter named geometrymetadatatable (which may be a simple table name or a schema-qualified one). The table has the following structure (the table name is flexible, just specify the one chosen in the data store connection parameter):

Creating geometry metadata table

Code Block

language	sql

CREATE TABLE GT_GEOMETRY_COLUMNSMETADATA (
   F_TABLE_SCHEMA VARCHAR(30) NOT NULL,
   F_TABLE_NAME VARCHAR(30) NOT NULL,
   F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL,
   COORD_DIMENSION INTEGER,
   SRID INTEGER NOT NULL,
   TYPE VARCHAR(30) NOT NULL,
   UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
   CHECK(TYPE IN ('POINT','LINELINESTRING', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINEMULTILINESTRING', 'MULTIPOLYGON', 'GEOMETRY') )
);

When the table is present the store first searches it for information about each geometry column to be classified, and falls back on data inspection only if the table does not contain any information.

The SRID column should contain the EPSG code for the projection that the data for the layer is stored in.

Using geometry metadata configuration

As of Weave 2.5.16 it's possible to specify the information contained in the geometry metadata table directly in the spatial engine configuration.

Name	Description
name	The table name
schema	The optional table schema
type	The geometry type (point, linestring, polygon, multipoint, multilinestring, multipolygon)
srid	The geometry srid
dimension	The geometry dimension

Using geometry metadata configuration

Code Block

language	xml

<spatial:spatialengine id="sqlserver">
  <dbtype>sqlserver</dbtype>
  <host>sqlhost</host>
  <port>1434</port>
  <user>gis</user>
  <passwd>hak0rz</passwd>
  <schema>GIS</schema>
  <minconnection>2</minconnection>
  <validateconnections>true</validateconnections>
  <geometrymetadata>
    <table schema="dbo" name="park" type="multipolygon" srid="28355" dimension="2"/>
    <table schema="dbo" name="property" type="polygon" srid="28355" dimension="2"/>
    <table schema="dbo" name="road" type="linestring" srid="28355" dimension="2"/>
  </geometrymetadata>
</spatial:spatialengine>

Versions Compared

Old Version 14

New Version Current

Key

Tags

spatialengine

Properties

Sub-tags

Content

Examples

Basic SQL Server connection

SQL Server connection using a URL

Row Identifiers

SQL create statement for gt_pk_metadata table

Primary Key Metadata

Using primary key metadata configuration

Using the geometry metadata table

Creating geometry metadata table

Using geometry metadata configuration

Using geometry metadata configuration