8.8 Spain Data

8.8.1 – Downloading IDEE data

In the Geoportal IDEE (Government of Spain, Ministry of Development, Geographic High Council, Spatial Data Infrastructure of Spain) site (featured image), clicking on the option Downloads Websites, the Centros de Descarga page opens and, in this page, clicking in Centro de Descargas -CNIG, the page Centro de Descargas – Centro Nacional de Informação Geográfica opens. On this page, by clicking on Reference geographic information, several download options are present.

Two files were downloaded: Spanish basic gazetteer, which is a NGBE’s list of toponyms, and Gazetteer of municipalities and population entities, with information of municipalities and population entities (names, coordinates, population, etc.).

The Spanish basic gazetteer has the ETRS89 for the Iberian Peninsula, Balearic Islands, Ceuta and Melilla, and REGCAN95 for the Canary Islands. It is a compressed file containing the MS Access database NGBE.accdb with 670 MB uncompressed size and 1,132,560 records in a single table.

The Gazetteer of municipalities and population entities has the information of municipalities and population entities (names, coordinates, population, etc.) with longitude and latitude coordinates in WGS-84 (compatible with ETRS89 and REGCAN95) for the entire Spain. Inside the compressed archive (BD_Municipios-Entidades.zip) there are two files, one in the .odb format and the other in the Microsoft Access .mdb format, with five tables.

The bullzip MS Access to MySQL program was used convert Microsoft Access Databases to MySQL. Below are the databases and tables created in MySQL.

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| BD_Municipios-Entidades | <-- Gazetteer of municipalities and population entities
| NGBE                    | <-- Spanish basic gazetteer
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
6 rows in set (0.00 sec)

The Spanish basic gazetteer `NGBE` database tables

mysql> SHOW TABLES FROM `NGBE`;
+----------------+
| Tables_in_NGBE |
+----------------+
| NGBE           |
+----------------+
1 row in set (0.01 sec)

NGBE table columns:

mysql> SELECT ORDINAL_POSITION, COLUMN_NAME
    -> FROM information_schema.columns
    -> WHERE table_schema = 'NGBE'
    -> ORDER BY ORDINAL_POSITION;
+------------------+--------------------------+
| ORDINAL_POSITION | COLUMN_NAME              |
+------------------+--------------------------+
|                1 | id                       |
|                2 | codigo_ngbe              |
|                3 | codigo_ngbe_text         |
|                4 | nombre_extendido         |
|                5 | identificador_geografico |
|                6 | nombre_alternativo_2     |
|                7 | nombre_alternativo_3     |
|                8 | nombre_variante_1        |
|                9 | nombre_variante_2        |
|               10 | nombre_variante_3        |
|               11 | fuente_extendido         |
|               12 | fuente_idg               |
|               13 | fuente_alternativo_2     |
|               14 | fuente_alternativo_3     |
|               15 | fuente_variante_1        |
|               16 | fuente_variante_2        |
|               17 | fuente_variante_3        |
|               18 | idioma_idg               |
|               19 | estatus_idg              |
|               20 | long_etrs89_regcan95     |
|               21 | lat_etrs89_regcan95      |
|               22 | huso_etrs89_regcan95     |
|               23 | x_utm_etrs89_regcan95    |
|               24 | y_utm_etrs89_regcan95    |
|               25 | hojamtn_25               |
|               26 | codigo_ine               |
+------------------+--------------------------+
26 rows in set (0.01 sec)

Gazetteer of municipalities and population entities `BD_Municipios-Entidades` database tables

mysql> SHOW TABLES FROM `BD_Municipios-Entidades`;
+-----------------------------------+
| Tables_in_BD_Municipios-Entidades |
+-----------------------------------+
| CONDOMINIOS                       |
| EATIMS                            |
| ENTIDADES                         |
| MUNICIPIOS                        |
| PROVINCIAS                        |
+-----------------------------------+
5 rows in set (0.00 sec)

To select the Spanish headlights, the `vw_faros_espana` view was created, as shown below:

-- Faros de España
-- Spanish headlights
DROP VIEW IF EXISTS `vw_faros_espana`;
CREATE VIEW `vw_faros_espana` AS
SELECT `identificador_geografico`,
       `id`,
       `lat_etrs89_regcan95`,
       `long_etrs89_regcan95`
