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)
|
