Loading CSV files

Loading OS NGD CSV files into databases

It is recommended that you have a basic understanding of database terminology before following the guides in the tabs below. The guides contain generic instructions, and it is recognised that there are multiple ways to load CSV files into databases which may be more suitable to your environment and existing processes.

Prior to loading the data into a database, it is necessary to create the relevant tables in the database. We have supplied the DDL statements that can be accessed in our OS NGD Resources GitHub repository.

These instructions are based on PostgreSQL version 14, but should work for all supported versions. The instructions assume that you have set-up your database with the PostGIS spatial extension.

Once connected to your PostgreSQL database, with the relevant schema and table created, the CSV file can be loaded with the following SQL statement using the COPY command:

COPY destination_schema.destination_table 
    FROM 'source_path_to_csv_file\source_name_of_csv_file.csv'
    DELIMITER ','
    CSV HEADER
    ENCODING 'UTF8';

PostGIS will automatically store the geometry data that is supplied in Well-Known Text (WKT) format.

There is a known bug affecting PostgreSQL versions 11, 12 and 13 in Windows environments, where the COPY command cannot load files larger than 4GB. As a workaround, version 14 (or later) of the COPY command can be used to load data into the affected database versions.

For reference, the error message states ERROR: could not stat file.

These instructions are based on Microsoft SQL Server 2019, but should work for all supported versions.

Once connected to your SQL Server database, with the relevant schema and table created, the CSV file can be loaded with the following SQL statement using the BULK INSERT command:

BULK INSERT destination_schema.destination_table
FROM 'source_path_to_csv_file\source_name_of_csv_file.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2
);

It is not possible to BULK INSERT the geometries directly in their Well-Known Text (WKT) format.

However, it is possible to change the destination geometry column to a nvarchar(max) type, and then either post process the table or use a a computed column to generate a geometry type column (see code examples below).

Post process example

-- Add new geometry column
ALTER TABLE destination_schema.destination_table
ADD new_geometry_column_name geometry;

-- Set new geometry column using existing WKT column
UPDATE destination_schema.destination_table
SET [new_geometry_column_name] =  geometry::STGeomFromText(destination_table.geometry, /* EPSG_CODE_OF_GEOM */); 

-- Optional drop original WKT column
ALTER TABLE destination_schema.destination_table
DROP COLUMN geometry;

Computed column example

ALTER TABLE destination_schema.destination_table
ADD new_computed_column_name AS
    geometry::STGeomFromText(destination_table.geometry, /* EPSG_CODE_OF_GEOM */) persisted;

PreviousGetting started with CSV NextProcessing COUs

Last updated 1 year ago

Was this helpful?