Related Topic
Reasons for lack of a ImportCSV() function:
- Based on the fact that CSV and TXT/TAB are very loose and variable defined format, successfully opening and reading a Text file usually requires several attempts before you finally "guess" the most appropriate combination of delimiters/separators.
- Therefore a 2 step import process is advised:
----------------------------------------
-- Step 1: create a virtual table using VirtualText
----------------------------------------
CREATE VIRTUAL TABLE virtual_cities1000 USING VirtualText
(
'cities1000.txt', -- absolute or relative path leading to the textfile
'UTF-8', -- charset encoding used by the textfile
0, -- does the first line contains column names [0=no, 1=yes]
POINT, -- the decimal separator [POINT or COMMA]
NONE, -- the text separator [NONE, SINGLEQUOTE or DOUBLEQUOTE]
TAB -- the field separator [TAB, ',', ':' or other charater]
);
----------------------------------------
At this point, a thorough check of the results is needed:
- do the column names make sense?
- are the dates correct?
- are the special characters correct ? (needed correction of charset encoding)
- is all the data really needed ?
- what filters are needed to import the needed data ?
----------------------------------------
-- Step 2: create a final TABLE using the virtual table source
----------------------------------------
CREATE TABLE cities_italy AS SELECT
COL002 AS name,
COL006 AS longitude,
COL005 AS latitude,
MakePoint(COL006, COL005, 4326) AS geom_wsg84, -- create the original POINT as Wgs84
ST_Transform(MakePoint(COL006, COL005, 4326),32632) AS geom_utm_19n -- create the POINT as WGS 84 / UTM zone 32N, which the other tables use
FROM virtual_cities1000 WHERE COL009 = 'IT' -- filter out all non-italien cities
;
----------------------------------------
-- such Geometries, created on the fly, must be registered:
----------------------------------------
SELECT RecoverGeometryColumn
(
'cities_italy', -- table-name
'geom_wsg84', -- geometry column-name
4326, -- srid of geometry
'POINT', -- geometry-type
);
SELECT RecoverGeometryColumn('cities_italy','geom_utm_19n',32632,'POINT');
----------------------------------------
-- Create a Spatial-Index for the Geometries
----------------------------------------
SELECT CreateSpatialIndex
(
'cities_italy', -- table-name
'geom_wsg84' -- geometry column-name
);
SELECT CreateSpatialIndex('cities_italy','geom_utm_19n');
----------------------------------------
-- Update the metadata for the Database
----------------------------------------
SELECT UpdateLayerStatistics
(
'cities_italy', -- table-name
'geom_wsg84' -- geometry column-name
);
SELECT UpdateLayerStatistics('cities_italy','geom_utm_19n');
----------------------------------------
DROP TABLE IF EXISTS virtual_cities1000 ;
----------------------------------------
|