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.

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.

share|improve this question
    
if you worry about view vs query performance have a look at his technical article, you'll find it very instructive: msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx –  unicoletti Sep 23 '11 at 9:59
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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/

share|improve this answer
add comment

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.