Changing Comma to Decimal for CSV Import in PostgreSQL – How to Guide

csvdatabasepostgispostgresql

I try to import a CSV file into a PostgreSQL database that runs on a remote server.
I use psql via SSH.

AIS_DB=# CREATE TABLE bet_srk_2008 (
AIS_DB(# Unique_VesselTraject numeric,
AIS_DB(# AISTrajectPositionTimestampLocal timestamp,
AIS_DB(# RandomVesselAISKey varchar,
AIS_DB(# AISTrajectPositionSpeedOverGround varchar,
AIS_DB(# AISTrajectPositionCourseOverGround varchar,
AIS_DB(# VesselAISLength varchar,
AIS_DB(# VesselAISBeam varchar,
AIS_DB(# VesselAISMaximumDraught varchar,
AIS_DB(# VesselTypeAISDescription varchar,
AIS_DB(# BOZ varchar,
AIS_DB(# AISTrajectPositionLatitude numeric,
AIS_DB(# AISTrajectPositionLongitude numeric,
AIS_DB(# TrajectAISDestination varchar
AIS_DB(# );

CREATE TABLE

AIS_DB=# \COPY bet_srk_2008 FROM '/home/thecaptain/Export2008.csv' DELIMITERS ';'CSV HEADER;

ERROR: invalid input syntax for type numeric: "51,2628135681152"
CONTEXT: COPY bet_srk_2008, line 2, column aistrajectpositionlatitude: "51,2628135681152"

I think the problem is that the data uses a comma instead of point as a decimal. Is there a way to circumvent this in the import process?
The datasets have 100 million+ lines and a find and replace is making nano run out of memory.
If I use VARCHAR instead of NUMERIC it won't let me ST_MAKEPOINT it to the geom column.

This is the whole text:

CREATE TABLE bet_srk_2008 (
Unique_VesselTraject numeric,
AISTrajectPositionTimestampLocal timestamp,
RandomVesselAISKey varchar,
AISTrajectPositionSpeedOverGround varchar,
AISTrajectPositionCourseOverGround varchar,
VesselAISLength varchar,
VesselAISBeam varchar,
VesselAISMaximumDraught varchar,
VesselTypeAISDescription varchar,
BOZ varchar,
AISTrajectPositionLatitude numeric,
AISTrajectPositionLongitude numeric,
TrajectAISDestination varchar
);

\COPY bet_srk_2008 FROM '/home/thecaptain/Export2008.csv' DELIMITERS 
';'CSV HEADER;

ALTER TABLE bet_srk_2008 ADD COLUMN geom geometry (POINT, 4326 );

UPDATE bet_srk_2008 SET geom 
ST_SetSRID(ST_MakePoint(AISTrajectPositionLongitude, 
AISTrajectPositionLatitude) ,4326);

CREATE INDEX bet_srk_2008_index ON bet_srk_2008 USING GIST ( geom );

Best Answer

Ok I found a solution for anybody that has the same problem.
I am still interested if somebody has a solution without having to alter the dataset prior to importing.

I use Linux as a operating system on desktop and servers. I changed the comma to point:

sed -i 's/,/./g' Export2008.csv

Related Solutions

[GIS] PostGIS Intermittent INDEX Performance

Effectively forcing the planner to do the thing you want might help. In this case, sub-setting the polygon table prior to executing the spatial join with the points table. You might be able to outwit the planner using "WITH" syntax:

WITH polys AS (
  SELECT * FROM area
  WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
)
SELECT * 
FROM polys JOIN data ON ST_Intersects(data.point, polys.polygon)
WHERE datasetid IN(3) 
AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19');

The trouble with trying to play these games is that you are coding into your statement the assumption "my polygon list will always be more selective than my other query portions". Which might not be true for all parameterizations of your query, or for all applications of a particular query over a heterogeneously distributed dataset.

But it might work.

UPDATE: This goes even further down the dangerous road of assuming you know the selectivity of your clauses beforehand, this time we also take the attribute selection on the point table out and do it separately before the spatial join:

WITH polys AS (
  SELECT * FROM area
  WHERE area.id in IN(28,29,30,31,32,33,25,26,27,18,19,20,21,12,13,14,15,16,17,34,35,1,2,3,4,5,6,22,23,24,7,8,9,10,11)
),
WITH points AS (
  SELECT * FROM data
  WHERE datasetid IN(3) 
  AND (readingdatetime BETWEEN '1948-01-01' AND '2012-11-19')
)
SELECT * 
FROM polys JOIN points ON ST_Intersects(points, polys.polygon);

[GIS] Understanding QGIS errors when importing .csv file

I found the solution for my problem. There were (probably) two "errors" in my .csv.

QGIS seems to detect that my data field called "text" contained exactly this - a text. Thus, it encoded it as a String (or character) data type. Within such a data field, however, double quotation marks are only allowed at beginning and end or not at all. Since my Tweets contained quotes within them, this seemed to cause the problem. Single quotation marks (the apostrophe) are no a problem (so I just converted them and since then it works). On the other hand, it does not make a difference whether the whole field is contained within quotation marks or not.
QGIS treats a sequence of characters that you enters as "custom delimiters" like a logical or, not and. Thus, when I used "$%&" as delimiters, it also split the record at a single "&". So my choice of delimiters also caused a problem. But I got the idea to use and alt-key character (pressing alt-key and a letter at the same time) as a delimiter from Branco's answer here. I used alt-key plus s and QGIS import the file without any problems.

Also, for the record and in case other German speaking users run into the same error: "Ungültiges Satzformat" translates into "invalid record format" which gets you on the right track while searching for a solution to this error. I ultimately got the idea that the problem has something to do with strings and quotation marks from the comments to this question.

Related Question