Example #1
0
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
Example #2
0
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
Example #3
0
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
Example #4
0
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
Example #5
0
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
Example #6
0
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
Example #7
0
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