Exemple #1
0
    def post(self):
        '''
        Import foreign schema for a rosbag file
        '''

        req = sql.SQL("""
            create schema if not exists {schema};
            select coalesce(max(pcid) + 1, 1) as pcid from pointcloud_formats
        """).format(schema=sql.Identifier(api.payload['schema']))
        pcid = Database.query_asdict(req)[0]['pcid']

        identifiers = {k: sql.Identifier(v) for k, v in api.payload.items()}
        req = sql.SQL("""
            import foreign schema {rosbag} limit to (pointcloud_formats)
            from server {server} into {schema} options (pcid %(pcid)s);

            insert into pointcloud_formats select pcid, srid, schema
            from {schema}.pointcloud_formats;

            import foreign schema {rosbag} except (pointcloud_formats)
            from server {server} into {schema} options (pcid %(pcid)s)
        """).format(**identifiers)
        Database.rowcount(req, {'pcid': str(pcid)})

        return "foreign schema imported", 201
Exemple #2
0
    def post(self):
        '''
        Create a foreign server
        '''
        drivers = Database.notices(multicorn_drivers_sql)[-1]
        drivers = drivers.strip('NOTICE: \n').split(',')

        if api.payload['driver'] not in drivers:
            return abort(
                400,
                '{} driver does not exist, available drivers are {}'
                .format(api.payload['driver'], drivers))

        options = api.payload['options']
        options.update(wrapper=api.payload['driver'])
        options = {k: str(v) for k, v in options.items()}

        options_sql = sql.SQL(', ').join([
            sql.SQL(' ').join((sql.Identifier(opt), sql.Placeholder(opt)))
            for opt in options
        ])

        req = sql.SQL("""
            create server {name} foreign data wrapper multicorn options (
                {options}
            );
        """).format(name=sql.Identifier(api.payload['name']), options=options_sql)

        Database.rowcount(req, options)

        req = servers_sql + ' where srvname = %(name)s'

        return Database.query_asjson(req, api.payload), 201
Exemple #3
0
 def delete(self, id):
     '''Delete a session given its identifier'''
     res = Database.rowcount("delete from li3ds.session where id=%s",
                             (id, ))
     if not res:
         nssession.abort(404, 'Session not found')
     return '', 410
Exemple #4
0
 def delete(self, id):
     '''Delete a transformation given its identifier'''
     res = Database.rowcount("delete from li3ds.transfo_tree where id=%s",
                             (id, ))
     if not res:
         nstft.abort(404, 'Transformation tree not found')
     return '', 410
Exemple #5
0
 def delete(self, id):
     '''Delete a transformation given its identifier'''
     res = Database.rowcount("delete from li3ds.transfo_tree where id=%s", (id,))
     if not res:
         nstft.abort(404, 'Transformation tree not found')
     return '', 410
Exemple #6
0
 def delete(self, id):
     '''Delete a sensor given its identifier'''
     res = Database.rowcount("delete from li3ds.sensor where id=%s", (id,))
     if not res:
         nssensor.abort(404, 'Sensor not found')
     return '', 410
Exemple #7
0
 def delete(self, id):
     '''Delete a datasource given its identifier'''
     res = Database.rowcount("delete from li3ds.posdatasource where id=%s", (id,))
     if not res:
         nspds.abort(404, 'PosDatasource not found')
     return '', 410
Exemple #8
0
 def delete(self, id):
     '''Delete a posprocessing entry'''
     res = Database.rowcount("delete from li3ds.posprocessing where id=%s", (id,))
     if not res:
         nspds.abort(404, 'PosProcessing not found')
     return '', 410
Exemple #9
0
 def delete(self, id):
     '''Delete a referential given its identifier'''
     res = Database.rowcount("delete from li3ds.referential where id=%s", (id,))
     if not res:
         nsrf.abort(404, 'referential not found')
     return '', 410
