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 referential'''
return Database.query_asdict(
"insert into li3ds.referential (name, description, srid, root, sensor) "
"values (%(name)s, %(description)s, %(srid)s, %(root)s, %(sensor)s) "
"returning *",
defaultpayload(api.payload)
), 201
def post(self, id):
'''Add a new posprocessing tool that has generated the given datasource'''
return Database.query_asdict(
"insert into li3ds.posprocessing (launched, tool, description, source, target) "
"values (%(launched)s, %(tool)s, %(description)s, %(source)s, {}) "
"returning *".format(id),
defaultpayload(api.payload)
), 201
def post(self, id):
'''Create a new platform configuration'''
return Database.query_asdict(
"insert into li3ds.platform_config (name, owner, platform, transfo_trees) "
"values (%(name)s, %(owner)s, {}, %(transfo_trees)s) "
"returning *".format(id),
defaultpayload(api.payload)
), 201
def post(self):
'''Create a session'''
return Database.query_asdict(
"insert into li3ds.session (name, start_time, end_time, project, platform) "
"values (%(name)s, %(start_time)s, %(end_time)s, %(project)s, %(platform)s) "
"returning *",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a platform'''
return Database.query_asdict(
"insert into li3ds.platform (name, description, start_time, end_time) "
"values (%(name)s, %(description)s, %(start_time)s, %(end_time)s) "
"returning *",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a referential'''
return Database.query_asdict(
"insert into li3ds.referential (name, description, srid, sensor) "
"values (%(name)s, %(description)s, %(srid)s, %(sensor)s) "
"returning *",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a datasource'''
return Database.query_asdict(
"insert into li3ds.posdatasource (referential, session, uri) "
"values (%(referential)s, %(session)s, %(uri)s) "
"returning *",
defaultpayload(api.payload)
), 201
def post(self):
'''
Create a project.
Also create a schema with the project's name.
'''
return Database.query_asdict(
"select li3ds.create_project(%(name)s, %(timezone)s, %(extent)s) as id",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a transformation between referentials'''
return Database.query_asdict(
"""
insert into li3ds.transfo_tree (name, isdefault, sensor_connections,
owner, transfos)
values (%(name)s,%(isdefault)s,%(sensor_connections)s,%(owner)s,%(transfos)s)
returning *
""", defaultpayload(api.payload)), 201
def post(self):
'''Create a sensor'''
return Database.query_asdict(
"""
insert into li3ds.sensor (serial_number, short_name, brand,
model, description, specifications, type)
values (%(serial_number)s, %(short_name)s, %(brand)s, %(model)s,
%(description)s, %(specifications)s, %(type)s)
returning *
""", defaultpayload(api.payload)), 201
def post(self):
'''Create a transformation between referentials'''
return Database.query_asdict(
"""
insert into li3ds.transfo_tree (name, owner, transfos)
values (%(name)s,%(owner)s,%(transfos)s)
returning *
""",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a transformation type'''
return Database.query_asdict(
"""
insert into li3ds.transfo_type (func_name, description, func_signature)
values (%(func_name)s,%(description)s,%(func_signature)s)
returning *
""",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a transformation type'''
return Database.query_asdict(
"""
insert into li3ds.transfo_type (name, description, func_signature)
values (%(name)s,%(description)s,%(func_signature)s)
returning *
""",
defaultpayload(api.payload)
), 201
def post(self):
'''Create a transformation between referentials'''
return Database.query_asdict(
"""
insert into li3ds.transfo (source, target, transfo_type, description,
parameters, tdate, validity_start, validity_end)
values (%(source)s, %(target)s, %(transfo_type)s, %(description)s,
%(parameters)s, %(tdate)s, %(validity_start)s, %(validity_end)s)
returning *
""", defaultpayload(api.payload)
), 201
def post(self):
'''Create a sensor'''
return Database.query_asdict(
"""
insert into li3ds.sensor (name, serial_number, brand,
model, description, specifications, type)
values (%(name)s, %(serial_number)s, %(brand)s,
%(model)s, %(description)s, %(specifications)s, %(type)s)
returning *
""",
defaultpayload(api.payload)
), 201
def post(self):
'''
Create a project.
'''
if 'timezone' not in api.payload:
api.payload.update(timezone=project_model_post['timezone'].default)
return Database.query_asdict(
"insert into li3ds.project (name, timezone, extent) "
"values (%(name)s, %(timezone)s, ST_Transform(%(extent)s::geometry,4326)) "
"returning *",
defaultpayload(api.payload)
), 201
def transfo_tree(id):
tftrees = Database.query_asdict(
"select * from li3ds.transfo_tree where id=%s", (id,)
)
if not tftrees:
return None
transfo_tree = tftrees[0]
name = "cluster_transfotree_{}".format(id)
url = url_for('transfotree', id=id, _external=True)
label = "TransfoTree: {name} ({id})".format_map(transfo_tree)
return transfo_trees(name, url, label, [id])
def post(self):
'''Create a transformation between referentials'''
payload = defaultpayload(api.payload)
payload['parameters'] = Json(payload['parameters'])
return Database.query_asdict(
"""
insert into li3ds.transfo (name, source, target, transfo_type, description,
parameters, parameters_column,
tdate, validity_start, validity_end)
values (%(name)s, %(source)s, %(target)s, %(transfo_type)s, %(description)s,
%(parameters)s, %(parameters_column)s,
%(tdate)s, %(validity_start)s, %(validity_end)s)
returning *
""", payload
), 201
def platform_config(id):
configs = Database.query_asdict("""
select p.name as pname, p.id as pid, c.transfo_trees, c.id, c.name
from li3ds.platform_config c
join li3ds.platform p on p.id = c.platform
where c.id = %s
""", (id, ))
if not configs:
return None
config = configs[0]
name = "cluster_config_{}".format(id)
url = url_for('platform_config', id=id, _external=True)
label = "Platform: {pname} ({pid})\\nConfiguration: {name} ({id})".format_map(config)
return transfo_trees(name, url, label, config['transfo_trees'])
def post(self):
'''Create a session'''
return Database.query_asdict(
"insert into li3ds.session (name, start_time, end_time, project, platform) "
"values (%(name)s, %(start_time)s, %(end_time)s, %(project)s, %(platform)s) "
"returning *", defaultpayload(api.payload)), 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 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
