apidata.py 28.3 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 psycopg2 as pg
23
24
25
26
27

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

28

29
30
31
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')
32
33
34
35

app = Flask(__name__)
CORS(app)

36
37
38
#app.config.from_object('config')
#port = app.config['PORT']      
port = '80'
Christine Plumejeaud's avatar
Christine Plumejeaud committed
39
postgresport = '5433'
Christine Plumejeaud's avatar
Christine Plumejeaud committed
40
database = 'portic_v5'
41

42
def retrieveDataFromPostgres(query) : 
43
    """
44
45
    Internal method to select data using SQL query
    return a dataframe
46
    """
47
    import pandas.io.sql as psql
48
    import pandas as pd
49
    #connection = pg.connect("host='134.158.33.179' port='5433' dbname='portic_v3' user='api_user' password='portic'")
50
    connection = pg.connect("""host='localhost' port='%s' dbname='%s' user='api_user' password='portic'"""% (postgresport, database))
51

52
53
54
55
    df = pd.read_sql_query(query,con=connection)
    connection.close()
    return df
    #print(df)
56
57

def formatCSV(mydataframe):
58
59
60
    """
    Internal method to output dataframe in a CSV file
    """
61
62
63
64
65
66
67
68
69
70
71
72
73
74
    #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

75
76
77
78
79
def formatJSON(dataframe):
    """
    Internal method to output dataframe as JSON
    """
    json_str = json.dumps(dataframe.to_json(orient='records'))
80
81
    return json.loads(json_str)

82
def formatOutput(dfcsv, api='travels'):
83
    """
84
85
    Internal method
    Apply various formatting on dataframe for output by processing request parameters
86
87
88
    - format : csv | **json**
    - zipped : true | **false**
    - shortenfields : true | **false**
89
90
91
92
    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
93
94
95
96
97
98
99
100
101
    """

    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
102
103
104
        #filename = os.path.join(APP_DATA, 'shorten_names.csv')
        #mapnames = pd.read_csv(filename, sep = ';') 
        mapnames = readFieldnames(api)
105
        #Filter according API
106
        #mapnames = mapnames[mapnames['API']=='pointcalls']
107
        #print(mapnames['colname'])
108
        mapper=mapnames.loc[:, ['name', 'shortname'] ].set_index('name')['shortname'].to_dict()
109
110
111
112
113
114
115
116
117
118
        #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)

119
def readFieldnames(api, schema='navigoviz') :
120
    """
121
122
123
124
125
126
    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)
127
128
    """
    import pandas as pd
129
130
131
132
133
134
135

    table_name = "pointcall','built_travels"
    if api is not None  :
        if api == 'travels' : 
            table_name = 'built_travels'
        if api == 'pointcalls' : 
            table_name = 'pointcall'
136
137
138
        if api == 'ports' : 
            table_name = 'port_points'
            schema = 'ports'
139
140
    
    #API;name;shortname;type;description
141
    query = """SELECT %s as API, 
142
        c.column_name as name, 
143
        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,
144
145
146
147
        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)
148
        where c.table_name in ('%s')  and c.table_schema = '%s' and pgd.objoid = st.relid;"""% (api, table_name, schema)
149
150
151
152
153

    #print(query)
    metadata = retrieveDataFromPostgres(query)
    return metadata
    """
154
155
    filename = os.path.join(APP_DATA, 'api_portic.csv')
    dfcsv = pd.read_csv(filename, sep = ';')
156
157
158
159
    
    if api is not None  :
        #Filter to retain this API
        dfcsv = dfcsv[dfcsv['API']==api]
160
161
162
163
164
165
166
167
168
169
170
171
172
173
    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

174
175
    http://data.portic.fr/api/fieldnames/?format=json

176
    """
177
    
178
179
180
    # Filter to keep desired API
    api = request.args.get("api")
    df = readFieldnames(api)
181

182
    return formatOutput(df, api)
183

184
185
186
187
188
@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)
189
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
Christine Plumejeaud's avatar
Christine Plumejeaud committed
190
        - date : 4 digits representing a year, to extract data for this given year only
191
192
    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
193
    http://127.0.0.1/api/pointcalls/?params=pointcall,pointcall_uhgs_id&date=1787&format=csv
194
195
196

    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
197

198
199
    """
    import pandas as pd
200
201
202
    #filename = os.path.join(APP_DATA, 'pointcalls_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')

203
204
205
    #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)
206
207
208

    query = 'select * from navigoviz.pointcall'

209
210
211
212
213
    ## 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')
214
        fields = readFieldnames('pointcalls')
215
        keepparams = str(params).split(',')
216
        #keepparams = ['pkid', 'pointcall', 'pointcall_uhgs_id']
217
218
219
220
221
        #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:
222
223
            #print(k)
            if k in fields['name'].tolist() : 
224
225
                labels.append(k)
        #print(labels)
226
227
228
229
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.pointcall'
        #dfcsv =  dfcsv.loc[:, labels]
    
Christine Plumejeaud's avatar
Christine Plumejeaud committed
230
231
232
233
234
235
    ## 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)

236
    dfcsv = retrieveDataFromPostgres(query)
237

238
    return formatOutput(dfcsv, 'pointcalls')
239
240


241
242
243
244
245
246
247

@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
248
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
249
        params=pointcall,pointcall_uhgs_id for instance
250
251
252
        - both_to : true | **false**

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

255
256
    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
257
258
    """
    import pandas as pd
