search

clarity-h2020 / docker-drupal

Drupal 8 Project for implememting the CLARTIY CSIS Platform.
https://csis.myclimateservice.eu
GNU Lesser General Public License v3.0
3 stars 0 forks source link

Create Citities Taxonomy and Import UA LE SHP #68

Closed p-a-s-c-a-l closed 5 years ago

p-a-s-c-a-l commented 5 years ago

See https://github.com/clarity-h2020/local-effects/issues/4 and https://github.com/clarity-h2020/map-component/issues/24

p-a-s-c-a-l commented 5 years ago

Status @therter ?

therter commented 5 years ago

The taxonomy was created by @patrickkaleta and the cities were imported. See also clarity-h2020/map-component#24

p-a-s-c-a-l commented 5 years ago

@therter In reference to https://github.com/clarity-h2020/data-package/issues/39#issuecomment-520923326 and https://github.com/clarity-h2020/local-effects/issues/5#issuecomment-520785440:

The spatial extent of the Cities Taxonomy in CSIS is in EPSG:3035 and reprojected inside of Map Component to EPSG:4326, right?

Is it possible to (locally) reproject to EPSG:4326 and import it again and then disable the re-projection in the map component. Pleas enote, we do not want to change the original EPSG:4326 projection on Geoserver, just temporary cnvert the downlaoded (Shape?) file to EPSG:4326 and then import into CSIS.

DenoBeno commented 5 years ago

I presume this was already done.

Would it be possible to have the data in both projections?

Two geospatial bb fields, one with data in EPSG:4326, the other with data in EPSG:3035.

One could then be used for drupal-internal data presentation and the other by the clarity map widget.

p-a-s-c-a-l commented 5 years ago

No, we don't need that. One field is enough.

therter commented 5 years ago

I was a bit confused about the current state of the used CRS. Therefore, here is the summary of the current state: The dp_spatial_extents are using EPSG:4326, because this is also the crs that is used by the geofield module. The problem that started the crs discussion was that the geometries of the city taxonomy cannot be shown on the drupal map because of the crs. But I have seen, that this was a problem with the city import. For the import I used also EPSG:4326 but with a wrong axis order. I can correct this and import the cities with the correct axis order and then the bounding boxes can be shown in the drupal system (and in the map-component).

The data of the WMS is currently in EPSG:3035 and should remain in EPSG:3035.

p-a-s-c-a-l commented 5 years ago

Ok, thanks.

DenoBeno commented 5 years ago

Haha, I should have noticed this - EPSG:3035 values are in 10000-s, whereas EPSG:4326 is in 10-s.

p-a-s-c-a-l commented 5 years ago

@therter

1) if there is an import script (SQL?) then it should be made available, e.g. in this issue so that we can reproduce the import.

