Spatial SQL -> MS-SQL 2008. UDF function that constucts geographic point

In this example we will create user function that constucts geographic point from given input of latitude/longitude.

USE milano
GO

/* ===================================================================
   this function returns point of geography type constructed from
   latitude/longitude input
  ==================================================================== */

-- drop prev version
IF EXISTS (SELECT name FROM sysobjects
WHERE object_id = OBJECT_ID(N'[dbo].[stfn_point]')
  AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  DROP FUNCTION [dbo].[stfn_point]
GO
-- create new

CREATE FUNCTION stfn_point (@lat FLOAT = 0, @lon FLOAT = 0) RETURNS GEOGRAPHY
AS
BEGIN


DECLARE @pt GEOGRAPHY;
-- create point
SET @pt = 'POINT(' + CAST(@lat AS Varchar(40)) +' '+ CAST(@lon AS Varchar(40)) + ')' ;

-- return spatial
RETURN @pt;

END;

-- test: construct point with lat-long of Teatro Dal Verme (Milan)
SELECT name, g FROM dbo.milano_reg

  UNION ALL

SELECT name, g FROM dbo.milano_str

  UNION ALL

SELECT 'point', dbo.stfn_point (9.18099916657438, 45.4672226816419).STBuffer(15)



Spatial results will look like this:



sqlexamples.info