Creating a simply OGC Geometry from SQL2005 Latitude / Longitude

It is very common to have a some of your spatial data sources still in SQL2005 and sometimes you don’t have the option to have this upgraded to SQL2008. Rather then having to deal with the data differently we decided we would treat every data source as Well Known Binary. If your data is simply some Latitude and Longitudes you can do this:

WKB:

select 0x00+0x00000001 + cast([Longitude] as binary(8)) + cast([Latitude] as binary(8))

WKT:

'POINT(' + cast([Longitude] as nvarchar(20)) + ' ' + cast([Latitude] as nvarchar(20)) + ')'

GML:

'<Point xmlns="http://www.opengis.net/gml"><pos>' + cast([Latitude] as nvarchar(20)) + ' ' + cast([Longitude] as nvarchar(20)) + '</pos></Point>'

Important to note that WKB and WKT are XY, Longitude, Latitude while GML is still YX, Latitude, Longitude.