1

I have a stored procedure which calculates the distance between 2 coordinate points:

CREATE PROC loc.CalcDistanceByPostalCode
(
@From VARCHAR(10),
@To   VARCHAR(10)
)
AS
BEGIN
    DECLARE @data table (From_Place VARCHAR(200), From_Region VARCHAR(200), "To_Place" VARCHAR(200), To_Region VARCHAR(200), Distance decimal(8,2))
    DECLARE @postal1 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))
    DECLARE @postal2 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9)) 

    INSERT INTO @postal1 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude 
                         from loc.locations l 
                         cross join loc.locations_postal lp 
                         where postal_code like @From and lp.city_id = l.ID
    INSERT INTO @postal2 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude 
                         from loc.locations l 
                         cross join loc.locations_postal lp 
                         where postal_code like @To and lp.city_id = l.ID

    INSERT INTO @data 
    SELECT
        t1.place_name,  t1.region_name, t2.place_name, t2.region_name,
        loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance
    FROM @postal1 as t1
    CROSS JOIN @postal2 as t2
    WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude

    DECLARE @Distance decimal(8,2) = 0
    select * from @data order by Distance desc
    SELECT @Distance = Distance from @data
    return @Distance;
END

It calculates correct result if @From and @To are different, but gives redundant data if @From and @To are same.

Example: Different @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '400%'`

Result:

From_Place  From_Region To_Place     To_Region      Distance
Delhi       Delhi        Mumbai      Maharashtra    1160.65
New Delhi   Delhi        Mumbai      Maharashtra    1157.80
Delhi       Delhi        Vasai-Virar Maharashtra    1115.68
New Delhi   Delhi        Vasai-Virar Maharashtra    1112.87

Example: Same @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '110%'`

Result:

From_Place  From_Region To_Place    To_Region   Distance
New Delhi   Delhi       Delhi       Delhi       3.51
Delhi       Delhi       New Delhi   Delhi       3.51

Do I need to write extra code to check if Distance is same then compare the From_Place with To_Place with the rows having the same distance? or any optimised way to prevent the duplicate data?

  • Do you need to put the calculation in a procedure? Seems like it would be rather straightforward to put everything inside an ordinary question. If you still want a procedure (perhaps for reusability), I would try to get rid of all variables inside the procedure. – Lennart Jan 20 '18 at 16:01
  • Why all these cross join that are really just joins? You have IDENTITY you don't use. – paparazzo Jan 20 '18 at 16:41
2

Try adding And t1.ID > t2.ID to the where clause , it will make sure that the join happens once . To the following query

SELECT t1.place_name, t1.region_name, t2.place_name, t2.region_name, loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance 
FROM @postal1 as t1 CROSS JOIN @postal2 as t2 
WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude
  • I don't this will work as those are different ID. It would fix the same postal but potentially break different postal. – paparazzo Jan 20 '18 at 16:41
  • @Paparazzi i guess it will work – Ahmad.Tr Jan 21 '18 at 19:27
  • If @from is greater than @to then it shows error – Gour Gopal Jan 22 '18 at 10:20
  • @Gour Gopal, iam not sure i got you right , check the answer again you should compare the IDs of t1 and t2 . – Ahmad.Tr Jan 23 '18 at 20:36
  • @Ahmad.Tr - If I add condition t1.ID > t2.ID, then it works well if @From and @To are same, but if @From value is greater than @To value then I get wrong results, so to fix the issue I added another condition to your condition: and (@From <> @To OR t1.ID > t2.ID). Thanks :) – Gour Gopal Jan 24 '18 at 5:54
1

Disclamer: I don't know sqlserver, so there may be syntactical errors. I would aim for getting rid of the variables, at a glance this appears to be what you are trying to do:

WITH postal (postal_code, place_name, region_name, latitude, longitude) as (
    SELECT distinct lp.postal_code, l.place_name, l.region_name
         , l.latitude, l.longitude 
    FROM loc.locations l 
    JOIN loc.locations_postal lp
         ON lp.city_id = l.id
    WHERE postal_code like @From
       OR postal_code like @To
)
SELECT t1.place_name,  t1.region_name, t2.place_name, t2.region_name
     , loc.FN_CalcAerialDistance(t1.latitude, t1.longitude
                                ,t2.latitude, t2.longitude)
    FROM postal as t1
    JOIN postal as t2
        ON (t1.latitude, t1.longitude) <> (t2.latitude, t2.longitude)

If:

(t1.latitude, t1.longitude) <> (t2.latitude, t2.longitude) 

is not supported, this is eqvivalent to:

t1.latitude <> t2.latitude OR t1.longitude <> t2.longitude
  • it didn't work. – Gour Gopal Jan 22 '18 at 14:20
  • thanks. but inner joins made the program run slower. I somehow solved: added condition: and (@From <> @To OR t1.ID > t2.ID). – Gour Gopal Jan 24 '18 at 6:00
1

I somehow found a solution to fix the issue

I added more condition to the stored procedure query which calculated distance: @From <> @To OR t1.ID > t2.ID

WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude and (@From <> @To OR t1.ID > t2.ID)

Thanks @Ahmad.Tr for the t1.ID > t2.ID conditon.

It gave correct results, so I optimized the stored procedure: (execution time takes 35 to 52 seconds for 2767634 rows, earlier it was more than 2 times more records and also 2 times slower.

CREATE PROC loc.CalcDistanceByPostalCode
(
@From VARCHAR(10),
@To VARCHAR(10)
)
AS
BEGIN
    DECLARE @postal1 table(ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))
    DECLARE @postal2 table(ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9)) 

    INSERT INTO @postal1 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude from loc.locations l cross join loc.locations_postal lp where postal_code like @From and lp.city_id = l.ID
    INSERT INTO @postal2 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude from loc.locations l cross join loc.locations_postal lp where postal_code like @To and lp.city_id = l.ID

    SELECT
        t1.place_name "From_Place", t1.region_name "From_Region", t2.place_name "To_Place", t2.region_name "To_Region",
        loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) as "Distance" -- Function calculates Distance
    FROM @postal1 as t1
    CROSS JOIN @postal2 as t2
    WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude and (@From <> @To OR t1.ID > t2.ID)
    order by Distance desc
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.