Evidence 1 — Data sourcing & inspection

Purpose

This page documents the inspection and controlled import of the source dataset dirty_sites_nl_v2.csv.

This workflow was used to work out the source’s structure, encoding, geometry information, coordinates, attribute types, record count and data-quality issues, before and after importing it into PostgreSQL/PostGIS.

The source data was imported as a raw dataset, with no cleaning or attribute type conversion at this stage.

1. Source encoding

The source file encoding was checked before looking at the CSV contents.

Finding

The source file is UTF-8 text.

2. Initial source inspection

The CSV was opened with GDAL using the CSV driver.

Finding

GDAL opened the source without issue and found one layer:

dirty_sites_nl_v2

At this stage, the layer had no native geometry.

3. Baseline layer inspection

The source layer was inspected without specifying coordinate fields.

Findings

The baseline inspection showed:

• 200 source records.
• No native geometry.
• No CRS defined in the source layer.
• The attribute fields were initially read as strings.
• The longitude and latitude fields were also read as strings in the baseline inspection.

This gave the initial state of the source before coordinate interpretation.

4. Coordinate and geometry inspection

The longitude and latitude fields were explicitly identified as the possible X and Y coordinate fields.

Findings

When the coordinate fields were used as X and Y:

• GDAL read the dataset as point geometry.
• The layer contained 200 features.
• The coordinate extent was 5.050024, 52.051135 to 5.199666, 52.129648.
• Longitude and latitude were read as numeric values for this inspection.
• The source CRS was still unknown.

The coordinate values are consistent with geographic longitude/latitude coordinates. The numeric range alone doesn’t prove the original datum or CRS.

5. CRS inspection

The source CRS was checked separately.

Finding

No CRS was defined in the source CSV at the time of inspection.

The source coordinates were later assigned EPSG:4326 during import. This was a CRS assignment, not a reprojection.

6. Record-count verification with GDAL SQL

The source record count was also checked using GDAL SQL.

Finding

The SQL count confirms the source contains 200 records.

7. Identification of records with missing coordinates

The source was queried using the coordinate interpretation described above to find records where longitude is NULL.

Finding

Eight source records have no longitude, so they can’t give a complete X/Y pair to build a point.

This finding matches the later PostGIS result showing eight records without geometry.

8. Controlled import to PostgreSQL/PostGIS

The source dataset was imported into PostgreSQL/PostGIS using ogr2ogr.

Import controls

The documented import command:

• uses the PostgreSQL driver;
• constructs point geometry from the longitude and latitude fields;
• assigns EPSG:4326 to the resulting geometry;
• explicitly selects the source attributes to transfer;
• stores the result in raw.sites_nl_dirty.

The longitude and latitude columns were not included in the selected attributes, since they were used to construct the point geometry.

-a_srs EPSG:4326 assigns a CRS to the imported geometry. It doesn’t transform the coordinates.

This was a controlled raw import, not a cleaning operation.

Evidence status

The import command is part of the documented workflow. The PostGIS queries below check the resulting database state independently.

Real database credentials are left out of this public page.

9. PostGIS record-count verification

The number of records in the imported table was checked in PostgreSQL.

SELECT COUNT(*)
FROM raw.sites_nl_dirty;

 count 
-------
   200
(1 row)

Finding

The PostGIS table contains 200 records, matching the source record count.

10. PostGIS SRID verification

The geometry SRIDs were inspected.

SELECT DISTINCT ST_SRID(wkb_geometry)
FROM raw.sites_nl_dirty;

st_srid
-------

4326

Two distinct results came back:

• NULL
• 4326

Finding

The imported dataset contains geometries with SRID 4326 and records with NULL geometry.

11. Verification of records without geometry

The number of records with NULL geometry was checked directly.

SELECT COUNT(*)
FROM raw.sites_nl_dirty
WHERE wkb_geometry IS NULL;

count
-----
8

Finding

Eight imported records have no geometry.

This matches the eight source records the GDAL query flagged with missing longitude.

12. Verification of geometries with SRID 4326

The number of geometries carrying SRID 4326 was checked separately.

SELECT COUNT(*)
FROM raw.sites_nl_dirty
WHERE ST_SRID(wkb_geometry) = 4326;

count
-----
192

Reconciliation

192 records with SRID 4326
+ 8 records with NULL geometry
= 200 records

This matches both the source and PostGIS record counts.

13. PostGIS attribute-type inspection

The imported attribute types were checked through PostgreSQL metadata.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'sites_nl_dirty';

  column_name   |     data_type     
