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 psycopg2 as pg

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

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

def retrieveDataFromPostgres(query) : 
    """
    Internal method to select data using SQL query
    return a dataframe
    """
    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)
    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 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 == '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)

    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, '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)

    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, 'travels')




@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)
    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, '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 
        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)
    df2 = retrieveDataFromPostgres(query)
    
    return formatOutput(df2, '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)
    df3 = retrieveDataFromPostgres(query)
    df3['id'] = df3['label']

    return formatOutput(df3, 'travels')


@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)    
    dfcsv = retrieveDataFromPostgres(query)

    return formatOutput(dfcsv, 'travels')

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

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

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





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