Import CSV file into Postgis DB with Location-field formatting?

Question

I am working to import CSV into table in my Postgis Database.

First, I created the table

Create Table test (
name varchar,
created_date Date,
location double precision
);

When I excuted the command to

COPY test FROM 'data/file.csv' DELIMITERS ',' CSV HEADER;

I got this error:

ERROR: invalid input syntax for type double precision: "(40.74053344654042, -74.00778384953068)"
CONTEXT: COPY test, line 2, column location: "(40.74053344654042, -74.00778384953068)"

What should I make as a type when I create the model ? Float, double precision or what exactly ?

Data are similar to (40.74053344654042, -74.00778384953068)

UPDATE: CSV File look like this:

name,created_date,location
"Coffe",01/02/2007,(40.74053344654042, -74.00778384953068)
"School",01/02/2007,(40.72324713800021, -74.00495699358042)
"Building 234",01/02/2007,(40.842741313574706, -73.83840584215893)
"Building 4",01/02/2007,(40.842741313574706, -73.83840584215893)
"Building 2435",01/02/2007,(40.75433132495244, -73.99262239963087)
"Building 255",01/02/2007,(40.74482004786735, -73.98511337722212)

• spatialhast: You created three columns in table (name,created_date,location), but when you perform a query import data with delimiter "," request attempts import data from four columns: |"Coffe"|01/02/2007|(40.74053344654042|-74.00778384953068)|. You need change '",' and '/2007,' on another delimiter (for example ;) and change field type 'location double precision' to 'location text'
• user3001937: You have a sharp-eye man, I didn't notice that. I'd look for a trick to solve this issue. any suggestions.
• user3001937: I am going to split the field to lang/lat. It's better ! what do you think !
• spatialhast: split the field to lang/lat - yes, it is correct
• spatialhast: if you want create postgis geometry field see example twiav-tt.blogspot.com/2012/07/…

Answer 1

I would suggest organizing your data like this:

name,created_date,lon, lat

"Coffe",01/02/2007,40.74053344654042,-74.00778384953068

Making a table like this:

CREATE TABLE test (
name varchar,
created_date Date,
lon numeric,
lat numeric
);

Loading it like this:

\COPY test FROM 'data/file.csv' DELIMITERS ',' CSV HEADER;

And then create a geometry field, using the lon/lat columns:

ALTER TABLE test ADD COLUMN geom geometry(POINT, 4326);
UPDATE test SET geom = ST_SetSRID(ST_MakePoint(lon, lat) ,4326);
CREATE INDEX idx_test ON test USING GIST ( geom );

Answer 2

try numeric rather than double precision,

it seems double precision can only support up to 15 decimal places, so maybe your coordinate precision is greater than that

more details on postgresql data types can be found here - http://www.postgresql.org/docs/9.1/static/datatype-numeric.html

• user3001937: It doesn't work as well. same problem.
• tjmgis: can you edit your post and put a sample of your csv up, say first 5 rows