Calculating Distance between Zipcodes

If you have done some searching before arriving here, you may have realized that this task is not a simple as it looks on the surface. Surely there is an function or something? No. Not exactly.

[In SAS there apparently is such a built-in function]

Here's what I used to solve this problem:

1. Functions (3)
2. Table of Zipcodes with Longitude/Latitude

I obtained the functions used from this thread: http://www.tek-tips.com/viewthread.cfm?qid=801025

To recap, I created these three Functions:

CREATE OR REPLACE function DISTANCE
    (p1lon number
    ,p1lat number
    ,p2lon number
    ,p2lat number)
    return number
is
begin
    return    get_distance
        (conv_LongLat(p1lon,'LO','SM')
        ,conv_LongLat(p1lat,'LA','SM')
        ,conv_LongLat(p2lon,'LO','SM')
        ,conv_LongLat(p2lat,'LA','SM')
        );
end;
/

CREATE OR REPLACE function Conv_LongLat
    (LongLat    in number
    ,LongOrLat    in varchar2
    ,Conversion_Units in varchar2
    )
    return number
is
    LoLa        Char(2);
    Degree_in_output_units    number;
begin
    LoLa    := upper(substr(LongOrLat,1,2));
    if    LoLa not in ('LO','LA') then
        raise_application_error(-20000,
            'Error: Longitude/Latitude indicator, "'||LongOrLat||
            '", must begin with "LO" or "LA".');
    end if;
    if    LoLa = 'LO' then     -- Longitude
        if    LongLat > 180 then
            raise_application_error(-20001,
                'Error: Longitude ('||LongLat||') must <= 180 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'     -- 'Meters' per degree 
            then Degree_in_output_units := 111303;
        elsif upper(Conversion_Units) = 'F'    -- 'Feet' per degree
            then Degree_in_output_units := 365166;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 69.16022727272727;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 60.098594281230234;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    elsif    LoLa = 'LA' then    -- Latitude
        if    LongLat > 90 then
            raise_application_error(-20003,
                'Error: Latitude ('||LongLat||') must <= 90 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'    -- 'Meters' per degree
            then Degree_in_output_units := 110575;
        elsif upper(Conversion_Units) = 'F'     -- 'Feet' per degree
            then Degree_in_output_units := 362778;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 68.70795454545454;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 59.70558002704562;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    end if;
    Return    LongLat*Degree_in_output_units;
end;
/

CREATE OR REPLACE function DISTANCE
    (p1lon number
    ,p1lat number
    ,p2lon number
    ,p2lat number)
    return number
is
begin
    return    get_distance
        (conv_LongLat(p1lon,'LO','SM')
        ,conv_LongLat(p1lat,'LA','SM')
        ,conv_LongLat(p2lon,'LO','SM')
        ,conv_LongLat(p2lat,'LA','SM')
        );
end;
/

The Table didn't seem like a big deal but after getting in a looking at the data, I had to try a few sources to get one that worked well. The problem I ran into was the same Long/Lat information being used for surrounding zipcodes as the major city.

What I finally settled on was this Zip Code Database from Softwaretools.com at: https://softwaretools.com/zip-code-database

Loading the data took me a few tries using Oracle SQL*Developer since it never gets the column sizing just right it seems. Here's is what I ended up with:

CREATE TABLE ZIPCODES_SWT
(
  ZIPCODE     VARCHAR2(5 BYTE),
  STATE       VARCHAR2(2 BYTE),
  CITY        VARCHAR2(50 BYTE),
  COUNTY      VARCHAR2(25 BYTE),
  STATEFIPS   VARCHAR2(2 BYTE),
  COUNTYFIPS  VARCHAR2(3 BYTE),
  LATITUDE    VARCHAR2(8 BYTE),
  LONGITUDE   VARCHAR2(8 BYTE),
  PREFERENCE  VARCHAR2(1 BYTE),
  TYPE        VARCHAR2(3 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          160K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

COMMENT ON TABLE ZIPCODES_SWT IS 'Data obtained at: https://softwaretools.com/zip-code-database on 11/30/2015.';

From here i was able to calculate someone's distance (hard-coding my Long/Lat in values 3 and 4) using:

SELECT ROUND(distance(-106.71, 35.14, -106.62, 35.11) AS DISTANCE

  FROM DUAL;

  DISTANCE
----------
6.55683725
1 row selected.

Or Join this cross reference in your query. Remember to filter these Table values: WHERE PREFERENCE = 'A'

Good luck!

SQL Developer 4 on OSX (10.8) - How to Connect to SQL Server

Installing SQL Developer 4 on OSX is pretty straightforward. The only issue I had was that it wasn't picking up on updated version of Java and was giving me a error upon launch. 

That was a pretty easy fix - you just need to update the SqlDeveloper.conf file with the path. On a OSX (10.8.5) - the path will be something like:

SetJavaHome /Library/Java/JavaVirtualMachines/1.7.0.jdk/Contents/Home 

To add drivers for MS SQL Server, download and install the add in: jtds-1.3.1.jar (not 1.2) following these instructions for Third-Party Drivers:

http://www.oracle.com/technetwork/developer-tools/sql-developer/thirdparty-095608.html

Get 1.3 here:  http://sourceforge.net/projects/jtds/files/jtds/1.3.1/

Download and extract to a permanent folder follow instructions on adding into the Application above.

Here's the part that seems to elude most users - especially if you are connecting using Windows Authentication. The GUI panel just doesn't seem to work. 

On a OSX - you will also need to modify the "connections.xml" file by adding the DOMAIN value such as:

<StringRefAddr addrType="customUrl">

   <Contents>jdbc:jtds:sqlserver://Server_FQDN:1433;instance=<instance_name>;domain=<domain_name>/</Contents>

This folder is hidden and is located at:

/Users/<user_name>/.sqldeveloper/system4.0.1.14.48/o.jdeveloper.db.connection.12.1.3.2.41.140207.1351/

Note: To access hidden files, navigate to /Users/<username> and the click the "Go" top menu drop-down and select "Go to folder..."  Type ".sqldeveloper" (without quotes) and then follow the path from there. Directories that start with "." are hidden by default.

Once the above is saved, just try connecting from the main Connections Tree by right-click the connection and choose "Connect" 

Note: To see what Java version you are running in OSX you can issue this at the cmd line:

/Library/Internet/Plug-Ins/JavaAppletPlugin.plugin/Contents/Home/bin/java -version

java version "1.7.0_51"

Java(TM) SE Runtime Environment (build 1.7.0_51-b13)

Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode)

Table Information - including: rows counts, column counts and size

The script below can be run to get a Row Count (from Stats), Column Count and size (in MEG) for all Tables in a Schema:

select 
   a.table_name, 
   a.num_rows ROW_count_FROM_STATS,
   b.col_count,
   C.MEG
from 
   dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE' and owner = '&owner'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5  /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where 
   a.owner = '&owner'
   and A.TABLE_NAME = b.table_name(+)
   AND A.TABLE_NAME = C.TABLE_NAME(+)
order by 
   a.table_name;