Exemple #10
0
    def post(self):
        '''
        Create a materialized view
        '''
        payload = defaultpayload(api.payload)

        view_parts = payload['view'].split('.')
        if len(view_parts) != 2:
            abort(400, 'view should be in the form schema.view ({view})'.format(**payload))
        view_schema, view = view_parts

        table_parts = payload['table'].split('.')
        if len(table_parts) != 2:
            abort(400, 'table should be in the form schema.table ({table})'.format(**payload))
        table_schema, table = table_parts

        if payload['srid'] is not None:
            if not payload['sbet']:
                abort(400, 'srid cannot be set when sbet is not')
            if payload['srid'] == 0:
                abort(400, 'srid must not be 0')

        if payload['sbet']:
            srid = payload['srid'] or 4326
            schema_quat = schema_quat_4326 if srid == 4326 else schema_quat_projected

            req = '''
                select pcid from pointcloud_formats
                where srid = %(srid)s and schema = %(schema_quat)s
            '''
            res = Database.query_asdict(req, {'schema_quat': schema_quat, 'srid': srid})
            if not res:
                req = '''
                    with tmp as (
                        select coalesce(max(pcid) + 1, 1) as newid from pointcloud_formats
                    )
                    insert into pointcloud_formats(pcid, srid, schema)
                    select tmp.newid, %(srid)s, %(schema_quat)s from tmp
                    returning pcid
                '''
                res = Database.query_asdict(req, {'schema_quat': schema_quat, 'srid': srid})
            pcid = res[0]['pcid']

            # Euler angles (roll, pitch, heading) are converted to quaternions. This is done
            # using sequence number 9 in
            # https://ntrs.nasa.gov/archive/nasa/casi.ntrs.nasa.gov/19770024290.pdf
            # The following angles are used:
            # θ1 = -m_plateformHeading + π/2
            # θ2 = m_roll
            # θ3 = -m_pitch
            #
            # For the heading a correction is used:
            # heading = m_plateformHeading - 0.72537437089 * (longitude - 0.0523598775598)
            # 0.72537437089 = sin(46.5°), 46.5° = latitude origin of Lambert93
            # 0.0523598775598 radians = 3 degrees (east of Greenwich): longitude origin of Lambert93
            # longitude is in radians (x is in degrees in the Sbet file)
            #
            select = '''
                with param as (
                    select sin(pc_get(point, 'm_roll') * 0.5) as t0,
                           cos(pc_get(point, 'm_roll') * 0.5) as t1,
                           sin(-pc_get(point, 'm_pitch') * 0.5) as t2,
                           cos(-pc_get(point, 'm_pitch') * 0.5) as t3,
                           sin((-(pc_get(point, 'm_plateformHeading') -
                                 0.72537437089 * (radians(pc_get(point, 'x')) - 0.0523598775598))
                               + pi() / 2) * 0.5) t4,
                           cos((-(pc_get(point, 'm_plateformHeading') -
                                 0.72537437089 * (radians(pc_get(point, 'x')) - 0.0523598775598))
                               + pi() / 2) * 0.5) t5,
                           st_transform(
                               st_setsrid(
                                   st_makepoint(pc_get(point, 'x'), pc_get(point, 'y')),
                                   4326),
                                %(srid)s) as xy,
                           pc_get(point, 'z') as z,
                           extract(epoch from
                                make_interval(weeks => (
                                    -- compute the GPS week number
                                    extract(days from
                                            timestamp %(filedate)s - gps.timestart) / 7)::int)
                                    -- find the beginning of GPS week
                                    + gps.timestart
                                    -- add the seconds
                                    + make_interval(secs => pc_get(point, 'm_time'))
                                ) as time
                            , paid
                    from (select
                            (row_number() over ())-1 as paid
                            , pc_explode(points) as point from {table_schema}.{table}) _
                    , (select timestamp '1980-01-06 00:00:00' timestart) as gps
                ),
                point as (
                    select pc_makepoint(%(pcid)s,
                                        ARRAY[
                                            -t4*t0*t2+t5*t1*t3,
                                            -t4*t2*t1+t0*t5*t3,
                                             t4*t0*t3+t2*t5*t1,
                                             t4*t1*t3+t0*t2*t5,
                                            st_x(xy), st_y(xy), z, time
                                        ]) as pt,
                           paid, param.time as time
                    from param
                )
                select paid as id, pc_patch(pt order by time)::pcpatch(%(pcid)s) as points
                from point group by paid
            '''
            # extract date from LANDINS_20170516_075157_PP
            filedate = payload['table'].split('_')[1]
            filedate = '{}-{}-{}'.format(filedate[0:4], filedate[4:6], filedate[6:8])
            parameters = {'pcid': pcid, 'srid': srid, 'filedate': filedate}
        else:
            select = '''
                select _id-1 as id, points from (
                    select row_number() over () as _id, points from {table_schema}.{table}
                ) _ order by id
            '''
            parameters = {}

        identifiers = map(sql.Identifier, (view_schema, view, table_schema, table))
        identifiers = zip(('view_schema', 'view', 'table_schema', 'table'), identifiers)
        identifiers = dict(identifiers)

        req = sql.SQL('''
            create materialized view {view_schema}.{view} as %s;
            create unique index on {view_schema}.{view} (id)
        ''' % select).format(**identifiers)
        Database.rowcount(req, parameters)

        if payload['sbet']:
            # create two indexes on pc_patchmin('time') and pc_patchmax('time'). This is
            # to make the time interpolation operation fast
            req = sql.SQL('''
                create index on {view_schema}.{view} (pc_patchmin(points, 'time'));
                create index on {view_schema}.{view} (pc_patchmax(points, 'time'))
            ''').format(**identifiers)
            Database.rowcount(req)
        else:
            req = sql.SQL('''
                create index on {view_schema}.{view} (pc_patchavg(points, 'time'));
            ''').format(**identifiers)
            Database.rowcount(req)

        req = views_sql + ' where v.schemaname = %(view_schema)s and v.matviewname = %(view)s'
        parameters = {'view_schema': view_schema, 'view': view}

        return Database.query_asjson(req, parameters), 201
