It has been a long time since the "calculating distance on earth" debate. In fact, I was only able to find some notes.

Although, somebody asked for an equivalent in TSQL. I could not do this because I do not have access to this type of database server. Although, I am running PostgreSQL in a couple of locations and dug up this stored procedure (in plpgsql):

CREATE OR REPLACE FUNCTION zip_distance(integer, integer) returns float4 as '
 DECLARE
   zip1                alias for $1;
   zip2                alias for $2;
   v_zip1               integer;
   v_zip2               integer;
   longitude1          float8;
   latitude1           float8;
   longitude2          float8;
   latitude2           float8;
   delta_lon           float8;
   delta_lat           float8;
   temp                float8;
   dist                float4;
 BEGIN
   v_zip1 := zip1 ;
   while longitude1 IS NULL LOOP
      SELECT longitude_rad, latitude_rad INTO longitude1, latitude1 FROM zip_loc WHERE zip_code = v_zip1;
      v_zip1 := v_zip1 + 1;
   END LOOP;      

   v_zip2 := zip2 ;
   WHILE longitude2 IS NULL LOOP
      SELECT longitude_rad, latitude_rad INTO longitude2, latitude2 FROM zip_loc WHERE zip_code = v_zip2;
      v_zip2 := v_zip2 + 1;
   END LOOP;

   delta_lon := longitude2 - longitude1;
   delta_lat := latitude2 - latitude1;

   temp := pow(      sin(delta_lat/2.0::float8), 2.0::float8) 
            + cos(latitude1) 
            * cos(latitude2) 
            * pow(   sin(delta_lon/2.0::float8), 2.0::float8);

   dist := (3956 
         * 2 
         * atan2( sqrt(temp),sqrt(1 - temp)) )::float4;

   return dist::float4 ;

 END;
' language 'plpgsql';

zip_loc was a lookup table of zipcode longitude and latitudes. Actually, the final code used databases from US Census along with a modified version of the above (rewritten in Python), to find distances between street addresses. Also this data may have been cross referenced with USPS data. I no longer work on this project although I believe it is still in use.

If I remember correctly, this equation got me in some trouble with my meteorologist friends, Michael Tobis. Still, it's crazy fast and does the trick.

With index scans to use an intermediate in-memory bitmap and improved buffer cache and performance for partitioned tables, PostgreSQL 8.1 will be sure to make us feel good we have no idea what these things actually do! On the other hand, the addition of features: user roles on shared objects, the separation of roles from user/group distinctions, share level row locking, auto-indexes on MAX() and MIN(),two part commits, and the inclusion of the all to familiar contributed module for pg_autovacuum into the main sources, are all features we will love and understand.

Again with this upgrade a pg_dump and restore is required for the upgrade.

Listening to people [1] coming back from Solutions Linux 2005 in Paris this week, I picked up some talk concerning database architecture. The long time buzz words, 'Cluster' and 'Replication' are so loose they are only understood by a fairly lengthy explanation to follow. With databases, I best understand these words when put into the master-slave context.

I have been hanging out at #postgresql on freenode for the past couple of months, watching the release candidates for PostgreSQL 8 wizz by, rc1, rc2, rc3, rc4, rc5 .... launch. On last Wednesday, 01/19/05 eight was finally launched!

The big news on the eight for much of the world was it's move to Native Windows. For example read this article or this one . If you know me, you would know I do not care to work on Windows... so this is not the biggest issue to me. The biggest new feature for me is called double dollars coded by Andrew Dunstan and documented by David Fetter.

What is the bid deal with double quotes, you ask. Quoting in postgres, in particular, plpgsql, is much like Oracle in that when you write a stored procedure you best use double-single-quotes:

CREATE FUNCTION blog (BLOG, integer) RETURNS boolean AS '
    DECLARE
        v_blog ALIAS FOR $1;
        v_blognum ALIAS FOR $2;
    BEGIN
        IF v_blog.text ISNULL THEN
            RETURN ''f'';
        END IF;
        RETURN v_blog.id > v_blognum;
    END;
' LANGUAGE 'plpgsql';

The problem here is this gets tricker and tricker the more escaping you need to do:

a_output := a_output || '' if v_'' || 
    referrer_keys.kind || '' like '''''''''' 
    || referrer_keys.key_string || '''''''''' 
    then return ''''''  || referrer_keys.referrer_type 
    || ''''''; end if;''; 

The double dollar alleviates some of this pain by allowing another quoting mechanism:

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type 
    || $$'; end if;$$; 

ahh, much better.