First off, I apologize if the question appears confusing. To elaborate, I have a scalar function that calculates the bearing in degrees between two geographic points. I wanted to test this function on two tables, both consisting of US cities and their respective latitude/longitude coordinates before I use it on much larger datasets. It accepts two parameters: an origin and a destination variable which are of type geography. The problem I currently have is that the function only calculates the last row of the two test tables. Please see the code below:
-- Create the test tables with columns
DECLARE @TableA TABLE (CITY VARCHAR(25), LAT NUMERIC(19,15), LON NUMERIC(19,15))
DECLARE @TableB TABLE (CITY VARCHAR(25), LAT NUMERIC(19,15), LON NUMERIC(19,15))
-- Insert cities, lat/lon coordinates
INSERT INTO @TableA (CITY, LAT, LON)
VALUES ('Kansas City, MO', 39.099724, -94.578331),
('Memphis, TN', 35.117500, -89.971107),
('Austin, TX', 30.266666, -97.733330),
('Palm Springs, CA', 33.830517, -116.545601)
INSERT INTO @TableB (CITY, LAT, LON)
VALUES ('Aurora, CO', 39.710835, -104.812500),
('Indianapolis, IN', 39.791000, -86.148003),
('Vancouver, WA', 45.633331, -122.599998),
('Joliet, IL', 41.520557, -88.150558)
-- Declare variables
DECLARE @origin AS GEOGRAPHY
DECLARE @dest AS GEOGRAPHY
-- Convert to geography
-- SELECT was used instead of SET since I thought SELECT allows for more than one column value
SELECT @origin = GEOGRAPHY::STPointFromText('POINT('+CONVERT(VARCHAR(50), LON)+' '+CONVERT(VARCHAR(50), LAT)+')', 4326) FROM @TableA
SELECT @dest = GEOGRAPHY::STPointFromText('POINT('+CONVERT(VARCHAR(50), LON)+' '+CONVERT(VARCHAR(50), LAT)+')', 4326) FROM @TableB
-- Print variables to 'Results'
SELECT @origin.ToString()
SELECT @dest.ToString()
-- Call function
SELECT dbo.fnBearing(@origin, @dest) AS RESULT
The two SELECT statements for @origin.ToString() and @dest.ToString() only display lat/lon coordinates for Palm Springs, CA and Joliet, IL. When I call the function, it also only returns the calculation for these two geographic points. I would like for this function to perform a row-by-row calculation between Tables A and B (i.e. Kansas City, MO is calculated against Aurora, CO; Memphis, TN is calculated against Indianapolis, IN, etc.). I understand CROSS APPLY can be used for such a case, though I am unsure how to write that out in this situation.
Is there a specific way to set the two variables equal to each subsequent geographic column value so that when I pass them in as parameters to the function it will return a column with four calculations?
