apidata.py 51.7 KB
Newer Older
1
2
#!/usr/bin/python3
# -*-coding:UTF-8 -*
3
4
5
6
7
8
9
10
11
12
'''
Created on 14 may 2020
@author: cplumejeaud, ggeoffroy
ANR PORTIC : used to publish data fo portic - pointcalls and travels API
This requires :
1. to have loaded data (pointcall, taxes, cargo) from navigo with LoadFilemaker.py, in schema navigo, navigocheck.
2. to have built a table ports.port_points listing all ports ( using geo_general ) with additional data and manual editing for admiralty, province.
@see navigocorpus/ETL/BuildNavigoviz.py
'''

13
from flask import Flask, jsonify, abort, render_template,url_for,request, make_response
14
from flask_cors import CORS, cross_origin
15

16
17
18
19
from flask_caching import Cache
import numpy as np
import csv
import json
20
import io
21
import os
22
import pandas as pd
23
import psycopg2 as pg
24
import re
25
26
27
28
29

#import flask_ext 
#import flask_excel as excel
#import pyexcel as pe

30

31
32
33
APP_ROOT = os.path.dirname(os.path.abspath(__file__))   # refers to application_top
APP_STATIC = os.path.join(APP_ROOT, 'static')
APP_DATA = os.path.join(APP_STATIC, 'data')
34
35

app = Flask(__name__)
36
CORS(app, origins='*', send_wildcard=True)
37

38
39
40
#app.config.from_object('config')
#port = app.config['PORT']      
port = '80'
41
42
postgresport = '5432'
database = 'portic_v6'
43

44
45
46
47
48
49
50
51
52
53
54
# URL doesn't exist
@app.errorhandler(404)
def resource_not_found(e):
    return jsonify(str(e)), 404

# Request with bad HTTP method (GET, PUT, POST, ...)
@app.errorhandler(405)
def method_not_allowed(e):
    return jsonify(str(e)), 405

def retrieveDataFromPostgres(query, cast_columns_to_types=None) : 
55
    """
56
57
    Internal method to select data using SQL query
    return a dataframe
58
59
    see read_sql_query() doc : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html
    columns can be type-casted using the cast_columns_to_types dictionary argument : { 'column_sql_name' : 'type'}. For 'type', see dtype String Aliases : https://pandas.pydata.org/docs/user_guide/basics.html#basics-dtypes
60
    """
61
    import pandas.io.sql as psql
62
    import pandas as pd
63
    #connection = pg.connect("host='134.158.33.179' port='5433' dbname='portic_v3' user='api_user' password='portic'")
64
    connection = pg.connect("""host='localhost' port='%s' dbname='%s' user='api_user' password='portic'"""% (postgresport, database))
65

66
    df = pd.read_sql_query(query,con=connection,coerce_float=False, dtype=cast_columns_to_types)
67
68
69
    connection.close()
    return df
    #print(df)
70
71

def formatCSV(mydataframe):
72
73
74
    """
    Internal method to output dataframe in a CSV file
    """
