This project converts the public subsurface data from Antwerpen to a format compatible with the Dutch 'geotop' format that is being used by digging visualisation software.
GeoTOP is a 3D model that represents the subsurface down to a maximum of 50 meters below Amsterdam Ordnance Datum (NAP) in blocks (voxels) of 100 × 100 × 0.5 meters. The model provides information about the layering and soil type (sand, gravel, clay, or peat) of the shallow subsurface of the Netherlands.
GeoTOP had the following lithology classed ('lithoklassen'):
| Code | Lithology | Color | Visual |
|---|---|---|---|
| 0 | Antropogeen | #c1c3c6 | |
| 1 | Veen | #985045 | |
| 2 | Klei | #189f48 | |
| 3 | Zandige klei | #b6d169 | |
| 5 | Fijn zand | #fff000 | |
| 6 | Matig grof zand | #ffdc00 | |
| 7 | Grof zand | #ffc800 | |
| 8 | Grind | #ffb400 |
Each GeoTOP voxel specifies the percentage likelihood of occurrence for each of the lithological classes listed above.
The voxel properties are:
| Property | Type | Description |
|---|---|---|
| geom | public.geometry(point, 28992) | Geometric point representation in Dutch coordinate system (RD) |
| x | float8 | X coordinate (RD) |
| y | float8 | Y coordinate (RD) |
| max_z | int4 | Maximum Z value, the z value (cm) at surface level |
| z | int4 | Z coordinate in cm to NAP |
| lithostrat | int2 | Lithostratigraphic unit code |
| lithoklasse | int2 | Lithologic class code (see above) |
| kans_1_veen | int2 | Probability class 1 - peat (%) |
| kans_2_klei | int2 | Probability class 2 - clay (%) |
| kans_3_kleig_zand | int2 | Probability class 3 - clayey sand (%) |
| kans_5_zand_fijn | int2 | Probability class 5 - fine sand (%) |
| kans_6_zand_matig_grof | int2 | Probability class 6 - moderately coarse sand (%) |
| kans_7_zand_grof | int2 | Probability class 7 - coarse sand (%) |
| kans_8_grind | int2 | Probability class 8 - gravel (%) |
| kans_9_schelpen | int2 | Probability class 9 - shells (%) |
| modelonzekerheid_lithoklasse | int2 | Model uncertainty for lithologic class (%) |
| modelonzekerheid_lithostrat | int2 | Model uncertainty for lithostratigraphic unit (%) |
| naam | varchar | Lithologic class name |
| kleur | bpchar(7) | Lithologic class color (RGB hex code) |
The Antwerp subsurface data is similar to GeoTOP, but has more detailed voxels (35m x 35m x 0.5m).
These voxels have the following properties (only relevant properties are included here):
| GROUP | COLUMN NAME | CONTENT |
|---|---|---|
| voxelIndex | id | Unique index number for each voxel |
| voxelCoordinates | x | X-coordinate of voxel |
| y | Y-coordinate of voxel | |
| z | Z-coordinate of voxel | |
| lithostratigraphy | lithostrat | Fixed code for lithostratigraphic G3D-code |
| lithofactions | ve | Lithofraction peat expressed in % |
| kl | Lithofraction clay expressed in % | |
| si | Lithofraction silt expressed in % | |
| fz | Lithofraction fine sand expressed in % | |
| mz | Lithofraction medium sand expressed in % | |
| gz | Lithofraction coarse sand expressed in % | |
| gr | Lithofraction gravel expressed in % | |
| zt | Lithofraction total sand expressed in % | |
| lithosom | Sum of lithofractions expressed in % | |
| dst | Minimal distance to input data for lithofractions ve, kl, si, gr and zt, expressed in m | |
| dst_zand | Minimal distance to input data for lithofractions fz, mz and gz, expressed in m | |
| lithological composition | lithocode | Fixed code for lithological description |
For this conversion, the following tools were used:
- bash
- Postgresql database with PostGIS extension
- psql
The goal of the conversion is to create a table 'geotop' that contains the Antwerp subsurface data. The idea is that the digging software designed to work with geotop can also dig in the Antwerp region.
- get the conversion scripts
git clone https://github.com/sogelink-research/antwerpengeotop
cd antwerpengeotop- update file config.sh to match the file and database settings
- import the Antwerp csv data into a Postgresql database
./importcsvdata.sh- convert the imported data (table 'voxels') to GeoTOP
./converttogeotop.shAfter conversion, database view 'geotop' returns a geotop version of the Antwerpen data. Also a pgfeatureserv compatible function 'postgisftw.geotop_query' is created.
You can test the function with:
-- return voxels around point with latitude 51.257138648 and longititude 4.295882852 up to a depth of 300000 cm (300 meter)
select * from postgisftw.geotop_query('{"coordinates":[4.295882852,51.257138648],"type":"Point"}', 30000);Any GeoJSON compatible geometry (point, line, polygon) can be used to spatially select voxels.