Copy data to postgres+postgis from csv file with special formated polyline

Question

I am working with Postgres+Postgis to handle spatial data, i was using a dataset with CSV files (id,timestamp,latitude,longitude) and i was copying the data from the CSV files into the database like so:

cat mycsv.csv | psql -d mydatabase -c "COPY mytable (id ,datetime ,lat ,lon) from stdin WITH DELIMITER ','"

Now i am using a diferent dataset of CSV files and im having trouble copying it to the database. The new CSV file has a polyline and is formated like this:

val1,val2,val3,"[[-8.618643, 41.141412], [-8.618499, 41.141376], [-8.620326, 41.14251]]"

I wanted to import the polyline into a geometry array or as a text field (WKT), my final goal is to import the polyline and store it as a geometry type line in a table.

The problem is, since the delimiter is ',' and the polyline is an array of coordinates separated my ',' i get the obvious error:

ERROR:  extra data after last expected column

Is there a way of having more than 1 delimiter, or a diferent aprouch for this problem?

Answer 1

I will assume you already can import the csv to postgres and your table will have a string field geom_text

The problem is the format of you string. geom_text

If you look here https://en.wikipedia.org/wiki/Well-known_text the format of MULTILINESTRING

 MULTILINESTRING ((10 10, 20 20, 10 40),
                  (40 40, 30 30, 40 20, 30 10))

So you have to use string replace to chage [] for () Then append the keyword MULTILINESTRING

And then use the function ST_GeomFromText to create your object geom.

 SELECT ST_GeomFromText(updated_geom_text) FROM YourCsvTable;

Answer 2

cat mycsv.csv | psql -d mydatabase -c "COPY mytable (id ,datetime ,lat ,lon) from stdin WITH DELIMITER ',' CSV"

You need to specify the CSV mode, you are currently just telling it to use the delimiter.

Answer 3

I worked around the problem using sed, awk and regex to modify the dataset.

Then used ST_GeomFromText function to get the line from text.