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.

Namespace

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

Tags

spatialengine

Properties

Sub-tags

None

Content

None

Examples

<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>

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 something 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. 

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') )
)

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.

Using primary key metadata configuration

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

The table name

The optional table schema

<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>
  <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):

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','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINE', '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

The table name

schema

The optional table schema

The geometry type (point, linestring, polygon, multipoint, multilinestring, multipolygon)

The geometry srid

The geometry dimension

<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>
  <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>