FROM `NGBE`
WHERE `codigo_ngbe_text` = 'Poblaciones y construcciones.Edificación'
AND `identificador_geografico` LIKE 'Faro %'
AND NOT `lat_etrs89_regcan95` IS NULL;

Below are the first five lines of the view:

mysql> SELECT * FROM `NGBE`.`vw_faros_espana` LIMIT 5;
+--------------------------+---------+---------------------+----------------------+
| identificador_geografico | id      | lat_etrs89_regcan95 | long_etrs89_regcan95 |
+--------------------------+---------+---------------------+----------------------+
| Faro de Santa Pola       | 2301141 |    38.2094727090001 |   -0.513488796999923 |
| Faro de Tabarca          | 2301143 |    38.1641237900001 |   -0.471191441999906 |
| Faro de San Antonio      | 2255938 |    38.8031012550001 |    0.197085783000091 |
| Faro de la Nao           | 2256029 |        38.732117632 |    0.231082644000082 |
| Faro de Orpesa           | 2126326 |        40.083129768 |    0.147094542000048 |
+--------------------------+---------+---------------------+----------------------+
5 rows in set (0.09 sec)

The view `vw_faros_espana` lines were exported to faros_espana.csv file using the command:

mysql --user=sistemas                           \
      --password                                \
      --execute "SELECT * FROM vw_faros_espana" \
      --batch                                   \
      NGBE > faros_espana.csv

8.8.2 – Conversion of IDEE data to GeoJSON format

The file faros_espana.csv was converted to the GeoJSON format using the OGR utility program ogr2ogr from GDAL, which converts simple features data between file formats, as shown bellow:

ogr2ogr -f GeoJSON faros_espana.geojson faros_espana.csv \
        -oo X_POSSIBLE_NAMES=long_etrs89_regcan95        \
        -oo Y_POSSIBLE_NAMES=lat_etrs89_regcan95         \
        -oo KEEP_GEOM_COLUMNS=NO

8.8.3 – Creating a collection in MongoDB to import IDEE data

To import the data from faros_espana.geojson file, the faros_espana collection was created using the commands:

// Create the faros_espana collection specifying the JSON schema validation rules
use reficio;
// Drop the collection, if exists
db.faros_espana.drop();
// Create the collection
db.createCollection("faros_espana", {
   validator : {
      $jsonSchema : {
         "properties" : {
            "type" : {
               bsonType : "string",
               description : "FeatureCollection",

            },
            "name" : {
               bsonType : "string",
               description : "faros_espana",

            },
            "features" : {
               bsonType : "array",
               "items" : {
                  bsonType : "object",
                  "properties" : {
                     "type" : {
                        bsonType : "string",
                        description : "Feature",

                     },
                     "properties" : {
                        bsonType : "object",
                        "properties" : {
                           "identificador_geografico" : {
                              bsonType : "string",
                              description : "geographical identifier",

                           },
                           "id" : {
                              bsonType : "string",
                              description : "name of geographical point in plain ascii characters, varchar(200)",

                           },
                        }
                     },
                     "geometry" : {
                        bsonType : "object",
                        "properties" : {
                           "type" : {
                              bsonType : "string",
                              description : "GeoJSON object type Point",

                           },
                           "coordinates" : {
                              bsonType : "array",
                              "items" : {
                                 bsonType : "number",
                                 description : "Point Longitude, Latitude",

                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   }
})

8.8.4 – Importing data

Importing the IDEE data into faros_espana collection of MongoDB was done using the mongoimport utility, as shown below:

mongoimport \
    --stopOnError \
    --db reficio \
    --collection faros_espana \
    --file faros_espana.geojson

producing the output:

2018-05-30T06:46:35.005-0300    connected to: localhost
2018-05-30T06:46:35.010-0300    imported 1 document

8.8.5 – A document for each point

As can be seen above, all points were imported into a single document. To place each point in a separate document, was created the idee_faros_espana collection as shown below:

$ mongo
use reficio;
db.createCollection("idee_faros_espana", {}, { collation: { locale: "es", strength: 1 } } );

and executed the command:

db.faros_espana.find().forEach(function(data) {
    db.idee_faros_espana.insert( data.features );
});

8.8.6 Seeing the headlights on the map

To see the headlights on the map was created the idee_faros_espana.py script in Python that generates the gpx (GPS eXchange Format) file idee_faros_espana.gpx. The map can be seen below in OpenStreetMap.

8. External data