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
import sqlhelp
c = sqlhelp.create('utcquery',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 'Spare1' in fields: c.addInt ('Spare1')
if 'DestID' in fields: c.addInt ('DestID')
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
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
import sqlhelp
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 None == fields: fields = fieldList
import sqlhelp
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')
commstate.sql_fields(c) # Include both itdma and sotdma so we have one position table
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':
c.addPostGIS('Position','POINT',2,SRID=4326);
return c
def sqlCreate(fields=None, extraFields=None, addCoastGuardFields=True, dbType='postgres'):
if None == fields: fields = fieldList
import sqlhelp
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')
#if 'state_syncstate' in fields: c.addInt ('state_syncstate')
#if 'state_slottimeout' in fields: c.addInt ('state_slottimeout')
#if 'state_slotoffset' in fields: c.addInt ('state_slotoffset')
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
