gistfile1.sh

#!/bin/bash
#set -x
# $1 - file containing points to search within
# $2 - interval of points for time diff message
# $3 - print time_diff messages?
pg_neighbours()
{
  filename="$RANDOM-$RANDOM"
  start_time=`date +%s.%N`
  i=0
  for point in `cat $1`
  do
    let i++
    p=(${point//,/ })
    lat=${p[0]}
    lng=${p[1]}

    echo "SELECT id,name, latitude, longitude from businesses where ST_DWithin(location, ST_PointFromText('POINT($lng $lat)', 4326), 1000) ORDER BY id ASC;" >> /tmp/$filename
    if [[ $(echo "$i%$2"|bc) -eq 0 ]]
    then
      psql tests -t -F"," -A < /tmp/$filename > /dev/null
      [ "$3" -eq 1 ] && time_diff $start_time $i
      > /tmp/$filename
    fi
  done
  rm /tmp/$filename
}

# $1 - file containing points to search within
# $2 - interval of points for time diff message
# $3 - print time_diff messages?
mysql_neighbours()
{
  oifs=$IFS
  IFS='
'
  filename="$RANDOM-$RANDOM"
  i=0
  start_time=`date +%s.%N`
  for point in $(cat $1)
  do
    [ "$i" -eq 0 ] && IFS=$oifs
    let i++
    p=(${point//\s*/ })
    lat=${p[0]}
    lng=${p[1]}

    echo "${cmd}set @lng1 = ${lng}-1000/abs(cos(radians($lat))*111133.00000000001);" >> /tmp/$filename
    echo "${cmd}set @lng2 = ${lng}+1000/abs(cos(radians($lat))*111133.00000000001);" >> /tmp/$filename
    echo "${cmd}set @lat1 = ${lat}-(1000/111133.00000000001);" >> /tmp/$filename
    echo "${cmd}set @lat2 = ${lat}+(1000/111133.00000000001);" >> /tmp/$filename
    echo "${cmd}set @search_area = PolyFromText(CONCAT('Polygon((', @lng1, ' ',  @lat1, ',', @lng1, ' ', @lat2, ',', @lng2, ' ', @lat2, ',', @lng2, ' ',  @lat1, ',', @lng1, ' ',  @lat1, '))'));" >> /tmp/$filename
    echo "${cmd}SELECT id, name, latitude, longitude FROM businesses WHERE MBRWithin(location, @search_area) ORDER BY id ASC;" >> /tmp/$filename
    if [[ $(echo "$i%$2"|bc) -eq 0 ]]
    then
      mysql -uXXX -pXXX test  -N < /tmp/$filename > /dev/null
      [ "$3" -eq 1 ] && time_diff $start_time $i
      > /tmp/$filename
    fi
  done
  rm /tmp/$filename
}

# $1 - start time
# $2 - amount of points done
time_diff()
{
  now=`date +%s.%N`
  time_diff=`echo "$now-$1"|bc|xargs printf "%.6f"`
  echo "$time_diff secs ($2 points done)"
}

PG()
{
  echo "testing PG..."
  psql tests -t -F"," -A -c "SELECT latitude, longitude FROM businesses ORDER BY id ASC OFFSET 12000 LIMIT 1000" > pg_points
  echo "linear load (1000 points to search within)"
  time pg_neighbours pg_points 100 1
  rm pg_points

  printf "\nparallel load (20x30 points to search within)\n"
  printf "preparing data...\n"

  for w in `seq 0 19`
  do
    offset=`expr $w \* 30 + 100000`
    psql tests -t -F"," -A -c "SELECT location FROM businesses ORDER BY id ASC OFFSET $offset LIMIT 30" > pg_w$w
  done
  printf "testing...\n"
  time (
    for w in `seq 0 19`
      do
        (
          pg_neighbours pg_w$w 30 0
          rm pg_w$w
        ) &
    done
    wait
  )
  printf "\ndone.\n"
}

MYSQL()
{
  echo "testing MySQL..."
  mysql -uXXX -pXXX test  -N -e "SELECT latitude, longitude FROM businesses ORDER BY id ASC LIMIT 12000,1000" > mysql_points
  echo "linear load (1000 points to search within)"
  time mysql_neighbours mysql_points 100 1
  rm mysql_points

  printf "\nparallel load (20x30 points to search within)\n"
  printf "preparing data...\n"

  for w in `seq 0 19`
  do
    offset=`expr $w \* 30 + 100000`
    mysql -uroot -proot test -N -e "SELECT latitude, longitude FROM businesses ORDER BY id ASC LIMIT $offset,30" > mysql_w$w
  done
  printf "testing...\n"
  time (
    for w in `seq 0 19`
      do
        (
          mysql_neighbours mysql_w$w 30 0
          rm mysql_w$w
        ) &
    done
    wait
  )
  printf "\ndone.\n"
}

#
# Run
#
Run()
{
case $1 in
  pg   ) PG   ;;
  mysql) MYSQL;;
  *    ) echo "$0: unknown argument: $1";;
esac
}

#
# Run a phase based on the selector
#
Run $1