GeoBI Architecture Based on Free Software: Experience and Review - Geographical Information Systems: Trends and Technologies - page 256

Physical Design
Before implementing the SDW an appropriate DBMS should be selected.
The selection process should consider many features that are also
important for the DW implementation, e.g., different kinds of indices,
parallel processing, fragmentation techniques, and materialized views
management, among others (Jensen et al. 2010; Golfarelli and Rizzi 2009;
Malinowski and Zimányi 2008). Furthermore, since our implementation
takes spatial data into account, the existence of spatial features in a DBMS
is fundamental. Currently, several DBMSs (e.g., Oracle, SQL Server, DB2,
PostgreSQL, MySQL) provide spatial extensions with different numbers
of spatial features related to data creation, manipulation, and analysis.
However, since one of the requirements was to rely on free software, our
options for choosing a DBMS were limited to PostgreSQL and MySQL. After
thorough evaluation of these systems and comparing their functionalities
and performance (Chen and Xie 2008), we have chosen PostgreSQL. It
integrates with PostGIS extending the conventional capabilities to spatial
features, e.g., spatial data types, spatial functions, spatial indexing, and a
spatial query language.
PostGIS provides geography and geometry data types that allow
spherical or planar representation of geometries, respectively. Currently,
there are fewer functions that apply to geography types than geometry
types, and the main difference in managing them is whether the calculations
are performed over sphere or plane (PostgreSQL 2013). Since Costa Rica is
contained in a small area, using geometry data type allows having more
functions and they are also less expensive in the computation.
The spatial data type specifi ed in the conceptual schema can be defi ned
as the attribute with geometry type that includes a specifi c data type and a
spatial reference identifi er (SRID) as shown in Fig. 5 for the District relation.
We use SRID 4326 that corresponds to the WGS84 (World Geodetic System
1984), accepted worldwide and required by law in Costa Rica since 2007.
Different constraints can be specifi ed, e.g., to check whether the geometry
is valid ( st_isvalid in Fig. 5). We included the last constraint due to the poor
data quality even though it takes longer to insert the data. Currently, our
ETL processes verify these aspects; however, in the future the system will
create table District
(DistrictPK integer not null,
--other conventional attributes
Geom geometry (MULTIPOLYGON, 4326),
constraint District_pkey primary key (DistrictPK),
--other constraints
constraint enforce_geom_valid check (st_isvalid(geom))
);
Fig. 5. Defi nition of the District dimension in PostGIS.