Load LINESTRING in WKT-format from CSV to pgAdmin?

Question

I've got a CSV file with a column containing LINESTRINGs that looks something like this:

1,"LINESTRING(20.331 54.33, 21.67 44.89, 12.67 13.5679)",2014-03-09 13:30:33,2014-03-09 13:31:33
2,"LINESTRING(25.331 14.33, 41.67 44.119, 15.67 23.5679, 34.55 48.11)",2014-03-10 13:30:33,2014-03-10 13:31:33
1,"LINESTRING(21.331 51.33, 11.67 44.32, 32.67 13.59)",2014-03-22 13:30:33,2014-03-23 13:31:33
5,"LINESTRING(10.331 24.22, 23.37 34.89, 22.17 12.29)",2014-03-24 13:30:33,2014-03-24 13:31:33

I created a table in pgAdmin 4 looking like this:

CREATE TABLE linestringtest (participant integer, geom geometry, startt time without time zone, endt time without time zone);

I now wanted to copy/import the CSV data, using the pgAdmin GUI, into the table but it doesn't work. I tried everything. Do I have to convert my linestrings into binary system first and if so how does it work?

Answer 1

If I understand your question correctly, then there are various ways to load data from the * .csv format in PostgreSQL DBMS, and here is one of them.

1) Prepare your data so that each data set is located in the corresponding cell of the EXCEL table, see Figure 1, note that the numeric fields have a numeric type, the text fields have a text type, and the temporary fields have a date type in the EXCEL table!

2) Create a table in your database for which run the script

CREATE TABLE public.linestringtest ( id integer NOT NULL, participant integer, geom text, startt timestamp without time zone, endt timestamp without time zone, CONSTRAINT linestringtest_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE public.linestringtest OWNER TO postgres;

3) Now run the script to copy data from your table in * .csv format to database table,

COPY linestringtest (id,participant,geom,startt,endt) FROM /home/linestringtest.csv' WITH (FORMAT csv , DELIMITER E';', NULL '');

note that for the experiment I created a table in * .csv format in the "home" folder on drive C.

4) Now convert the "geom" field from the "text" type to the "geometry" type and if you know your SRID, assign it, in my case it corresponds to 4326

ALTER TABLE public.linestringtest ALTER COLUMN geom TYPE geometry USING geom ::geometry; UPDATE public.linestringtest set geom=ST_SETSrid ((geom),4326);

edit: With the additions proposed @ThingumaBob

ALTER TABLE public.linestringtest ALTER COLUMN geom TYPE geometry USING ST_SetSRID((geom::GEOMETRY), 4326);

5) Open QGIS and see your data, see Figure 2

good luck ...

Answer 2

If you have Microsoft Excel or LibreOffice you could open your csv file in one of these applications. I'll describe procedure for Microsoft Office but it should be very similar also to do it in Libre Office.

Your separators in CSV file should be semi-colon (;). In that way excel automatically puts data into separate columns. ID in column A Geom in column B startt in column C endt in column D

Then in some column after D you paste formula and copy/paste in each row:

=CONCATENATE("INSERT INTO linestringtest VALUES(";A1;", ST_GeomFromText('";B1;"'), to_date('";TEXT(C1;"dd.mm.yyyy hh:m");"', 'DD.MM.YYYY HH24:MI'), to_date('";TEXT(D1;"dd.mm.yyyy hh:m");"', 'DD.MM.YYYY HH24:MI');")

This formula would generate SQL which you can paste into pgAdmin SQL console and execute statements.

If you need to assign coordinate system to geometry than you need second argument in function ST_GeomFromText, e.g.

=CONCATENATE("INSERT INTO linestringtest VALUES(";A1;", ST_GeomFromText('";B1;"', 4326), to_date('";TEXT(C1;"dd.mm.yyyy hh:m");"', 'DD.MM.YYYY HH24:MI'), to_date('";TEXT(D1;"dd.mm.yyyy hh:m");"', 'DD.MM.YYYY HH24:MI');")