porticgaz.py 27.5 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/usr/bin/python3
# -*-coding:UTF-8 -*
'''
Created on 10 june 2020
@author: cplumejeaud
ANR PORTIC : used to publish geographic data for portic - ports, amiraute and province 
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.
3. to have imported the geonames database (see instruction here : https://download.geonames.org/export/dump/readme.txt)
@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
18
#import numpy as np
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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)

    
port = '80'
39
40
postgresport = '5433'
database = 'portic_v5'
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

names = dict()
names['Corse'] = 'Isles de Corse'
names['Eu'] = 'Eu et Tréport'
names['Port-Bail'] = 'Portbail et Carteret'
names['Sables-d’Olonne'] = 'Les Sables d''Olonnes'
names['Isles de Corse'] = 'Corse'
names['Eu et Tréport'] = 'Eu'
names['Portbail et Carteret '] = 'Port-Bail'
names['Les Sables d''Olonnes'] = 'Sables-d’Olonne'

 
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))
62
63
    #connection = pg.connect("host='localhost' port='8004' dbname='portic_v5' user='api_user' password='portic'")
    #ssh -N -L 8004:localhost:5433 -v navigo@134.158.33.179 
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214

    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(sep=';')

    #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='ports'):
    """
    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")
    if (shortenfields != None and shortenfields=='true') :
        mapnames = readFieldnames(api)
        mapper=mapnames.loc[:, ['name', 'shortname'] ].set_index('name')['shortname'].to_dict()
        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='ports') :
    """
    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 = 'port_points'
    schema = 'ports'
    if api is not None  :
        if api == 'travels' : 
            table_name = 'built_travels'
            schema = 'navigoviz'
        if api == 'pointcalls' : 
            table_name = 'pointcall'
            schema = 'navigoviz'
        if api == 'ports' : 
            table_name = 'port_points'
            schema = 'ports'
    
    #API;name;shortname;type;description
    query = """SELECT case when c.table_name= 'port_points' then 'ports' else 'pointcalls' end as API, 
        c.column_name as name, 
        case when c.table_name= 'built_travels' then 't' else 'p' end||navigo.pystrip(to_char(c.ordinal_position::int, '09')) 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;"""% (table_name, schema)

    #print(query)
    metadata = retrieveDataFromPostgres(query)
    return metadata

         
@app.route('/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 the gazetteer with short and long name, type and definition

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

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

    return formatOutput(df, api)

@app.route('/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, status, geonameid, admiralty, province, shiparea , point
    User can get a description of the attributes by using /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/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)

215
    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
216
        FROM ports.port_points p, 
217
                (select pointcall_uhgs_id, count(*) as total
218
219
220
221
222
223
                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')

224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
def describePlace(kind_of_entity, identifier):
    # Read the template_feature
    template = os.path.join(APP_ROOT, 'template_feature.json')
    test = os.path.join(APP_ROOT, 'test.json')

    #print(template)
    template_description = ""
    inputjson = open(template, "r")
    for x in inputjson:
        template_description = template_description + x
    inputjson.close()

    #param_uhgs_id = 'A0209756' #Menton 
    #param_uhgs_id = 'A0222919' # Isles de Saint Pierre
    #param_uhgs_id = 'A0115898' # Mahon
    #param_uhgs_id = 'A0198999' #La Rochelle
    param_uhgs_id = identifier

    if kind_of_entity == 'port' :
        ## Read data about the entity port 
        query = """select uhgs_id, toponyme, country2019_iso2code, relation_state, longitude, latitude, geonameid, toustopos 
        from ports.port_points where uhgs_id = '%s' """ %( param_uhgs_id)
        data = retrieveDataFromPostgres(query)
        title = data.loc[0, 'toponyme']
        ccodes = data.loc[0, 'country2019_iso2code']
        toponym = data.loc[0, 'toponyme']
    else :
        title = param_uhgs_id
        ccodes = 'FR'
        toponym = param_uhgs_id

    ## When
    start='1749'
    end='1815'

    ## Les noms
    if kind_of_entity == 'port':
        names = getPortNames(toponym, param_uhgs_id)
    else:
        names =  getEntityNames(param_uhgs_id)

    ## Les types
    types = getTypes(kind_of_entity)

    ## Les geometries
    if kind_of_entity == 'port':
        geometries = getPortGeometry(param_uhgs_id, data.loc[0, 'longitude'] , data.loc[0, 'latitude'], data.loc[0, 'geonameid'])
    elif kind_of_entity == 'amiraute':
        geometries = getAmirauteGeometry(param_uhgs_id)
    else :
        geometries = getProvinceGeometry(param_uhgs_id)
    
    ## les Links
    links = getLinks(param_uhgs_id, kind_of_entity)

    ## Les Relations
    if kind_of_entity == 'port':
        relations = getPortRelations(param_uhgs_id)
    elif kind_of_entity == 'amiraute' :
        relations =  getAmirauteRelation(param_uhgs_id)
    else : 
        relations =  getProvinceRelation()

    ## Fill template with description
    description = template_description % (param_uhgs_id, title, ccodes, start, end, names[:-1], types, geometries, links, relations)

    '''
    output = open(test, "w") #"utf-8"
    output.write( description) #json.dumps(description)
    output.close()
    '''
    
    return description
    #return json.loads(description)

299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
@app.route('/places/')   
def getPlaces():
    """
    Return the places using linked places model as specified in 
    https://github.com/LinkedPasts/linked-places
    See : http://linkedpasts.org/ontology/lpo_latest.ttl

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


    http://127.0.0.1:80/places/?Dunkerque
    http://127.0.0.1:80/places/?A0198999
    http://127.0.0.1:80/places/?Flandre

    """

    kind_of_entity = 'port'

    dico = request.args.to_dict()
    for k in dico.keys():
        #print('search for '+k)
        #Check that this one exists in the database (case sensitive)
        query = """select uhgs_id, toponyme, amiraute, province from ports.port_points"""
        results = retrieveDataFromPostgres(query)
        if k in results['uhgs_id'].tolist() : 
            kind_of_entity = 'port'
        elif k in results['amiraute'].tolist() : 
            kind_of_entity = 'amiraute'
        elif k in results['province'].tolist() : 
            kind_of_entity = 'province'
        else : 
            #We stop here
            return '{ "No entry like this :" '+k+'}'
        
333
        return describePlace(kind_of_entity, k)
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
        


def getPortGeometry(uhgs_id, longitude, latitude, geonameid = None):
    """
    Internal method to get port geometries : Point of geo_general plus Point from geonames if a match exists with 
    (less than 5 km around, similar by not exactly identic name)
    Data are given in WGS84 coordinates (EPSG 4326)
    uncertain by default (we explain that elsewhere) due to a lack a precision which is current. 
    The port is sometines on the docks, or placed in the center of the town. 
    Sometime may have multiple representations (as in Douardenez, with multiple small places around the limits of present town)
    """
    #return geometries 
    geometries = """{ "type": "Point",
              "coordinates": [%s,%s],
              "when": {"timespans":[
                  {"start":{"in":"1749"},"end":{"in":"1815"}}]},
              "certainty": "uncertain"
            }
    """ %(longitude, latitude)

    if geonameid is not None:
        query = """ select longitude, latitude from ports.myremote_geonamesplaces where geonameid = %s """ %(geonameid)
        coords = retrieveDataFromPostgres(query)
        geometries = geometries + """, { "type": "Point",
              "coordinates": [%s,%s],
              "when": {"timespans":[
                {"start":{"in":"1750"},"end":{"in":"2020"}}]},
              "citations": [
                {"label": "Geonames (retrieved november 2019)",
                 "@id":"geoname:%s"}],
              "certainty": "uncertain"
            }""" % (coords.loc[0, 'longitude'], coords.loc[0, 'latitude'], geonameid)

    return     geometries

def getAmirauteGeometry(toponyme) :
    """
    Internal method to get amiraute geometries : Lines drawing the borders of those former administrative division, according to Chardon text
    Lines were hand-drawn by Christine Plumejeaud and Thierry Sauzeau using QGIS, and Cassini map as base layer. 
    Data are given in WGS84 coordinates (EPSG 4326)
    The text is hard to interpret sometimes, and geography is not clear, which explain uncertain quality
    Moreover, some borders were much disputed between admiralties
    """
    query = """ select st_asgeojson(st_transform(geom, 4326))::json->>'coordinates' as coordinates from ports.limites_amirautes la where standardized_name1 = '%s' OR standardized_name2 = '%s'""" % (toponyme, toponyme)
    coords = retrieveDataFromPostgres(query)
    geometries = ''
    for k in coords['coordinates'] :
        #print(k)
        geometries = geometries + """
            { "type": "MultiLineString",
              "coordinates": [%s],
              "when": {"timespans":[
                {"start":{"in":"1781"},"end":{"in":"1785"}}]},
              "citations": [
                {"label": "Archives Nationales, C4 174 à 176. Procès-verbaux d'inspections des ports et amirautés de France par le commissaire Chardon. 1781-1785",
                 "@id":"doi:10.4000/books.pur.115293"}],
              "certainty": "uncertain"
            },""" % k
    geometries = geometries [:-1]
    #print(geometries)
    return geometries

def getProvinceGeometry(toponyme) :
    """
    Internal method : gives only the set of ports points that are inside the province. 
    This a is bias of the study : we do only study maritime provinces, and we don't know borders of province inside hinter lands
    Data are given in WGS84 coordinates (EPSG 4326)
    """
    query = """ select uhgs_id, longitude, latitude from ports.port_points where province = '%s' """ % (toponyme)
    ports = retrieveDataFromPostgres(query)
    geometries = ''
    for uhgs_id in ports['uhgs_id'] :
407
408
        #print((ports.loc[ports['uhgs_id'] == uhgs_id, 'longitude'].values[0]))
        #print(ports.loc[ports['uhgs_id'] == uhgs_id, 'latitude'].values[0])
409
410
411
412
413
414
415
416
417
418
419
420
421
422

        geometries = geometries + getPortGeometry(uhgs_id, ports.loc[ports['uhgs_id'] == uhgs_id, 'longitude'].values[0], ports.loc[ports['uhgs_id'] == uhgs_id, 'latitude'].values[0]) + ','
    return geometries[:-1]

def getPortNames(toponym, uhgs_id):
    """
    Internal method : gives only the set of ports toponymes that have been noted in Geo_general. 
    This a is bias of the study : 
    - we may have some mispellings inside like "Isles Saint Pierres", 
    - or name like Saint Pierre [en Sardaigne] with [] put the person who made the retranscription to explain her/his interpretation of Saint Pierre
    We tries to removed obvious things like LA Rochelle and La Rochelle : its the same. 
    
    The validity period is those names are limited to the one of the study : 1749-1815
    They may have been spelled like or diffently in other periods
Christine Plumejeaud's avatar
Christine Plumejeaud committed
423
424

    import difflib : npm install difflib
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
    """
    # Read toponymes about the entity port
    #print ('toponym officiel :'+toponym)
    list_othertopo = list()
    list_othertopo.append(toponym)
    query = """ CREATE  type  topofreq_type  AS (
    topo   text,
    freq  integer
    ); 
    select (json_populate_recordset(null::topofreq_type , topofreq )).topo   from ports.port_points where  uhgs_id = '%s' ;""" % (uhgs_id)
    toponymes = retrieveDataFromPostgres(query)
    import difflib
    for topo in toponymes['topo'].tolist():
        # We filter the toponym alternative if similar to one of the list
        foundIdem = False
        for k in list_othertopo :
            #https://towardsdatascience.com/overview-of-text-similarity-metrics-3397c4601f50
            #https://stackabuse.com/comparing-strings-using-python/
            similarity = difflib.SequenceMatcher(None, topo.upper(), k.upper()).ratio()
            if (similarity == 1) :
                foundIdem = True
        if foundIdem is False:
            list_othertopo.append(topo)

    ## Add names to the template
    names = ""
    for item in list_othertopo:
        names = names + """ 
        { "toponym":"%s",
        "lang":"fr",
        "citations": [
          {"label": "Portic Gazetteer (1787)",
            "@id":"http://anr.portic.fr/"}],
            "when": { "timespans":[{"start":{"in":"1749"}, "end":{"in":"1815"}}]}
        },""" % (item)
    #print(names)
    return names    

def getEntityNames(toponym):
    """
    Internal method : gives only one name (toponym) for the amiraute or province entity. 
    """
    name = """ { 
        "toponym":"%s",
        "lang":"fr",
        "citations": [
          {"label": "Portic Gazetteer (1787)",
            "@id":"http://anr.portic.fr/"}]
        } """ % (toponym)
    return name

def getLinks(uhgs_id, type = 'port') :
    """
    Internal method : gives the related information on the Web to the entity
    Note that we didn't look up for any wiki page for ech port, but it could be done in a future step.
    For port : the  geoname id of the related entity in geoname (less than 5 km and string similarity with one the the toponymes of the port)
    """ 
    wiki_provinces = dict()
    wiki_provinces['Languedoc'] = 'https://fr.wikipedia.org/wiki/Languedoc'
    wiki_provinces['Normandie'] = 'https://fr.wikipedia.org/wiki/Duch%C3%A9_de_Normandie'
    wiki_provinces['Aunis'] = 'https://fr.wikipedia.org/wiki/Aunis'
    wiki_provinces['Saintonge'] = 'https://fr.wikipedia.org/wiki/Saintonge'
    wiki_provinces['Poitou'] = 'https://fr.wikipedia.org/wiki/Poitou'
    wiki_provinces['Guyenne'] = 'https://fr.wikipedia.org/wiki/Duch%C3%A9_d%27Aquitaine'
    wiki_provinces['Picardie'] = 'https://fr.wikipedia.org/wiki/Picardie_(province)'
    wiki_provinces['Provence'] = 'https://fr.wikipedia.org/wiki/Comt%C3%A9_de_Provence'
    wiki_provinces['Bretagne'] = 'https://fr.wikipedia.org/wiki/Province_de_Bretagne'
    wiki_provinces['Roussillon'] = 'https://fr.wikipedia.org/wiki/Roussillon_(province)'
    wiki_provinces['Flandre'] = 'https://fr.wikipedia.org/wiki/Flandre_fran%C3%A7aise'
    wiki_provinces['Corse'] = 'https://fr.wikipedia.org/wiki/Corse_(province)'

496
    links = ""
497
498
499
500
    if type == 'port':
        query = """select id2, simtext, distgeo, certainity from ports.matching_port 
        where source1 = 'geo_general' and source2='geonames' and uhgs_id ='%s' and best is true""" %(uhgs_id)
        matches = retrieveDataFromPostgres(query)
501
502
503
504
505
506
507
508
        #print(" nombre de links %d pour le port %s " %(matches.shape[0], uhgs_id))
        if (matches.shape[0]> 0) :
            link_type = 'exactMatch' 
            if (matches.shape[0]> 1) : 
                link_type = 'closeMatch'
            links = """{"type": "%s", "identifier": "http://www.geonames.org/%s/"}""" % (link_type, matches.loc[0, 'id2'])
        #print(links)

509
510
511
512
513
514
    if type == 'amiraute' :
        links = """{"type": "primaryTopicOf", "identifier": "https://fr.wikipedia.org/wiki/Amiraut%C3%A9_(justice)"} """
        
    if type == 'province' :
        links = """{"type": "primaryTopicOf", "identifier": "https://fr.wikipedia.org/wiki/Territoires_du_royaume_de_France"}"""
        links = links +""", {"type": "primaryTopicOf", "identifier": "https://fr.wikipedia.org/wiki/Territoires_du_royaume_de_France#Liste_des_anciennes_provinces_de_France"}"""
515
516
517
518
        if uhgs_id in wiki_provinces.keys():
            links = links +""", {"type": "primaryTopicOf", "identifier": "%s"} """% (wiki_provinces[uhgs_id])
        else :
            print('no entry in wiki_provinces for '+uhgs_id)
519
520
521
522
523
        if uhgs_id == 'Bretagne' : 
            links = links +""", {"type": "subjectOf", "identifier": "https://fr.wikipedia.org/wiki/Amiraut%C3%A9s_de_Bretagne"} """
        if uhgs_id == 'Guyenne' : 
            links = links +""", {"type": "subjectOf", "identifier": "https://fr.wikipedia.org/wiki/Amiraut%C3%A9_de_Guyenne"}"""
    
524

525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
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
    return links
       

def getTypes(type = 'port'):
    """
    Internal method : gives the object class of each entity, according to aat (Getty Vocabulary : http://vocab.getty.edu/aat/)
    and if possible according other source (wikidata)
    """ 
    
    if type == 'port':
        port_type = """{ "identifier": "aat:300120599", "label": "ports (settlements)"} """
        return port_type
    
    if type == 'amiraute':
        amiraute_type = """{ "identifier": "aat:300387179",
            "label": "former administrative divisions",
            "sourceLabels": [{"label":"Admiralty","lang":"en"}, {"label":"Amirauté","lang":"fr"}],
            "when": {"timespans":[{"start":{"earliest":"1669?"}, "end":{"latest":"1791"}}]}
        },
        { "identifier": "wiki:Q2843514",
            "label": "Amirauté (justice)",
            "sourceLabels": [{"label":"Admiralty","lang":"en"}, {"label":"Amirauté","lang":"fr"}],
            "when": {"timespans":[{"start":{"earliest":"1669?"}, "end":{"latest":"1791"}}]}
        } """
        return amiraute_type

    if type == 'province':    
        province_type = """ { "identifier": "aat:300000774",
            "label": "province",
            "sourceLabels": [{"label":"Province","lang":"en"}, {"label":"Province","lang":"fr"}],
            "when": {"timespans":[{"start":{"earliest":"843"}, "end":{"latest":"1791"}}]}
        },
        { "identifier": "wiki:Q209495",
            "label": "historical province of France",
            "sourceLabels": [{"label":"Province","lang":"en"}, {"label":"Province","lang":"fr"}],
            "when": {"timespans":[{"start":{"earliest":"843"}, "end":{"latest":"1791"}}]}
        }
        """
        return province_type

def getPortRelations(uhgs_id) : 
    """
    Internal method : gives the state belongings of the port during the period [1749-1815]
    and its relation to amiraute and province if exists  during the period [1781-1785] (citation of Chardon)
    """ 
    query = """ select relation_state, province, amiraute from ports.port_points where uhgs_id = '%s'""" % (uhgs_id)
    state = retrieveDataFromPostgres(query)
    relations = ""
    #print(state.loc[0, 'relation_state'])
    if state.loc[0, 'relation_state']  is not None:
        relations = relations + state.loc[0, 'relation_state']+","
    #print(str(relations, 'utf-8'))
    #relations = relations.decode().encode()
    #print(relations)

    #Si il a une amirauté : 
    #print(state.loc[0, 'amiraute'])
    if state.loc[0, 'amiraute'] is not None:
        relations = relations + """
        { "relationType": "gvp:broaderPartitive",
          "relationTo": "http://gaz.portic.fr/places/?%s",
          "label": "Admiralty %s",
          "when":{"timespans":[
            {"start":{"in":"1781"}, "end":{"in":"1785"}}]},
          "citations": [
            {"label": "Archives Nationales, C4 174 à 176. Procès-verbaux d'inspections des ports et amirautés de France par le commissaire Chardon. 1781-1785",
             "@id": "doi:10.4000/books.pur.115293"}],
          "certainty": "less-certain"
        },""" % (state.loc[0, 'amiraute'], state.loc[0, 'amiraute'])

    # Si il a une province : 
596
    if state.loc[0, 'province'] is not None:
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
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
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
        relations = relations + """
        { "relationType": "gvp:broaderPartitive",
          "relationTo": "http://gaz.portic.fr/places/?%s",
          "label": "Province %s",
          "when":{"timespans":[
            {"start":{"in":"1781"}, "end":{"in":"1785"}}]},
          "citations": [
            {"label": "Archives Nationales, C4 174 à 176. Procès-verbaux d'inspections des ports et amirautés de France par le commissaire Chardon. 1781-1785",
             "@id": "doi:10.4000/books.pur.115293"}],
          "certainty": "less-certain"
        },""" % (state.loc[0, 'province'], state.loc[0, 'province'])
    return relations[:-1]

def getAmirauteRelation(toponym) : 
    """
    Internal method : gives the province belongings of the amiraute during the period [1781-1785]
    Certain for the period because of Chardon text.
    """ 
    relations = getProvinceRelation()
    
    # Sa province : 
    if toponym in names.keys() :
        query = """ select province from ports.port_points where amiraute = '%s' or amiraute = '%s' """ % (toponym, names[toponym])
    else: 
        query = """ select province from ports.port_points where amiraute = '%s' """ % (toponym)

    state = retrieveDataFromPostgres(query)
    if state.loc[0, 'province'] is not None:
        relations = relations + """
        , { "relationType": "gvp:broaderPartitive",
            "relationTo": "http://gaz.portic.fr/places/?%s",
            "label": "Province %s",
            "when":{"timespans":[
                {"start":{"in":"1781"}, "end":{"in":"1785"}}]},
            "citations": [
                {"label": "Archives Nationales, C4 174 à 176. Procès-verbaux d'inspections des ports et amirautés de France par le commissaire Chardon. 1781-1785",
                "@id": "doi:10.4000/books.pur.115293"}],
            "certainty": "certain"
        }""" % (state.loc[0, 'province'], state.loc[0, 'province'])
    return relations

def getProvinceRelation() : 
    """
    Internal method : gives the province belongings of the amiraute during the period [843-1791]
    It is uncertain for the belonging period because we do not know using our sources when the province started to exist, neither when it finished.
    """ 
    ## Le Royaume de France
    relations = """
        { "relationType": "gvp:broaderPartitive",
          "relationTo": "http://symogih.org/?q=named-place-record/17",
          "label": "Royaume de France",
          "when":{"timespans":[
            {"start":{"earliest":"843"}, "end":{"in":"1791"}}]},
          "citations": [
            {"label": "Archives Nationales, C4 174 à 176. Procès-verbaux d'inspections des ports et amirautés de France par le commissaire Chardon. 1781-1785",
             "@id": "doi:10.4000/books.pur.115293"}],
          "certainty": "uncertain"
        },
    """
    #France actuelle
    relations = relations + """ 
        {"relationType" : "gvp:broaderPartitive", 
        "relationTo" : "http://www.geonames.org/3017382", 
        "label" : "France", 
        "when" : {"timespans" : [{"start" : {"earliest" : "1792"}, "end" : {"latest" : "2020"}}]}
        } 
        """
    return relations
    

if __name__ == '__main__':
    app.run(debug=True,port=port,threaded=True)  
669
670
671
    #describePlace('port', 'A0198999')

    #saveGazetteer()
672