2) we want to create links to urban adaptation city profiles (See https://github.com/clarity-h2020/csis/issues/60). So we have to map our city names to EEA city names. Therefore an additional field, e.g. eea_city_profile_name has to be added to the taxonomy and populated with one of the following values: (Note: This is required only, if our Urban Atlas city names are different from the citiy names used by EEA!)

's-Gravenhage
's-Hertogenbosch
A Coruña
Aachen
Aalborg
Aberdeen City
Acireale
Aix-en-Provence
Ajaccio
Alba Iulia
Albacete
Albi
Alcalá de Henares
Alcobendas
Alcorcón
Algeciras
Alicante/Alacant
Alkmaar
Almada
Almelo
Almere
Almería
Alphen aan den Rijn
Alytus
Amadora
Amersfoort
Amiens
Amstelveen
Amsterdam
Ancona
Angers
Angoulême
Annecy
Annemasse
Antwerpen
Apeldoorn
Arad
Argenteuil - Bezons
Århus
Arnhem
Arras
Arrecife
Aschaffenburg
Ashford
Asti
Athina
Aubagne
Augsburg
Aveiro
Avellino
Avignon
Avilés
Bacău
Badajoz
Badalona
Baia Mare
Bamberg
Banská Bystrica
Barakaldo
Barcelona
Bari
Barking and Dagenham
Bârlad
Barletta
Barnet
Barnsley
Barreiro
Basel
Basildon
Basingstoke and Deane
Bath and North East Somerset
Bayonne
Bayreuth
Beauvais
Bedford
Belfast
Belfort
Benevento
Benidorm
Bergamo
Bergen
Bergen op Zoom
Bergisch Gladbach
Berlin
Bern
Besançon
Bexley
Béziers
Białystok
Biel/Bienne
Bielefeld
Biella
Bielsko-Biała
Bilbao
Birmingham
Bistriţa
Blackburn with Darwen
Blackpool
Blagoevgrad
Bochum
Bologna
Bolton
Bolzano
Bonn
Borås
Bordeaux
Botoşani
Bottrop
Boulogne-sur-Mer
Bourges
Bournemouth
Bracknell Forest
Bradford
Braga
Brăila
Brandenburg an der Havel
Braşov
Bratislava
Braunschweig
Breda
Bremen
Bremerhaven
Brent
Brescia
Brest
Brighton and Hove
Bristol
Brive-la-Gaillarde
Brno
Bromley
Brugge
Bruxelles / Brussel
Bucureşti
Budapest
Burgas
Burgos
Burnley
Bury
Busto Arsizio
Buzău
Bydgoszcz
Bytom
CA Brie Francilienne
CA de la Vallée de Montmorency
CA de Seine Essonne
CA de Sophia-Antipolis
CA des deux Rives de la Seine
CA des Lacs de l'Essonne
CA du Plateau de Saclay
CA du Val d'Orge
CA du Val d'Yerres
CA Europ' Essonne
CA le Parisis
CA les Portes de l'Essonne
CA Marne et Chantereine
CA Sénart - Val de Seine
CA Val de France
CA Val et Forêt
Cáceres
Cádiz
Caen
Cagliari
Calais
Cǎlǎraşi
Cambridge
Camden
Campobasso
Cannock Chase
Capelle aan den IJssel
Cardiff
Carlisle
Carrara
Cartagena
Caserta
Castelldefels
Castellón de la Plana/Castelló de la Plana
Catania
Catanzaro
CC de l'Ouest de la Plaine de France
CC de la Boucle de la Seine
CC des Coteaux de la Seine
Celle
Cerdanyola del Vallès
Cergy-Pontoise
České Budějovice
Ceuta
Chalon-sur-Saône
Châlons-en-Champagne
Chambéry
Charleroi
Charleville-Mézières
Chartres
Châteauroux
Chełm
Chelmsford
Cheltenham
Chemnitz
Cherbourg
Cheshire West and Chester
Chesterfield
Chomutov-Jirkov
Chorzów
City of Edinburgh
City of London
Ciudad Real
Cluj-Napoca
Coimbra
Colchester
Colmar
Como
Compiègne
Constanţa
Córdoba
Cork
Cornellà de Llobregat
Cosenza
Coslada
Cottbus
Coventry
Craiova
Crawley
Creil
Cremona
Croydon
Częstochowa
Dacorum
Darlington
Darmstadt
Daugavpils
Debrecen
Delft
Derby
Derry
Dessau-Roßlau
Deventer
Dijon
Dobrich
Doncaster
Dordrecht
Dortmund
Dos Hermanas
Douai
Dresden
Drobeta-Turnu Severin
Dublin
Dudley
Duisburg
Dundee City
Dunkerque
Düsseldorf
Ealing
East Staffordshire
Eastbourne
Ede
Eindhoven
Elbląg
Elche/Elx
Elda
Ełk
Enfield
Enschede
Erfurt
Erlangen
Espoo / Esbo
Essen
Esslingen am Neckar
Evreux
Evry
Exeter
Falkirk
Fareham
Faro
Ferrara
Ferrol
Firenze
Flensburg
Focşani
Foggia
Forlì
Frankenthal (Pfalz)
Frankfurt (Oder)
Frankfurt am Main
Freiburg im Breisgau
Fréjus
Friedrichshafen
Fuengirola
Fuenlabrada
Fulda
Fürth
Galaţi
Galway
Gandia
Gateshead
Gdańsk
Gdynia
Gelsenkirchen
Genève
Genova
Gent
Gera
Getafe
Getxo
Gießen
Gijón
Girona
Giugliano in Campania
Giurgiu
Glasgow City
Gliwice
Głogów
Gloucester
Gniezno
Gondomar
Görlitz
Gorzów Wielkopolski
Göteborg
Göttingen
Gouda
Granada
Granollers
Gravesham
Graz
Great Yarmouth
Greenwich
Greifswald
Grenoble
Groningen
Grudziądz
Guadalajara
Guildford
Guimarães
Gyõr
Haarlem
Haarlemmermeer
Hackney
Hagen
Halle an der Saale
Halton
Hamburg
Hamm
Hammersmith and Fulham
Hanau
Hannover
Haringey
Harlow
Harrow
Hartlepool
Haskovo
Hastings
Havering
Havířov
Heerlen
Heidelberg
Heilbronn
Helmond
Helsingborg
Helsinki / Helsingfors
Hengelo
Hénin - Carvin
Herne
Hildesheim
Hillingdon
Hilversum
Hoorn
Hounslow
Hradec Králové
Huelva
Hyndburn
Iaşi
Ingolstadt
Innsbruck
Inowrocław
Ioannina
Ipswich
Irakleio
Irun
Iserlohn
Islington
Jaén
Jastrzębie-Zdrój
Jelenia Góra
Jelgava
Jena
Jerez de la Frontera
Jihlava
Jönköping
Jyväskylä
Kaiserslautern
Kalamata
Kalisz
Karlovy Vary
Karlsruhe
Karviná
Kassel
Katowice
Katwijk
Kaunas
Kavala
Kecskemét
Kempten (Allgäu)
Kensington and Chelsea
Kiel
Kielce
Kingston upon Thames
Kingston-upon-Hull
Kirklees
Kladno
Klagenfurt
Klaipėda
København
Koblenz
Köln
Konin
Konstanz
Kortrijk
Košice
Koszalin
Kraków
Krefeld
Kristiansand
Kuopio
L'Hospitalet de Llobregat
La Rochelle
La Spezia
Lahti / Lahtis
Lambeth
Landshut
Larisa
Latina
Lausanne
Le Havre
Le Mans
Lecce
Lecco
Leeds
Leeuwarden
Lefkosia
Leganés
Legnica
Leicester
Leiden
Leidschendam--Voorburg
Leipzig
Lelystad
Lemesos
Lens - Liévin
León
Leszno
Leuven
Leverkusen
Lewisham
Liberec
Liège
Liepāja
Lille
Limerick
Limoges
Lincoln
Línea de la Concepción, La
Linköping
Linz
Lisboa
Lisburn
Liverpool
Livorno
Ljubljana
Lleida
Łódź
Logroño
Łomża
Lorient
Lübeck
Lubin
Lublin
Ludwigsburg
Ludwigshafen am Rhein
Lugano
Lugo
Lund
Lüneburg
Luton
Luxembourg
Luzern
Lyon
Maastricht
Madrid
Magdeburg
Maidstone
Mainz
Majadahonda
Málaga
Malmö
Manchester
Mannheim
Manresa
Mansfield
Mantes en Yvelines
Marbella
Marburg
Maribor
Marne la Vallée
Marseille
Martigues
Massa
Mataró
Matera
Matosinhos
Meaux
Medway
Melun
Merton
Messina
Metz
Middelburg
Middlesbrough
Milano
Milton Keynes
Miskolc
Modena
Moers
Mollet del Vallès
Mönchengladbach
Mons
Montbéliard
Montpellier
Monza
Most
Móstoles
Mülheim a.d.Ruhr
Mulhouse
München
Münster
Murcia
Namur
Nancy
Nantes
Napoli
Narva
Neu-Ulm
Neubrandenburg
Neumünster
Neuss
Newcastle upon Tyne
Newcastle-under-Lyme
Newham
Newport
Nice
Nijmegen
Nîmes
Niort
Nitra
Norrköping
North East Lincolnshire
North Lanarkshire (Airdrie/Bellshill/Coatbridge/Motherwell)
North Tyneside
Northampton
Norwich
Nottingham
Novara
Nowy Sącz
Nuneaton and Bedworth
Nürnberg
Nyíregyháza
Oberhausen
Odense
Odivelas
Offenbach am Main
Offenburg
Oldenburg (Oldenburg)
Oldham
Olomouc
Olsztyn
Oostende
Opole
Oradea
Örebro
Orléans
Osijek
Oslo
Osnabrück
Ostrava
Ostrów Wielkopolski
Ostrowiec Świętokrzyski
Oulu / Uleåborg
Ourense
Oviedo
Oxford
Pabianice
Paderborn
Padova
Palencia
Palermo
Palma de Mallorca
Pamplona/Iruña
Panevėžys
Pardubice
Paredes
Paris
Parla
Parma
Passau
Pátra
Pau
Pavia
Pazardzhik
Pécs
Pernik
Perpignan
Perugia
Pesaro
Pescara
Peterborough
Pforzheim
Piacenza
Piatra Neamţ
Piła
Piotrków Trybunalski
Pisa
Piteşti
Plauen
Pleven
Płock
Ploieşti
Plovdiv
Plymouth
Plzeň
Poitiers
Ponferrada
Pontevedra
Poole
Pordenone
Porto
Portsmouth
Potenza
Potsdam
Póvoa de Varzim
Poznań
Pozuelo de Alarcón
Praha
Prat de Llobregat, El
Prato
Prešov
Preston
Przemyśl
Puerto de Santa María, El
Purmerend
Quimper
Radom
Râmnicu Vâlcea
Ravenna
Reading
Recklinghausen
Redbridge
Redditch
Regensburg
Reggio di Calabria
Reggio nell'Emilia
Reims
Remscheid
Rennes
Reus
Reutlingen
Reykjavík
Richmond upon Thames
Rīga
Rijeka
Rimini
Roanne
Rochdale
Roma
Roman
Roosendaal
Rosenheim
Rostock
Rotherham
Rotterdam
Rouen
Rozas de Madrid, Las
Rubí
Ruda Śląska
Ruse
Rybnik
Rzeszów
Saarbrücken
Sabadell
Saint-Brieuc
Saint-Etienne
Saint-Nazaire
Saint-Quentin
Saint-Quentin en Yvelines
Salamanca
Salerno
Salford
Salzburg
Salzgitter
San Fernando
San Sebastián de los Reyes
San Sebastián/Donostia
Sandwell
Sankt Augustin
Sanlúcar de Barrameda
Sanremo
Sant Boi de Llobregat
Sant Cugat del Vallès
Santa Coloma de Gramenet
Santander
Santiago de Compostela
Sassari
Satu Mare
Savona
Schiedam
Schweinfurt
Schwerin
Sefton
Seixal
Sénart en Essonne
Setúbal
Sevilla
Sheffield
Shumen
Šiauliai
Sibiu
Siedlce
Siegen
Sindelfingen
Sintra
Siracusa
Sittard-Geleen
Slatina
Slavonski Brod
Sliven
Slough
Słupsk
Sofia
Solihull
Solingen
Sosnowiec
South Tyneside
Southampton
Southend-on-Sea
Southwark
Speyer
Spijkenisse
Split
St Albans
St. Gallen
St. Helens
Stalowa Wola
Stara Zagora
Stargard Szczeciński
Stavanger
Stevenage
Stockholm
Stockport
Stockton-on-Tees
Stoke-on-trent
Stralsund
Strasbourg
Stuttgart
Suceava
Sunderland
Sutton
Suwałki
Swansea
Świdnica
Swindon
Szczecin
Szeged
Székesfehérvár
Szombathely
Talavera de la Reina
Tallinn
Tameside
Tampere / Tammerfors
Tamworth
Taranto
Tarbes
Târgovişte
Târgu Jiu
Târgu Mureş
Tarnów
Tarragona
Tartu
Tczew
Telford and Wrekin
Terni
Terrassa
Thanet
Thessaloniki
Thurrock
Tilburg
Timişoara
Toledo
Tomaszów Mazowiecki
Torino
Torrejón de Ardoz
Torremolinos
Torrevieja
Toruń
Toulon
Toulouse
Tours
Tower Hamlets
Trafford
Trenčín
Trento
Treviso
Trier
Trieste
Trnava
Tromsø
Trondheim
Troyes
Tübingen
Tulcea
Tunbridge Wells
Turku / Åbo
Tychy
Udine
Ulm
Umeå
Uppsala
Ústí nad Labem
Utrecht
Valence
Valencia
Valenciennes
Valladolid
Valletta
Valongo
Vannes
Vantaa / Vanda
Varese
Varna
Västerås
Veliko Tarnovo
Velsen
Venezia
Venlo
Verona
Versailles
Viana do Castelo
Viareggio
Vicenza
Vidin
Vigevano
Vigo
Vila Franca de Xira
Vila Nova de Gaia
Viladecans
Vilanova i la Geltrú
Villingen-Schwenningen
Vilnius
Viseu
Vitoria/Gasteiz
Vlaardingen
Volos
Vratsa
Wakefield
Wałbrzych
Walsall
Waltham Forest
Wandsworth
Warrington
Warszawa
Warwick
Waterford
Waveney
Weimar
Westminster
Wetzlar
Wien
Wiesbaden
Wigan
Wilhelmshaven
Winterthur
Wirral
Witten
Włocławek
Woking
Wolfsburg
Wolverhampton
Worcester
Worthing
Wrexham
Wrocław
Wuppertal
Würzburg
Wycombe
Yambol
York
Zaanstad
Zabrze
Zagreb
Zamora
Zamość
Zaragoza
Zgierz
Zielona Góra
Žilina
Zlín
Zoetermeer
Żory
Zürich
Zwickau
Zwolle

3) The the EEA city name has to be made available in $entityinfo, so that we can construct an URL like https://tableau.discomap.eea.europa.eu/t/Aironline/views/2019_Urban_vulnerability_links/mainpage?iframeSizedToWindow=false&%3Aembed=y&%3AshowAppBanner=false&%3Adisplay_count=yes&%3AshowVizHome=yes&%3Atoolbar=yes&City_param=Napoli

