CODEWITHSUNDEEP
sql-servert-sql
Niana
Niana

Reputation: 1047

Merge stored procedure and CTE(function) together to get results from reference table

Situation

I'm working on a project where my GPS sends latitude and longitude to a SQL Server database. This all works fine. Then I have a report that calls a stored procedure that returns latitude/longitude and other information.

Then using any of my latitude and longitude, I have a CTE that returns the nearest city based on the latitude/longitude.

Difficulty

The difficulty that I am experiencing right now, is that I am stomped for a way to tie in those two queries in such a way that when I'm running my report the name of my city is retrieved as well.

As mentioned the stored procedure returns the Lat/lng and the CTE (maybe it should be a function ) gets me the name from the Lat/lng (right now it's hard coded).

Stored procedure:

ALTER PROCEDURE [dbo].[usp_MovementRepP]
    @GPSID nvarchar(50),
    @rSdate date,
    @rEdate date 
AS
    SELECT 
        SUBSTRING(CONVERT(varchar(12), CreateTime, 120), 1, 10) as RecordDate,
        SUBSTRING(CONVERT(varchar(12), CreateTime, 114), 0, 13) as RecordTime,
        Latitude, Longitude, DeviceId,
        * 
    FROM 
        locationhistory A
    INNER JOIN 
        devices B ON A.DeviceId = B.id
    WHERE
        DeviceName = @GPSID
        AND SUBSTRING(CONVERT(varchar(8), CreateTime, 112), 1, 10) BETWEEN @rSdate AND @rEdate

CTE that gets me cityname/city ID based on the lat/long it's supplied:

;WITH Distance_CTE ([geonameid], distance) AS
-- Define the CTE query.
(
    SELECT 
        [geonameid], 
        (3959 * acos(cos(radians(15.312015)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-61.388295)) + sin(radians(15.312015)) * sin(radians(latitude )))) AS distance 
    FROM 
        [GeoNames] 
)
-- Define the outer query referencing the CTE name.
SELECT TOP 1 [geonameid], distance
FROM Distance_CTE
WHERE DISTANCE < 20
ORDER BY DISTANCE

EDIT

Results from the stored procedure:

ID  Deviceid     Latitude    Longitude   Speed  Dir     CreateTime
505     4       15.31156    -61.3880467 6.52    0   2016-12-19 11:41:59.000
506     4       15.3175567  -61.39057   32.47   0   2016-12-19 11:43:00.000
507     4       15.32653    -61.39533   36.91   0   2016-12-19 11:44:01.000
508     4       15.330445   -61.3912917 13.45   0   2016-12-19 11:45:01.000
509     4       15.334425   -61.39001   20.46   0   2016-12-19 11:46:02.000
510     4       15.334445   -61.389295  14.57   0   2016-12-19 11:47:05.000

Results from CTE 

 geonameid  distance
 9063952    0.173565252078886

When I pass in a latitude/longitude to the CTE a geonameid is returned. What I am trying to do is, get this geonameid INTO my stored procedure.

Hope it's a bit clearer

Upvotes: 0

Views: 1543

Answers (3)

T.H.
T.H.

Reputation: 859

I haven't tested this (update: confirmed to work after removing a semi-colon from function), but I believe it will do what you need -- possibly too slowly though. You may need to fix the parameter types, and just the cross apply line has been added to your stored proc.

CREATE FUNCTION [dbo].[GetCity]
(

@Latitude decimal(9,6), @Longitude decimal(9,6) 
)    
RETURNS TABLE
AS  

RETURN

WITH Distance_CTE ([geonameid], distance)
AS
-- Define the CTE query.
(
SELECT [geonameid], (3959 * acos(cos(radians(@Latitude)) * cos(radians(latitude)) * cos(radians(longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(latitude )))) AS distance 
FROM [GeoNames] 
)
-- Define the outer query referencing the CTE name.
SELECT top 1 [geonameid], distance
FROM Distance_CTE
WHERE DISTANCE < 20
order by DISTANCE

ALTER PROCEDURE [dbo].[usp_MovementRepP]
@GPSID nvarchar(50),
@rSdate date,
@rEdate date 
AS
SELECT SUBSTRING(CONVERT(varchar(12), CreateTime, 120),1,10) as RecordDate,
SUBSTRING(CONVERT(varchar(12), CreateTime, 114),0,13) as RecordTime,Latitude,Longitude,DeviceId,
* FROM locationhistory A
INNER JOIN devices B 
ON A.DeviceId=B.id
CROSS APPLY dbo.GetCity(Latitude, Longitude)
WHERE
DeviceName=@GPSID
AND
SUBSTRING(CONVERT(varchar(8), CreateTime, 112),1,10)
BETWEEN @rSdate AND @rEdate

Upvotes: 3

edosoft
edosoft

Reputation: 17271

You will need to use CROSS APPLY to get the nearest city. An example would look like:

SELECT SUBSTRING(CONVERT(varchar(12), CreateTime, 120),1,10) as RecordDate,
SUBSTRING(CONVERT(varchar(12), CreateTime, 114),0,13) as RecordTime,Latitude,Longitude,DeviceId,
* FROM locationhistory A
cross apply
(
  SELECT top 1 [geonameid], (3959 * acos(cos(radians(A.Latitude)) * cos(radians(g.latitude)) * cos(radians(g.longitude) - radians(A.Longitude)) + sin(radians(A.Latitude)) * sin(radians(g.latitude )))) AS distance 
  FROM [GeoNames] g
  ORDER BY distance 
) nearest
INNER JOIN devices B 
ON A.DeviceId=B.id
WHERE
DeviceName=@GPSID
AND
SUBSTRING(CONVERT(varchar(8), CreateTime, 112),1,10)
BETWEEN @rSdate AND @rEdate

Default disclaimer, code is untested, use at your own risk.

Upvotes: 1

Serge
Serge

Reputation: 4036

Use SQL Server built-in geography::Point and STDistance methods:

ALTER PROCEDURE [dbo].[usp_MovementRepP] @GPSID  NVARCHAR(50),
                                         @rSdate DATE,
                                         @rEdate DATE
AS
BEGIN
     SELECT SUBSTRING(CONVERT( VARCHAR(12), CreateTime, 120), 1, 10) AS RecordDate,
            SUBSTRING(CONVERT( VARCHAR(12), CreateTime, 114), 0, 13) AS RecordTime,
            Latitude,
            Longitude,
            DeviceId,
            g.geonameid,
          geography::Point(g.latitude, g.longitude, 4326).STDistance(geography::Point(A.latitude, A.longitude)) AS Distance
     FROM locationhistory A
          INNER JOIN devices B ON A.DeviceId = B.id
        INNER JOIN [GeoNames] g
          ON geography::Point(g.latitude, g.longitude, 4326).STDistance(geography::Point(A.latitude, A.longitude)) < 20
     WHERE DeviceName = @GPSID
           AND SUBSTRING(CONVERT(VARCHAR(8), CreateTime, 112), 1, 10) BETWEEN @rSdate AND @rEdate;

END

Upvotes: 0

Related Questions