Exemple #11
0
    def post(self):
        '''
        Create a foreign table
        '''
        payload = defaultpayload(api.payload)

        if len(payload['table'].split('.')) != 2:
            abort(400, 'table should be in the form schema.table ({table})'.format(**payload))

        for server in Database.query_asdict(servers_sql):
            if payload['server'] == server['name']:
                break
        else:
            abort(400, 'no server {}'.format(payload['server']))

        schema_options = {'metadata': 'true'}

        if server['driver'] == 'fdwli3ds.Rosbag':
            if 'topic' not in payload.get('options', {}):
                abort(400, '"topic" option required for Rosbag')
            schema_options.update(topic=payload['options']['topic'])
        elif server['driver'] == 'fdwli3ds.EchoPulse':
            if 'directory' not in payload.get('options', {}):
                abort(400, '"directory" option required for EchoPulse')
            schema_options.update(directory=payload['options']['directory'])

        schema_options = {k: str(v) for k, v in schema_options.items()}

        schema, tablename = payload['table'].split('.')

        server_identifier = sql.Identifier(payload['server'])
        schema_identifier = sql.Identifier(schema)
        table_identifier = sql.Identifier(tablename)
        table_schema_identifier = sql.Identifier(tablename + '_schema')

        schema_options_sql = sql.SQL(',').join([
            sql.SQL(' ').join((sql.Identifier(opt), sql.Placeholder(opt)))
            for opt in schema_options
        ])

        req = sql.SQL("""
            create foreign table {schema}.{table_schema} (
                schema text
            )
            server {server} options (
                {options}
            );
            with tmp as (
                select coalesce(max(pcid) + 1, 1) as newid from pointcloud_formats
            )
            insert into pointcloud_formats(pcid, srid, schema)
            select tmp.newid, %(srid)s, schema from {schema}.{table_schema}, tmp
            returning pcid
        """).format(schema=schema_identifier, table_schema=table_schema_identifier,
                    server=server_identifier, options=schema_options_sql)

        parameters = {'srid': payload['srid']}
        parameters.update(schema_options)
        pcid = Database.query_asdict(req, parameters)[0]['pcid']

        req = sql.SQL("drop foreign table {schema}.{table_schema}").format(
            schema=schema_identifier, table_schema=table_schema_identifier)

        Database.rowcount(req)

        options = payload['options']
        options.update(pcid=str(pcid))
        options = {k: str(v) for k, v in options.items()}

        options_sql = sql.SQL(', ').join([
            sql.SQL(' ').join((sql.Identifier(opt), sql.Placeholder(opt)))
            for opt in options
        ])

        req = sql.SQL("""
            create foreign table {schema}.{table} (
                points pcpatch(%(pcid_int)s)
            ) server {server}
                options (
                    {options}
                )
        """).format(schema=schema_identifier, table=table_identifier,
                    server=server_identifier, options=options_sql)

        parameters = {'pcid': str(pcid), 'pcid_int': pcid}
        parameters.update(options)
        Database.rowcount(req, parameters)

        req = tables_sql + ' where c.relname = %(tablename)s and s.srvname = %(server)s' \
                           ' and n.nspname = %(schema)s'

        parameters = {'schema': schema, 'tablename': tablename, 'server': payload['server']}

        return Database.query_asjson(req, parameters), 201
Exemple #12
0
 def delete(self, id):
     '''Delete a referential given its identifier'''
     res = Database.rowcount("delete from li3ds.referential where id=%s", (id,))
     if not res:
         nsrf.abort(404, 'referential not found')
     return '', 410
Exemple #13
0
 def delete(self, id):
     '''Delete a platform given its identifier'''
     res = Database.rowcount("delete from li3ds.platform where id=%s", (id,))
     if not res:
         nspfm.abort(404, 'Platform not found')
     return '', 410