apidata.py 27.9 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
141
142
    
    #API;name;shortname;type;description
    query = """SELECT case when c.table_name= 'built_travels' then 'travels' else 'pointcalls' end as API, 
        c.column_name as name, 
Christine Plumejeaud's avatar
Christine Plumejeaud committed
143
        case when c.table_name= 'built_travels' then 't' else 'p' 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;"""% (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
174
    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

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

180
    return formatOutput(df, api)
181

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

    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
195

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

201
202
203
    #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)
204
205
206

    query = 'select * from navigoviz.pointcall'

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

234
    dfcsv = retrieveDataFromPostgres(query)
235

236
    return formatOutput(dfcsv, 'pointcalls')
237
238


239
240
241
242
243
244
245

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

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

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


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


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

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

295
296
297
    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, 'travels')
298
299
300



301
302
303
304
305
306
307

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

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

342
343
    #print(query)
    dfcsv = retrieveDataFromPostgres(query)
344

345
    return formatOutput(dfcsv, 'travels')
346
347
348
349

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

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


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

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

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

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


405
    """
Christine Plumejeaud's avatar
Christine Plumejeaud committed
406
407
408
409
410
411
412
413

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


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

442
    return formatOutput(df3, 'travels')
443

444

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

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

471
472

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

    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
490

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


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

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


523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
    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
541
542
    filter_degree_in = "true"
    filter_degree_out = "true"
543
544
545
546
547
548
    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
549
550
        filter_degree_in = "travel_rank >= k.subject_order " +"-"+ str(degree-1)
        filter_degree_out = "travel_rank <= k.subject_order" +"+"+ str(degree-1)
551
552
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
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604

    ## 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
                            and (departure_action like 'Sailing around' or departure_action like 'In-out' )
                        	) 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')

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

Christine Plumejeaud's avatar
Christine Plumejeaud committed
638
    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
639
640
641
642
643
644
645
646
647
648
649
650
        FROM ports.port_points p, 
                (select pointcall_uhgs_id, count( *) as total
                from navigoviz.pointcall gg group by pointcall_uhgs_id) as k
                where p.toponyme is not null and p.uhgs_id = k.pointcall_uhgs_id""" %(srid)

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





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