…and data analysis
The use of structured query language (sql) is an effective way of accessing and updating – also spatial – information. Structured data management plays a significant role for subsequent processing, the use in applications, models or visualisations and presenting meaningful information. Before storing data in relational database systems, it is often necessary to handle flat-file or raw plain text information, via ways of cleansing and formatting. This can be achieved through scripting solutions (python; R) transforming data into a uniform schema or meet defined standards. In addition, data needs to be organised in a way allowing it to easily be linked to other related information, which maybe be of different type, such as spatial or aspatial (textual). The data organisation and linkages are performed through establishing and managing databases, ideally capable of also storing spatial data.
Over many years, we have been working with a variety of relational database systems, conceptual tasks (EER), including the creation of spatial sql based queries and establishing data linkages, or relationships. Common systems we are familiar with are:
MS SqlServer, Postgres/Postgis, Esri Geodatabase, MS Access and MySql.
We are hosting a spatial postgres/postgis server where client data can be stored, managed and deployed e.g. as web-maps. This also enables us to capture and manage data uploads, and submissions of mobile field data surveys (e.g. ODK).
Our proficiency of analysing and processing spatial and aspatial data is mostly performed with established GIS applications, and – often offering greater flexibility – open-source based solutions such as R, Gdal, or other python based libraries. The use of R allows statistical analyses and plotting graphs.
We started working with the ‘Big Brother’ of Postgres – the Analytical Massively Parallel Processing (MPP) Database Vertica – using its community edition allowing a 1 TB storage. The associated Python-library verticapy allows additional big data analysis and provides data science functionalities.