3

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)
  • 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' – HasT Feb 3 '14 at 16:27
  • You have a sharp-eye man, I didn't notice that. I'd look for a trick to solve this issue. any suggestions. – user3001937 Feb 3 '14 at 16:28
  • I am going to split the field to lang/lat. It's better ! what do you think ! – user3001937 Feb 3 '14 at 16:31
  • split the field to lang/lat - yes, it is correct – HasT Feb 3 '14 at 16:32
  • if you want create postgis geometry field see example twiav-tt.blogspot.com/2012/07/… – HasT Feb 3 '14 at 16:37
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 );
0

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

  • It doesn't work as well. same problem. – user3001937 Feb 3 '14 at 15:54
  • can you edit your post and put a sample of your csv up, say first 5 rows – tjmgis Feb 3 '14 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.