therter commented 5 years ago

The Urban Atlas city names are different from the citiy names used by EEA. I will add the eea_city_profile_name field

therter commented 5 years ago

In Order to import the cities, the following steps are required:

The code of the import_cities() function

CREATE OR REPLACE FUNCTION public.import_cities(
    )
    RETURNS boolean
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

declare
        ref RECORD;
    tidv integer;
    revId integer;
    eea_name character varying;
    hitCount integer;
    suffix text;
    entIds bigint[];
    targetNames text[];
begin
    --save the assigned cities
    select array_agg(r.entity_id), array_agg(d.name) into entIds, targetNames from group__field_city_region r join taxonomy_term_field_data d on (r.field_city_region_target_id = d.tid);

    --delete the cities from the database
    delete from taxonomy_term_field_data where tid in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term__field_country where entity_id in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term__field_boundaries where entity_id in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term__parent where entity_id in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term_revision where tid in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term_revision__parent where entity_id in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term_revision where tid in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term__field_eea_city_profile_name where entity_id in (select tid from taxonomy_term_data where vid = 'cities_regions');
    delete from taxonomy_term_data where vid = 'cities_regions';
    --import the cities to the database. The Data is contained in the table raw.city_bbox
        for ref in select name, st_asText((st_transform(st_setsrid(ST_FlipCoordinates(the_geom), 3035), 4326))) as the_geom, st_transform(st_setsrid(st_envelope(ST_FlipCoordinates(the_geom)), 3035), 4326) as env, code, case when heat_wave then 1 else 0 end as heat_wave , case when pluvial_fl then 1 else 0 end as pluvial_fl from raw.city_bbox join taxonomy_term__field_country_code on field_country_code_value = substring(replace(replace(code, 'UK', 'GB'), 'EL', 'GR') from 0 for 3) LOOP
        insert into taxonomy_term_data (vid, uuid, langcode)
               values ('cities_regions', uuid_generate_v4(), 'en');

        select currval('taxonomy_term_data_tid_seq') into tidv;
        insert into taxonomy_term_revision (tid, langcode, revision_default) values (tidv, 'en', 1);
        select currval('taxonomy_term_revision_revision_id_seq') into revId;
        update taxonomy_term_data set revision_id = revId where tid = tidv;

        insert into taxonomy_term_field_revision (tid, revision_id, langcode, name, changed, default_langcode, status, revision_translation_affected) 
        values (tidv, revId,'en', ref.name,EXTRACT(EPOCH FROM now())::int, 1, 1, 1);
        insert into taxonomy_term_revision__parent (bundle, deleted, entity_id, revision_id, langcode,delta,parent_target_id) 
        values('cities_regions', 0, tidv, revId, 'en', 0, 0);

        insert into taxonomy_term_field_data (tid, revision_id, vid, langcode, name, description__value, description__format, weight, changed, default_langcode, status, revision_translation_affected)
               values (tidv, revId, 'cities_regions', 'en', ref.name, null, null, 0, EXTRACT(EPOCH FROM now())::int, 1, 1, 1);
