#!/usr/bin/env python

"""
Read in the USGS walrus info bank navigation data

Integrated navigation (SINS) data. For example:

  http://walrus.wr.usgs.gov/infobank/w/w1377sc/seis/w-13-77-sc.stk.segy.tar

! 07/07/2004 clint w-13-77-sc.051
! /infobank/programs/time/lines2times/lines2times
! Integrated navigation (SINS) data
! (Year, Day, Hour, Min, Sec, Tenths, Latitude, Longitude)
! (i4.4, i3.3, 3i2.2, i1.1, t16, f9.5, t26, f10.5)
! (%4.4i%3.3i3%2.2i%1.1i %9.5f %10.5f\\n)
! times are fake
!  
!*year,day,hr,min,sec,tenths,lat,long,line,shot,easting,northing,depth

Here is a sample line with no depth:

19770170001373  34.16973 -120.16525 WD-017              1072   1345077  250781                

"""

import os	# os.remove()
import sys	# sys.exit()
import string
import sqlite	# pysqlite
import time

def createDatabaseTable(cx):
    """
    Create an sqlite database table for USGS navigation data
    """
    createStr = "CREATE TABLE nav"
    createStr += " ( time TIMESTAMP, lat REAL, lon REAL, line VARCHAR(10), shot INTEGER"
    createStr += " ,easting INTEGER, northing INTEGER"
    createStr += " ,depth REAL"
    createStr += " );"
    cu = cx.cursor()
    cu.execute(createStr)
    cx.commit()

def loadData(cx,datafile):
    """
    read data from a USGS walrus nav file
    """
    count = 0
    cu = cx.cursor()
    for line in open(datafile).xreadlines():
        count += 1
        if 0==count%1000: print count
        if '!' == line[0]: continue # skip comments
        list = line.split()

        yr = list[0][0:4]
        julianday = list[0][4:7]
        hr = list[0][7:9]
        min = list[0][9:11]
        sec = list[0][11:13]
        tenths = list[0][13:14]

        strTime = yr+julianday#+hr#+sec
        t = time.strptime(strTime,"%Y%j")
        month = t.tm_mon
        day = t.tm_mday
        sqlDayTime  = yr+'-'+("%02d" % month)+'-'+ ("%02d" % day)
        sqlDayTime += " " + str(hr)+':'+min+':'+sec

        lat = list[1]
        lon = list[2]
        seisLine = list[3]
        shot = list[4]
        easting = list[5]
        northing = list[6]
        if len(list) == 8:
            depth=list[7]
        else: depth=0
        # Create the string for the insertion
        insStr =  "INSERT INTO nav"
        insStr += " ( time, lat, lon, line, shot, easting, northing, depth )"
        insStr += " values"
        insStr += " ('"+sqlDayTime+"'"
        insStr += " , "+str(lat)
        insStr += " , "+str(lon)
        insStr += " , '"+seisLine+"'"
        insStr += " , "+str(shot)
        insStr += " , "+str(easting)
        insStr += " , "+str(northing)
        insStr += " , "+str(depth)
        insStr += " );"
        cu.execute(insStr)
    cx.commit()
    return

def createDatabase(dbFilename,datafile=None):
    if os.access(dbFilename,os.W_OK):
        # FIX: catch exception if bad permissions
        os.remove(dbFilename);
    cx = sqlite.connect (dbFilename)

    createDatabaseTable(cx)
    if datafile: loadData(cx,datafile)
    return cx

if __name__ == "__main__":
    createDatabase('nav.db','w-13-77-sc.420_051')