Install script for Geoserver on Ubuntu EC2 instance

Continuing with the theme of running stuff on the free tier of Amazon Web Services, here's a script to install Geoserver proxied through apache for when you want to throw up a quick map server. The script installs Geoserver 2.02 on Ubuntu Maverick 10.10 using ami-cef405a7.

The script is available here.

#
# install Geoserver on Ubuntu Maverick 10.10
# note: Geoserver is proxied through apache so port 8080 is not used
#
# @spara 11/15/10
#

# setup sources 
sudo sh -c "echo ' ' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb http://us.archive.ubuntu.com/ubuntu/ maverick multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb-src http://us.archive.ubuntu.com/ubuntu/ maverick multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb http://us.archive.ubuntu.com/ubuntu/ maverick-updates multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb-src http://us.archive.ubuntu.com/ubuntu/ maverick-updates multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb http://archive.canonical.com/ maverick partner' >> /etc/apt/sources.list"
sudo apt-get update

# magic! (installs java without physically accepting license)
echo "sun-java6-jdk shared/accepted-sun-dlj-v1-1 boolean true" | sudo -E debconf-set-selections

# setup prerequisites 
sudo apt-get -y install sun-java6-bin
export JAVA_HOME=/usr/lib/jvm/java-6-sun
sudo apt-get -y install unzip

# set java paths
sudo touch /etc/profile.d/java.sh
sudo sh -c "echo 'export JAVA_HOME=/usr/lib/jvm/java-6-sun' >> /etc/profile.d/java.sh"
sudo sh -c "echo 'export PATH=$PATH;$JAVA_HOME/bin' >> /etc/profile.d/java.sh"
sudo source /etc/profile.d/java.sh

#install tomcat6
sudo apt-get install -y tomcat6
sudo chgrp -R tomcat6 /etc/tomcat6
sudo chmod -R g+w /etc/tomcat6

# install and config apache
sudo apt-get install -y apache2
sudo ln -s /etc/apache2/mods-available/proxy.conf /etc/apache2/mods-enabled/proxy.conf
sudo ln -s /etc/apache2/mods-available/proxy.load /etc/apache2/mods-enabled/proxy.load
sudo ln -s /etc/apache2/mods-available/proxy_http.load /etc/apache2/mods-enabled/proxy_http.load

# add tomcat proxy
sudo chmod 666 /etc/apache2/sites-available/default
sudo sed -i '$d'  /etc/apache2/sites-available/default
sudo sh -c "echo ' ' >> /etc/apache2/sites-available/default"
sudo sh -c "echo 'ProxyRequests Off' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '# Remember to turn the next line off if you are proxying to a NameVirtualHost' >> /etc/apache2/sites-available/default"
sudo sh -c "echo 'ProxyPreserveHost On' >> /etc/apache2/sites-available/default"
sudo sh -c "echo ' ' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '    Order deny,allow' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '    Allow from all' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '' >> /etc/apache2/sites-available/default"
sudo sh -c "echo ' ' >> /etc/apache2/sites-available/default"
sudo sh -c "echo 'ProxyPass /geoserver http://localhost:8080/geoserver' >> /etc/apache2/sites-available/default"
sudo sh -c "echo 'ProxyPassReverse /geoserver http://localhost:8080/geoserver' >> /etc/apache2/sites-available/default"
sudo sh -c "echo ' ' >> /etc/apache2/sites-available/default"
sudo sh -c "echo '' >> /etc/apache2/sites-available/default"
sudo chmod 644 /etc/apache2/sites-available/default

# get geoserver, change to version you want
sudo service tomcat6 stop
wget http://downloads.sourceforge.net/geoserver/geoserver-2.0.2-war.zip
sudo unzip -d /var/lib/tomcat6/webapps/ geoserver-2.0.2-war.zip
sudo chown -R tomcat6 /var/lib/tomcat6/webapps/geoserver.war
sudo chgrp g+w tomcat6 /var/lib/tomcat6/webapps/geoserver.war

# restart
sudo service tomcat6 restart
sudo service apache2 restart

