[GIS] Import CSV file into Postgis DB with Location-field formatting

csvpostgis

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)

Best Answer

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 );
Related Question