[GIS] Create a geography/geometry column from x and y fields – SQL Server 2008

geometrysql serversql-server-spatial

I'm new in SQL Server 2008 and I hope you will understand my question/need.

Thus, I have a table which contains 3 fields (Name, Lat and Long) in my data base (spatial). I want to create a geometry/geography column based on those fields (Lat and Long) but unfortunately without any success.

My question is: How can I manage to do that?

Best Answer

You can add a computed column like this

alter table yourTable add geographyColumn as geography::STGeomFromText('POINT('+convert(varchar(20),Long)+' '+convert(varchar(20),Lat)+')',4326)

I have add the conversion from lng or lat because I store the Long and Lats as numbers.

Related Solutions

[GIS] Find closest lat long to an input lat long (SQL Server 2008)

What you are looking for is Nearest Neighbor Query. Look at the following links, I think you will find what you are looking for.

[GIS] How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

Here's an implementation of the formula from Wikipedia, except that this will truncate fractional seconds because SQL only allows the modulo operator with ints.

DECLARE @geom geometry;

SET @geom = geometry::STGeomFromText('POINT(-83.255 32.567477)', 4326);


SELECT CASE WHEN @geom.STX < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STX)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"',
  CASE WHEN @geom.STY < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STY)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"'

or for N, S, E, W rather than signs

DECLARE @geom geometry;

SET @geom = geometry::STGeomFromText('POINT(-83.255 32.567477)', 4326);


SELECT CAST(FLOOR(ABS(@geom.STX)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"' +
    CASE WHEN @geom.STX < 0 then ' W' ELSE ' E' END,
  CAST(FLOOR(ABS(@geom.STY)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"' +
    CASE WHEN @geom.STY < 0 then ' S' ELSE ' N' END

Best Answer

Related Solutions

[GIS] Find closest lat long to an input lat long (SQL Server 2008)

[GIS] How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

Related Question