--      raise notice '% %', substring(ref.code from 0 for 3), (select entity_id from taxonomy_term__field_country_code where field_country_code_value = substring(ref.code from 0 for 3));

        insert into taxonomy_term__field_country (bundle, deleted, entity_id, revision_id, langcode, delta, field_country_target_id)
               values ('cities_regions', 0, tidv, revId, 'en', 0, (select entity_id from taxonomy_term__field_country_code where field_country_code_value = substring(replace(replace(ref.code, 'UK', 'GB'), 'EL', 'GR') from 0 for 3)));

        insert into taxonomy_term__field_boundaries (bundle, deleted, entity_id, revision_id, langcode, delta, field_boundaries_value, field_boundaries_geo_type, field_boundaries_lat, field_boundaries_lon, field_boundaries_left, field_boundaries_top, field_boundaries_right, field_boundaries_bottom, field_boundaries_geohash)
            values ('cities_regions', 0, tidv, revId, 'en', 0, ST_AsTExt(ref.the_geom)::bytea, 'MultiPolygon', st_ymin(ref.env) + (st_ymax(ref.env) - st_ymin(ref.env)) / 2, st_xmin(ref.env) + (st_xmax(ref.env) - st_xmin(ref.env)) / 2, st_xmin(ref.env), st_ymax(ref.env), st_xmax(ref.env), st_ymin(ref.env) , geohash(st_ymin(ref.env) + (st_ymax(ref.env) - st_ymin(ref.env)) / 2, st_xmin(ref.env) + (st_xmax(ref.env) - st_xmin(ref.env)) / 2, 1));

        insert into taxonomy_term__parent (bundle, entity_id, revision_id, langcode, parent_target_id, delta) values ('cities_regions', tidv, revId, 'en', 0, 0);
        --set the eea_name
        select name into eea_name from raw.eea_city_name where name ilike ref.name or name ilike 'city of ' || ref.name;

        if eea_name is null then
            if position('_' in ref.name) <> 0 then
                suffix = '%';
            else
                suffix = '';
            end if;
            select count(*) into hitCount from raw.eea_city_name where name ilike regexp_replace(replace(replace(ref.name, '_', '%'), 'SS', '_'), '[aous]', '_', 'ig') || suffix;

            if (hitCount is not null and hitCount = 1) then
                select name into eea_name from raw.eea_city_name where name ilike regexp_replace(replace(replace(ref.name, '_', '%'), 'SS', '_'), '[aous]', '_', 'ig') || suffix;
                raise notice 'check city: % found: %', ref.name, eea_name;
            end if;
        end if;

        if eea_name is null then
            select count(*) into hitCount from raw.eea_city_name where name ilike regexp_replace(replace(ref.name, '_', '%'), '[ns]', '_', 'ig') || suffix;

            if (hitCount is not null and hitCount = 1) then
                select name into eea_name from raw.eea_city_name where name ilike regexp_replace(replace(ref.name, '_', '%'), '[ns]', '_', 'ig') || suffix;
                raise notice 'check city: % found: %', ref.name, eea_name;
            end if;
        end if;

        if eea_name is null then
            raise notice 'no name found for city %', ref.name;
        else
            insert into taxonomy_term__field_eea_city_profile_name (bundle, deleted, entity_id, revision_id, langcode, delta, field_eea_city_profile_name_value)
                   values ('cities_regions', 0, tidv, revId, 'en', 0, eea_name);
        end if;
    END LOOP;

    --restore the assigned cities
        FOR i IN array_lower(entIds, 1) .. array_upper(entIds, 1)
        LOOP
        RAISE NOTICE 'update group__field_city_region set field_city_region_target_id = (select tid from taxonomy_term_field_data where name = %) where entity_id = %;',targetNames[i], entIds[i];
        update group__field_city_region set field_city_region_target_id = (select tid from taxonomy_term_field_data where name = targetNames[i]) where entity_id = entIds[i];
        END LOOP;

    return true;
end

$BODY$;
patrickkaleta commented 5 years ago
  1. The the EEA city name has to be made available in $entityinfo, so that we can construct an URL like https://tableau.discomap.eea.europa.eu/t/Aironline/views/2019_Urban_vulnerability_links/mainpage?iframeSizedToWindow=false&%3Aembed=y&%3AshowAppBanner=false&%3Adisplay_count=yes&%3AshowVizHome=yes&%3Atoolbar=yes&City_param=Napoli

EEA city name now available in the $entityinfo as eea_city_name.

p-a-s-c-a-l commented 5 years ago

This is still not done yet. We are waiting for an updated list of cities. See also https://github.com/clarity-h2020/emikat/issues/25

p-a-s-c-a-l commented 5 years ago

We'll create a new issue when changes to the cities taxonomy have to be made.