Operand type clash geography is incompatible with float

Question

I am trying to figure out how to build a function that measures the distance from one location to another in miles, using longitude and latitude. The parameters would be added when EXECUTE is run. I am trying to do this using the "geography::Point()" and the "STDistance" instead of float.

IF OBJECT_ID('dbo.udfDistanceMiles') IS NOT NULL
  DROP FUNCTION dbo.udfDistanceMiles
GO
CREATE FUNCTION dbo.udfDistanceMiles 
(
    @long1 geography,
    @long2 geography,
    @lat1 geography,
    @lat2 geography
)
RETURNS geography
AS
BEGIN
    DECLARE @from geography
    DECLARE @to geography
    DECLARE @kilo geography
    DECLARE @miles as geography

    SET @from = geography::Point(@lat1,@long1,4268);
    SET @to = geography::Point(@lat2,@long2,4268);
    SET @kilo = (SELECT @from.STDistance(@to));
        BEGIN
            SET @miles = (@kilo * '.621371');
        END 

    RETURN @miles

END
GO

This is an assignment for a class I am taking on spatial databases, but I've run into a snag that I cannot figure out. I am running into this Operand type clash when trying to just create the function:

Msg 206, Level 16, State 2, Procedure udfDistanceMiles, Line 19
Operand type clash: geography is incompatible with float
Msg 206, Level 16, State 2, Procedure udfDistanceMiles, Line 19
Operand type clash: geography is incompatible with float
Msg 206, Level 16, State 2, Procedure udfDistanceMiles, Line 20
Operand type clash: geography is incompatible with float
Msg 206, Level 16, State 2, Procedure udfDistanceMiles, Line 20
Operand type clash: geography is incompatible with float
Msg 206, Level 16, State 2, Procedure udfDistanceMiles, Line 21
Operand type clash: float is incompatible with geography
Msg 403, Level 16, State 1, Procedure udfDistanceMiles, Line 23
Invalid operator for data type. Operator equals multiply, type equals geography.

Any help for a newb would be appreciated.

Thanks

John

score 0 · Answer 1 · answered Feb 04 '15 at 17:03

0

It looks like the parameters to your function are typed incorrectly. That is, if you're passing lat/long pairs, those are of type float, not type geography. You may be able to just change those and have everything else just work out.

answered Feb 04 '15 at 17:03

Ben Thul

31,080
4
45
68

score 0 · Accepted Answer · answered Feb 05 '15 at 20:19

Here is the finished Function that worked for me, along with its EXEC to measure miles from SLC, UT to LA, CA.

ALTER FUNCTION [dbo].[udfDistanceMiles] 
(
    @long1 float,
    @long2 float,
    @lat1 float,
    @lat2 float
)
RETURNS float
AS
BEGIN
    DECLARE @from geography
    DECLARE @to geography
    DECLARE @miles as float

    SET @from = geography::Point(@lat1,@long1,4326);
    SET @to = geography::Point(@lat2,@long2,4326);
    SET @miles = (SELECT ((@from.STDistance(@to) * '.001') * '.621371'));
    --SET @miles = (SELECT (@from.STDistance(@to) * '.621371'));

    RETURN @miles

END

And the EXEC:

USE DBM384;
GO

DECLARE @miles float= NULL;

EXEC @miles = dbo.udfDistanceMiles @lat1= 34, @long1= -118, @lat2= 40.758701, @long2= -111.876183;

PRINT @miles;

Operand type clash geography is incompatible with float

2 Answers2