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