I try to extract non-spatial data from mssql as spatial data to my GeoServer 2.1.0 installation from a SQL statement looking like this:
SELECT database.table1.id, geometry::STPointFromText('POINT(' + CAST(database.table1.YCOORD AS VARCHAR(6)) + ' ' + CAST(database.table1.XCOORD AS VARCHAR(7)) + ')', 3008) AS geometry FROM database.table1
The above given statement returns good spatial info in mssql management studio but I'm not sure how to get it working in GeoServer (I have to CAST the data as it is stored as integer). Setting up mssql as a store works fine and I know I can access its tables. I then try to execute the statement as a SQL view. That does not work :(
The geometry attribute shows up as byte and I can't change that.
I can't write anything in native SRS and filling in epsg:3008 in declared SRS doesn't seem to do anything when calculating the bounding box, giving the values MIN X & Y as -1 and the MAX X & Y as 0 in native bounds.
@jdeolive - not an option to try trunk at this stage, I have to convince my boss who is a die-hard ESRI-fan and stability is a must.
@unicoletti - I guess I have to read my statements better not to mix geometry and geography objects :-) thank you. It makes sense when calculating bonds, will try it tonight. Creating a view of it is what I have in mind later on. The only thing is that I'm taking my information from another view but as it is only about 6500 points I don't think it will give any larger performance issues.