Question How To Calculate Distance Between Zip Codes

Dean Reed

New Member
Unix
v11.3

I am in need of calculating the distance between two zip codes. I have all the zip codes and longitudes / latitudes data.

Does anyone have code that does this? I figured I'd check here before starting to dig in.
 

rzr

Member

Dean Reed

New Member
That API would work except this is for a production site and I need it to be in Progress ABL code. It will be calculated on the server with the data returned to the client.
 
Dean,

This is a very dated code/solution but it might work for you. I think I still have the lat/long data (ziploc table) if you want it for a start. It's not near as accurate as today's web services but would get you close enough for government work:) Note it ran on windows but could be ported to Linux by changing the internal procedure call.

Rod


Code:
/* real database tables used for example.  Very dated at this point */

DEF TEMP-TABLE ziploc
    FIELD ziploc AS CHAR
    FIELD lat    AS DEC
    FIELD Long   AS DEC.
   

/* Housed the possible results based on distance */
DEF TEMP-TABLE Stores 
  FIELD zip    AS CHAR
  FIELD active AS LOG
  FIELD state  AS CHAR
  FIELD Chain  AS CHAR
  FIELD Storeno AS CHAR
  FIELD SortSeq AS DEC.


DEFINE TEMP-TABLE tempLocations
  FIELD store    AS CHAR
  FIELD distance AS DEC FORMAT "9999.99"
  FIELD chain    AS CHAR
  FIELD city     AS CHAR FORMAT "x(100)"
  FIELD address  AS CHAR FORMAT "x(100)"
  FIELD seq      AS CHAR
  INDEX seq IS PRIMARY seq ASCENDING distance
  INDEX dist distance ASCENDING seq.


FUNCTION getLocations RETURNS HANDLE  (INPUT cZipCode AS CHAR,
                                      INPUT iMilesDist   AS INT):
   
    DEF VAR dLongitude1 AS DEC  FORMAT "9999.9999"    NO-UNDO.
    DEF VAR dLongitude2 AS DEC  FORMAT "9999.9999"    NO-UNDO.
    DEF VAR dLatitude1  AS DEC  FORMAT "9999.9999"    NO-UNDO.
    DEF VAR dLatitude2  AS DEC  FORMAT "9999.9999"    NO-UNDO.
    DEF VAR dAverage    AS DEC  FORMAT "999999.9999"  NO-UNDO.
    DEF VAR dCosnum     AS DEC  FORMAT "999999.9999"  NO-UNDO.
    DEF VAR dEq1        AS DEC  FORMAT "999999.9999"  NO-UNDO.
    DEF VAR dEq2        AS DEC  FORMAT "999999.9999"  NO-UNDO.
    DEF VAR dEq3        AS DEC  FORMAT "999999.9999"  NO-UNDO.
    DEF VAR dDistance   AS DEC  FORMAT "9999.9999999" NO-UNDO.
    DEF VAR cMiles      AS CHAR FORMAT "x(10)"        NO-UNDO.
    DEF VAR decResult   AS DEC                        NO-UNDO.
    DEF VAR cState      AS CHAR                       NO-UNDO.
    DEF VAR icount      AS INT                        NO-UNDO.
   
   
   
    FIND FIRST ziploc WHERE ziploc.zip EQ cZipCode NO-LOCK NO-ERROR.
   
    IF NOT AVAILABLE ziploc OR ziploc.lat EQ 0 THEN
    DO:
      /* Technically not correct both close enough to get them to the store.
         Means data is getting old.*/
      FIND FIRST ziploc WHERE ziploc.zip  >= cZipCode
                          AND ziploc.lat  <> 0
                          AND ziploc.LONG <> 0 NO-LOCK NO-ERROR.
    END.
    ELSE
    DO:
      ASSIGN
        dLongitude1 = ziploc.LONG
        dLatitude1  = ziploc.lat.
     
      FOR EACH stores WHERE stores.zip    EQ cZipCode
                        AND stores.ACTIVE NO-LOCK:
        CREATE tempLocations.
        ASSIGN
          tempLocations.chain = stores.chain
          tempLocations.store = stores.storeno.
      END.   /* for each stores */
     
     
     
      FOR EACH stores WHERE stores.zip    <> cZipCode
                        AND stores.ACTIVE NO-LOCK:
       
        FIND FIRST ziploc WHERE ziploc.zip EQ SUBSTRING(stores.zip,1,5) NO-LOCK NO-ERROR.
        IF NOT AVAILABLE ziploc THEN NEXT.
       
        ASSIGN
          dLongitude2 = ziploc.LONG
          dLatitude2  = ziploc.lat
          dAverage    = dLongitude1 + dLongitude2.
          dCosnum     = (dAverage * .017452329252).
       
        RUN cos (INPUT dCosnum, OUTPUT decResult).
       
        ASSIGN
          dEq3      = (dLatitude2 - dLatitude1) * decResult
          dEq1      = dEq3 * dEq3
          dEq2      = (dLongitude2 - dLongitude1) * (dLongitude2 - dLongitude1)
          dDistance = SQRT(dEq1 + dEq2)
          dDistance = 69.171 * dDistance.
       
        IF dDistance <= iMilesDist THEN
        DO:
          CREATE tempLocations.
          ASSIGN
            tempLocations.chain    = stores.chain
            tempLocations.store    = stores.storeno
            tempLocations.distance = dDistance
            tempLocations.seq      = STRING(stores.SortSeq).
        END. /* dist <= miles */
      END. /* for each stores */
    END.  /* if available ziploc */
    RETURN BUFFER tempLocations:HANDLE.
END. /* Function */


PROCEDURE cos EXTERNAL "MSVCRT40.DLL" CDECL:
        DEFINE INPUT  PARAMETER dblValue  AS DOUBLE NO-UNDO.
        DEFINE RETURN PARAMETER dblResult AS DOUBLE NO-UNDO.
END PROCEDURE.
 

Dean Reed

New Member
Rod, that is awesome. Thanks. I have all the data I need, I'll try out that code. I don't need it to be accurate down to the Nth of a mile, just general will work. Thanks.
 

Dean Reed

New Member
I modified it to suit my needs in regards to table data and it looks good. Hard to test for exact matches b/c the web APIs work of zip code to zip code in terms of longitude and latitude, my data is using coords of exact addresses. But, the majority of hits from the web API I tested with came up as hits from my code.

Thank you very much. Now I just need to get it sorted out to run on Unix. The issue is the cos procedure, the install here doesn't have the Unix counterpart ... at least not in the same place.
 
Top