sql server - How to prevent duplicate or similar data from getting processed? - Database Administrators Stack Exchange
i have stored procedure calculates distance between 2 coordinate points:
create proc loc.calcdistancebypostalcode ( @from varchar(10), @to varchar(10) ) 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 @postal1 select distinct l.place_name, l.region_name, l.latitude, l.longitude loc.locations l cross join loc.locations_postal lp postal_code @from , lp.city_id = l.id insert @postal2 select distinct l.place_name, l.region_name, l.latitude, l.longitude loc.locations l cross join loc.locations_postal lp postal_code @to , lp.city_id = l.id insert @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 @postal1 t1 cross join @postal2 t2 t1.latitude <> t2.latitude , t1.longitude <> t2.longitude declare @distance decimal(8,2) = 0 select * @data order distance desc select @distance = distance @data return @distance; end
it calculates correct result if @from
, @to
different, gives redundant data if @from
, @to
same.
example: different @from
, @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
, @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 need write code check if distance same compare from_place to_place rows having same distance? or optimised way prevent duplicate data?
try adding and t1.id > t2.id
clause , make sure join happens once . 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 @postal1 t1 cross join @postal2 t2 t1.latitude <> t2.latitude , t1.longitude <> t2.longitude
Comments
Post a Comment