Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment