Script used to convert lat-lon footprints to map coordinates, reading them from one database and writing to another.

fpc.groovy

/**
 Script to take footprint polygons from one database, perform a conversion on
 them and write them to another database.
*/
MARS_RADIUS_METERS = 3394500 as double
MARS_CIRCUM_METERS = 2*Math.PI*MARS_RADIUS_METERS as double

// cutoff
EQ_POLAR_LAT_LIMIT = 65

// Mysql Connector/J
@Grab(group='mysql', module='mysql-connector-java', version='3.1.13')

// JTS
@Grab(group='com.vividsolutions', module='jts', version='1.13')
import com.vividsolutions.jts.io.*
import com.vividsolutions.jts.geom.*
import com.vividsolutions.jts.geom.impl.*

// number of inserts to batch
BATCH_COUNT = 100

// short code => column definition enum
proj =
[
    np: 'polar_north',
    sp: 'polar_south',
    eq: 'equatorial'
]

// URL, username, password, driver
DB_IN =
[
    'jdbc:mysql://host:3306/CATALOG1',
    'USERNAME',
    'PASSWORD',
    'com.mysql.jdbc.Driver'
]

DB_OUT =
[
    'jdbc:mysql://host:3306/CATALOG2',
    'USERNAME',
    'PASSWORD',
    'com.mysql.jdbc.Driver'
]

/**
 @param coord Coordinate object
 @param pc Projection code (eq, np, sp)
*/
def transformCoordinate(coord, pc)
{

    double r, t

    switch (pc)
    {
        case 'np':
        case 'sp':
            r = (90 - Math.abs(coord.y)) / ( 90 - EQ_POLAR_LAT_LIMIT) * MARS_CIRCUM_METERS / 2
            t = Math.toRadians(coord.x)
            coord.x = r * Math.cos(t)
            coord.y = r * Math.sin(t)
        break

        default:
            coord.x = (coord.x > 180 ? coord.x - 360 : coord.x) * MARS_CIRCUM_METERS / 360
            coord.y = coord.y * MARS_CIRCUM_METERS / 180
        break
    }
}

/**
 Represents any of our typical (non-WFS) tables
*/
class FootprintTable
{

    // padded ranges
    final POLAR_LAT_UPPER = +60
    final POLAR_LAT_LOWER = -60

    final EQ_LAT_UPPER = +70
    final EQ_LAT_LOWER = -70

    def catalog, table
    def id = 'product_id', txt = 'rationale_desc', roi = 'FOOTPRINT'
    def ctr = 'center', lat = 'center_latitude', lon = 'center_longitude'

    def getSelectSQL()
    {
        "SELECT $id, $lat, $lon, $txt, AsBinary($ctr) AS $ctr, AsBinary($roi) AS $roi FROM $catalog.$table tab"
    }

    def getFootprints(pc)
    {
        String sql = getSelectSQL()
        switch (pc)
        {
            case 'np':
                return sql + " WHERE tab.$lat > $POLAR_LAT_UPPER"
            case 'sp':
                return sql + " WHERE tab.$lat < $POLAR_LAT_LOWER"
            case 'eq':
                return sql + " WHERE tab.$lat >= $EQ_LAT_LOWER AND tab.$lat <= $EQ_LAT_UPPER"
            default:
                return sql
        }
    }
}

/**
 Represents our WFS tables (which are actually views) which need to be joined
 with their non-WFS sibling.
*/
class WfsFootprintTable extends FootprintTable
{
    @Override
    def getSelectSQL()
    {
        "SELECT tab.$id, tab.$lat, tab.$lon, tab.$txt, AsBinary(wfs.$ctr) AS $ctr, AsBinary(wfs.$roi) AS $roi FROM $catalog.$table tab " +
        "JOIN $catalog.${table}_WFS wfs ON tab.$id = wfs.$id"
    }
}

/**
 *Sigh*
*/
class RdrFootprintTable extends WfsFootprintTable
{
    @Override
    def getFootprints(pc)
    {
        super.getFootprints(pc) + " AND tab.rdr_product_type = 'RED'"
    }
}

