Library Function

Handles the geometry, expressed for example as a database blob (an array of bytes) to strings and vice versa. The text (character string) is the geometric data is stored and read in WKT format, see, specifications for OLE SFS or SFS for SQL.
The library also provides conversion of data in binary form between proprieterním format Intergraph GDO and open WKB format.
Both of these conversion functions can be combined and perform any data conversion required.

Note: The library operates either independently or as an integral part of TSQL in sqlserver (from the 2005 version above). If used alone, is the disadvantage that it uses the database data types. NET CLR both input and output, because it is primarily intended for the database.

Installation Procedure

The library is installed directly on the database server. Can be installed into any folder on the condition that folder will have read access to the account under which sql server is running itself. There is therefore a practical solution to save the library directly to the folder where installed sqlserver, mostly:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn.

Installation requires administrator privileges on the DB server and access to the file system server.

The library is always installed into a specific database and never for whole database server as would seem logical. The reason is that access rights for ordinary users are set up at the database level and not at the server.

For convenience. installation comes with install.sql script that contains all the necessary commands with explanations.
After proper installation of the library is immediately ready for use.

Note: To upgrade you need to remove previous versions of sql server (see installation script), the disk copy the new version and re-register the new version.

Use of library

The use of the library is then very easy and intuitive and requires no special documentation, just the normal SQL skills. Library helps with writing a spatial (geometric) data into the database by converting WKT string representation of geometry to a database blob format GDO Intergraph (GeoMedia technology) or the transfer of geometry between the binary representations of Intergraph OGC WKB and GDO.

A specific data warehouses built on the technology Geomedia is that when inserting geometry in the database must meet a minimum total of 6 columns:
• Primary key (indeed it is mandatory for GeoMedia)
• Custom blob geometry (for example, a column titled Geometry)
• 4 columns with minimal bounding rectangle of geometry, the MBR (according to example the columns designate Geometry_xlo, Geometry_ylo, Geometry_xhi, Geometry_yhi)

In the sql script to recommend the following syntax for data entry:

DECLARE @INPUTGEOM nvarchar (max)

SET @INPUTGEOM = 'POINT (-64,523,423.345 -9452234.343)';

INSERT INTO FP_MereniGPS (ID, Geometry, Geometry_xlo, Geometry_ylo, Geometry_xhi, Geometry_yhi)

VALUES (1, uhulSpatialUtils.Wkt2gdoBlob (@ INPUTGEOM) uhulSpatialUtils.getEnvelopeCoordinate (@ INPUTGEOM, 'XLO'), uhulSpatialUtils.getEnvelopeCoordinate (@ INPUTGEOM, 'ylo' ) uhulSpatialUtils.getEnvelopeCoordinate (@ INPUTGEOM, 'xhi'), uhulSpatialUtils.getEnvelopeCoordinate (@ INPUTGEOM, 'yhi'))

NOTE: when primary key is the automatic number is not necessary to insert PK values.\

The library uses the relational syntax to work with spatial data (data is simple blob), which implies the need to send a text on geometry input for the calculation of each value separately MBR.
It is not possible to use custom data types, because they are in sqlserver 2005 limited the size of 8 kilobytes and geometric data are almost always larger.

Reading data from a database is much easier:

SELECT uhulSpatialUtils.gdoBlob2Wkt (geometry) as WKT FROM FA_MereniGPS

The function can also be used for batch export and import data to and from sqlseveru which can convert a geometry to WKT format so that data can be used in other relational databases.

Functions for geometry validation

This function performs a very simple geometry validation. It does not check a complete topological accuracy, but focuses on the basic deficiencies such as:
• The line has only 1 point
• The area has only 2 points
• Start and end points, or holes in the surface area is not identical

Therefore solves only errors that could prevent the storage of geometry to the database.

Hybrid Database

Library can also be easily used for the operation of hybrid systems. Example, if the required maintenance data in WKB format and presentation in GeoMedia Webmap, data can be physically stored in WKB format (for example, by Topol) and with views of transfer functions using the library and then create GeoMedia metadata present in the GeoMedia products. Library performance is optimized for these operations.

Czech specifics ;-)

Geomedia and data structure used by sqlserver, defines a coordinate system in s-JTSK 3rd-quadrant, both coordinates must therefore be with-JTSK negative. Therefore, please be sure to insert check mark coordinate geometry.

Number of coordinate values must be in English convention, rather than decimal comma, with decimal point.

The library is optimized for maximum performance data transfers, but we experimentally found that within our data exist geometries so large, that their conversion to WKT format is not possible because it exceeds the limit of varchar (MAX) (or this is a bug in. NET ??)

Last edited Jul 15, 2011 at 7:44 PM by asida, version 3


No comments yet.