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
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
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
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
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
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
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
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
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
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
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
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
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