def types =
[
    'ctx':    new FootprintTable( catalog: 'CTX', table: 'CTX' ),
    'crism':  new FootprintTable( catalog: 'CRISM', table: 'CRISM', id: 'observation_id', txt: 'comment', lat: 'image_center_latitude', lon: 'image_center_longitude'),
    'moc':    new FootprintTable( catalog: 'MGS', table: 'MOC' ),
    'hiwish': new WfsFootprintTable( catalog: 'HiRISE', table: 'Suggested_Observations', id: 'id', txt: 'stl_description', roi: 'region_of_interest' ),
    'hirdr':  new RdrFootprintTable( catalog: 'HiRISE', table: 'RDR_Products', id: 'observation_id', lat: 'image_center_latitude', lon: 'image_center_longitude')
]

cli = new CliBuilder(usage: ' [options] ', header:'options')
cli.n('No Insert (dry run)')
cli.t(args: 1, argName: 'Type', types.keySet().join('|'))
cli.p(args: 1, argName: 'Proj', proj.keySet().join('|'))
cli.h('help')

def opts = cli.parse(args)

if (opts.h || !(opts.t && opts.p) || !types[opts.t] || !proj[opts.p] )
{
    cli.usage()
    System.exit(1)
}

def type = types[opts.t]

SQL_IN = type.getFootprints(opts.p)

SQL_OUT = """
INSERT INTO Footprints
(
    IDENTIFIER,
    DESCRIPTION,
    CENTER_POINT,
    CENTER_Y,
    CENTER_X,
    REGION_OF_INTEREST,
    PROJECTION,
    TYPE
)
VALUES (?, ?, PointFromWKB(?), ?, ?, GeomFromWKB(?), ?, ?)"""

import java.sql.*
import groovy.sql.*

Connection cin = Sql.newInstance(*DB_IN).createConnection()
Connection cout = Sql.newInstance(*DB_OUT).createConnection()

cout.setAutoCommit(false)

println '<<<' + SQL_IN

PreparedStatement pin = cin.prepareStatement(SQL_IN, Statement.NO_GENERATED_KEYS)
ResultSet rs = pin.executeQuery()

println '>>>' + SQL_OUT

PreparedStatement pout = cout.prepareStatement(SQL_OUT)

WKBReader bin = new WKBReader()
WKBWriter bout = new WKBWriter() // 2D, Big endian

try
{
    for (row = 0 ; rs.next() ; row++)
    {
    String id = rs.getString(type.id)

    lat = rs.getDouble(type.lat)
    lon = rs.getDouble(type.lon)

    Geometry geo = bin.read(rs.getBytes(type.roi));
    Geometry ctr = bin.read(rs.getBytes(type.ctr))

    Coordinate cen = new Coordinate(lon, lat, 0)
    transformCoordinate(cen, opts.p)

    println "$id ${cen.x},${cen.y}"

    if (geo.isEmpty()) throw new Exception("Empty footprint ${opts.t}.$id")
    if (!geo.isValid()) throw new Exception("Invalid footprint for ${opts.t}.$id = ${geo.toText()}")

    //if (!ctr.within(geo)) throw new Exception("Invalid center for ${opts.t}.$id")
    //if (!geo.contains(ctr)) throw new Exception("Invalid center for ${opts.t}.$id")

    geo.coordinates.each() { transformCoordinate it,opts.p }

    if (!geo.isValid()) throw new Exception("Invalid transform ${opts.t}.$id = ${geo.toText()}")

    n = 0

    pout.with
    {
       setString(++n, id)
       setString(++n, rs.getString(type.txt))
       setBytes(++n, bout.write(ctr))
       setDouble(++n, cen.y)
       setDouble(++n, cen.x)
       setBytes(++n, bout.write(geo))
       setString(++n, proj[opts.p])
       setString(++n, opts.t)

       addBatch()

       if(!opts.n && (row % BATCH_COUNT) == 1) executeBatch()
    }
    }
    if (!opts.n) pout.executeBatch()
    cout.commit()
}
catch (Exception ex)
{
   println ex
   cout.rollback()
}
finally
{
    rs.close()

    pout.close()
    pin.close()

    cout.close()
    cin.close()
}