SQLTeam.com | Weblogs | Forums

Reading another file to input variables into a SQL query

|Address latitude longitude distance
|2840 Bastogne Ave|36.6460123|87.4526458|5|
|2605 Street Rd|40.1091626|74.9534336|5|
|1991 Sproul Rd|39.9599205|75.3575943|5|

i have a file with 3000 different addresses with latitude and longitude
i have a SQL query that does a radius around a lat/long where the parameters are Latitude and Longitude and radius distance. i want to pass those three variables from another file by reading my file of 3000 different latitude/ longitudes and distance. i hope that makes sense

Welcome

You are passing 3 values from reading your 3000 addresses.

How are you passing the 3 values and to whom?

What does the other file contain?

great questions
Here is the query
SELECT *
FROM [Consumer_Database].[dbo].[Consumer_New_Layout_August_2022]
((3959 * acos( cos( radians(40.198793) ) * cos( radians(GeoCode_Latitude) ) * cos( radians(GeoCode_Longitude) - radians(-77.17558) )

  • sin( radians(40.198793) ) * sin( radians(GeoCode_Latitude) ) ) ) <= 5

I would like to read the other table and bring in those variables on the radians entries

Ultimate goal is to pull consumer data around 3000 different locations by doing a 5 mile radius around 3000 different lat/long combinations instead of running the query and hand entering the lat/long 3000 times

so when you say a file do you mean a table?

Is this proper SQL syntax?

SELECT *
FROM [Consumer_Database].[dbo].[Consumer_New_Layout_August_2022]
((3959 * acos( cos( radians(40.198793) ) * cos( radians(GeoCode_Latitude) ) * cos( radians(GeoCode_Longitude) - radians(-77.17558) )

* sin( radians(40.198793) ) * sin( radians(GeoCode_Latitude) ) ) ) <= 5

Please provide the schema of the second table you want to compare this to? with a small sample of data for it?

yes proper syntax
i had sent the schema for first file in the initial request
here it is again
Address | latitude | longitude | distance
|2840 Bastogne Ave|36.6460123|87.4526458|5|
|2605 Street Rd|40.1091626|74.9534336|5|
|1991 Sproul Rd|39.9599205|75.3575943|5|

schema of table Consumer_New_Layout_August_2022 not of file.

Here is something for you to run with

create table #addresses
(
bogus varchar(150),
Address varchar(150),
latitude varchar(150),
longitude varchar(150),
distance varchar(150))

BULK INSERT #addresses
FROM 'D:\DATA\address.csv'
WITH (
 FIRSTROW = 2,
 FIELDTERMINATOR = '|',
 ROWTERMINATOR = '0x0a'
);


update #addresses set distance = replace(distance,'|','')

select * from #addresses;