A spatial engine provides access to a service that can perform spatial operations, one of the available spatial engines is for SQLServerSQL Server.
| Warning |
|---|
There may be performance issues when drawing selections with SQL Server. It's possible that altering the Weave 2.5.12 has been updated to resolve this issue. |
Weave does not currently support the SQL Server |
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 |
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 |
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 | 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 |
interaction 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 |
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 | ||||
|---|---|---|---|---|
| ||||
<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
| Code Block | ||
|---|---|---|
| ||
<spatial:spatialengine id="sqlserver"> <dbtype>sqlserver</dbtype> <url>jdbc:sqlserver://sqlhost:1434;DatabaseName=INTERNAL</url> <user>gis</user> <passwd>ENCSMAAFGJGAMGESBUK</passwd> <schema>GIS</schema> <minconnection>2</minconnection> <validateconnections>true</validateconnections> </spatial:spatialengine> |
| Note |
|---|
Note: Because of a bug in the |
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 | ||
|---|---|---|
| ||
CREATE TABLE GT_PK_METADATA (
TABLE_SCHEMA varchar(255) NOT NULL,
TABLE_NAME varchar(255) NOT NULL,
PK_COLUMN varchar(255) NOT NULL,
PK_COLUMN_IDX int NOT NULL,
PK_POLICY varchar(255) NOT NULL,
PK_SEQUENCE 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: |
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 | ||
|---|---|---|
| ||
<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>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> |
...
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 | ||||
|---|---|---|---|---|
| ||||
CREATE TABLE GT_GEOMETRY_METADATA (
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','LINESTRING', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINESTRING', '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.
...
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 | ||||
|---|---|---|---|---|
| ||||
<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>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> |
...