def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): """Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create """ if fields is None: fields = fieldList c = sqlhelp.create('bin_broadcast',dbType=dbType) c.addPrimaryKey() if 'MessageID' in fields: c.addInt ('MessageID') if 'RepeatIndicator' in fields: c.addInt ('RepeatIndicator') if 'UserID' in fields: c.addInt ('UserID') if 'Spare' in fields: c.addInt ('Spare') if 'dac' in fields: c.addInt ('dac') if 'fi' in fields: c.addInt ('fi') if 'BinaryData' in fields: c.addBitVarying('BinaryData',1024) if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt('cg_t_arrival') # Receive timestamp from the AIS equipment 'T' c.addInt('cg_s_slotnum') # Slot received in c.addVarChar('cg_r',15) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt('cg_sec') # UTC seconds since the epoch c.addTimestamp('cg_timestamp') # UTC decoded cg_sec - not actually in the data stream return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType="postgres"): """Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create """ if fields is None: fields = fieldList c = sqlhelp.create("waterlevel", dbType=dbType) c.addPrimaryKey() if "MessageID" in fields: c.addInt("MessageID") if "RepeatIndicator" in fields: c.addInt("RepeatIndicator") if "UserID" in fields: c.addInt("UserID") if "Spare" in fields: c.addInt("Spare") if "dac" in fields: c.addInt("dac") if "fid" in fields: c.addInt("fid") if "month" in fields: c.addInt("month") if "day" in fields: c.addInt("day") if "hour" in fields: c.addInt("hour") if "min" in fields: c.addInt("min") if "stationid" in fields: c.addVarChar("stationid", 7) if "waterlevel" in fields: c.addInt("waterlevel") if "datum" in fields: c.addInt("datum") if "sigma" in fields: c.addInt("sigma") if "source" in fields: c.addInt("source") if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt("cg_t_arrival") # Receive timestamp from the AIS equipment 'T' c.addInt("cg_s_slotnum") # Slot received in c.addVarChar("cg_r", 15) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt("cg_sec") # UTC seconds since the epoch c.addTimestamp("cg_timestamp") # UTC decoded cg_sec - not actually in the data stream return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): """Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create """ if fields is None: fields = fieldList c = sqlhelp.create('ChanMngmt',dbType=dbType) c.addPrimaryKey() if 'MessageID' in fields: c.addInt ('MessageID') if 'RepeatIndicator' in fields: c.addInt ('RepeatIndicator') if 'UserID' in fields: c.addInt ('UserID') if 'Spare' in fields: c.addInt ('Spare') if 'ChanA' in fields: c.addInt ('ChanA') if 'ChanB' in fields: c.addInt ('ChanB') if 'TxRxMode' in fields: c.addInt ('TxRxMode') if 'power' in fields: c.addInt ('power') if dbType != 'postgres': if 'corner1_lon' in fields: c.addDecimal('corner1_lon',5,2) if dbType != 'postgres': if 'corner1_lat' in fields: c.addDecimal('corner1_lat',5,2) if dbType != 'postgres': if 'corner2_lon' in fields: c.addDecimal('corner2_lon',5,2) if dbType != 'postgres': if 'corner2_lat' in fields: c.addDecimal('corner2_lat',5,2) if 'IndicatorType' in fields: c.addInt ('IndicatorType') if 'ChanABandwidth' in fields: c.addInt ('ChanABandwidth') if 'ChanBBandwidth' in fields: c.addInt ('ChanBBandwidth') if 'TransZoneSize' in fields: c.addInt ('TransZoneSize') if 'Spare2' in fields: c.addInt ('Spare2') if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt('cg_t_arrival') # Receive timestamp from the AIS equipment 'T' c.addInt('cg_s_slotnum') # Slot received in c.addVarChar('cg_r',15) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt('cg_sec') # UTC seconds since the epoch c.addTimestamp('cg_timestamp') # UTC decoded cg_sec - not actually in the data stream if dbType == 'postgres': #--- EPSG 4326 : WGS 84 #INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '); c.addPostGIS('corner1','POINT',2,SRID=4326); #--- EPSG 4326 : WGS 84 #INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '); c.addPostGIS('corner2','POINT',2,SRID=4326); return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): """Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create """ if not fields: fields = fieldList c = sqlhelp.create('position', dbType=dbType) c.addPrimaryKey() if 'MessageID' in fields: c.addInt ('MessageID') if 'RepeatIndicator' in fields: c.addInt ('RepeatIndicator') if 'UserID' in fields: c.addInt ('UserID') if 'NavigationStatus' in fields: c.addInt ('NavigationStatus') if 'ROT' in fields: c.addInt ('ROT') if 'SOG' in fields: c.addDecimal('SOG',4,1) if 'PositionAccuracy' in fields: c.addInt ('PositionAccuracy') if dbType != 'postgres': if 'longitude' in fields: c.addDecimal('longitude',8,5) if dbType != 'postgres': if 'latitude' in fields: c.addDecimal('latitude',8,5) if 'COG' in fields: c.addDecimal('COG',4,1) if 'TrueHeading' in fields: c.addInt ('TrueHeading') if 'TimeStamp' in fields: c.addInt ('TimeStamp') if 'RegionalReserved' in fields: c.addInt ('RegionalReserved') if 'Spare' in fields: c.addInt ('Spare') if 'RAIM' in fields: c.addBool('RAIM') # Include both ITDMA and SOTDMA so we have one position table. commstate.sql_fields(c) if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt('cg_t_arrival') # Receive timestamp from the AIS equipment 'T' c.addInt('cg_s_slotnum') # Slot received in. c.addVarChar('cg_r',15) # Receiver station ID. c.addInt('cg_sec') # UTC seconds since the epoch. c.addTimestamp('cg_timestamp') # UTC decoded cg_sec - not actually in the data stream if dbType == 'postgres': c.addPostGIS('Position','POINT',2,SRID=4326); return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): ''' Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create ''' if None == fields: fields = fieldList c = sqlhelp.create(dbTableName,dbType=dbType) c.addPrimaryKey() if 'MessageID' in fields: c.addInt ('MessageID') if 'RepeatIndicator' in fields: c.addInt ('RepeatIndicator') if 'UserID' in fields: c.addInt ('UserID') if 'partnum' in fields: c.addInt ('partnum') if 'name' in fields: c.addVarChar('name',20) if 'shipandcargo' in fields: c.addInt ('shipandcargo') if 'callsign' in fields: c.addVarChar('callsign',7) if 'vendorid' in fields: c.addVarChar('vendorid',7) if 'dimA' in fields: c.addInt ('dimA') if 'dimB' in fields: c.addInt ('dimB') if 'dimC' in fields: c.addInt ('dimC') if 'dimD' in fields: c.addInt ('dimD') if 'mothership' in fields: c.addInt ('mothership') if 'spare' in fields: c.addInt ('Spare') if addCoastGuardFields: # c.addInt('cg_rssi') # Relative signal strength indicator # c.addInt('cg_d') # dBm receive strength # c.addInt('cg_T') # Receive timestamp from the AIS equipment # c.addInt('cg_S') # Slot received in # c.addVarChar('cg_x',10) # Idonno c.addVarChar('cg_r',15) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt('cg_sec') # UTC seconds since the epoch c.addTimestamp('cg_timestamp') # UTC decoded cg_sec - not actually in the data stream return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): """Return the sqlhelp object to create the table. @param fields: which fields to put in the create. Defaults to all. @param extraFields: A sequence of tuples containing (name,sql type) for additional fields @param addCoastGuardFields: Add the extra fields that come after the NMEA check some from the USCG N-AIS format @type addCoastGuardFields: bool @param dbType: Which flavor of database we are using so that the create is tailored ('sqlite' or 'postgres') @return: An object that can be used to generate a return @rtype: sqlhelp.create """ if fields is None: fields = fieldList c = sqlhelp.create('sls_lockorder', dbType=dbType) c.addPrimaryKey() if 'vessel' in fields: c.addVarChar('vessel', 15) if 'direction' in fields: c.addBool('direction') if 'ETA_month' in fields: c.addInt('ETA_month') if 'ETA_day' in fields: c.addInt('ETA_day') if 'ETA_hour' in fields: c.addInt('ETA_hour') if 'ETA_min' in fields: c.addInt('ETA_min') if 'reserved' in fields: c.addInt('reserved') if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt( 'cg_t_arrival') # Receive timestamp from the AIS equipment 'T' c.addInt('cg_s_slotnum') # Slot received in c.addVarChar( 'cg_r', 15 ) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt('cg_sec') # UTC seconds since the epoch c.addTimestamp( 'cg_timestamp' ) # UTC decoded cg_sec - not actually in the data stream return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'): if not fields: fields = fieldList c = sqlhelp.create('bsreport',dbType=dbType) c.addPrimaryKey() if 'MessageID' in fields: c.addInt ('MessageID') if 'RepeatIndicator' in fields: c.addInt ('RepeatIndicator') if 'UserID' in fields: c.addInt ('UserID') if 'Time_year' in fields: c.addInt ('Time_year') if 'Time_month' in fields: c.addInt ('Time_month') if 'Time_day' in fields: c.addInt ('Time_day') if 'Time_hour' in fields: c.addInt ('Time_hour') if 'Time_min' in fields: c.addInt ('Time_min') if 'Time_sec' in fields: c.addInt ('Time_sec') if 'PositionAccuracy' in fields: c.addInt ('PositionAccuracy') if dbType != 'postgres': if 'Position_longitude' in fields: c.addDecimal('Position_longitude',8,5) if dbType != 'postgres': if 'Position_latitude' in fields: c.addDecimal('Position_latitude',8,5) if 'fixtype' in fields: c.addInt ('fixtype') if 'Spare' in fields: c.addInt ('Spare') if 'RAIM' in fields: c.addBool('RAIM') commstate.sotdma_sql_fields(c) if addCoastGuardFields: # c.addInt('cg_s_rssi') # Relative signal strength indicator # c.addInt('cg_d_strength') # dBm receive strength # c.addVarChar('cg_x',10) # Idonno c.addInt('cg_t_arrival') # Receive timestamp from the AIS equipment 'T' c.addInt('cg_s_slotnum') # Slot received in c.addVarChar('cg_r',15) # Receiver station ID - should usually be an MMSI, but sometimes is a string c.addInt('cg_sec') # UTC seconds since the epoch c.addTimestamp('cg_timestamp') # UTC decoded cg_sec - not actually in the data stream if dbType == 'postgres': #--- EPSG 4326 : WGS 84 #INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES (4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]','+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '); c.addPostGIS('Position','POINT',2,SRID=4326); return c