----------------+-------------------
 ogc_fid        | integer
 id             | character varying
 address        | character varying
 category       | character varying
 quantity       | character varying
 area           | character varying
 price_eur      | character varying
 date_collected | character varying
 notes          | character varying
 wkb_geometry   | USER-DEFINED
(10 rows)

Finding

Several fields that could potentially represent numeric or date values remain stored as character varying.

No type conversion was performed during this raw import stage.

14. Geometry validity verification

Geometry validity was checked in PostGIS, only for records that have geometry.

SELECT
    COUNT(*) AS invalid_geometries
FROM raw.sites_nl_dirty
WHERE wkb_geometry IS NOT NULL
  AND NOT ST_IsValid(wkb_geometry);

invalid_geometries
------------------
0

Then the number of valid geometries was checked:

SELECT
    COUNT(*) AS valid_geometries
FROM raw.sites_nl_dirty
WHERE wkb_geometry IS NOT NULL
  AND ST_IsValid(wkb_geometry);

valid_geometries
----------------
192

A further query was run to find invalid geometry reasons:

SELECT
    id,
    ST_IsValidReason(wkb_geometry) AS validity_reason
FROM raw.sites_nl_dirty
WHERE wkb_geometry IS NOT NULL
  AND NOT ST_IsValid(wkb_geometry);

0 row(s) fetched.

Finding

All 192 imported records that contain geometry pass the PostGIS ST_IsValid check.

There are no invalid geometries among the 192 records with geometry.

15. Failed geometry-validity attempt in GDAL

An earlier attempt was made in GDAL to check geometry validity using SQLite SQL:

ogrinfo \
  dirty_sites_nl_v2.csv \
  -dialect SQLite \
  -sql "SELECT COUNT(*)
        FROM dirty_sites_nl_v2
        WHERE ST_IsValid(geometry) = 0"


ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(): no such column: geometry

Interpretation

This query failed because the CSV layer didn’t expose a column named geometry for that query.

This failed attempt is not used as evidence of geometry validity.

The validity result reported above comes from the successful PostGIS checks.

16. Inspection findings relevant to later cleaning

The inspection also turned up examples of inconsistent attribute values.

Examples from the source records include:

• hospitaal
• warehouse — trailing whitespace
• PARK — different capitalisation
• parc
• retail

One address also has surrounding whitespace:

  Dorpsstraat 196

These observations flag attribute-quality issues to address during the data-cleaning stage.

No normalisation or cleaning was done as part of this inspection evidence.

17. Evidence summary

18. Data exploration dashboard (QGIS)

A QGIS print-layout dashboard was built from the raw imported dataset to pull together the findings from sections 3, 4, 7, 16 and 17 visually.

Download the full-resolution dashboard (PDF)

Findings

Using QGIS directly on the raw imported layer, the dashboard confirms:

• 200 total records, 192 mapped, 8 with missing geometry — matching the GDAL and PostGIS counts above.
• The layer CRS is reported as EPSG:4326 (WGS 84), consistent with the CRS assigned during import.
• The category field contains at least 26 visually distinct raw values, consistent with the attribute-quality issues in Section 16.
• The quantity and area fields contain a mix of numeric, non-numeric, and missing values, consistent with these fields still being stored as character varying (Section 13).

Evidence status

This dashboard was built from the raw, pre-cleaning dataset in QGIS. It is a visual cross-check of findings already established with GDAL and PostGIS, not a separate verification method.

Evidence boundaries

This evidence shows that a spatial CSV can be inspected, its coordinate fields interpreted, missing coordinates found, source structure and types checked, a controlled import into PostGIS run and the result verified.

It doesn’t show that:

• the original source CRS was definitively EPSG:4326;
• the source attributes were cleaned or normalised;
• string attributes were converted to appropriate numeric or date types;
• the eight missing coordinates were repaired;
• the imported data was corrected after inspection.

EPSG:4326 was assigned during the import workflow. The source inspection itself reported the CRS as unknown.

The geometry validity result applies to the 192 records that contain geometry. It doesn’t establish validity for the eight records without geometry.

The failed GDAL geometry-validity query is kept on this page for transparency, but it is not counted as evidence.

Technical capability demonstrated

This evidence shows the ability to:

• inspect an unfamiliar spatial CSV before loading it into a spatial database;
• identify its structure, encoding, record count, coordinate fields and CRS state;
• use GDAL to read longitude and latitude as point geometry;
• identify records that can’t produce geometry because coordinates are missing;
• run a controlled raw import into PostgreSQL/PostGIS;
• verify record counts, geometry presence, SRIDs and attribute types after import;
• check geometry validity with PostGIS;
• reconcile source and database results so all 200 records are accounted for.

This supports demonstrated hands-on capability in data sourcing and inspection. Data cleaning and correction are outside its scope.