Postgres Point fields - should RecordParser and other downstream Transforms/Writers support?

[davidpablocohn]

We could handle this after the parsing phase with a derived data transform. Alternatively, maybe we should just handle this at the data publishing step rather than at the data aggregation step. Regardless, we're going to hold off on this for now.

From cromsos notes:

On the Friday March 27 call we identified the potential problem that the point geometry filed might cause for writers that write to the database.  The problem is that there is no 1-1 mapping of this field from the incoming sensor data.  The value for this field must be synthesized from the latitude and longitude information.  A quick outline of the point geometry field and how we write to it is below.

Postgres has a POINT data type:
https://www.postgresql.org/docs/12/datatype-geometric.html

Not all tables have this point field - but, any sensor that records a geographic position has a POINT field in its corresponding data table.

An example from our models.py file for the gnss_gga data table shows how we create the point feld using the native postgres point data type:

class GnssGgaBowArchive(models.Model):
   """Bow GNSS GGA archive data."""
 
   sensor_id = models.CharField(max_length=20)
   datetime = models.DateTimeField(primary_key=True)
   point = models.PointField()
   latitude = models.FloatField()
   longitude = models.FloatField()
   point_flags = models.CharField(max_length=8)
   latitude_flags = models.CharField(max_length=8)
   longitude_flags = models.CharField(max_length=8)
 
   class Meta(object):
       """Foo."""
 
       managed = True
       db_table = 'gnss_gga_bow_archive'

To write to this point field we simply combine the latitude and longitude variables into a POINT string of format ‘POINT(yyy.yyyyy, xx.xxxxx)’

Note yyy.yyyy and xx.xxxxx are just meant to represent decimal degrees latitude and longitude.

def insertGPS(LAT, LON, ST_ISO8601):
   """A function to insert gps gga data to the sensor tables."""
   SENSOR_ID = 'gnss001'
   POINT = 'POINT(' + str(LON) + ' ' + str(LAT) + ')'
   LAT_FLAGS = flag.mainflag(SENSOR_ID, 'latitude', LAT)
   LON_FLAGS = flag.mainflag(SENSOR_ID, 'longitude', LON)
   POINT_FLAGS = flag.combine(LAT_FLAGS, LON_FLAGS)
 
   # Create the database insert statement
   new_insert = am.GnssGgaBowArchive(
       sensor_id=SENSOR_ID,
       datetime=ST_ISO8601,
       latitude=float(LAT),
       longitude=float(LON),
       point=POINT,
       latitude_flags=LAT_FLAGS,
       longitude_flags=LON_FLAGS,
       point_flags=POINT_FLAGS)
   new_insert.save(force_insert=True)

Summary, so I guess a transformation is needed here because we are creating data to populate the point field.  But the transformation is very straightforward.