def get(self, name):
'''List all sessions for a given project'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
return Database.query_asjson(
"""select s.* from li3ds.session s
join li3ds.project p on s.project=p.id where p.name=%s
""", (name,)
)
def get(self, name):
'''List all sessions for a given project'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
return Database.query_asjson(
"""select s.* from li3ds.session s
join li3ds.project p on s.project=p.id where p.name=%s
""", (name,)
)
def get(self, id):
'''Get the posprocessing tool used to generate this datasource'''
res = Database.query_asjson(
"select * from li3ds.posdatasource where id=%s", (id,)
)
if not res:
nspds.abort(404, 'PosDatasource not found')
return Database.query_asjson(
" select p.* from li3ds.posprocessing p"
" join li3ds.posdatasource s on s.id = p.target where s.id=%s",
(id,)
)
def get(self, id):
'''List session datasources'''
return Database.query_asjson(
"""select d.* from li3ds.session s
join li3ds.datasource d on d.session = s.id
where s.id = %s
""", (id,))
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 get(self, id):
'''Get one transformation given its identifier'''
res = Database.query_asjson(
"select * from li3ds.transfo_tree where id=%s", (id, ))
if not res:
nstft.abort(404, 'Transformation tree not found')
return res
def get(self, id):
'''List session positional datasources'''
return Database.query_asjson(
"""select d.* from li3ds.session s
join li3ds.posdatasource d on d.session = s.id
where s.id = %s
""", (id, ))
def get(self, id):
'''Get the platform used by the given session'''
return Database.query_asjson(
"""select p.* from li3ds.platform p
join li3ds.session s on s.platform = p.id where s.id=%s
""", (id,)
)
def get(self, id):
'''Get one sensor given its identifier'''
res = Database.query_asjson("select * from li3ds.sensor where id=%s",
(id, ))
if not res:
nssensor.abort(404, 'sensor not found')
return res
def get(self, id):
'''Get one platform given its identifier'''
res = Database.query_asjson(
"select * from li3ds.platform where id=%s", (id,)
)
if not res:
nspfm.abort(404, 'Platform not found')
return res
def get(self, id):
'''Get one transformation given its identifier'''
res = Database.query_asjson(
"select * from li3ds.transfo_tree where id=%s", (id,)
)
if not res:
nstft.abort(404, 'Transformation tree not found')
return res
def get(self, id):
'''Get one datasource given its identifier'''
res = Database.query_asjson(
"select * from li3ds.posdatasource where id=%s", (id,)
)
if not res:
nspds.abort(404, 'PosDatasource not found')
return res
def get(self, id):
'''Get one sensor given its identifier'''
res = Database.query_asjson(
"select * from li3ds.sensor where id=%s", (id,)
)
if not res:
nssensor.abort(404, 'sensor not found')
return res
def get(self, id):
'''Get one referential given its identifier'''
res = Database.query_asjson(
"select * from li3ds.referential where id=%s", (id,)
)
if not res:
nsrf.abort(404, 'Referential not found')
return res
def get(self, id):
'''Get one referential given its identifier'''
res = Database.query_asjson(
"select * from li3ds.referential where id=%s", (id,)
)
if not res:
nsrf.abort(404, 'Referential not found')
return res
def get(self, session_id):
'''List all images in a given session with their location'''
# get project name
res = Database.query(
"""
select p.id, p.name from li3ds.project p
join li3ds.session s on s.project = p.id
where s.id = %s
""", (session_id, ))
if not res:
nsitowns.abort(404, 'Session not found')
project_name, project_id = res[0].name, res[0].id
return Database.query_asjson(
"""
with images as (
-- extract image epoch
select
i.id,
i.filename,
i.etime,
extract(epoch from etime) as epoch,
rf.sensor
from %(project)s.image i
join li3ds.datasource ds on i.datasource = ds.id
join li3ds.referential rf on ds.referential = rf.id
where ds.session = %(session)s
), posdatasource as (
-- get latest version of the route
select max(pds.id) as id, max(version)
from li3ds.project p
join li3ds.session s on s.project = p.id
join li3ds.posdatasource pds on pds.session = s.id
where p.id = %(project_id)s
)
select
i.id,
i.filename,
i.etime as date,
i.sensor,
pc_get(newpt, 'x') as easting,
pc_get(newpt, 'y') as northing,
pc_get(newpt, 'z') as altitude,
pc_get(newpt, 'm_roll') as roll,
pc_get(newpt, 'm_pitch') as pitch,
pc_get(newpt, 'm_plateformHeading')
- pc_get(newpt, 'm_wanderAngle') as heading
from posdatasource pds
join %(project)s.route r on pds.id = r.posdatasource
join images i on i.etime <@ tstzrange(r.start_time, r.end_time),
pc_interpolate(r.points, 'm_time', i.epoch) as newpt
""", ({
'project': AsIs(project_name),
'project_id': project_id,
'session': session_id
}))
def delete(self, name):
'''
Delete a project.
'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
Database.query_aslist("delete from li3ds.project where id=%s", (name,))
return '', 410
def delete(self, name):
'''
Delete a project.
'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
Database.query_aslist("select li3ds.delete_project(%s)", (name,))
return '', 410
def get(self, session_id):
'''List all camera calibrations for a given session'''
if 'platform_config' not in request.args:
nsitowns.abort(500, 'parameter required : platform_config')
# get all cameras for this platform configuration
values = Database.query_asjson(
"""
with transfos as (
-- all transformations for this platform config
select
unnest(tt.transfos) as tid
from li3ds.platform_config pf
join li3ds.transfo_tree tt on tt.id = ANY(pf.transfo_trees)
where pf.id = %(pconfig)s
), ins_transfo as (
-- get ins first transformation
select trlist.tid as target
from transfos trlist
join li3ds.transfo tr on tr.id = trlist.tid
join li3ds.referential r on tr.source = r.id or tr.target = r.id
join li3ds.sensor s on s.id = r.sensor
where s.type = 'ins' and r.root
), camera_transfo as (
-- get all camera first transformations
select tr.id as source, se.*
from li3ds.session s
join li3ds.datasource ds on ds.session = s.id
join li3ds.referential r on r.id = ds.referential
join li3ds.sensor se on se.id = r.sensor
join li3ds.transfo tr on tr.source = r.id or tr.target = r.id
join transfos trlist on trlist.tid = tr.id
where s.id = %(session)s and se.type = 'camera'
)
select
id
, ARRAY[specifications->'size_x', specifications->'size_y'] as size
, _.transfos
from
camera_transfo
, ins_transfo
, li3ds.dijkstra(%(pconfig)s, source, target) as path
, lateral (
select jsonb_agg(row_to_json(s)) as transfos from
(
select t.id, t.parameters, tt.description, tt.func_name as type
from unnest(path) with ordinality as u(tid, ord)
join li3ds.transfo t on t.id = tid
join li3ds.transfo_type tt on tt.id = t.transfo_type
) as s
) as _
""", ({
'pconfig': request.args['platform_config'],
'session': session_id
}))
return values
def get(self, id):
'''Get all sensors used in a given platform configuration'''
return Database.query_asjson("""
select
distinct s.*
from li3ds.platform_config pf
join li3ds.transfo_tree tt on tt.id = ANY(pf.transfo_trees)
, lateral unnest(tt.transfos) as tid
join li3ds.transfo t on t.id = tid
join li3ds.referential r on r.id = t.source or r.id = t.target
join li3ds.sensor s on s.id = r.sensor
where pf.id = %s
""", (id,))
def get(self, name):
'''Get a project given its name'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
return res
def get(self):
'''
Retrieve foreign server list
'''
return Database.query_asjson(servers_sql)
def get(self):
'''Get all sessions'''
return Database.query_asjson("select * from li3ds.session")
def get(self, id):
'''Get the platform used by the given session'''
return Database.query_asjson(
"""select p.* from li3ds.platform p
join li3ds.session s on s.platform = p.id where s.id=%s
""", (id, ))
def get(self):
'''List Referentials'''
return Database.query_asjson("select * from li3ds.referential")
def get(self):
'''List sensors'''
return Database.query_asjson("select * from li3ds.sensor")
def get(self):
'''
Retrieve foreign view list
'''
return Database.query_asjson(views_sql)
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 get(self, name):
'''Get a project given its name'''
res = Database.query_asjson("select * from li3ds.project where name=%s", (name,))
if not res:
nsproject.abort(404, 'Project not found')
return res
def get(self):
'''List sensors'''
return Database.query_asjson("select * from li3ds.sensor")
def get(self):
'''List platforms'''
return Database.query_asjson("select * from li3ds.platform")
def get(self, id):
'''Get a platform configuration given its identifier'''
return Database.query_asjson(
"select * from li3ds.platform_config where id = %s", (id,)
)
def get(self):
'''List all transformation trees'''
return Database.query_asjson("select * from li3ds.transfo_tree")
def get(self):
'''List all transformation types'''
return Database.query_asjson("select * from li3ds.transfo_type")
def get(self):
'''Get all sessions'''
return Database.query_asjson("select * from li3ds.session")
def get(self):
'''List all projects'''
return Database.query_asjson("select * from li3ds.project")
def get(self):
'''Get all datasources'''
return Database.query_asjson("select * from li3ds.posdatasource")
def get(self, id):
'''Get one session given its identifier'''
return Database.query_asjson("select * from li3ds.session where id=%s",
(id, ))
def get(self, id):
'''List all platform configurations'''
return Database.query_asjson(
"select * from li3ds.platform_config where platform = %s", (id,)
)
def get(self, id):
'''Get posprocessing tool given its id'''
return Database.query_asjson(
" select * from li3ds.posprocessing where id = %s", (id,)
)
def get(self):
'''List Referentials'''
return Database.query_asjson("select * from li3ds.referential")
def get(self):
'''
Retrieve foreign table list
'''
return Database.query_asjson(tables_sql)
def get(self, id):
'''Get one session given its identifier'''
return Database.query_asjson(
"select * from li3ds.session where id=%s", (id,)
)
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 get(self):
'''List all projects'''
return Database.query_asjson("select * from li3ds.project")
