It may look a common question . I am interested in finding all the zipcodes and distance from the zipcodes given in the table row and display them as one table. I tried to write procedure SQL for that but I failed . Till now I am only able to hard code on lat/long of single zipcode to arcsine formula to get neighbouring zips and I want to retrieve for all zips in the table and all their neighbouring zips and distances.
I am finding difficulty in traversing through all the zipcodes and accessing their respective Lat/Lng values
The Schema of table is
My code is:
CREATE PROCEDURE cursor_row()
BEGIN
DECLARE zip INT ;
DECLARE lat FLOAT ;
DECLARE lng FLOAT;
DECLARE zip_cursor CURSOR FOR
SELECT Zip_Code , lat_radians, lng_radians
FROM ZipLatLngRadians;
DECLARE CONTINUE handler FOR NOT found SET done=1;
SET done = 0 ;
OPEN zip_cursor;
zip_loop: LOOP
FETCH zip_cursor INTO zip , lat , lng;
IF done = 1 then leave zip_loop;
ELSE CALL zip_procedure(zip,lat,lng)
END IF;
END LOOP zip_loop;
CLOSE zip_cursor
DEALLOCATE zip_cursor
END ;
DELIMITER //
I don't know what I am actually doing wrong in the two procedures
I need to call zip_nearby() procedure inside cursor_row() procedure .So that cursor_row() can loop through each row of the table and pass zipcode,lat,long to zip_nearby() as parameter and zip_nearby() will give result for its parameter as demonstrated below in the image in black background.
DELIMITER //
CREATE PROCEDURE zip_nearby (IN p_lat1 double,IN p_long1 double,IN p_radius
double)
BEGIN
SELECT (2*3956*ASIN( (SQRT( POWER(SIN(((lat_radians-p_lat1))/2),2) +
COS(p_lat1) * COS(lat_radians) * POWER(SIN(((abs(lng_radians)-
p_long1))/2),2) )) ) ) AS dist, Zip_Code FROM ZipLatLngRadians
WHERE (2*3956*ASIN( (SQRT( POWER(SIN(((lat_radians-p_lat1))/2),2) +
COS(p_lat1) * COS(lat_radians) * POWER(SIN(((abs(lng_radians)-
p_long1))/2),2) )) ) ) < p_radius ORDER BY dist;
END
//
DELIMITER ;
This is the result I am getting by hardcoding 1 zip in leftmost column and I want to find for all zips in the leftmost column and all the neighbouring zips and their distance