# echo message
addy=$(GET http://169.254.169.254/latest/meta-data/public-hostname)
echo " "
echo "Geoserver is available at: http://$addy/geoserver"


# additional tweaks for production instances
#
# add the following options to catalina.sh
#
# JAVA_OPTS="-Djava.awt.headless=true -Xms256m -Xmx768m -Xrs -XX:PerfDataSamplingInterval=500 -XX:MaxPermSize=128m -DGEOSERVER_DATA_DIR=/var/lib/tomcat6/webapps/geoserver/data"

Install script for ThinkUp 0.7 on Ubuntu EC2 instance

ThinkUp is a nifty web app for managing social media; from the site:

ThinkUp captures your posts, replies, retweets, friends, followers, and links on social networks like Twitter and Facebook. We'll be adding more networks in the future. ThinkUp stores your social data in a database you control, and makes it easy to search, sort, filter, export, and visualize in useful ways.

ThinkUp requires the LAMP stack, a number of php packages, and sendmail. Installing these individually can be daunting so I wrote a script that takes care of all the prerequisites and installs ThinkUp on an Ubuntu EC2 instance. I commented out the phpmyadmin installation because it isn't necessary, but it is nice to have if you need to make changes to the database.

For testing, I used my Ubuntu 10.10 AMI that complies with AWS free tier requirements: ami-8548bfec. One caveat, I set my ThinkUp account email to gmail which seems to mark the autoregistration notification email as spam, so check your spam folder first.


UPDATE 12/30/10: Canonical released refreshed UEC images for 10.10 (Maverick Meerkat) with 8GB root EBS volumes that will run on the AWS free tier. The list of Amazon published AMIs is available here.


UPDATE 1/5/11: Andy Baio updated the script and wrote a tutorial to perform the whole install in the browser. The tutorial is on the ThinkUp wiki.

# install ThinkUp on EC2 Ubuntu instance:
#
# @spara 12/23/10
#

echo "Installing required packages, follow the prompts"
sleep 2

# install required packages
sudo apt-get update
sudo tasksel install lamp-server
sudo apt-get -y install unzip
sudo apt-get -y install curl libcurl3 libcurl3-dev php5-curl php5-mcrypt php5-gd --fix-missing
sudo apt-get -y install sendmail

# restart apache to init php packages
sudo service apache2 restart

# not necessary but nice to have
#sudo apt-get -y install phpmyadmin

wget https://github.com/downloads/ginatrapani/ThinkUp/thinkup-0.7.zip --no-check-certificate
sudo unzip -d /var/www/ thinkup-0.7.zip

# config thinkup installer
sudo ln -s /usr/sbin/sendmail /usr/bin/sendmail
sudo chown -R www-data /var/www/thinkup/_lib/view/compiled_view/
sudo touch /var/www/thinkup/config.inc.php
sudo chown www-data /var/www/thinkup/config.inc.php

# create database
echo -n "Enter the MySQL admin password: "
read -e pword
mysqladmin -h localhost -u root -p$pword create thinkup

# echo message
addy=$(GET http://169.254.169.254/latest/meta-data/public-hostname)
echo "Copy the URL below to install and configure Thinkup"
echo "http://$addy/thinkup/install/"

Build-out Script for Postgres/PostGIS with RAID 10 on Amazon EBS volumes

My iteration on Simon Tokumine's script to install Postgres on Amazon Web Services. This version is based on Ubuntu 10.4 and builds out a RAID 10 drive, installs GEOS, Proj4, osm2pgsql and PostGIS from source, and creates a database ready for loading OSM data.

################################################################
#
# Amazon EC2 PostGIS 1.5 on RAID10,f2 EBS Array Build Script
#
# Complete Rip off of:
# http://github.com/tokumine/ebs_raid_postgis/blob/master/build.sh
# http://alestic.com/2009/06/ec2-ebs-raid
# http://biodivertido.blogspot.com/2009/10/install-postgresql-84-and-postgis-140.html
#
# Additional glue by Simon Tokumine, 15/11/09
# Additions by Sophia Parafina, 10/08/10
#        added additional repos to sources.list
#        custom postgis, proj4, geos build
#        added packages for building postgis, proj4, geos
#        configured to build RAID10
#        customized for Canonical Ubuntu AMIs
#
# INSTALL ON ALESTIC UBUNTU AMI'S - http://alestic.com/
# I ORIGINALLY USED THE 32-bit AMI: ami-ccf615a5 (jaunty)
#
# NOTE, THIS IS ONLY FOR TESTING
################################################################

################################################################
#SETUP
#Please complete the parts that are in []'s (over writing the []'s)
#then just run the script on the server
################################################################

# change this to you keypair and cert
export EC2_PRIVATE_KEY=~[key.pem]
export EC2_CERT=~[cert.pem]
# change this to your instance
instanceid=[my-instance]
# change to the instance's availability zone
availability_zone=us-east-1d
# builds out RAID10, so size of RAID=volumes*size/2,
#   change this to your needs
volumes=[10]
size=[100]
# change to your mount point
mountpoint=[/mnt/vol1]
# change to a device
raid_array_location=[/dev/md0]
raid_level=10
raid_layout=f2
raid_chunk=256
# change to your password
postgres_password=[postgres]
# create a postgis template
db_name=template_postgis
################################################################

#####
# TODO
#
# UNMOUNT AND DETACH/DESTROY EBS & TERMINATE EC2
#
#####


################################################################
# CREATE EBS VOLUMES & RAID ARRAY
################################################################
sudo sh -c "echo ' ' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb http://us.archive.ubuntu.com/ubuntu/ lucid multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb-src http://us.archive.ubuntu.com/ubuntu/ lucid multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb http://us.archive.ubuntu.com/ubuntu/ lucid-updates multiverse' >> /etc/apt/sources.list"
sudo sh -c "echo 'deb-src http://us.archive.ubuntu.com/ubuntu/ lucid-updates multiverse' >> /etc/apt/sources.list"
# this is specific for lucid only
sudo sh -c "echo 'deb http://archive.canonical.com/ lucid partner' >> /etc/apt/sources.list"
sudo apt-get update
sudo apt-get -y install ec2-api-tools
sudo apt-get -y install sun-java6-bin
export JAVA_HOME=/usr/lib/jvm/java-6-sun

devices=$(perl -e 'for$i("h".."k"){for$j("",1..15){print"/dev/sd$i$j\n"}}'|
head -$volumes)
devicearray=($devices)
volumeids=
i=1
while [ $i -le $volumes ]; do
   volumeid=$(ec2-create-volume -z $availability_zone --size $size | cut -f2)
   echo "$i: created $volumeid"
   device=${devicearray[$(($i-1))]}
   echo $volumeid
   ec2-attach-volume $volumeid -i $instanceid -d $device
   volumeids="$volumeids $volumeid"
   let i=i+1
done
echo "volumeids='$volumeids'"

sudo apt-get update &&
sudo apt-get install -y mdadm xfsprogs

devices=$(perl -e 'for$i("h".."k"){for$j("",1..15){print"/dev/sd$i$j\n"}}'|
head -$volumes)


#builds out RAID10
yes | sudo mdadm \
--create $raid_array_location \
--chunk=$raid_chunk \
--level=$raid_level \
--layout=$raid_layout \
--metadata=1.1 \
--raid-devices $volumes \
$devices

echo DEVICE $devices | sudo tee /etc/mdadm.conf
sudo mdadm --detail --scan | sudo tee -a /etc/mdadm.conf

sudo mkfs.xfs $raid_array_location

echo "$raid_array_location $mountpoint xfs noatime 0 0" | sudo tee -a /etc/fstab
sudo mkdir $mountpoint
sudo mount $mountpoint

################################################################
# INSTALL POSTGRES, POSTGIS & SETUP DATABASE ON RAID VOLUME
################################################################
#echo " " >> /etc/apt/sources.list
#echo "deb http://ppa.launchpad.net/pitti/postgresql/ubuntu jaunty main" >> /etc/apt/sources.list
#echo "deb-src http://ppa.launchpad.net/pitti/postgresql/ubuntu jaunty main" >> /etc/apt/sources.list
#sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8683D8A2
#sudo apt-get update
sudo apt-get install libxml2-dev

sudo apt-get -y install postgresql-8.4 postgresql-server-dev-8.4 postgresql-contrib-8.4 libpq-dev
sudo /etc/init.d/postgresql-8.4 stop

sudo mkdir $mountpoint/data
sudo chmod -R 700 $mountpoint/data
sudo chown -R postgres.postgres $mountpoint/data
sudo -u postgres /usr/lib/postgresql/8.4/bin/initdb -D $mountpoint/data
sudo sed -i.bak -e 's/port = 5433/port = 5432/' /etc/postgresql/8.4/main/postgresql.conf
sudo sed -i.bak -e "s@\/var\/lib\/postgresql\/8.4\/main@$mountpoint\/data@" /etc/postgresql/8.4/main/postgresql.conf
sudo sed -i.bak -e 's/ssl = true/#ssl = true/' /etc/postgresql/8.4/main/postgresql.conf
sudo /etc/init.d/postgresql-8.4 start
cd /tmp
sudo apt-get -y install bzip2
sudo apt-get -y install g++
sudo apt-get -y install checkinstall

# install geos
wget http://download.osgeo.org/geos/geos-3.2.2.tar.bz2
bunzip2 geos-3.2.2.tar.bz2
tar xvf geos-3.2.2.tar
cd geos-3.2.2
./configure
make && sudo checkinstall --pkgname geos --pkgversion 3.2.2-src --default

# install proj
cd ../
wget http://download.osgeo.org/proj/proj-4.7.0.tar.gz
tar xvfz proj-4.7.0.tar.gz
cd proj-4.7.0
./configure
make && sudo checkinstall --pkgname proj4 --pkgversion 4.70-src --default
cd ../

# install postgis as a package for easier removal if needed
wget http://postgis.refractions.net/download/postgis-1.5.2.tar.gz
tar xvfz postgis-1.5.2.tar.gz
cd postgis-1.5.2
./configure
make && sudo checkinstall --pkgname postgis --pkgversion 1.5.2-src --default # remove with dpkg -r postgis
sudo /sbin/ldconfig

# config template_postgis
sudo -u postgres psql -c"ALTER user postgres WITH PASSWORD '$postgres_password'"
sudo -u postgres createdb $db_name
sudo -u postgres createlang -d$db_name plpgsql
sudo -u postgres psql -d$db_name -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
sudo -u postgres psql -d$db_name -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
sudo -u postgres psql -d$db_name -c"select postgis_lib_version();"

# osm
# install osm2pgsql
cd /tmp
sudo apt-get -y install subversion
sudo apt-get -y install autoconf
sudo apt-get -y install libbz2-dev
svn export http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/
cd osm2pgsql
./autogen.sh
./configure
sed -i 's/-g -O2/-O2 -march=native -fomit-frame-pointer/' Makefile
make
sudo make install

# create osm database
# sudo -u postgres createdb -T template_postgis osm

Recipe: Ubuntu Maverick 10.10 on the AWS Free Tier

I love free stuff, who doesn't? In grad school, I once found a fancy cake in a shopping cart in the grocery store parking lot. I brought it to our graduate seminar to share with my fellow grad students, who enjoyed it until I mentioned where the cake came from.


I pretend to read the fine print, but in my excitement over free I usually fail to comprehend it. So it was for the AWS free tier announcement. I excitedly spun up an Official Ubuntu AMI and went on my merry way setting up apps, thinking all the time how wonderful it was to have my very own server on the interwebs humming away for gratis. At the end of the month, I got a bill for $0.50. Huh, not free, so I read the fine print (again):

10 GB of Amazon Elastic Block Storage, plus 1 million I/Os, 1 GB of snapshot storage, 10,000 snapshot Get Requests and 1,000 snapshot Put Requests*

Oops, the Official Unbuntu images have a 15GB root file system. 


Scott Moser provided a recipe for making an AWS Ubuntu 10.10 AMI with a 10GB root file system. So I made a public AMI based on his recipe that y'all can use: ami-8548bfec


UPDATE 12/30/10: Canonical released refreshed UEC images for 10.10 (Maverick Meerkat) with 8GB root EBS volumes that will run on the AWS free tier. The list of Amazon published AMIs is available here.

Trigonometric and Spatial functions for MySQL

MySQL function snippets for azimuth, perpendicular of the the azimuth, coordinates of a point given a bearing and a distance, and Vincenty Distance. These functions are for use at large scales, i.e. short distances.


Calculate the azimuth between two points (from Aviation Formulary V1.45):

DELIMITER //
DROP FUNCTION IF EXISTS azimuth//

CREATE FUNCTION azimuth(lng1 DOUBLE, lat1 DOUBLE, lng2 DOUBLE, lat2 DOUBLE)
RETURNS DOUBLE
    DETERMINISTIC
BEGIN
   DECLARE az DOUBLE;
   DECLARE lat_rad1 DOUBLE;
   DECLARE lat_rad2 DOUBLE;
   DECLARE dLon DOUBLE;
   DECLARE var DOUBLE;

   SET var = lng1;

   SET lat_rad1 := RADIANS(lat1);
   SET lat_rad2 := RADIANS(lat2);
   SET dlon := RADIANS(lng2-lng1);
   SET az := atan2( 
                 sin(dLon)*cos(lat_rad2),
                 (cos(lat_rad1)*sin(lat_rad2)) - (sin(lat_rad1)*cos(lat_rad2)*cos(dLon))
                );
   SET az := (DEGREES(az) + 360) % 360;
   RETURN az;
END//
DELIMITER ;

Calculate perpendicular bearing of an azimuth (note: MySQL was not correctly calculating values for 90° and 270°, so I hard coded the correct values):

DELIMITER //
DROP FUNCTION IF EXISTS perpendicularBearing//
CREATE FUNCTION perpendicularBearing(azimuth DOUBLE, side VARCHAR(5))
RETURNS DOUBLE
BEGIN
   DECLARE perp_az DOUBLE;

   SET perp_az = DEGREES(atan(1/tan(RADIANS(azimuth))*-1));
   IF side = "left" THEN
      SET perp_az = 180 + (180 -(180 - perp_az));
   END IF;
   IF azimuth = 90 THEN
      SET perp_az = 0;
   END IF;
   IF azimuth = 270 THEN
      SET perp_az = 0;
   END IF;
   IF azimuth = -90 THEN
      SET perp_az = 0;
   END IF;
   IF azimuth = -270 THEN
      SET perp_az = 0;
   END IF;
   RETURN perp_az;
END//
DELIMITER ;

Calculate the coordinates of a point given a start point, an azimuth, and a distance in feet (from Aviation Formulary V1.45):

DELIMITER //
DROP FUNCTION IF EXISTS destinationPoint//
CREATE FUNCTIOn destinationPoint(azimuth DOUBLE, distance DOUBLE, lat DOUBLE, lng DOUBLE)
RETURNS Point
   
BEGIN
   DECLARE az_rad DOUBLE;
   DECLARE lat_rad DOUBLE;
   DECLARE lng_rad DOUBLE;
   DECLARE dist_km DOUBLE;
   DECLARE dist_rad DOUBLE;
   DECLARE lat_dest DOUBLE;
   DECLARE lng_dest DOUBLE;
   DECLARE dest_point Point;  
 
   SET dist_km = distance * 0.0003048;
   SET dist_rad = dist_km/6371;
   SET lat_rad = RADIANS(lat);
   SET lng_rad = RADIANS(lng);
   SET az_rad = RADIANS(azimuth);
   
   SET lat_dest = asin(sin(lat_rad)*cos(dist_rad) + 
                      cos(lat_rad)*sin(dist_rad)*cos(az_rad) );
   SET lng_dest = lng_rad + atan2(sin(az_rad)*sin(dist_rad)*cos(lat_rad), 
                             cos(dist_rad)-sin(lat_rad)*sin(lat_rad));
   SET lng_dest = (lng_dest+3*pi())%(2*pi()) - pi();
   SET dest_point = Point(DEGREES(lng_dest),DEGREES(lat_dest));
   RETURN dest_point;
END//
DELIMITER ;

Finally, Vincenty Distance calculations from bramsi at forge.mysql.com:

DELIMITER ;;
DROP FUNCTION IF EXISTS `vd`;;

CREATE FUNCTION `vd`(lng1 DOUBLE, lat1 DOUBLE, lng2 DOUBLE, lat2 DOUBLE, metric VARCHAR(2)) RETURNS DOUBLE
    DETERMINISTIC
    COMMENT 'Vincenty Distance WGS-84 http://code.google.com/p/geopy/'
BEGIN
DECLARE gcdx DOUBLE;
DECLARE lng_rad1 DOUBLE;
DECLARE lat_rad1 DOUBLE;
DECLARE lng_rad2 DOUBLE;
DECLARE lat_rad2 DOUBLE;

DECLARE wgs84_major DOUBLE;
DECLARE wgs84_minor DOUBLE;
DECLARE wgs84_flattening DOUBLE;

DECLARE delta_lng DOUBLE;
DECLARE reduced_lat1 DOUBLE;
DECLARE reduced_lat2 DOUBLE;
DECLARE sin_reduced1 DOUBLE;
DECLARE cos_reduced1 DOUBLE;
DECLARE sin_reduced2 DOUBLE;
DECLARE cos_reduced2 DOUBLE;

DECLARE lambda_lng DOUBLE;
DECLARE lambda_prime DOUBLE;

DECLARE iter_limit INT;

DECLARE sin_lambda_lng DOUBLE;
DECLARE cos_lambda_lng DOUBLE;
DECLARE sin_sigma DOUBLE;
DECLARE cos_sigma DOUBLE;
DECLARE sigma DOUBLE;
DECLARE sin_alpha DOUBLE;
DECLARE cos_sq_alpha DOUBLE;
DECLARE cos2_sigma_m DOUBLE;
DECLARE C DOUBLE;
DECLARE u_sq DOUBLE;
DECLARE A DOUBLE;
DECLARE B DOUBLE;
DECLARE delta_sigma DOUBLE;

SET lng_rad1 := RADIANS(lng1);
SET lat_rad1 := RADIANS(lat1);
SET lng_rad2 := RADIANS(lng2);
SET lat_rad2 := RADIANS(lat2);

SET wgs84_major := 6378.137;
SET wgs84_minor := 6356.7523142;
SET wgs84_flattening := 1 / 298.257223563;

SET delta_lng := lng_rad2 - lng_rad1;

SET reduced_lat1 := atan((1 - wgs84_flattening) * tan(lat_rad1));
SET reduced_lat2 := atan((1 - wgs84_flattening) * tan(lat_rad2));

SET sin_reduced1 := sin(reduced_lat1);
SET cos_reduced1 := cos(reduced_lat1);
SET sin_reduced2 := sin(reduced_lat2);
SET cos_reduced2 := cos(reduced_lat2);

SET lambda_lng := delta_lng;
SET lambda_prime := 2 * pi();

SET iter_limit = 20;

WHILE abs(lambda_lng - lambda_prime) > pow(10, -11) and iter_limit > 0 DO
     SET sin_lambda_lng := sin(lambda_lng);
     SET cos_lambda_lng := cos(lambda_lng);

     SET sin_sigma := sqrt(pow((cos_reduced2 * sin_lambda_lng), 2) +
                      pow((cos_reduced1 * sin_reduced2 - sin_reduced1 *
                       cos_reduced2 * cos_lambda_lng), 2));

     IF sin_sigma = 0 THEN
        RETURN 0;
     END IF;

     SET cos_sigma := (sin_reduced1 * sin_reduced2 +
                         cos_reduced1 * cos_reduced2 * cos_lambda_lng);

     SET sigma := atan2(sin_sigma, cos_sigma);

     SET sin_alpha := cos_reduced1 * cos_reduced2 * sin_lambda_lng / sin_sigma;
     SET cos_sq_alpha := 1 - pow(sin_alpha, 2);

     IF cos_sq_alpha != 0 THEN
         SET cos2_sigma_m := cos_sigma - 2 * (sin_reduced1 * sin_reduced2 /
                                         cos_sq_alpha);
     ELSE
         SET cos2_sigma_m := 0.0;
     END IF;

     SET C := wgs84_flattening / 16.0 * cos_sq_alpha * (4 + wgs84_flattening * (4 - 3 * cos_sq_alpha));

     SET lambda_prime := lambda_lng;
     SET lambda_lng := (delta_lng + (1 - C) * wgs84_flattening * sin_alpha *
                   (sigma + C * sin_sigma *
                    (cos2_sigma_m + C * cos_sigma *
                     (-1 + 2 * pow(cos2_sigma_m, 2)))));
     SET iter_limit := iter_limit - 1;
END WHILE;

IF iter_limit = 0 THEN
    RETURN NULL;
END IF;

SET u_sq := cos_sq_alpha * (pow(wgs84_major, 2) - pow(wgs84_minor, 2)) / pow(wgs84_minor, 2);

SET A := 1 + u_sq / 16384.0 * (4096 + u_sq * (-768 + u_sq *
                                        (320 - 175 * u_sq)));

SET B := u_sq / 1024.0 * (256 + u_sq * (-128 + u_sq * (74 - 47 * u_sq)));

SET delta_sigma := (B * sin_sigma *
               (cos2_sigma_m + B / 4. *
                (cos_sigma * (-1 + 2 * pow(cos2_sigma_m, 2)) -
                 B / 6. * cos2_sigma_m * (-3 + 4 * pow(sin_sigma, 2)) *
                 (-3 + 4 * pow(cos2_sigma_m, 2)))));

SET gcdx := wgs84_minor * A * (sigma - delta_sigma);

IF metric = 'km' THEN
 RETURN gcdx;
ELSEIF metric = 'mi' THEN
 RETURN gcdx * 0.621371192;
ELSEIF metric = 'nm' THEN
 RETURN gcdx / 1.852;
ELSE
 RETURN gcdx;
END IF;
END;;

DELIMITER ;