-2

I have tried to upload an excel spreadsheet but I get a column with "invalid_the_geometry" and of course "the_geometry" column unpopulated. The result of the spreadsheet I am working with comes from a cvs file downloaded from a Fusion Table map from Google. I assume there is a hierarchy for the columns to respect, but uploading KML files I never had any problem.

How do I make an Excel file ready for cartodb?

I am trying to upload lines.

Improve this question
5
  • Have you checked the geometry columns in your Excel file are 'Number' and not something else?
    – MyFamily
    Commented Feb 6, 2015 at 17:06
  • If I change the column to Number part of these info wouldn't go away?<LineString><coordinates>-84.807658,40.197874,0.0 -84.806468,40.198067,0.0 -84.806381,40.198055,0.0 -84.795083,40.197745,0.0 -84.795125,40.197773,0.0 -84.79526,40.197867,0.0</coordinates></LineString>
    – Giovassi
    Commented Feb 6, 2015 at 17:16
  • They should be fine provided you have the correct number of decimals etc, remove the commas
    – MyFamily
    Commented Feb 6, 2015 at 17:20
  • Well, it doesn't work for me. Changed column to Number in Excel it still uploads on Cartodb as a string column with invalid_the_geometry title.
    – Giovassi
    Commented Feb 6, 2015 at 17:40
  • Try it as as numeric again but as a .csv
    – MyFamily
    Commented Feb 7, 2015 at 14:16

2 Answers 2

Reset to default
1

You can add your LineStrings in a regular column and then use the function ST_GeomFromKML:

UPDATE tablename set the_geom = ST_GeomfromKML(kml_column)

You'd need to get rid of the third coordinate in your text, though. You can do it with:

UPDATE tablename set kml_column = replace(kml_column, ',0.0', ' ')
Improve this answer
6
  • I first updated the column: UPDATE tablename set kml_column = replace(kml_column, ',0.0', ' '). Then changed KML column to the_geom: UPDATE tablename set the_geom = ST_GeomfromKML(kml_column). I got this message: Geometry has Z dimension but column does not. What have I done wrong?
    – Giovassi
    Commented Feb 9, 2015 at 16:04
  • This is because the first query hasn't had an effect. If you check your KML column, it shouldn't contain any ',0.0' text, this is what the first query is trying to do. Are you getting an error after applying the first one?
    – iriberri
    Commented Feb 9, 2015 at 16:50
  • After the first query I get this:<LineString><coordinates>-84.807658,40.197874 -84.806468,40.198067 -84.806381,40.198055 -84.805589,40.197867 -84.805551,40.197865 -84.805507,40.197865 -84.805453,40.197877 . Running the second query instead I get this message: invalid KML representation.!?!?!?!
    – Giovassi
    Commented Feb 9, 2015 at 20:50
  • There is any way to have a map in CartoDB form an excel file? I followed your suggestions, double triple checked but it doesn't work. I don't understand why I get this message: invalid KML representation. What it is a KML representation? Since the file comes directly from a KML map saved in csv. As I already said I am looking to create a CartoDb map from an already made Google map. I am trying to uploading it as an Excel file because for what it is my knowledge is the only way to keep it synced with a CartoDb table.
    – Giovassi
    Commented Feb 10, 2015 at 21:11
  • Giovassi, CartoDB supports KML files. Have you tried to import it directly? :-)
    – iriberri
    Commented Feb 11, 2015 at 9:58
1

I have done my homework. To have an Excel spreadsheet from KML (actually I am referring to a Fusion Table) file that I can sync using Gdrive or Dropbox first I have to save it to .csv. Once open it in Excel I have to type in the first row the titles of the columns, it gets lost through the .csv Excel translation !!! (IMPORTANT: Coordinates column call it geometry). Last step I was missing is to save the file with .xls extention. CartoDb now recognizes the file and also reads long lats directly without needing to update the table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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