259
260
    #filename = os.path.join(APP_DATA, 'travels_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')
261
    
262
263
264
    query = 'select * from navigoviz.built_travels'


265
266
    ## Filter the result according requested params
    params = request.args.get("params")
267
268
269
270
271
272
273
274
275
276
277
    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'
278
279
280
281
282
283
284


    # 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')
285
286
        #dfcsv = dfcsv[dfcsv['source_entry']!='both-to']
        query = query + " where source_entry <> 'both-to'"
Christine Plumejeaud's avatar
Christine Plumejeaud committed
287
288
289
290
291
292
293
294
295
296

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

297
298
299
    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, 'travels')
300
301
302



303
304
305
306
307
308
309

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

Christine Plumejeaud's avatar
Christine Plumejeaud committed
311
    http://localhost:80/api/details/departures?lat=46&lon=-1&radius=100&date=1787
312
313
314
315
316
317
318
319
320
321
322
    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"))
323
    except :
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
        #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
339
340
341
342
343
    ## 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)

344
345
    #print(query)
    dfcsv = retrieveDataFromPostgres(query)
346

347
    return formatOutput(dfcsv, 'travels')
348
349
350
351

@app.route('/api/agg/departures/', methods = ['GET']) 
def getDeparturesAgg():
    """
352
    Return the count of departures, for the points located in a radius km neighbourhood from the lat/lon given in parameter
353
354
    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
355

356
357
358
359
360
361
362
363
364
365
    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"))
366
    except :
367
368
369
370
371
        #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
372
373
374
375
376
377
378
    ## 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)


379
380
    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
381
        where  source_entry<> 'both-to'  %s 
382
        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
383
        group by departure""" % (filterclause, lon,lat,radius*1000)
384
385
386
    else :
        query = """select departure, count(*) 
        from navigoviz.built_travels 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
387
388
        where source_entry<> 'both-to'  %s
        group by departure""" % (filterclause)
389
390
    #print(query)
    df2 = retrieveDataFromPostgres(query)
391
    
392
    return formatOutput(df2, 'travels')
393
394

    #return json.loads(json.dumps(df2.to_json(orient='records')))   
395
396
397
398

@app.route('/api/agg/destinations/', methods = ['GET'])   
def getDestinationsAgg():
    """
399
400
    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
401
402
    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
403

Christine Plumejeaud's avatar
Christine Plumejeaud committed
404
    http://localhost/api/agg/destinations/?lat=45.2333&lon=-1&radius=100&date=1789
405
406


407
    """
Christine Plumejeaud's avatar
Christine Plumejeaud committed
408
409
410
411
412
413
414
415

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


416
417
418
419
420
421
422
    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"))
423
    except :
424
425
426
427
428
429
430
431
        #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
432
        where source_entry<> 'both-to' %s 
433
        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
434
        group by destination_admiralty""" % (filterclause, lon,lat,radius*1000)
435
436
437
    else :
        query = """select destination_admiralty as label, count(*) as value 
        from navigoviz.built_travels 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
438
439
        where source_entry<> 'both-to' %s
        group by destination_admiralty""" % (filterclause)
440
441
    #print(query)
    df3 = retrieveDataFromPostgres(query)
442
    df3['id'] = df3['label']
443

444
    return formatOutput(df3, 'travels')
445

446

447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
@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
463
        - date : **1787** | yyyy (4 digits)
464
465
466
467
468
469
470

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

473
474

    [{"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
475
    [{"t001":"0008663N- 05","t004":"Bordeaux","t020":"Bordeaux"},{"t001":"0010656N- 01","t004":"Bordeaux","t020":"Bordeaux"}]
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491

    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
492

493
494
495
496
497
498
499
500
501
502
503
504
505
    ## 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'
506
507


508
509
510
511
512
513
514
    # 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
515
516
517
518
519
520
521
522
523
524

    ## 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')""" 


525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
    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
543
544
    filter_degree_in = "true"
    filter_degree_out = "true"
545
546
547
548
549
550
    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
551
552
        filter_degree_in = "travel_rank >= k.subject_order " +"-"+ str(degree-1)
        filter_degree_out = "travel_rank <= k.subject_order" +"+"+ str(degree-1)
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585

    ## 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
586
                            and (departure_action like 'Sailing around' or departure_action like 'In-out' or departure_action like 'Transit')
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
                        	) 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)    
    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, 'travels')

607
608
609
610
611
612
@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
613
    ogc_fid, uhgs_id, total, toponym, belonging_states, belonging_substates, status, geonameid, admiralty, province, shiparea , point
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
    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")
    #print (srid)

    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'

    print (srid)

640
641
642
643
644
645
    ## filter following a date
    filterDate = 'where true'
    date = request.args.get("date")
    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)
        
Christine Plumejeaud's avatar
Christine Plumejeaud committed
646
    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
647
648
        FROM ports.port_points p, 
                (select pointcall_uhgs_id, count( *) as total
649
650
                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)
651
652
653
654
655
656
657
658

    data = retrieveDataFromPostgres(query)
    return formatOutput(data, 'ports')





659
660
if __name__ == '__main__':
    app.run(debug=True,port=port,threaded=True)