In [3]:

    

import time
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap
import matplotlib
from matplotlib import colors,colorbar
import matplotlib
%matplotlib inline
import csv 
import math
from math import radians, cos, sin, asin, sqrt
# from scipy import stats
import bq


    

In [4]:

    

client = bq.Client.Get()
def Query(q):
    t0 = time.time()
    answer = client.ReadTableRows(client.Query(q)['configuration']['query']['destinationTable'])
    #print 'Query time: ' + str(time.time() - t0) + ' seconds.'
    return answer


    

In [6]:

    

# create a bounding box:
max_lat = 85
min_lat = -80
max_lon = 180
min_lon = -180


# for the mapping
firstlat = max_lat
lastlat =  min_lat
firstlon = min_lon
lastlon = max_lon

# scale of the 
cellsize = 1
scale = cellsize
one_over_cellsize = 1

# num_lats = (max_lat-min_lat)*one_over_cellsize+1
# num_lons = (max_lon-min_lon)*one_over_cellsize+1
numlats = int((firstlat-lastlat)/scale+.5)
numlons = int((lastlon-firstlon)/scale+.5)


    

In [14]:

    

year = 2015
q = '''SELECT
  INTEGER(FLOOR(lat*{one_over_cellsize})) lat_bin,
  INTEGER(FLOOR(lon*{one_over_cellsize})) lon_bin,
  SUM(if(measure_new_score>.5,hours,0)) hours,
#   SUM(hours*measure_new_score) hours_weighted,
  //INTEGER(IF(LENGTH(STRING(mmsi))= 9,LEFT(STRING(mmsi),3), '0')) code
FROM (
  SELECT
    lat,
    lon,
    mmsi,
    measure_new_score,
    hours,
    seg_id
  FROM
      TABLE_DATE_RANGE([pipeline_classify_logistic_715_fishing.], TIMESTAMP("{year}-01-01"), TIMESTAMP("{year}-12-31"))
  WHERE
   lat > {min_lat}
   AND lat < {max_lat}
   AND lon > {min_lon}
   AND lon < {max_lon})
WHERE
  seg_id IN (
  SELECT
    seg_id
  FROM
    [scratch_david_seg_analysis.good_segments] )
  AND mmsi IN (
  SELECT
    mmsi
  FROM (
    SELECT
      mmsi,
      SUM(active_points) tot_active_points,
    FROM
      [scratch_david.{year}_mmsi_summaries_v2]
    GROUP BY
      mmsi
    HAVING
      tot_active_points > 500) )
GROUP BY
  lat_bin,
  lon_bin
order by lat_bin, lon_bin
'''.format(one_over_cellsize = one_over_cellsize, min_lat = min_lat,
           max_lat = max_lat, min_lon = min_lon, max_lon = max_lon, year = year)
fishing = Query(q)


    

    




Waiting on bqjob_r2bf1d737632ab8ed_00000157f4f31a7f_3 ... (0s) Current status: DONE   

In [15]:

    

with open("2015_fishing_hours.csv", 'w') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(["lat","lon","fishing hours"])
    writer.writerows(fishing)