SQL Query - Average distance to providers

wsilage · March 15, 2018, 2:10pm

Hi need help. The query below is pulling set zip that we need to analyze. The access standards is 2 in 10 miles.

I need to figure out a way to add in to this query Average dDstance to 1 Provider and Average Distance to 2 providers.

The person that wrote this code is no longer with the company, any help would be great. I am not that fluent with this type of coding.

Select zipcode,CityName, stateabbr as State, Count(distinct [PCS Number]) ProviderCount,Count(distinct case when distance < 10 then [PCS Number] else null end) Within10mileRadius---,---Distance
from
(
select distinct
ROUND(2 * ASIN(SQRT(POWER(SIN((( cast(z.Latitude as decimal(15,6)) / 180) * PI() - (CONVERT(Decimal(15,12),p.Latitude) / 180) * PI()) / 2) ,2)
+ COS(( cast(z.Latitude as decimal(15,6)) / 180) * PI()) * COS((CONVERT(Decimal(15,12),p.Latitude) / 180) * PI()) * POWER(SIN((( cast(z.Longitude as decimal(15,6)) / 180) * PI() - (CONVERT(Decimal(15,12), p.Longitude) / 180) * PI()) / 2), 2)))
* (6366.707019 * .621371), 4) as Distance
,z.zipcode,p.Latitude ,p.Longitude,[PCS Number]--,p.LastName ,p.FirstName
,z.CityName ,z.StateAbbr
from Vw_pfall p
left join uszipcodedata z on LEFT(p.zipCode,5) = z.ZIPCode
where p.SpecType = 'P' and CityType = 'D' --and p.ZipCode like '19406%'
and Z.zipcode in ('15042',
'15209',
'15314',
'15401',
'16630',
'16635',
'16648',
'17007',
'17011',
'17013',
'17015',
'17018',
'17019',
'17020',
'17022',
'17025',
'17033',
'17037',
'17038',
'17045',
'17046',
'17047',
'17050',
'17055',
'17057',
'17062',
'17065',
'17070',
'17078',
'17090',
'17093',
'17102',
'17103',
'17104',
'17109',
'17110',
'17111',
'17112',
'17113',
'17201',
'17241',
'17257',
'17266',
'17315',
'17319',
'17323',
'17324',
'17339',
'17365',
'17366',
'17370',
'17403',
'17404',
'17543',
'17603',
'18103',
'18250',
'18508',
'19072',
'19121' )
-----order by zipCode,Distance
)A
group by zipcode,CityName,StateAbbr----, Distance

-----select cast(z.Longitude as decimal(15,12)),Latitude,Longitude from uszipcodedata where ZIPCode = '19406'

------select latitude , cast(latitude as decimal(15,12)) from vw_pfall where zipcode like '19406%'

------ Select Distinct ZIPCode,CityName, StateAbbr
------ from uszipcodedata
------ WHERE CityType = 'D' and ZIPCode IN('17018',
------'17037',
------'17038',
------'17045',
------'17046',
------'17065',
------'17078',
------'17090',
------'17093',
------'17102',
------'17104',
------'17241',
------'17266',
------'17315',
------'17319',
------'17323',
------'17324',
------'17365',
------'17366',
------'17370',
------'18250',
------'19121')

------ order by ZIPCode

Jason_A_Long · March 15, 2018, 7:18pm

Create a function to handle the distance calculations. Something like the following...

