@Kartones GEOSPATIAL CSV IMPORTS HIDDEN COMPLEXITY 

@Kartones CartoDB 

@Kartones Agenda 1) CSV Format Issues 2) Import Issues 

@Kartones CSV FORMAT ISSUES 

@Kartones Intro .csv / MIME:text/csv Unknown birthdate (80s?) RFC 4180 (2005) 

@Kartones Intro Plain text Simple format Simple rules 

@Kartones Usage 

@Kartones CSV 0101000020E610000000000000008049C000000000000038C0,1083 "alien",2014-11-04 15:24:40.43413+00 category 1, "jump jump up!", {""value"":""es""} 

@Kartones WKT: Well-Known Text POINT (30 10) LINESTRING (30 10, 10 30, 40 40) POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)) MULTIPOINT ((10 40), (40 30), (20 20), (30 10)) MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5))) https://en.wikipedia.org/wiki/Well-known_text 

@Kartones WKB: Well-Known Binary POINT(2.0 4.0) = 000000000140000000000000004010000000000000 https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary 

@Kartones GeoJSON { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } } http://geojson.org/ 

@Kartones IMPORT ISSUES 

@Kartones Typical Huge files (>1GB) Lots of rows (+2M) Lots of columns (~1600) XLS/XLSX -> CSV 

@Kartones Typical Stream HTTP downloaded file Stream file between servers Stream data import to DB 

@Kartones Typical 

@Kartones CartoDB-specific Content guessing (e.g. lat/lon) Type guessing Geometry errors fixing Sync tables -> No downtime allowed 

@Kartones DB-Specific Leave DB indexes as last step Prefer big INSERT to multiple UPDATE GDAL’s ogr2ogr > Ruby/Python scripts http://www.gdal.org/ogr2ogr.html 

@Kartones Questions? Thanks! [email protected]