0

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

enter image description here

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 ;

enter image description here

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

Community
  • 1
  • 1

0 Answers0