75
76
77
78
79
80
81
82
83
84
85
86
87
88
    #print(mydataframe)

    #Options de compression possibles to_csv 
    #https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html
    csvdata = mydataframe.to_csv()

    #https://stackoverflow.com/questions/26997679/writing-a-csv-from-flask-framework
    dest = io.StringIO()
    dest.write(csvdata)
    output = make_response(dest.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=export.csv"
    output.headers["Content-type"] = "text/csv"
    return output

89
90
91
92
93
def formatJSON(dataframe):
    """
    Internal method to output dataframe as JSON
    """
    json_str = json.dumps(dataframe.to_json(orient='records'))
94
95
    return json.loads(json_str)

96
def formatOutput(dfcsv, api='travels'):
97
    """
98
99
    Internal method
    Apply various formatting on dataframe for output by processing request parameters
100
101
102
    - format : csv | **json**
    - zipped : true | **false**
    - shortenfields : true | **false**
103
104
105
106
    NB : 
    - shortenfields : the shortname is based on the ordinal position of the attribute in the table 
    thus it can change with living database
    - zipped is not yet implemented
107
108
109
110
111
112
113
114
115
    """

    import pandas as pd

    ##Shorten names or not
    shortenfields = request.args.get("shortenfields")
    #print('shortenfields ? '+shortenfields)
    if (shortenfields != None and shortenfields=='true') :
        #API;colname;short_colname
116
117
118
        #filename = os.path.join(APP_DATA, 'shorten_names.csv')
        #mapnames = pd.read_csv(filename, sep = ';') 
        mapnames = readFieldnames(api)
119
        #Filter according API
120
        #mapnames = mapnames[mapnames['API']=='pointcalls']
121
        #print(mapnames['colname'])
122
        mapper=mapnames.loc[:, ['name', 'shortname'] ].set_index('name')['shortname'].to_dict()
123
124
125
126
127
128
129
130
131
132
        #print(mapper) 
        dfcsv = dfcsv.rename(columns=mapper)

    ## Format output
    format = request.args.get("format")
    if (format != None and format == 'csv') :
        return formatCSV(dfcsv)
    else: 
        return formatJSON(dfcsv)

133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
def retrieveDataAndFormatOutput(query, api='travels', cast_df_columns_to_types=None):
    """
    Internal method
    Executes SQL query, and formats resulting data, with error handling.
    Calls :
    - dfcsv=retrieveDataFromPostgres(query, cast_columns_to_types)
    - formatOutput(dfcsv, api)
    then return the JSON or CSV data.
    If an error occurs, an error message is returned in JSON format.
    """
    try:
        operation="retrieving data from PostgreSQL"
        data = retrieveDataFromPostgres(query, cast_df_columns_to_types)
        operation="formatting data"
        formatted_output=formatOutput(data, api)
    except BaseException as err:
        error_message=f"Error of type {type(err)} occured while {operation} :\n\n{err.args}"
        return jsonify(error_message), 500
    else:
        return formatted_output


155
def readFieldnames(api, schema='navigoviz') :
156
    """
157
158
159
160
161
162
    Internal method
    We read the information schema to be sure to be conform to real living database

    Name of tables differ from API names.
    We generate the shortname using the order of the attribute in the table 
    (3 characters, beginning either with t if travels, either with p if pointcalls)
163
164
    """
    import pandas as pd
165
166
167
168
169

    table_name = "pointcall','built_travels"
    if api is not None  :
        if api == 'travels' : 
            table_name = 'built_travels'
170
171
        if api == 'rawflows' : 
            table_name = 'raw_flows'
172
173
        if api == 'pointcalls' : 
            table_name = 'pointcall'
174
175
176
        if api == 'ports' : 
            table_name = 'port_points'
            schema = 'ports'
177
178
    
    #API;name;shortname;type;description
179
    query = """SELECT '%s' as API, 
180
        c.column_name as name, 
181
        case when c.table_name= 'built_travels' then 't' else (case when c.table_name= 'port_points' then 'pp' else 'p' end) end||navigo.pystrip(to_char(c.ordinal_position::int, '009')) as shortname,
182
183
184
185
        c.data_type as type, pgd.description as description
        FROM information_schema.columns c 
        left outer join pg_catalog.pg_description pgd on (pgd.objsubid=c.ordinal_position  )
        left outer join pg_catalog.pg_statio_all_tables st on (pgd.objoid=st.relid and  c.table_schema=st.schemaname and c.table_name=st.relname)
186
        where c.table_name in ('%s')  and c.table_schema = '%s' and pgd.objoid = st.relid;"""% (api, table_name, schema)
187

188
    print(query)
189
190
191
    metadata = retrieveDataFromPostgres(query)
    return metadata
    """
192
193
    filename = os.path.join(APP_DATA, 'api_portic.csv')
    dfcsv = pd.read_csv(filename, sep = ';')
194
195
196
197
    
    if api is not None  :
        #Filter to retain this API
        dfcsv = dfcsv[dfcsv['API']==api]
198
199
200
201
202
203
204
205
206
207
208
209
210
211
    return  dfcsv   
    """
         
@app.route('/api/fieldnames/')   
def getFieldnames():
    """
    récupère des métadonnées sur l'API, avec la liste des attributs, avec leur nom court et long, leur type et leur signification. 
    get metadata about API with short and long name, type and definition

    http://127.0.0.1:80/api/fieldnames/?format=json
    http://127.0.0.1/api/fieldnames/?format=json&shortenfields=true
    http://127.0.0.1/api/fieldnames/?format=json&shortenfields=true&api=pointcalls
    http://127.0.0.1/api/fieldnames/?format=csv&shortenfields=true&api=pointcalls

212
213
    http://data.portic.fr/api/fieldnames/?format=json

214
    """
215
    
216
217
218
219
    # Filter to keep desired API
    api = request.args.get("api")
    df = readFieldnames(api)
    return formatOutput(df, api)
220

221
222
223
224
225
@app.route('/api/pointcalls/')   
def getPointcalls():
    """
    Return the pointcalls as specified in API
    Will be extracted from postgres, schema navigoviz, table pointcall (see navigocorpus/ETL)
226
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
Christine Plumejeaud's avatar
Christine Plumejeaud committed
227
        - date : 4 digits representing a year, to extract data for this given year only
228
229
    http://127.0.0.1:80/api/pointcalls/?params=pointcall,pointcall_uhgs_id&shortenfields=true
    http://127.0.0.1:80/api/pointcalls/?format=csv
Christine Plumejeaud's avatar
Christine Plumejeaud committed
230
    http://127.0.0.1/api/pointcalls/?params=pointcall,pointcall_uhgs_id&date=1787&format=csv
231
232
233

    http://127.0.0.1:80/api/pointcalls/?params=pointcall,pointcall_uhgs_id&shortenfields=false
    http://127.0.0.1:80/api/pointcalls/?format=json&params=id,pointcall,ship_name,destination,destination_uhgs_id&shortenfields=true
234

235
236
    """
    import pandas as pd
237
238
239
    #filename = os.path.join(APP_DATA, 'pointcalls_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')

240
241
242
    #https://stackoverflow.com/questions/24251219/pandas-read-csv-low-memory-and-dtype-options
    #pd.read_csv('static/data/pointcalls_API_11mai2020.csv', dtype={"all_cargos": object, "pkid": int})
    #print(dfcsv.columns)
243
244
245

    query = 'select * from navigoviz.pointcall'

246
247
248
249
250
    ## Filter the result according requested params
    params = request.args.get("params")
    #params=pointcall,pointcall_uhgs_id
    if (params is not None and len(params)>0) :
        #print('selecting some columns')
251
        fields = readFieldnames('pointcalls')
252
        keepparams = str(params).split(',')
253
        #keepparams = ['pkid', 'pointcall', 'pointcall_uhgs_id']
254
255
256
257
258
        #https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike
        #dfcsv = dfcsv.loc[:, keepparams]
        #labels = dfcsv.index.intersection(keepparams)
        labels = []
        for k in keepparams:
259
260
            #print(k)
            if k in fields['name'].tolist() : 
261
262
                labels.append(k)
        #print(labels)
263
264
265
266
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.pointcall'
        #dfcsv =  dfcsv.loc[:, labels]
    
Christine Plumejeaud's avatar
Christine Plumejeaud committed
267
268
269
270
271
272
    ## filter following a date
    date = request.args.get("date")
    if (date is not None and len(date)==4) :
        query = query + """ where (substring(pointcall_out_date for 4) = '%s' or substring(pointcall_in_date for 4) = '%s') """ % (date, date)
        print(query)

273
    return retrieveDataAndFormatOutput(query, api='pointcalls')
274
275


276
277
278
279
280
281
282

@app.route('/api/travels/')   
def getTravels():
    """
    Return the travels as specified in API
    Will be extracted from postgres, schema navigoviz, table built_travels (see navigocorpus/ETL), 
    but with a filter by default : only source_entry = from and both-from, to avoid duplicates
283
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
284
        params=pointcall,pointcall_uhgs_id for instance
285
286
287
        - both_to : true | **false**

    http://127.0.0.1:5004/api/travels/?format=csv&both_to=false
Christine Plumejeaud's avatar
Christine Plumejeaud committed
288
    http://127.0.0.1:80/api/travels/?format=csv&both_to=true&shortenfields=true&date=1789
289

290
291
    http://127.0.0.1:80/api/travels/?format=json&params=id,departure,destination,destination_uhgs_id
    http://127.0.0.1:80/api/travels/?format=json&params=id,departure,destination,destination_uhgs_id&shortenfields=true
292
293
    """
    import pandas as pd
294
295
    #filename = os.path.join(APP_DATA, 'travels_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')
296
    
297
298
299
    query = 'select * from navigoviz.built_travels'


300
301
    ## Filter the result according requested params
    params = request.args.get("params")
302
303
304
305
306
307
308
309
310
311
312
    if (params is not None and len(params)>0) :
        #print('selecting some columns')
        fields = readFieldnames('travels')
        keepparams = str(params).split(',')
        #https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike
        labels = []
        for k in keepparams:
            if k in fields['name'].tolist() : 
                labels.append(k)
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.built_travels'
313
314
315
316
317
318
319


    # Filter to remove duplicates (by default) - if both_to is given, then do not filter and travels will contain duplicates
    getduplicates = request.args.get("both_to")
    if getduplicates is None or getduplicates != 'true' :
        #Filter travels
        #print('filtering duplicates out of travels')
320
321
        #dfcsv = dfcsv[dfcsv['source_entry']!='both-to']
        query = query + " where source_entry <> 'both-to'"
Christine Plumejeaud's avatar
Christine Plumejeaud committed
322
323
324
325
326
327
328
329
330
331

    ## filter following a date
    date = request.args.get("date")
    if (date is not None and len(date)==4) :
        if 'where' in query : 
            query = query + """ and (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s')""" % (date, date)
        else : 
            query = query + """ where (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s') """ % (date, date)
        print(query)

332
    return retrieveDataAndFormatOutput(query, api='travels')
333

334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
@app.route('/api/rawflows/')   
def getRawFlows():
    """
    Return the raw flows as specified in API (same as travel for the attributes)
    Raw_flows is built by making a auto-join on pointcall for source_doc_id, 
    and departure are "Out" action, destination are "In" action, ordered chronologically
    Thus we do not take care of net_route_marker (A or Z), neither of ship_id to built travels.
    Data about the cargo, the captain, the homeport are those filled at the departure (there is no doublons)

    Will be extracted from postgres, schema navigoviz, table raw_flows (see navigocorpus/ETL)
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
        params=pointcall,pointcall_uhgs_id for instance

    http://127.0.0.1:5004/api/rawflows/?format=csv
    http://127.0.0.1:80/api/rawflows/?format=csv&shortenfields=true&date=1789

    http://127.0.0.1:80/api/rawflows/?format=json&params=id,departure,destination,destination_uhgs_id
    http://127.0.0.1:80/api/rawflows/?format=json&params=id,departure,destination,destination_uhgs_id&shortenfields=true
    """
    import pandas as pd
    #filename = os.path.join(APP_DATA, 'travels_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')
    
    query = 'select * from navigoviz.raw_flows'


    ## Filter the result according requested params
    ## raw_flows has same attributes than built_travels
    params = request.args.get("params")
    if (params is not None and len(params)>0) :
        #print('selecting some columns')
        fields = readFieldnames('rawflows')
        keepparams = str(params).split(',')
        #https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike
        labels = []
        for k in keepparams:
            if k in fields['name'].tolist() : 
                labels.append(k)
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.raw_flows'

    ## filter following a date given as a year (4 digits)
    date = request.args.get("date")
    if (date is not None and len(date)==4) :
        if 'where' in query : 
            query = query + """ and (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s')""" % (date, date)
        else : 
            query = query + """ where (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s') """ % (date, date)
        print(query)

384
    return retrieveDataAndFormatOutput(query, api='rawflows')
385

386
387
388
389
390
391
392

@app.route('/api/details/departures/', methods = ['GET'])   
def getDeparturesDetails():
    """
    Return the travels, at the departure of the points located in a 100 km radius neighbourhood from the lat/lon given in parameter
    Will be extracted from postgres, schema navigoviz, table built_travels (see navigocorpus/ETL), 
    but with a filter by default : only source_entry = from and both-from, to avoid duplicates
393

Christine Plumejeaud's avatar
Christine Plumejeaud committed
394
    http://localhost:80/api/details/departures?lat=46&lon=-1&radius=100&date=1787
395
396
397
398
399
400
401
402
403
404
405
    http://localhost/api/details/departures/?lat=45.2333&lon=-1.5&radius=100
    http://localhost/api/details/departures/?lat=45.2333&lon=toto&radius=100
    """
    
    lat = None
    lon = None
    radius = None
    try:
        lat = float(request.args.get("lat"))
        lon = float(request.args.get("lon"))
        radius = int(request.args.get("radius"))
406
    except :
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
        #We go on, silenciously, by selecting all data
        pass

    if lat != None and lon != None and radius!= None : 
        query = """select distinct departure, departure_uhgs_id, departure_latitude, departure_longitude from navigoviz.built_travels 
        where source_entry<> 'both-to' and
        st_distance(departure_point, 
        st_setsrid(st_transform(st_setsrid(st_makepoint(%f, %f), 4326), 3857), 3857)) < %f""" % (lon,lat,radius*1000)
    else :
        query = """select distinct departure, departure_uhgs_id, departure_latitude, departure_longitude 
        from navigoviz.built_travels
        where source_entry<> 'both-to'"""
    #df1 = calcul_isInside(lat, lon, radius)
    #dfcsv = df1[['departure','departure_uhgs_id','departure_latitude','departure_longitude']].drop_duplicates()
    
Christine Plumejeaud's avatar
Christine Plumejeaud committed
422
423
424
425
426
    ## filter following a date
    date = request.args.get("date")
    if (date is not None and len(date)==4) :
        query = query + """ AND (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s') """ % (date, date)

427
    #print(query)
428
    return retrieveDataAndFormatOutput(query, api='travels')
429
430
431
432

@app.route('/api/agg/departures/', methods = ['GET']) 
def getDeparturesAgg():
    """
433
    Return the count of departures, for the points located in a radius km neighbourhood from the lat/lon given in parameter
434
435
    Will be extracted from postgres, schema navigoviz, table built_travels (see navigocorpus/ETL), 
    but with a filter by default : only source_entry = from and both-from, to avoid duplicates
436

437
438
439
440
441
442
443
444
445
446
    http://localhost/api/agg/departures/?lat=45.2333&lon=-1&radius=100

    """
    lat = None
    lon = None
    radius = None
    try:
        lat = float(request.args.get("lat"))
        lon = float(request.args.get("lon"))
        radius = int(request.args.get("radius"))
447
    except :
448
449
450
451
452
        #We go on, silenciously, by selecting all data
        pass
    #df2 = calcul_isInside(lat, lon, radius).departure.value_counts().reset_index()
    #df2.columns = ['departure', 'count']

Christine Plumejeaud's avatar
Christine Plumejeaud committed
453
454
455
456
457
458
459
    ## filter following a date
    date = request.args.get("date")
    filterclause = ""
    if (date is not None and len(date)==4) :
        filterclause = filterclause + """ and (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s') """ % (date, date)


460
461
    if lat != None and lon != None and radius!= None : 
        query = """select  departure, count(*) as count from navigoviz.built_travels  
Christine Plumejeaud's avatar
Christine Plumejeaud committed
462
        where  source_entry<> 'both-to'  %s 
463
        and st_distance(departure_point, st_setsrid(st_transform(st_setsrid(st_makepoint(%f, %f), 4326), 3857), 3857)) < %f 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
464
        group by departure""" % (filterclause, lon,lat,radius*1000)
465
466
467
    else :
        query = """select departure, count(*) 
        from navigoviz.built_travels 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
468
469
        where source_entry<> 'both-to'  %s
        group by departure""" % (filterclause)
470
    #print(query)
471
    return retrieveDataAndFormatOutput(query, api='travels')
472
    #return json.loads(json.dumps(df2.to_json(orient='records')))   
473
474
475
476

@app.route('/api/agg/destinations/', methods = ['GET'])   
def getDestinationsAgg():
    """
477
478
    Return the count of destination for each different admiralties, 
    for the points located in a radius km  neighbourhood from the lat/lon given in parameter
479
480
    Will be extracted from postgres, schema navigoviz, table built_travels (see navigocorpus/ETL), 
    but with a filter by default : only source_entry = from and both-from, to avoid duplicates
481

Christine Plumejeaud's avatar
Christine Plumejeaud committed
482
    http://localhost/api/agg/destinations/?lat=45.2333&lon=-1&radius=100&date=1789
483
    """
Christine Plumejeaud's avatar
Christine Plumejeaud committed
484
485
486
487
488
489
490
491

    ## filter following a date
    date = request.args.get("date")
    filterclause = ""
    if (date is not None and len(date)==4) :
        filterclause = filterclause + """ and (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s') """ % (date, date)


492
493
494
495
496
497
498
    lat = None
    lon = None
    radius = None
    try:
        lat = float(request.args.get("lat"))
        lon = float(request.args.get("lon"))
        radius = int(request.args.get("radius"))
499
    except :
500
501
502
503
504
505
506
507
        #We go on, silenciously, by selecting all data
        pass
    
    #df3 = calcul_isInside(lat, lon, radius).destination_amiraute.value_counts().reset_index()
    #df3.columns = ['label', 'value']

    if lat != None and lon != None and radius!= None : 
        query = """select  destination_admiralty as label, count(*) as value from navigoviz.built_travels  
Christine Plumejeaud's avatar
Christine Plumejeaud committed
508
        where source_entry<> 'both-to' %s 
509
        and st_distance(departure_point, st_setsrid(st_transform(st_setsrid(st_makepoint(%f, %f), 4326), 3857), 3857)) < %f 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
510
        group by destination_admiralty""" % (filterclause, lon,lat,radius*1000)
511
512
513
    else :
        query = """select destination_admiralty as label, count(*) as value 
        from navigoviz.built_travels 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
514
515
        where source_entry<> 'both-to' %s
        group by destination_admiralty""" % (filterclause)
516
    #print(query)
517

518
519
520
521
522
523
524
525
526
527
528
529
    # Retrieve data from PostgreSQL, and format output, with error handling :
    try:
        operation="retrieving data from PostgreSQL"
        df3 = retrieveDataFromPostgres(query)
        df3['id'] = df3['label']
        operation="formatting data"
        formatted_output=formatOutput(df3, 'travels')
    except BaseException as err:
        error_message=f"Error of type {type(err)} occured while {operation} :\n\n{err.args}"
        return jsonify(error_message), 500
    else:
        return formatted_output
530

531

532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
@app.route('/api/flows/')   
def getFlows():
    """
    Return the flows as specified in API :  a list of travels linked to the specified ports, either by entering in 
    (direction In), either by existing from (direction Out), either by having sailing around (direction In-out), 
    or for any direction (default, or specify direction No). 
        - ports : ports identifiers (UHGS_id) for which travels are filtered, comma separated(,) 
        - direction : In | Out | In-out | **No**
        - degree : **0**, 1 or more 
        (when 0 : all flows going through out the list of cited ports, with also the list of previous and next pointcalls)
 
    Will be extracted from postgres, schema navigoviz, table built_travels (see navigocorpus/ETL), 
    but with a filter by default : only source_entry = from and both-from, to avoid duplicates
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
        params=pointcall,pointcall_uhgs_id for instance
        - both_to : true | **false**
Christine Plumejeaud's avatar
Christine Plumejeaud committed
548
        - date : **1787** | yyyy (4 digits)
549
550
551
552
553
554
555

    http://localhost:80/api/flows/?format=csv&both_to=false
    http://localhost:80/api/flows/?format=csv&both_to=true&shortenfields=true

    http://localhost:80/api/flows/?format=json&ports=A0180923,A0152606&direction=In&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity
    http://localhost:80/api/flows/?format=json&ports=A0180923,A0152606&direction=Out&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity
    http://localhost/api/flows/?format=json&ports=A0180923&direction=In-out&params=id,departure,destination&shortenfields=true
Christine Plumejeaud's avatar
Christine Plumejeaud committed
556
557
    http://localhost/api/flows/?format=json&ports=A0180923,A0152606&direction=Out&date=1787&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity

558
559

    [{"t01":"0008663N- 05","t04":"Bordeaux","t19":"Bordeaux"},{"t01":"0009557N- 01","t04":"Bordeaux","t19":"Bordeaux"},{"t01":"0010656N- 01","t04":"Bordeaux","t19":"Bordeaux"}]
Christine Plumejeaud's avatar
Christine Plumejeaud committed
560
    [{"t001":"0008663N- 05","t004":"Bordeaux","t020":"Bordeaux"},{"t001":"0010656N- 01","t004":"Bordeaux","t020":"Bordeaux"}]
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576

    http://localhost:80/api/flows/?format=json&ports=A0180923,A0152606&direction=No&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity
    
    http://localhost:80/api/flows/?format=json&ports=A0152606&degree=0&direction=In&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity
    http://localhost:80/api/flows/?format=json&ports=A0180923,A0152606&degree=1&direction=Out&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity
    http://localhost:80/api/flows/?format=json&ports=A0180923&degree=2&direction=In-out&params=travel_rank,ship_id,departure,destination,departure_action,destination_action,distance_dep_dest,travel_uncertainity

    """
    import pandas as pd
    #filename = os.path.join(APP_DATA, 'travels_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')
    

    attributes = '*'
    filter_clause = 'true'

Christine Plumejeaud's avatar
Christine Plumejeaud committed
577

578
579
580
581
582
583
584
585
586
587
588
589
590
    ## Filter the result according requested params
    params = request.args.get("params")
    if (params is not None and len(params)>0) :
        #print('selecting some columns')
        fields = readFieldnames('travels')
        keepparams = str(params).split(',')
        #https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike
        labels = []
        for k in keepparams:
            if k in fields['name'].tolist() : 
                labels.append(k)
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.built_travels'
591
592


593
594
595
596
597
598
599
    # Filter to remove duplicates (by default) - if both_to is given, then do not filter and travels will contain duplicates
    getduplicates = request.args.get("both_to")
    if getduplicates is None or getduplicates != 'true' :
        #Filter travels
        #print('filtering duplicates out of travels')
        #dfcsv = dfcsv[dfcsv['source_entry']!='both-to']
        filter_clause =  " source_entry <> 'both-to' "
Christine Plumejeaud's avatar
Christine Plumejeaud committed
600
601
602
603
604
605
606
607
608
609

    ## filter following a date
    date = request.args.get("date")
    if (date is not None and len(date)==4) :
        filter_clause = filter_clause + """ and (substring(departure_out_date for 4) = '%s' or substring(destination_in_date for 4) = '%s')""" % (date, date)
    else :
        #By default, we return only 1787 flow data
        filter_clause = filter_clause + """ and (substring(departure_out_date for 4) = '1787' or substring(destination_in_date for 4) = '1787')""" 


610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
    query = 'select '+attributes+' from navigoviz.built_travels '+filter_clause

    ## Filter the result according requested ports 
    filter_clauseIN = filter_clause
    filter_clauseOUT = filter_clause
    filter_clauseINOUT = filter_clause
    ports = request.args.get("ports")
    if (ports is not None and len(ports)>0) :
        port_list = str(ports).split(',') 
        port_list = "','".join(port_list)
        print(port_list)
        filter_clauseIN =  filter_clauseIN+""" and destination_uhgs_id in ('%s') """% (port_list)
        filter_clauseOUT =  filter_clauseOUT+""" and departure_uhgs_id in ('%s')"""% (port_list)
        filter_clauseINOUT =  filter_clauseINOUT+""" 
            and (destination_uhgs_id in ('%s') OR departure_uhgs_id in ('%s'))"""% (port_list,port_list)

    ## Filter the result according the requested degree
    degree = 0
Christine Plumejeaud's avatar
Christine Plumejeaud committed
628
629
    filter_degree_in = "true"
    filter_degree_out = "true"
630
631
632
633
634
635
    try:
        degree = int(request.args.get("degree"))
    except :
        #We go on, silenciously, by selecting all data
        pass
    if (degree > 0) : 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
636
637
        filter_degree_in = "travel_rank >= k.subject_order " +"-"+ str(degree-1)
        filter_degree_out = "travel_rank <= k.subject_order" +"+"+ str(degree-1)
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670

    ## Filter the result according requested direction
    direction = request.args.get("direction")
    if (direction is not None and len(direction)>0 and direction != 'No') :
        if (direction == 'In') : 
            query = """select distinct  """+attributes+"""
                    from navigoviz.built_travels, 
                            ( select ship_id as subject, travel_rank as subject_order
                            from navigoviz.built_travels 
                            where  """+filter_clauseIN+"""
                            and distance_dep_dest > 0
                        	) as k 
                    where ship_id = k.subject and """+filter_clause+""" 
                    and travel_rank <= k.subject_order and """+filter_degree_in+""" 
            """
        if (direction == 'Out') : 
            query = """select distinct  """+attributes+"""
                    from navigoviz.built_travels, 
                            ( select ship_id as subject, travel_rank as subject_order
                            from navigoviz.built_travels 
                            where  """+filter_clauseOUT+"""
                            and distance_dep_dest > 0
                        	) as k 
                    where ship_id = k.subject and """+filter_clause+""" 
                    and travel_rank >= k.subject_order and """+filter_degree_out+""" 
                """
        if (direction == 'In-out') :     
            query = """select distinct  """+attributes+"""
                    from navigoviz.built_travels, 
                            ( select ship_id as subject, travel_rank as subject_order
                            from navigoviz.built_travels 
                            where  """+filter_clauseINOUT+"""
                            and distance_dep_dest = 0
671
                            and (departure_action like 'Sailing around' or departure_action like 'In-out' or departure_action like 'Transit')
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
                        	) as k 
                    where ship_id = k.subject and """+filter_clause+""" 
                    and """+filter_degree_in+""" and """+filter_degree_out+""" 
                """
    else : 
        # Take all, whatever the direction, but restrict according the degree
        query = """select distinct  """+attributes+"""
                    from navigoviz.built_travels, 
                            ( select ship_id as subject, travel_rank as subject_order
                            from navigoviz.built_travels 
                            where  """+filter_clauseINOUT+"""
                        	) as k 
                    where ship_id = k.subject and """+filter_clause+""" 
                    and """+filter_degree_in+""" and """+filter_degree_out    

    print(query)    

689
    return retrieveDataAndFormatOutput(query, api='travels')
690

691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
def validateSrid(srid):
    """
    Internal method
    Checks in spatial_ref_sys of the database if given srid is valid, otherwise returns 900913
    """
    if srid is None : 
        srid = '900913'
    else :
        #we chek if it is valid by looking in the postgres spatial_ref_sys table : 4326, 3857 for instance          
        query = """select distinct srid from public.spatial_ref_sys srs"""
        srids = retrieveDataFromPostgres(query)
        if int(srid) not in srids['srid'].tolist() : 
            srid = '900913'
    return srid

def getFilterdateForPointcall(date):
    """
    Internal method
    returns SQL to filter pointcalls by date (4 digits format)
    """
    ## filter following a date
    filterDate = 'where true'
    if (date is not None and len(date)==4) :
        filterDate = """ where (substring(pointcall_out_date for 4) = '%s' or substring(pointcall_in_date for 4) = '%s') """ % (date, date)
    return filterDate
716

717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
    
def validateLang(lang):
    """
    Internal method
    returns "fr" or "en" (defaults to "fr" if no lang or anything else is given)
    """
    result="fr" # par défaut
    if lang is not None:
        lang_en_minuscules=lang.lower()
        # tests if lang_en_minuscules matches a regular expression :
        regexp="^(fr|en)$"  # ^ = at the start of string, $ = at the end of string => exactly equal to 'fr' or (|) 'en'
        match=re.match(regexp, lang_en_minuscules)  # tests if lang, converted to lowercase, is exactly "fr" or "en"
        if match is not None:
            result=match[0]
    return result 

def getLocalizedFieldnamesForSources(lang):
    """
    Internal method
    returns a string containing the correct names of the fields in the database (comma-separated) for "fr" or "en" languages
    """
738
    return f"toponyme_standard_{lang} as  toponym, substate_1789_{lang} as substate,"
739
740


741
742
743
744
745
746
@app.route('/api/ports/')
def getPorts():
    """
    export list of ports_points (in 900013 projection or what is specified by user) in json format, 
    with all required attributes for visualisations (selection of parameters is not possible for the moment)
    List of attributes : 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
747
    ogc_fid, uhgs_id, total, toponym, belonging_states, belonging_substates, status, geonameid, admiralty, province, shiparea , point
748
749
750
751
752
753
754
755
756
757
758
759
760
    User can get a description of the attributes by using /api/fieldnames?api=ports

    Default srid is 900913
    You get another by specifying a srid param

    Will be extracted from postgres, schema ports, table port_points (see navigocorpus/ETL)

    Tested alone :  http://localhost/api/ports?srid=4326 ou http://localhost/api/ports?
    and by using explorex.portic.fr application (code alphaportic for visualisation)
    Test OK on 06 June 2020
    """
    # select the srid given by user for geometry transformation
    srid = request.args.get("srid")
761
    srid=validateSrid(srid)
762

763
    # filter following a date
764
    date = request.args.get("date")
765
766
    filterDate=getFilterdateForPointcall(date)
   
Christine Plumejeaud's avatar
Christine Plumejeaud committed
767
    query = """SELECT ogc_fid, uhgs_id, total, toponyme as  toponym, belonging_states, belonging_substates, status, geonameid, amiraute as admiralty, province, shiparea , ST_AsGeoJSON(ST_Transform(geom, %s)) as point
768
769
        FROM ports.port_points p, 
                (select pointcall_uhgs_id, count( *) as total
770
771
                from navigoviz.pointcall gg %s group by pointcall_uhgs_id) as k
                where p.toponyme is not null and p.uhgs_id = k.pointcall_uhgs_id""" %(srid, filterDate)
772

773
    return retrieveDataAndFormatOutput(query, api='ports')
774

775
776
777
def getTupleNomChamp(champ):
    """
    Internal method
778
    called by getDictChampsSelect(fields_for_select) :
779
    returns a tuple (nom='name of SQL field', champ='code of field in select SQL clause')
Christine Plumejeaud's avatar
Christine Plumejeaud committed
780
781
782
    Examples
        ST_AsGeoJSON(ST_Transform(geom,4326)) as point RENVOIE (point, ST_AsGeoJSON(ST_Transform(geom,4326)) as point)
        toponyme_standard_fr as  toponym RENVOIE (toponym, toponyme_standard_fr)
783
    """
Christine Plumejeaud's avatar
Christine Plumejeaud committed
784
    #print("getTupleNomChamp champ : " + champ)
785
786
787
788
789
790
    separateur=" as "
    if separateur in champ :
        parties_champ=champ.split(separateur)
        nom=parties_champ[1].strip()
    else :
        nom=champ.strip()
Christine Plumejeaud's avatar
Christine Plumejeaud committed
791
    #print("nom : " + nom)
792
793
    return (nom, champ.strip())

794
795
796
797
798
799
800
801
def getDictChampsSelect(fields_for_select):
    """
    Internal method
    called by handleParams(champs) :
    returns dictionnary {"nom du champ": "code du champ"} of the fields contained in the select clause
    fields_for_select : fields are separated by ", " (and ", " is nowhere else in the clause !), and a field can be renamed with " as "
    """
    list_champs=fields_for_select.split(", ")
Christine Plumejeaud's avatar
Christine Plumejeaud committed
802
803
804
    dict_champs=dict([getTupleNomChamp(champ) for champ in list_champs])  # dictionnaire {"nom du champ": "code du champ"} 
    # Example : (toponym : toponyme_standard_fr), 
    #            (point : ST_AsGeoJSON(ST_Transform(geom,4326)) as point)
805
806
    return dict_champs

807
808
809
810
811
812
813
814
815
816
817
def handleParams(champs):
    """
    internal method
    if the 'params' parameter is defined, this function filters the 'champs' parameter (string) to keep only desired ones
    'champs' is the content of the SQL select clause : fields are separated by ", " (and ", " is nowhere else in the clause !), and a field can be renamed with " as "
    """
    params=request.args.get("params")
    if (params is None) or (params.lower().strip() == "all"):    # on ne souhaite pas filtrer les champs renvoyés
        pass   # renvoyer directement le contenu de la clause select complète
    else:        
        # faire un dictionnaire des champs de la clause select
818
        dict_champs=getDictChampsSelect(champs) # dictionnaire {"nom du champ": "code du champ"}
819
820
821
822
823
824
825
826
        # séparer les champs demandés :
        list_params=params.replace(" ", "").split(",")  # retire les espaces de 'params', et sépare les paramètres demandés avec ","
        # filtrer le dictionnaire des champs, pour ne garder que ceux demandés :
        list_champs_gardes=[dict_champs[nom_champ] for nom_champ in list_params if nom_champ in dict_champs]
        # assembler le contenu, prêt pour la clause 'select' :
        champs=", ".join(list_champs_gardes)
    return champs

827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
def handleOrder(fields_for_select):
    """
    internal method
    if the 'order' API parameter (string) is defined, this function filters it to keep only those fields contained in the select clause
    'fields_for_select' is the content of the SQL select clause : fields are separated by ", " (and ", " is nowhere else in the clause !), and a field can be renamed with " as "
    """
    order_by_clause=""  # vide par défaut : si rien demandé, pas de clause 'order by' dans la requête SQL
    order=request.args.get("order") # lecture du paramètre 'order=...' dans la requête à l'API
    if (order is None): # on ne souhaite pas ordonner les résultats de la requête SQL
        pass    # renvoyer la chaine par défaut
    else:
        # faire un dictionnaire des champs de la clause select
        dict_champs_select=getDictChampsSelect(fields_for_select) # dictionnaire {"nom du champ": "code du champ"}
        # séparer les champs demandés :
        list_order=order.replace(" ", "").split(",")  # retire les espaces de 'order', et sépare les champs demandés avec ","
        # ne garder dans list_order que les champs figurant dans les clés du dictionnaire dict_champs_select :
        list_order=[nom_champ for nom_champ in list_order if nom_champ in dict_champs_select]        
        # formatter la clause 'order by' :
        nb_champs_order=len(list_order)
        if (nb_champs_order > 0):
            order_txt=", ".join(list_order) # liste txt des champs à ordonner (séparateur : virgule espace)
            order_by_clause=f"order by {order_txt}" # formattage clause
    return order_by_clause

def keepDfCastsFoundInSelect(dict_cast_colonnes_df, champs_select):
    """
    Internal method
    called by getSources() :
    returns the entries of the 'dict_cast_colonnes_df' dictionnary, which are in the list of fields ('champs_select') of the 'select' clause of the SQL query
    """
    # faire un dictionnaire des champs de la clause 'select' :
    dict_champs_select=getDictChampsSelect(champs_select)
    # dans dict_cast_colonnes_df, ne garder que les entrées qui sont aussi dans dict_champs_select :
    dico_result={key: value for (key, value) in dict_cast_colonnes_df.items() if key in dict_champs_select}
    return dico_result

863
864
865
@app.route('/api/sources/')
def getSources():
    """
Christine Plumejeaud's avatar
Christine Plumejeaud committed
866
Get statistics about the amount of pointcall data found in sources (couverture des sources de navigo), related to ports of France only. Only observed ("O") pointcalls are taken into account.
867

Christine Plumejeaud's avatar
Christine Plumejeaud committed
868
869
870
871
872
Port data is a dataframe grouped by : source, port, and year. 
This means that for a given source (source_suite = G5 | Santé Marseille | Registre du petit cabotage (1786-1787) | ...), 
the API lists the ports (identified by a : toponym, ogc_fid or uhgs_id), and for each port, 
there is data for each year (pointcall_year = 1787 | 1789 | ...) except if port is missing in all sources. 
So there may be more than one record for each port ! You can filter or sum port data received, by source, and / or by year, to build your own map for instance.
873
874

Fields available :
Christine Plumejeaud's avatar
Christine Plumejeaud committed
875
- source_suite : the source (G5 | Santé Marseille | Registre du petit cabotage (1786-1787) | Expéditions "coloniales" Marseille (1789) ) where the data comes from
876
877
878
- pointcall_year : the year ( 1787 | 1789 ) for which data is computed. A data line is returned for each year (if data is available)

Fields counting congés data from sources :  
Christine Plumejeaud's avatar
Christine Plumejeaud committed
879
880
881
882
  - nb_conges_inputdone : total number of observed ("O") pointcalls (congés) actually recorded in database for this port in this source, outgoing ships, computed for a given year or null if none
  - nb_conges_cr : number of congés that are known to exist, according to national records (archives), and that are expected. For a given year  or null if none
  - nb_conges_sante : number of congés recorded in database, from register of the office of health of Marseille, incoming ships only. For a given year or null if none
  - nb_petitcabotage : number of congés recorded in database, from register "Petit cabotage" of Marseille (mainly ships coming from Mediterranean areas), incoming ships only. For a given year or null if none
883
884

Other computed fields :  
Christine Plumejeaud's avatar
Christine Plumejeaud committed
885
886
887
888
889
890
891
- nb_tonnage_filled : number of pointcalls where tonnage data (volume de marchandise en tonneaux ou quintaux) is given or null if none
- nb_homeport_filled : number of pointcalls where homeport (port d'attache du navire) is known or null if none
- nb_product_filled : number of pointcalls where commodity_purpose (nature de la marchandise transportée, ou objet du voyage) is given or null if none
- nb_birthplace_filled : number of pointcalls where birthplace (lieu d'origine du capitaine du bateau) is known or null if none
- nb_citizenship_filled : number of pointcalls where citizenship (nationalité du capitaine du bateau) is known or null if none
- nb_flag_filled : number of pointcalls where flag (pavillon du navire, i.e. nationalité/étendard du navire) is known or null if none
- good_sum_tonnage : sum of the tonnage that goes by this port (converted to barrel unit if given in quintals) or null if none
892
893
894
895
896
897
898

A join is made between pointcall and port data, to get nearly the same information about a port as with the ports api (see /api/fieldnames?api=ports) :  
- ogc_fid : id
- uhgs_id : geo_general text id of the port
- toponym : standardised name of the port, in the 'lang' language
- substate : belonging substate of the port in 1787 / 1789. In the 'lang' language
- status : null | "oblique" | "siège amirauté", type of port
Christine Plumejeaud's avatar
Christine Plumejeaud committed
899
- has_a_clerk : true if there was a clerk (cases of "oblique" ou "siège amirauté")
900
901
902
903
- geonameid : nearest geoname identifier for the port
- admiralty : name of the home admiralty (amirauté) for the port, in 1787
- province : name of the home province for the port, in 1787
- shiparea : name of the maritime area for the port in 2020
Christine Plumejeaud's avatar
Christine Plumejeaud committed
904
- point : projeted coordinates for representation on a map
905
    
906
907
908
909
910
911
912
913
914
915
916
Will be extracted from postgres, schema navigoviz, table pointcall (see navigocorpus/ETL), completed with schema ports, table port_points.

Parameters :  
- srid : **900913** | 4326 | 3857, for geometry transformation of the coordinates of the point representing the port on a map
- date : **none** | 4 digits representing a year (1787 | 1789), to extract pointcall data from the source, for this given pointcall_year only. Otherwise, when no date is given, the API returns one line per year of data (sums are done per year). Make sure to specify an 'order' if you wish to compute sums per port over the years.
- lang : **fr** | en, language for toponym(_standard), substate_1789
- params : **all** | coma-separated list of fields to be returned by the API
- order : **none** | coma-separated list of fields in desired order. Data lines will be sent in ascending order of first field values, and if equals then in order of second field listed, ...  
Example : order=toponym,pointcall_year

Examples :  
917
918
- http://data.portic.fr/api/sources/?srid=4326&date=1789
- http://data.portic.fr/api/sources/?srid=4326&date=1789&format=csv
919
920
921
922
- http://localhost/api/sources/?srid=4326
- http://localhost/api/sources/?srid=4326&date=1789
- http://localhost/api/sources/?srid=4326&date=1789&lang=en
- http://localhost/api/sources/?srid=4326&params=uhgs_id,toponym,pointcall_year,nb_conges_inputdone,nb_conges_cr,point
Christine Plumejeaud's avatar
Christine Plumejeaud committed
923
924
925
- http://localhost/api/sources/?srid=4326&params=uhgs_id,toponym,pointcall_year,nb_conges_inputdone,nb_conges_cr,point,source_suite&format=csv
- http://localhost/api/sources/?srid=4326&params=uhgs_id,toponym,pointcall_year,nb_conges_inputdone,nb_conges_cr,point,source_suite&order=toponym,pointcall_year,source_suite&format=csv
- http://localhost/api/sources/?srid=4326&params=uhgs_id,toponym,pointcall_year,nb_conges_inputdone,nb_conges_cr,nb_conges_sante,nb_petitcabotage,source_suite,point&order=toponym,pointcall_year,source_suite&format=csv
926
927
928
929
930
931
932
933
934
935
936
937
938
939
    """
    # select the srid given by user for geometry transformation
    srid = request.args.get("srid")
    srid=validateSrid(srid)

    # filter following a date
    date = request.args.get("date")
    filterDate=getFilterdateForPointcall(date)

    # language given by user (defaults to "fr")
    lang = request.args.get("lang") # read requested language
    lang=validateLang(lang) # "fr" or "en" valid only (defaults to "fr")
    localizedDatabaseFieldNames=getLocalizedFieldnamesForSources(lang)

940
941
    # liste de tous les champs qui peuvent être renvoyés, formatés pour la clause 'select' :
    # WARNING : ", " is the separator : each line (except last one) must end with ", " (the space is important). There must be ", " between two field names (not just ",") AND no ", " inside a field !
942
    fields_for_select=f"""
Christine Plumejeaud's avatar
Christine Plumejeaud committed
943
	ogc_fid, uhgs_id, {localizedDatabaseFieldNames} status, has_a_clerk, geonameid, amiraute as admiralty, province, shiparea , ST_AsGeoJSON(ST_Transform(geom,{srid})) as point, 
944
945
946
947
948
	source_suite, pointcall_year, 
	(case when pointcall_year = '1787' then nb_conges_1787_inputdone else nb_conges_1789_inputdone end) as nb_conges_inputdone, 
	(case when pointcall_year = '1787' then nb_conges_1787_cr else nb_conges_1789_cr end) as nb_conges_cr, 
	(case when pointcall_year = '1787' then nb_sante_1787 else nb_sante_1789 end) as nb_conges_sante, 
	(case when pointcall_year = '1787' then nb_petitcabotage_1787 else nb_petitcabotage_1789 end) as nb_petitcabotage, 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
949
	nb_tonnage_filled, nb_homeport_filled, nb_product_filled, nb_birthplace_filled, nb_citizenship_filled, nb_flag_filled, good_sum_tonnage
950
951
    """
    fields_for_select=handleParams(fields_for_select)   # ne garde que les champs demandés dans 'params'
952
953
    print(fields_for_select)

Christine Plumejeaud's avatar
Christine Plumejeaud committed
954
    union_G5_1787_CRonly=f"""
955
    ogc_fid, uhgs_id, {localizedDatabaseFieldNames} status, has_a_clerk, geonameid, amiraute as admiralty, province, shiparea , ST_AsGeoJSON(ST_Transform(geom,{srid})) as point, 'G5' as source_suite, 1787 as pointcall_year, nb_conges_1787_inputdone as nb_conges_inputdone, nb_conges_1787_cr as nb_conges_cr, nb_sante_1787 as nb_conges_sante, nb_petitcabotage_1787 as nb_petitcabotage, null as nb_tonnage_filled, null as nb_homeport_filled, null as nb_product_filled, null as nb_birthplace_filled, null as nb_citizenship_filled, null as nb_flag_filled, null as good_sum_tonnage
Christine Plumejeaud's avatar
Christine Plumejeaud committed
956
957
958
959
960
961
962
963
    """
    union_G5_1787_CRonly=handleParams(union_G5_1787_CRonly)   # ne garde que les champs demandés dans 'params'
    
    union_missing = union_G5_1787_CRonly.replace('\'G5\'', 'null' )
    union_missing = union_missing.replace(' 1787 ', ' null ' )#L'espace est important ?
    #print('########### union_missing ###############'+union_missing)

    union_G5_1789_CRonly=f"""
964
    ogc_fid, uhgs_id, {localizedDatabaseFieldNames} status, has_a_clerk, geonameid, amiraute as admiralty, province, shiparea , ST_AsGeoJSON(ST_Transform(geom,{srid})) as point, 'G5' as source_suite, 1789 as pointcall_year, nb_conges_1789_inputdone as nb_conges_inputdone, nb_conges_1789_cr as nb_conges_cr, nb_sante_1789 as nb_conges_sante, nb_petitcabotage_1789 as nb_petitcabotage, null as nb_tonnage_filled, null as nb_homeport_filled, null as nb_product_filled, null as nb_birthplace_filled, null as nb_citizenship_filled, null as nb_flag_filled, null as good_sum_tonnage
Christine Plumejeaud's avatar
Christine Plumejeaud committed
965
966
967
968
    """
    union_G5_1789_CRonly=handleParams(union_G5_1789_CRonly)   # ne garde que les champs demandés dans 'params'
    

969
970
    # clause order by (qui peut être vide) : ne retenir que les champs demandés dans le paramètre 'order', qui figurent dans le select
    clause_order_by=handleOrder(fields_for_select)
971
972
973

    # assemble the SQL query :
    query=f"""
974
975
976
977
978
979
980
981
982
	select {fields_for_select}
	from ports.port_points pp ,
		(
			select pointcall_uhgs_id, source_suite, pointcall_year, 
			count(tonnage) as nb_tonnage_filled, 
			count(homeport) as nb_homeport_filled,
			count(commodity_purpose) as nb_product_filled, 
			count(birthplace) as nb_birthplace_filled,
			count(citizenship) as nb_citizenship_filled,
Christine Plumejeaud's avatar
Christine Plumejeaud committed
983
            count(flag) as nb_flag_filled,
984
985
986
987
988
			sum(tonnage_numeric) as good_sum_tonnage
			from (
				select pointcall_uhgs_id, source_suite, 
				(coalesce(extract(year from TO_DATE(substring(pointcall_out_date2 for 4),'YYYY')), extract(year from TO_DATE(substring(pointcall_in_date2 for 4),'YYYY'))))::int4 as pointcall_year, 
				nb_conges_1787_inputdone, nb_conges_1787_cr, nb_conges_1789_inputdone, nb_conges_1789_cr, 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
989
				tonnage, homeport, commodity_purpose, birthplace, citizenship, flag,
990
991
992
993
994
995
				(case when tonnage_unit = 'quintaux' then (tonnage::float)/24  else tonnage::float end) as tonnage_numeric 
			from navigoviz.pointcall p 
				{filterDate} and state_1789_fr = 'France' and pointcall_function = 'O'
			) as k
		group by  pointcall_uhgs_id , source_suite, pointcall_year
		) as toto
Christine Plumejeaud's avatar
Christine Plumejeaud committed
996
997
998
999
1000
	where toto.pointcall_uhgs_id = pp.uhgs_id and pp.state_1789_fr = 'France'
    union (
        select {union_G5_1787_CRonly} from ports.port_points pp
        where state_1789_fr = 'France' and nb_conges_1787_cr is not null and nb_conges_1787_inputdone is null
    ) union (