fuente14 rows
Who supplied the data: primary (the operator of the instrument) or redistributor. That distinction alone resolves discrepancies.
idintegerPKnombretexttipotextaportatexturltextaccesotextconsultada_entext
The data model · 13 tables · 3 views · PPM_DB3 + homog_v5 + chirps_v2 + era5_land
How the database is stored
A precipitation value never travels alone: it carries the station and source it came from, the version of the computation that produced it, whether it is a measurement or an estimate, and whether anyone flagged it as suspect. This is the schema that guarantees it, drawn directly from the downloadable database.
294,271
rows in observacion
13
tables, 3 views
23.8 MB
the SQLite database
5.9 MB
the downloadable package
The diagram
Swipe the diagram sideways to explore it; the same information is listed below.
Arrows run from the referencing column (FK) to the identifying key (PK). Hover over or tap a table to see only its relationships; click to go to its record.
The measurement and its provenance
posicion_estacion790 rows
One row per known position, with the source that reports it. A relocated station adds a row; discrepancies between sources are stored rather than lost.
idintegerPKestaciontext→ estacionlatreallonrealaltitud_mrealdem_mrealfuente_idinteger→ fuentees_vigenteintegerdist_a_vigente_kmreal
institucion12 rows
DGA, DMC, CEAZA, INIA, SENAMHI and the other networks.
idintegerPKnombretextpaistext
estacion530 rows
Identity of each series: code, name, institution. No coordinates, since they change and have their own provenance. Includes the ERA5-Land pixels that climatol used as reference, marked with es_referencia.
codigotextPKnombretextinstitucion_idinteger→ institucionpaistextnombre_climatoltextorigentextes_referenciaintegernotatext
accion_limpieza36 rows
Each cleaning decision, with its rationale and how many cells it affected.
idintegerPKversion_idinteger→ version_datasetetapatextoperaciontextdetalletextestaciones_afectadasintegerceldas_afectadasinteger
marca_calidad157 rows
Suspicions and exclusions, kept separate from the value. A JOIN brings them in and a WHERE excludes them, but the measured value is never deleted.
idintegerPKestaciontext→ estacionmestexttipotextmotivotextevidenciatextversion_idinteger→ version_dataset
The central fact
producto4 rows
Observed, homogenized, CHIRPS and ERA5, each with its scale: how many integer units make up 1 mm.
idintegerPKcodigotextnombretextunidadtextescalaintegeres_medicioninteger
conteo_estacion1,089 rows
How many months each station has in each product, with its zeros and its period. Computed when the database is built; the API reads it instead of scanning observacion.
estaciontextPKproducto_idintegerPKversion_idintegerPKfilasintegercerosintegerdesdetexthastatext
observacion294,271 rows
The fact table: station × month × product × version → value and flag. Long format. The value is an integer; a month without a measurement has no row, whereas a month measured at 0 mm does.
estaciontextPKmestextPKproducto_idintegerPKversion_idintegerPKvalorintegerbanderainteger
version_dataset5 rows
Each rebuild, with its parent: obsolescence propagates automatically and is queried with WITH RECURSIVE.
idintegerPKetiquetatextproducto_idinteger→ productopadre_idinteger→ version_datasetgenerada_entextgenerada_portextdescripciontextes_vigenteinteger
Homogenization
segmento_regimen316 rows
Which station was assigned to which regime, whether it estimated the regime itself or inherited it from a reliable neighbor, and the stations left out for being too short.
estaciontextPKversion_idintegerPKregimentextf_veranorealmm_anorealn_mesesintegermin_obs_mesintegerfiableintegerregimen_origentextvecina_origentext→ estaciondist_vecina_kmrealregimen_v4text
corrida_climatol8 rows
One row per regime and scale, with the parameters exactly as climatol recorded using them, inht included.
idintegerPKversion_idinteger→ version_datasetregimentextescalatextanyiintegeranyfintegern_seriesintegern_referenciaintegerparametrostextinhttextmetadintegerversion_climatoltextscripttextejecutada_entext
salto_climatol8 rows
The detected breaks, with their month and SNHT.
idintegerPKcorrida_idinteger→ corrida_climatolestaciontext→ estacioncodigo_climatoltextmestextsnhtreal
The views: where to start
v_estacion_vigenteEach station with its current position and its regime.
v_medicion_limpiaCurrent measurements only: no infilling, no correction and no flagged cells. The one to use for any trend analysis.
v_observacionThe value already in millimeters (valor_mm = valor / escala), with the product and version names. The default query.
A value is an integer
The column observacion.valor stores millimeters multiplied by the scale of its product, and the view v_observacion returns them already divided. This way the measured zero —65 % of the measurements— always compares exactly, takes up zero bytes, and sums accumulate no error.
| producto | escala | 1 mm is stored as |
|---|---|---|
observado | 100 | 100 |
homogeneizado | 100 | 100 |
chirps | 10,000 | 10,000 |
era5 | 10,000 | 10,000 |
An example query
-- one station's measurements, in mm, with no infilling and no flagged cells
SELECT mes, valor_mm
FROM v_medicion_limpia
WHERE estacion = '3450004'
ORDER BY mes;Download and query
The complete database opens in R, Python or DB Browser for SQLite with nothing else to install. The same database can be queried online through the API.