CREATE FUNCTION dbo.tfn_LatLonDistanceInMiles
/* ==========================================================================================================
01/06/2015 JL, Created as a iTVF version of the LatLonDistanceInMiles scalar function
========================================================================================================== */
(
	@LatBeg DECIMAL(15,12),
	@LonBeg DECIMAL(15,12),
	@LatEnd DECIMAL(15,12),
	@LonEnd DECIMAL(15,12)
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN

	WITH 
		cte_Variables AS (
			SELECT 
				earthSphereRadiusKilometers = 6366.707019,
				kilometerConversionToMilesFactor = .621371,
				LatBegRadians = (@LatBeg / 180) * PI(),
				LonBegRadians = (@LonBeg / 180) * PI(),
				LatEndRadians = (@LatEnd / 180) * PI(),
				LonEndRadians = (@LonEnd / 180) * PI()
			)
	SELECT 
		DistanceInMiles = ROUND(2 * ASIN(SQRT(POWER(SIN((v.LatBegRadians - v.LatEndRadians) / 2) ,2) + 
		COS(v.LatBegRadians) * COS(v.LatEndRadians) * POWER(SIN((v.LonBegRadians - v.LonEndRadians) / 2), 2)))
			* (v.earthSphereRadiusKilometers * v.kilometerConversionToMilesFactor), 4)
	FROM 
		cte_Variables v;
GO

The the query looks something like this...

SELECT 
	p.PatientName,
	p1.Adress,
	p1.City,
	p1.State,
	p1.ZipCode,
	p2.ProviderName,
	p2.Adress,
	p2.City,
	p2.State,
	p2.ZipCode,
	d.DistanceInMiles
FROM
	dbo.Patients p1
	CROSS JOIN dbo.Providers p2
	CROSS APPLY dbo.tfn_LatLonDistanceInMiles(p1.Lat, p1.Lng, p2.Lat, p2.Lng) d
WHERE 
	p1.ZipCode IN (...)
	AND d.DistanceInMiles <= 10;

If you have a large provider network (too large to do the aforementioned cross join) there are a few tricks you can do to group "rounded" lats & lngs for an easier direct join so that you only have to calculate the distances of near by providers. That's a bit more complex and outside the scope of the current question though.

wsilage · March 19, 2018, 2:26pm

Thank you. So I found that we actually have a function in place. See below, Since I never worked with Functions, how do I add this into my query that I want to see results in.

Here is the function that has been written.....

CREATE FUNCTION [dbo].[uf_LatLonRadiusDistance]
(
@lat1Degrees decimal(15,12),
@lon1Degrees decimal(15,12),
@lat2Degrees decimal(15,12),
@lon2Degrees decimal(15,12)
)
RETURNS decimal(9,4)
AS
BEGIN

DECLARE @earthSphereRadiusNauticalMiles as decimal(10,6)
DECLARE @nauticalMileConversionToMilesFactor as decimal(7,6)
SELECT @earthSphereRadiusNauticalMiles = 6366.707019
SELECT @nauticalMileConversionToMilesFactor = .621371

-- convert degrees to radians
DECLARE @lat1Radians decimal(15,12)
DECLARE @lon1Radians decimal(15,12)
DECLARE @lat2Radians decimal(15,12)
DECLARE @lon2Radians decimal(15,12)
SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

-- formula for distance from [lat1,lon1] to [lat2,lon2]
RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)
    + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))
    * (@earthSphereRadiusNauticalMiles * @nauticalMileConversionToMilesFactor), 4)

END
GO

Then this is what I need to find out.

I am given 4 zipcode ('15042','15209','15314','15401').

-Full count of providers within 10 mile radius per zip code

Then I will need the details of each provider within that location to be able to determine the two closest providers for average distance of two providers within 10 mile radius

Here is my query....

Select
z.zipcode, Cityname, stageabbr as State, Count(distinct [PCS Number]) ProviderCount,Count(distinct case when distance < 10 then [PCS Number] else null end) as Number_Of_Providers_Within10_MileRadius
from Vw_pfall p
left join uszipcodedata z on LEFT(p.zipCode,5) = z.ZIPCode
where p.SpecType = 'P' and CityType = 'D'
group by zipcode,CityName,StateAbbr

Jason_A_Long · March 19, 2018, 3:07pm

Not all functions are created equal... Whenever possible avoid using scalar & multi-statement functions. Inline table valued functions (iTVF) perform far better.
Either use the function I supplied or rewrite your scalar function so that it's an iTVF.

As far as how to use it... I included that on my first post.

wsilage · March 19, 2018, 3:26pm

Ok thanks Jason. This is good to know. Thanks for you help.

wsilage · March 20, 2018, 4:02pm

Thanks Jason this did work for me. However, how do I calculate the average distance to a provider? Like this below that is highlighted? I am not familiar with this. Thanks.