Is it possible to configure the results from a STCentroid select statement?

If I run a select statement at the minute, I will get overly precise responses i.e. POINT (399419.42500005924 90290.17500002397) I just need to grab the numbers before the decimal, I don't need the detail provided.


You could extract the coordiantes and round these values:

   From dbo.MY_TABLE;

And you can extend this so that the result is returned as a POINT:

Select 'POINT(' 
      + Cast(ROUND(SP_GEOMETRY.STX, 0) As VarChar) 
      + ' ' 
      + Cast(ROUND(SP_GEOMETRY.STY, 0) As VarChar)
      + ')' As WKT
   From dbo.MY_TABLE;

where SP_GEOMETRY is the name of the column holding your spatial data

  • Hi Peter I had thought about this, but I didn't want to split this out into holding tables, I did find a different solution which I will post below. – SteveC81 Jul 1 '14 at 14:59
  • Hi Steve, well my suggestion can by used like yours. You don't need to create a new table, you can just add the 'POINT...)' to you select statement – Peter Horsbøll Møller Jul 2 '14 at 8:49

I kind of answered my own question by grabbing the relevant parts of the geometry that make up the X and Y of my record:

cast(SUBSTRING(M.GEOMETRY.STCentroid().ToString(),8,6)as int) AS EASTING

This grabs the for me the Easting and the following grabs the Northing for me:

cast(SUBSTRING(M.GEOMETRY.STCentroid().ToString(),CHARINDEX(' ',M.GEOMETRY.STCentroid().ToString(),1+CHARINDEX(' ',M.GEOMETRY.STCentroid().ToString(),0)),6)as int) AS NORTHING

This enables me to create my Easting and Northing Column at the same time as the other relevant information in my query, without having to have a temporary holding table.

  • Also a possible workaround. Not that different from my suggestion in other ways than that you are modifying the string and I'm modifying the numerical value. Or am missing something? – Peter Horsbøll Møller Jul 2 '14 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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