3

I have been importing data into PostgreSQL tables all day using ogr2ogr and then spatially enabling the data with no issues up to now. I have been cutting and pasting the code and this is what I have been using:

ALTER TABLE mytable ADD COLUMN geom geometry(POINT,4326);
UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(cast (lon AS double precision),cast (lat AS double precision ) ),4326);

On the last table, I suddenly get this error:

ERROR:  invalid input syntax for type double precision: "#VALUE!"

Nothing has changed, at least as far as I know, I was importing a csv file, same as all the others.

I checked the csv file for spaces or unusual characters but it looks in order

Column lon, lon are of type character varying

here is a sample of the data

id,name,name_alt,lat,lon,description
1,Abbott Seamount,,31.8,174.3,A seamount located north of Midway Islands (US) in the North Pacific Ocean; name origin unknown.
2,Abra Canyon,,17.51667,120.36667,An undersea canyon located off the northwest coast of the Philippine island of Luzon in the South China Sea; named after the Abra River
3,Abraham Canyon,Abraham Sea Valley,52.61667,172.33333,An undersea canyon in the area of the Aleutian Islands in the North Pacific Ocean; named in association with Abraham Bay; Attu Island; Alaska (US). 
4,Abrolhos Bank,,-18.5,38.75,A bank located off the coast of Brazil in the Atlantic Ocean; upon which sits the Arquipélago dos Abrolhos (archipelago). 
5,Abrolhos Seamounts,Abrolhos Ridge; Cadeia dos Abrolhos,-17.33333,36.5,Seamounts located off the coast of Brazil in the Atlantic Ocean; named in association with the Abrolhos Bank and Arquipélago dos Abrolhos (archipelago). 
6,Abubacer Ridge,,36.8,1.65,An undersea ridge located off the coast of Spain in the western Mediterranean Sea; named for Ibn Tufayl (known as Abubacer to Europeans); 12th-century Spanish-Arab philosopher and physician. 
7,Aby Canyon,,4.25,-3.66667,An undersea canyon located off the coast of Côte d'Ivoire; named in association with Lagune Aby; Côte d'Ivoire (lagoon)

What am I missing here?

  • Does it work without that last line? Have you checked all of the name and name_alt values for an embedded comma? – BradHards Oct 25 '13 at 10:22
  • The first line works, the second line does not. I had excel do a global replace on the file and changed all commas to semi-colons. I have scanned the table visually as well and cannot see anything untoward either. It would have to be in the data, but why would it issue a syntax, or is that referring to the data. Character encoding is latin1 when imported, but I just noticed that the database is utf. I have about 100 tables in that database with no problem before. – Mark Cupitt Oct 25 '13 at 10:30
  • Does it work if you change the semicolon in the first line description to something else (or just remove it)? – BradHards Oct 25 '13 at 10:35
  • removed it, same error. was probably not a good choice to replace commas with in hindsight – Mark Cupitt Oct 25 '13 at 10:44
  • Ok, found it. One # char towards the bottom of the file. Just happened to precede the word VALUE. OMG! – Mark Cupitt Oct 25 '13 at 11:05
5

For future users:

This problem was caused by bad data. There was the "#" Character on one cell of the csv that I missed that just happened to be the word "#VALUE!" (actually generated by someones formula at one stage I would guess)

The error:

ERROR:  invalid input syntax for type double precision: "#VALUE!"

was being reported by PostgreSQL as that exactly what it was seeing in the data, which was invalid. I had a lot of rows and just missed it.

Moral of the story, check your data carefully. If preparing CSV files for uploading and later GeoSpatial uses, sort on the lat and lon columns, that will show any non numeric values in there quickly.

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.