[FrontPage] [TitleIndex] [WordIndex] 

Accessing MS SQLServer with spatial capabilities

Contents

  1. SQL Server Installation
  2. Accessing SQL Server via JDBC
  3. Using large text fields
  4. Handling Geomerties
    1. Inserting and reading geometries
    2. Using spatial indices for topological operations
    3. Known problems and possible solutions
  5. Useful SQL snippets
  6. Links

1. SQL Server Installation

For installing SQL Server complete installation package including tools and management software should be used. From Microsoft pages you can download SQLEXPRWT_x86_DEU.exe (or the same installation package for other languages; just enter SQLEXPRWT_x86_DEU.exe to google) or choose an installation through a network installer (using SQLEXPRWT_x86_DEU.exe has been tested). Installation just can be performed on Windows operation systems. Installation took some time but it worked without problems.

After installation 'SQL Server Management Studio' application can be started to create new databases and add new users. To connect to SQL Server from other clients - including JDBC - some additional work is required because though a default installation TCP/IP connection will not be activated. Start 'SQL Server Configuration Manager' application; in section 'network protocols' TCP/IP must be activated. Opening the options of TCP/IP (click with right mouse button on it) in section IP ALL parameters 'Dynamische TCP Ports' and TCP-Port must be set. Try 1967 for the first and 1433 for the second. 1433 is default port assigned to a SQL Server instance but any other valid port number should work too.

2. Accessing SQL Server via JDBC

Because SQL Server has not much in common with Java no JDBC driver is delivered with installation. A driver can be downloaded from Microsoft pages (try entering - sqlserver jdbc 2008 driver download - to google). Beside some documentation the downloaded zip-file contains to jar-files. The one you need is sqljdbc.jar. After adding the library to java classpath SQL Server can be accessed from a Java program like this: 

Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;databaseName=deegree;user=sqlserver;password=sqlserver;";
Connection con = DriverManager.getConnection( connectionUrl );

3. Using large text fields

If a string is too long to be stored in a normal VARCHAR or a NVARCHAR field the type NVARCHAR(MAX) must be used. Field of this type can not be indexed by usual CREATE INDEX operation. Instead a so called nonclustered index must be used: 

CREATE NONCLUSTERED INDEX abstract_idx ON IDXTB_MAIN (ID) include (abstract);

Note that a table must contain a valid primary key (ID in the example) to use this kind of index.

4. Handling Geomerties

4.1. Inserting and reading geometries

In opposite to Oracle or PostGIS SQL Server does not provide an own java geometry model implementation. Because of this access to geometries - for reading and writing - always must use WKTs or WKBs (there is also a GML converter but it has not been tested yet). To use geometries with SQL Server a table must have a column of type GEOMETRY. If a table with a geometry column has been created geometries can be inserted and read like this: 

Statement stmt = con.createStatement();
stmt.execute("INSERT INTO testTable (id,name, Geom) VALUES (1,'a name', geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0))");

...

ResultSet rs = stmt.executeQuery( "select geom.STAsText(), geom.STAsBinary()  from testtable" );
rs.next();
System.out.println( "SQLServer: " + rs.getObject( 1 ) );
byte[] b = (byte[]) rs.getObject( 2 );
WKBReader reader = new WKBReader();
Geometry geom = reader.read( b );
System.out.println( "JTS:       " + geom );
System.out.println( "deegree:   " + JTSAdapter.wrap( geom ) );

Like in Oracle or Postgis geometries has a SRID to indicated the coordinate reference system they use. Just two geometries with same SRID can be compared or combined in geospatial operations like INTERSECTS, UNION etc.. If no SRID is defined 0 will be used as default.

4.2. Using spatial indices for topological operations

If spatial queries should be performed like 'find all objects/rows where assigned geometry intersects with envelope 10,13,55,62' it is useful to create a spatial index on a geometry column. The easiest way to this is: 

CREATE SPATIAL INDEX SIndx_testTable_geom ON testTable2(geom) WITH ( BOUNDING_BOX = ( 17300,4049850,867300,4699850 ) )

SQL Server offers several parameters to create more specific and optimized spatial indexes but for most cases this will do.

IMPORTANT: If a spatial index should be created a table must have a primary key!

Now queries can be performed with spatial constraints: 

PreparedStatement pstmt = con.prepareStatement( "Select id, name, geom.STAsText() from testTable2 where geom.STIntersects( geometry::STGeomFromText(?, 0) ) = 1" );
String s = "POLYGON ((425593 4503920,425593 4505159,427138 4505159,427138 4503920,425593 4503920))";
pstmt.setString( 1, s );
ResultSet rs = pstmt.executeQuery();
while ( rs.next() ) {
    System.out.println( rs.getObject( 2 ) );
}

Notice that boolean operations like STIntersects does not return true or false but 1 or 0.

4.3. Known problems and possible solutions

Form a first test everything seems to work fine but there is a strange behavior with invalid geometries (like polygons with self intersecting boundary). There is no problem inserting invalid geometry even performing a spatial query on it does not cause a problem. But when reading the result of a query by stepping through the result set invoking ResultSet.next() method an Exception will be thrown if the next row contains an invalid geometry. At the moment we think that there are four options to avoid this problem:

5. Useful SQL snippets

Insert statement containing geometry 

INSERT INTO SpatialTable (geography1) VALUES (geography::STGeomFromText('POINT(10 10)',4326));

Check geometry validity 

select geom.STIsValid() from dbo.tablename;

To make invalid geometries valid: 

update dbo.tablename set geom = geom.MakeValid() where geom.STIsValid() = 0;

Check for supported CRSes (The coordinate systems in sys.spatial_reference_systems are for the geography type only) 

select * from sys.spatial_reference_systems where spatial_reference_id in (4258, 4326);

Here are some useful links to get more information about handling spatial data with SQL Server:

CategoryDeegree2

CategoryDeegree3

2018-04-20 12:04