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 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