SQL server views in geoserver - problem with sql

Question

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 cant change that.

I cant write anything in native SRS and filling in epsg:3008 in declared SRS don'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 diehard 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 Im taking my information from another view but as it is only about 6500 points I dont think it will give any larger performance issues.

Answer 1

The function STPointFromText returns a geography type. Try to use:

geometry::STGeomFromText('POINT(' + CAST(database.table1.YCOORD AS VARCHAR(6)) + ' ' + CAST(database.table1.XCOORD AS VARCHAR(7)) + ')', 3008)

instead.

I also suggest you create a view in the database and then have geoserver query the view. This will add a layer of indirection that increases manageability while not sacrificing performance.

If you worry about view vs query performance have a look at his technical article on MSDN, you'll find it very instructive.

Answer 2

There have been a few bugs fixed since geoserver 2.1.0, you might want to try upgrading to the latest 2.1.x nightly [1]. I actually think this may have been fixed but not 100% sure.

[1] http://gridlock.opengeo.org/geoserver/2.1.x/