In [2]:
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
import bq
import time
import cPickle
from scipy import stats
to generate the mmsi:
SELECT
a.shiptype_text shiptype_text,
a.mmsi mmsi,
a.number number_of_points
FROM (
SELECT
mmsi,
shiptype_text,
COUNT(*) number
FROM
TABLE_DATE_RANGE([type5and24messages.], TIMESTAMP('2015-01-01'), TIMESTAMP('2016-03-31'))
GROUP BY
mmsi,
shiptype_text) a
JOIN (
SELECT
mmsi,
COUNT(*) COUNT
FROM
TABLE_DATE_RANGE([pipeline_classify.], TIMESTAMP('2015-01-01'), TIMESTAMP('2016-03-31'))
WHERE
lon > 145
AND lon < 155
AND lat > -5
AND lat <-2
AND speed < 5
GROUP BY
mmsi
HAVING
COUNT >= 10)b
ON
a.mmsi = b.mmsi
WHERE
a.number > 20
AND a.mmsi > 99999999
AND shiptype_text NOT IN ("",
"Sailing",
"Fishing",
"Law Enforcement",
"Military ops",
"Pleasure Craft",
"Tanker, all ships of this type",
"Tanker, Hazardous category A",
"Tanker, Hazardous category B",
"Tanker, No additional information",
"Tanker, Reserved for future use",
"Wing in ground (WIG), Reserved for future use")Now create a table:
SELECT
mmsi,
timestamp,
lat,
lon,
speed
FROM
TABLE_DATE_RANGE([pipeline_classify.], TIMESTAMP('2015-01-01'), TIMESTAMP('2016-03-31'))
WHERE
lon > 145
AND lon < 155
AND lat > -5
AND lat <-2
and mmsi in (412203740,440014000,564165000,371364000,636090267,440123000,372030000,441822000,548003500,431686000,311000205,553111690,353656000,371685000,636014031,355709000,576285000,373615000,353656000,235070716,477435100,553111764,353180000,355813000,416237500,412322280,440014000,355813000,553111757,235060306,553111592,561003000,356483000,548906000,553111688,533000000,211512000,538005430,538003218,477739500,477059000,311019700,477950800,477852600,533000000,440214000,354426000,440552000,212031000,477066300,352366000,444080710,477440400,525025072,353656000,376227000,529739000,477060000,431602240,636015895,305212000,533795000,576277000,477173300,304303000,538090164,503492000,441837000,357020000,553111712,636014031,371198000,339300780,351413000,477549000,525025072,553111697,553111742,227222000,477860000,357687000,235070716,309913000,357910000,564856000,371337000,477903200,564583000,416241500,564042000,477440400,636092331,477860000,477454400,440575000,355133000,371685000,311019700,503359400,477897800,235113823,477476100,525015960,416221900,235070716,310625000,477083300,553111722,533000000,477062000,373349000,227222000,353563000,477938400,564861000,431700030,477059000,354246000,370966000,477061000,553111628,352206000,529605000,249672000,548352100,548305000,553111341,256524000,371198000,636090267,351178000,310465000,548092100,341834000,352586000,357020000,576277000,215737000,235060306,553111564,565758000,529086000,353242000,305212000,553111802,553111713,553111764,273457210,553111110,440095000,538090164,304303000,511011033,357203000,477542800,564190000,311019700,351943000,477296500,355966000,533170143,477440400,636014031,355813000,576397000,553111735,548924000,372594000,356065000,373371000,636012294,636090756,525025072,209828000,477059000,553111110,477795600,416238700,212169000,525021304,431704110,477440300,553111765,538005430,538004048,354246000,511011000,372381000,352778000,211512000,477066300,351260000,441429000,441034000,412439607,477060000,311021600,553111637,576277000,529739000,564260000,553111110,477860000,304303000,235060306,370887000,477549000,432470000,357091000,518393000,371198000,441675000,440450000,372580000,477158200,416411000,441923000,357050000,567046600,636090756,353923000,372865000,355230000,370927000,564856000,305051000,356591000,440222000,372613000,477852600,357154000,441166000,441822000,416411000,372907000,477062000,374148000,305212000,416243700,432969000,357203000,553111367,553111677,636092476,477739500,416171700,538003563,353563000,235100519,529604000,441493000,553111696,477061000,352085000,209828000,553111716,548446000,305051000,566613000,440196000,338090743,357146000,565967000,477947600,477440300,209828000,352796000,538090164,477250800,533000000,356889000,564943000,352533000,354746000,548092100,431671000,355430000,553111747,477261700,352181000,553111485,477066300,311000205,477066300,477440300,441240000,538003835,477062000,357910000,374230000,477110800,310465000,538090164,503486000,229016000,353496000,574442000,477060000,440113000,371319000,371529000,548487100,553111630,372580000,432957000,431310000,576277000,440257000,412330910,566533000,553111534,309955000,353628000,565967000,533000000,416241500,533000000,371319000,440481000,565236000,235073854,636015895,373462000,564856000,477852500,477685500,542185110,215737000,235059368,477351500,457648000,553111757,538090164,431587000,553111712,553111170,440123000,564042000,553111698,553111747,416171700,477435100,538003563,235059368,431167000,432744000,416043800,341834000,477454400,357055000,431006105,310465000,412420919,529586000,477947600,227222000,564943000,374315000,553111643,477860000,576276000,212031000,356158000,511011000,548895000,431167000,431308000,416003632,304303000,351589000,215222000,440195000,563010260,477163700,357154000,236273000,413278000,636092331,564683000,353069000,431294000,374148000,553111765)saved in [scratch_david.Papua_DSM_Test]
In [3]:
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]:
mmsi = [412203740,440014000,564165000,371364000,636090267,440123000,372030000,441822000,548003500,431686000,311000205,553111690,353656000,371685000,636014031,355709000,576285000,373615000,353656000,235070716,477435100,553111764,353180000,355813000,416237500,412322280,440014000,355813000,553111757,235060306,553111592,561003000,356483000,548906000,553111688,533000000,211512000,538005430,538003218,477739500,477059000,311019700,477950800,477852600,533000000,440214000,354426000,440552000,212031000,477066300,352366000,444080710,477440400,525025072,353656000,376227000,529739000,477060000,431602240,636015895,305212000,533795000,576277000,477173300,304303000,538090164,503492000,441837000,357020000,553111712,636014031,371198000,339300780,351413000,477549000,525025072,553111697,553111742,227222000,477860000,357687000,235070716,309913000,357910000,564856000,371337000,477903200,564583000,416241500,564042000,477440400,636092331,477860000,477454400,440575000,355133000,371685000,311019700,503359400,477897800,235113823,477476100,525015960,416221900,235070716,310625000,477083300,553111722,533000000,477062000,373349000,227222000,353563000,477938400,564861000,431700030,477059000,354246000,370966000,477061000,553111628,352206000,529605000,249672000,548352100,548305000,553111341,256524000,371198000,636090267,351178000,310465000,548092100,341834000,352586000,357020000,576277000,215737000,235060306,553111564,565758000,529086000,353242000,305212000,553111802,553111713,553111764,273457210,553111110,440095000,538090164,304303000,511011033,357203000,477542800,564190000,311019700,351943000,477296500,355966000,533170143,477440400,636014031,355813000,576397000,553111735,548924000,372594000,356065000,373371000,636012294,636090756,525025072,209828000,477059000,553111110,477795600,416238700,212169000,525021304,431704110,477440300,553111765,538005430,538004048,354246000,511011000,372381000,352778000,211512000,477066300,351260000,441429000,441034000,412439607,477060000,311021600,553111637,576277000,529739000,564260000,553111110,477860000,304303000,235060306,370887000,477549000,432470000,357091000,518393000,371198000,441675000,440450000,372580000,477158200,416411000,441923000,357050000,567046600,636090756,353923000,372865000,355230000,370927000,564856000,305051000,356591000,440222000,372613000,477852600,357154000,441166000,441822000,416411000,372907000,477062000,374148000,305212000,416243700,432969000,357203000,553111367,553111677,636092476,477739500,416171700,538003563,353563000,235100519,529604000,441493000,553111696,477061000,352085000,209828000,553111716,548446000,305051000,566613000,440196000,338090743,357146000,565967000,477947600,477440300,209828000,352796000,538090164,477250800,533000000,356889000,564943000,352533000,354746000,548092100,431671000,355430000,553111747,477261700,352181000,553111485,477066300,311000205,477066300,477440300,441240000,538003835,477062000,357910000,374230000,477110800,310465000,538090164,503486000,229016000,353496000,574442000,477060000,440113000,371319000,371529000,548487100,553111630,372580000,432957000,431310000,576277000,440257000,412330910,566533000,553111534,309955000,353628000,565967000,533000000,416241500,533000000,371319000,440481000,565236000,235073854,636015895,373462000,564856000,477852500,477685500,542185110,215737000,235059368,477351500,457648000,553111757,538090164,431587000,553111712,553111170,440123000,564042000,553111698,553111747,416171700,477435100,538003563,235059368,431167000,432744000,416043800,341834000,477454400,357055000,431006105,310465000,412420919,529586000,477947600,227222000,564943000,374315000,553111643,477860000,576276000,212031000,356158000,511011000,548895000,431167000,431308000,416003632,304303000,351589000,215222000,440195000,563010260,477163700,357154000,236273000,413278000,636092331,564683000,353069000,431294000,374148000,553111765]
In [9]:
plt.rcParams["figure.figsize"] = [12,7]
In [26]:
for m in mmsi:
themmsi = str(m)
q = '''
select
timestamp,
lat,
lon,
speed
from
[scratch_david.Papua_DSM_Test]
where mmsi = '''+str(m)+'''
and speed is not null
order by timestamp
'''
orb = Query(q)
lats_fast = np.array([float(o[1]) for o in orb if float(o[3])>=3])
lons_fast = np.array([float(o[2]) for o in orb if float(o[3])>=3])
lats_slow = np.array([float(o[1]) for o in orb if float(o[3])<3])
lons_slow = np.array([float(o[2]) for o in orb if float(o[3])<3])
#times_orb = np.array([float(o[0]) for o in orb])
fig = plt.figure()
m = Basemap(llcrnrlon=145, llcrnrlat=-5,
urcrnrlon=155,urcrnrlat=-2,projection='mill', resolution ='h')
m.drawmapboundary()
# m.drawcoastlines(linewidth=.2)
m.fillcontinents('#333333',lake_color='#333333')
x_fast,y_fast = m(lons_fast,lats_fast)
m.plot(x_fast,y_fast,".",alpha = .5)
x_slow,y_slow = m(lons_slow,lats_slow)
m.plot(x_slow,y_slow,".",alpha = .5, color = "#FF0000")
plt.title("mmsi = "+str(themmsi))
plt.show()
#plt.savefig(str(mmsi)+".png",bbox_inches='tight',dpi=300,transparent=True,pad_inches=.1, facecolor="#000000")
#plt.savefig(str(themmsi)+".png",bbox_inches='tight',dpi=300,pad_inches=.1,transparent=True)
plt.clf()
#break
In [ ]: