apidata.py

#!/usr/bin/python3
# -*-coding:UTF-8 -*
'''
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
'''

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

from flask_caching import Cache
import numpy as np
import csv
import json
import io
import os
import pandas as pd
import psycopg2 as pg
import re

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


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

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

#app.config.from_object('config')
#port = app.config['PORT']      
port = '80'
postgresport = '5432'
database = 'portic_v6'

# 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) : 
    """
    Internal method to select data using SQL query
    return a dataframe
    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
    """
    import pandas.io.sql as psql
    import pandas as pd
    #connection = pg.connect("host='134.158.33.179' port='5433' dbname='portic_v3' user='api_user' password='portic'")
    connection = pg.connect("""host='localhost' port='%s' dbname='%s' user='api_user' password='portic'"""% (postgresport, database))

    df = pd.read_sql_query(query,con=connection,coerce_float=False, dtype=cast_columns_to_types)
    connection.close()
    return df
    #print(df)

def formatCSV(mydataframe):
    """
    Internal method to output dataframe in a CSV file
    """
    #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

def formatJSON(dataframe):
    """
    Internal method to output dataframe as JSON
    """
    json_str = json.dumps(dataframe.to_json(orient='records'))
    return json.loads(json_str)

def formatOutput(dfcsv, api='travels'):
    """
    Internal method
    Apply various formatting on dataframe for output by processing request parameters
    - format : csv | **json**
    - zipped : true | **false**
    - shortenfields : true | **false**
    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
    """

    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
        #filename = os.path.join(APP_DATA, 'shorten_names.csv')
        #mapnames = pd.read_csv(filename, sep = ';') 
        mapnames = readFieldnames(api)
        #Filter according API
        #mapnames = mapnames[mapnames['API']=='pointcalls']
        #print(mapnames['colname'])
        mapper=mapnames.loc[:, ['name', 'shortname'] ].set_index('name')['shortname'].to_dict()
        #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)

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


def readFieldnames(api, schema='navigoviz') :
    """
    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)
    """
    import pandas as pd

    table_name = "pointcall','built_travels"
    if api is not None  :
        if api == 'travels' : 
            table_name = 'built_travels'
        if api == 'rawflows' : 
            table_name = 'raw_flows'
        if api == 'pointcalls' : 
            table_name = 'pointcall'
        if api == 'ports' : 
            table_name = 'port_points'
            schema = 'ports'
    
    #API;name;shortname;type;description
    query = """SELECT '%s' as API, 
        c.column_name as name, 
        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,
        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)
        where c.table_name in ('%s')  and c.table_schema = '%s' and pgd.objoid = st.relid;"""% (api, table_name, schema)

    print(query)
    metadata = retrieveDataFromPostgres(query)
    return metadata
    """
    filename = os.path.join(APP_DATA, 'api_portic.csv')
    dfcsv = pd.read_csv(filename, sep = ';')
    
    if api is not None  :
        #Filter to retain this API
        dfcsv = dfcsv[dfcsv['API']==api]
    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

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

    """
    
    # Filter to keep desired API
    api = request.args.get("api")
    df = readFieldnames(api)
    return formatOutput(df, api)

@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)
        - params : **all** | tableau des noms longs des attributs de l'API à renvoyer
        - date : 4 digits representing a year, to extract data for this given year only
    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
    http://127.0.0.1/api/pointcalls/?params=pointcall,pointcall_uhgs_id&date=1787&format=csv

    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

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

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

    query = 'select * from navigoviz.pointcall'

    ## 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')
        fields = readFieldnames('pointcalls')
        keepparams = str(params).split(',')
        #keepparams = ['pkid', 'pointcall', 'pointcall_uhgs_id']
        #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:
            #print(k)
            if k in fields['name'].tolist() : 
                labels.append(k)
        #print(labels)
        attributes = ",".join(labels)
        query = 'select '+attributes+' from navigoviz.pointcall'
        #dfcsv =  dfcsv.loc[:, labels]
    
    ## 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)

    return retrieveDataAndFormatOutput(query, api='pointcalls')



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

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

    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
    """
    import pandas as pd
    #filename = os.path.join(APP_DATA, 'travels_API_11mai2020.csv')
    #dfcsv = pd.read_csv(filename, sep = ';')
    
    query = 'select * from navigoviz.built_travels'


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


    # 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']
        query = query + " where source_entry <> 'both-to'"

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

    return retrieveDataAndFormatOutput(query, api='travels')

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

    return retrieveDataAndFormatOutput(query, api='rawflows')


@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

    http://localhost:80/api/details/departures?lat=46&lon=-1&radius=100&date=1787
    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"))
    except :
        #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()
    
    ## 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)

    #print(query)
    return retrieveDataAndFormatOutput(query, api='travels')

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

    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"))
    except :
        #We go on, silenciously, by selecting all data
        pass
    #df2 = calcul_isInside(lat, lon, radius).departure.value_counts().reset_index()
    #df2.columns = ['departure', 'count']

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


    if lat != None and lon != None and radius!= None : 
        query = """select  departure, count(*) as count from navigoviz.built_travels  
        where  source_entry<> 'both-to'  %s 
        and st_distance(departure_point, st_setsrid(st_transform(st_setsrid(st_makepoint(%f, %f), 4326), 3857), 3857)) < %f 
        group by departure""" % (filterclause, lon,lat,radius*1000)
    else :
        query = """select departure, count(*) 
        from navigoviz.built_travels 
        where source_entry<> 'both-to'  %s
        group by departure""" % (filterclause)
    #print(query)
    return retrieveDataAndFormatOutput(query, api='travels')
    #return json.loads(json.dumps(df2.to_json(orient='records')))   

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

    http://localhost/api/agg/destinations/?lat=45.2333&lon=-1&radius=100&date=1789
    """

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


    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"))
    except :
        #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  
        where source_entry<> 'both-to' %s 
        and st_distance(departure_point, st_setsrid(st_transform(st_setsrid(st_makepoint(%f, %f), 4326), 3857), 3857)) < %f 
        group by destination_admiralty""" % (filterclause, lon,lat,radius*1000)
    else :
        query = """select destination_admiralty as label, count(*) as value 
        from navigoviz.built_travels 
        where source_entry<> 'both-to' %s
        group by destination_admiralty""" % (filterclause)
    #print(query)

    # 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


@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**
        - date : **1787** | yyyy (4 digits)

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


    [{"t01":"0008663N- 05","t04":"Bordeaux","t19":"Bordeaux"},{"t01":"0009557N- 01","t04":"Bordeaux","t19":"Bordeaux"},{"t01":"0010656N- 01","t04":"Bordeaux","t19":"Bordeaux"}]
    [{"t001":"0008663N- 05","t004":"Bordeaux","t020":"Bordeaux"},{"t001":"0010656N- 01","t004":"Bordeaux","t020":"Bordeaux"}]

    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'


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


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

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


    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
    filter_degree_in = "true"
    filter_degree_out = "true"
    try:
        degree = int(request.args.get("degree"))
    except :
        #We go on, silenciously, by selecting all data
        pass
    if (degree > 0) : 
        filter_degree_in = "travel_rank >= k.subject_order " +"-"+ str(degree-1)
        filter_degree_out = "travel_rank <= k.subject_order" +"+"+ str(degree-1)

    ## 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' or departure_action like 'Transit')
                        	) 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)    

    return retrieveDataAndFormatOutput(query, api='travels')

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

    
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
    """
    return f"toponyme_standard_{lang} as  toponym, substate_1789_{lang} as substate,"


@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 : 
    ogc_fid, uhgs_id, total, toponym, belonging_states, belonging_substates, status, geonameid, admiralty, province, shiparea , point
    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")
    srid=validateSrid(srid)

    # filter following a date
    date = request.args.get("date")
    filterDate=getFilterdateForPointcall(date)
   
    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
        FROM ports.port_points p, 
                (select pointcall_uhgs_id, count( *) as total
                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)

    return retrieveDataAndFormatOutput(query, api='ports')

