SIOSE

Exercise 1: JOINS

Exercise A

Completa la capa del SIOSE de la provincia de Castellón con la descripción del tipo de cobertura, para ello debemos relacionar la capa SIOSE_CASTELLON.shp con su leyenda. La leyenda se encuentra desglosada en 2 tablas T_VALORES y TC_SIOSE_COBERTURAS. Estudia las tablas, para establecer las relaciones necesarias entre ellas y así lograr incorporar la información de cobertura a la capa SIOSE. Nota: La capa SIOSE_CASTELLON.shp ha sido tomada en el S.C. ETRS89 UTM 30N. Finalmente representa la capa a través del campo cobertura con un tipo de simbología adecuado.

Capas a utilizar:

• SIOSE_CASTELLON.shp
• T_VALORES
• T_COBERTURAS

PostGIS

CREATE TABLE siose AS 
SELECT
    s.geom,
    v."ID_COBERTURAS" AS cobertura_id,
    c."DESCRIPCION_COBERTURAS" AS description
FROM
    siose_shape AS s
JOIN siose_valores AS v ON  v."ID_POLYGON" = s."ID_POLYGON" 
JOIN siose_coberturas AS c ON c."ID_COBERTURAS" = v."ID_COBERTURAS" 

QGIS

Exercise B

We use the tool “join” to link the Corine Land Over (CLC_ambito.shp) layer to its legend (excel spreadsheet). Name it “CLC_LEGEND.shp” to the resulting joined layer.

cat("Utilizamos la herramienta join para relacionar la capa del Corine Land Cover (CLC_ambito.shp) con su leyenda (hoja Excel CLC2000legend.xls). Llama 'CLC_LEYEDA.shp' a la capa resultante de la unión 

Capas a utilizar:
- CLC_ambito.shp
- CLC2000legend.xls")

PostGIS

Import the data is possible using ogr2ogr or shp2pgsql. The following ogr2ogr command import the data, since it combines polygon and multipolygon, it is need to add the parameter -nln (post)

ogr2ogr -f PostgreSQL PG:"host=localhost port=25432 user=docker password=docker dbname=gis schemas=tycgis" CLC_ambito.shp -nln clc_ambito -lco GEOMETRY_NAME=geom -nlt PROMOTE_TO_MULTI

--- Explore relevant data, columns used for join & srid of the geometry
SELECT pg_typeof(code_06) FROM clc_ambito; ---character
SELECT pg_typeof("CLC_CODE") FROM clc2000legend; ---integer
SELECT st_srid(geom) FROM  clc_ambito ;  --- check srid is imported correctly automatically
---- Create table CLC_LEYENDA
CREATE TABLE CLC_LEYENDA AS
SELECT 
    *,
    replace(xls,"RGB", '-',',')AS RGB_csv 
FROM 
    clc_ambito AS shp
LEFT JOIN  
    clc2000legend AS xls
ON shp.code_06::int = xls."CLC_CODE"; --- the ::int cast the character into integer.

Note: - The column “RGB” stored the colour values and it was used replacing the “-” for “,” as this post indicated. If the RGB values were in three different columns, then this other post would have applied.

QGIS

After clicking on properties of the selected vector data “CLC_ambito_qgis” and join, the following screenshot shows the parameters required to add columns from the spreadsheet “CLC2000legend.xls”.

IMFE Use case

• Municipality layer with geometries
• Additional data for Málaga in csv

Importing the file malaga-en-cifras_v2.csv using dbeaver returned errors, since the string contained “..”. Using R to write the table was a workaround to import the data in the spreadsheet to the database.

malaga_cifras <- read.csv("/home/ricardo/cursos-2024-2025/Unidad 1/ejercicio1/malaga-en-cifras_v2.csv", sep=",")
DBI::dbWriteTable(docker_connection,  Id(schema = "tycgis", table = "malaga_cifras"),malaga_cifras)

For the vector data, the error “ERROR 1: Non UTF-8 content found when writing feature” is solved following this post where it explains how to use other encoding systems.

PGCLIENTENCODING=LATIN1 ogr2ogr -f PostgreSQL PG:"host=localhost port=25432 user=docker password=docker dbname=gis schemas=tycgis" sitmap_municipios_viewPolygon.shp -nln municipalities_agp -lco GEOMETRY_NAME=geom -nlt PROMOTE_TO_MULTI

CREATE TABLE muncipalities_agp_crop AS
SELECT 
    codigo AS code,
    shape.nombre AS muncipality,
    csv."Principal.cultivo.leñoso.de.regadío..2011" AS woody_crops_2011,
    shape.geom
FROM 
    municipalities_agp AS shape
LEFT JOIN 
    malaga_cifras AS csv
ON 
    "Id_Municipio" = codigo::integer
WHERE "Principal.cultivo.leñoso.de.regadío..2011" NOT IN ('-','..');