Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
Try 'the_geom' column 'MULTILINESTRING' instead of String (Plain Text?) - could it be that simple? –  Mapperz Feb 10 '12 at 15:35
    
No, there isn't such datatype in MS SQL 2005. And if it was, it would still probably be a string for GeoServer. –  Imp Feb 13 '12 at 10:38
    
Are you just dealing with lat/lon point datasets? –  geographika Feb 13 '12 at 13:25
    
Yes. The previous implementation of the project simply defined each point in the database as a pair of integers. However, if I want to use WFS from GeoServer, this is not sufficient. –  Imp Feb 13 '12 at 14:28

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.