def getTupleNomChamp(champ):
    """
    Internal method
    called by getDictChampsSelect(fields_for_select) :
    returns a tuple (nom='name of SQL field', champ='code of field in select SQL clause')
    """
    separateur=" as "
    if separateur in champ :
        parties_champ=champ.split(separateur)
        nom=parties_champ[1].strip()
    else :
        nom=champ.strip()
    return (nom, champ.strip())

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(", ")
    dict_champs=dict([getTupleNomChamp(champ) for champ in list_champs])  # dictionnaire {"nom du champ": "code du champ"}
    return dict_champs

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
        dict_champs=getDictChampsSelect(champs) # dictionnaire {"nom du champ": "code du champ"}
        # 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

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

@app.route('/api/sources/')
def getSources():
    """
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 in account.

Port data is grouped by : source, port, and year. This means that for a given source (source_suite = G5 | Marseille | ...), 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 | ...). 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.

Fields available :
- source_suite : the source (G5 | Marseille | ...) where the data comes from
- 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 :  
  - 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
  - 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  
  - 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
  - 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

Other computed fields :  
- nb_tonnage_filled : number of pointcalls where tonnage data (volume de marchandise en tonneaux ou quintaux) is given
- nb_homeport_filled : number of pointcalls where homeport (port d'attache du navire) is known
- nb_product_filled : number of pointcalls where commodity_purpose (nature de la marchandise transportée, ou objet du voyage) is given
- nb_birthplace_filled : number of pointcalls where birthplace (lieu d'origine du capitaine du bateau) is known
- nb_citizenship_filled : number of pointcalls where citizenship (nationalité du capitaine du bateau) is known
- good_sum_tonnage : sum of the tonnage that goes by this port (converted to barrel unit if given in quintals)

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
- 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
- point : coordinates for representation on a map
    
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 :  
- http://data.portic.fr/api/sources/?srid=4326&date=1789
- http://data.portic.fr/api/sources/?srid=4326&date=1789&format=csv
- 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
- http://localhost/api/sources/?srid=4326&params=uhgs_id,toponym,pointcall_year,nb_conges_inputdone,nb_conges_cr,point&order=toponym,pointcall_year,source_suite
    """
    # 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)

    # 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 !
    fields_for_select=f"""
	ogc_fid, uhgs_id, {localizedDatabaseFieldNames} status, geonameid, amiraute as admiralty, province, shiparea , ST_AsGeoJSON(ST_Transform(geom,{srid})) as point, 
	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, 
	nb_tonnage_filled, nb_homeport_filled, nb_product_filled, nb_birthplace_filled, nb_citizenship_filled, good_sum_tonnage
    """
    fields_for_select=handleParams(fields_for_select)   # ne garde que les champs demandés dans 'params'
    print(fields_for_select)

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

    # assemble the SQL query :
    query=f"""
	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,
			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, 
				tonnage, homeport, commodity_purpose, birthplace, citizenship, 
				(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
	where toto.pointcall_uhgs_id = pp.uhgs_id 
	{clause_order_by}
    """
    # print(query)

    # Pandas casts some integers to float when executing SQL request, so to correct this, we ask to cast some columns :
    # ( dtype String Aliases : https://pandas.pydata.org/docs/user_guide/basics.html#basics-dtypes )
    cast_df_columns_to_types = {
        'nb_conges_inputdone' : 'Int64',
        'nb_conges_cr' : 'Int64',
        'nb_conges_sante' : 'Int64',
        'nb_petitcabotage' : 'Int64',
    }
    # keep only fields present in the 'select' clause :
    cast_df_columns_to_types=keepDfCastsFoundInSelect(cast_df_columns_to_types, fields_for_select)

    # execute SQL query, and format resulting data :
    return retrieveDataAndFormatOutput(query, 'ports', cast_df_columns_to_types)

@app.route('/api/test/')
def getTest():
    print("/api/test/")

    import pandas as pd2
    print("PANDAS version :"+pd2.__version__)

    import sys
    current_version = str(sys.version_info[0])+"."+str(sys.version_info[1])+"."+str(sys.version_info[2])
    print("PYTHON version :"+current_version)

    return json.loads('{"pandas version" : "'+pd2.__version__+'", "python version (of WSGI !)" : "'+current_version+'"}')



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