Using MS SQL 2005 as the data store in GeoServer

Question

In my project, I am trying to set up a simple WFS service in GeoServer in order to create a layer consisting of point features. I have succeeded doing so with the points being stored in a shapefile. However, now I need the data to be stored in MS SQL 2005.

I know that unlike the 2008 version, MS SQL 2005 does not support geometry/geography. Googleing for some advice, I kept encountering this article:

https://www.seegrid.csiro.au/wiki/Infosrvices/GeoserverMsSqlServer2005Connectivity

I followed the instructions, installed the MsSqlSpatial extension to MSSQL and the MSSQL plugin into GeoServer. Indeed, now I can add my database as a store into GeoServer and publish a table from it as a layer. However, this does not work entirely.

The table consists of several columns, most of them containing metadata like "town", "street" etc., one of them named "the_geom" contains the points themselves. The points are stored in the WKB (well known binary) format, so the datatype of "the_geom" column is varbinary(max). However, GeoServer doesn't seem to like this. When trying to publish the layer, GeoServer correctly reads all the columns with the metadata, but for the "the_geom" column, it publishes the message:

Could not find mapping for 'the_geom', ignoring the column and setting the feature type read only

The last chapter of the article I posted suggests to use the WKT (well known text) format to represent the points. In this case, the type of the database column would be Text. When trying to publish the layer, GeoServer recognizes the "the_geom" column, but with the type 'String' instead of 'Point'. Thus, when I request the WFS service with parameter geometryName = "the_geom", GeoServer throws an exception stating that

Property the_geom is not geometric

Have I misunderstood something? Is there another datatype to use for the points in the database? I have tried different approaches and none worked for me; however, the author of the given article claims he successfully managed to connect MS SQL 2005 to GeoServer, so it should be possible.

Finally, please do not reply in the manner "Upgrade to 2008". I want to. Or I would use PostGis. But, in this case, I just can't.

Thank you for your help in advance.

Imp

Answer 1

Well, I have read the article again and I think I finally understand. Indeed, converting the points from WKB to WKT is necessary, as in the former case GeoServer will ignore the varbinary(MAX) column. In the latter case, GeoServer will publish the point - marked in WKT - as an attribute (metadata) of type String. Therefore, when requesting the WFS service, the point coordinates are really present in the returned XML, they are just not marked as <gml:point>. It is still possible to parse them out manually, if one wishes to.

However, it is probably not possible to persuade GeoServer to recognize that this column is a geometry and not some metadata. Therefore, technologies such as OpenLayers which do expect to find a <gml:point> tag in the returned XML will not work with this.