Geospatial CSV Imports Hidden Complexity

@ Mindcamp 7.0 2015, Madrid

Transcript

1. @Kartones GEOSPATIAL CSV IMPORTS HIDDEN COMPLEXITY
2. @Kartones CartoDB
3. @Kartones Agenda 1) CSV Format Issues 2) Import Issues
4. @Kartones CSV FORMAT ISSUES
5. @Kartones Intro .csv / MIME:text/csv Unknown birthdate (80s?) RFC 4180 (2005)
6. @Kartones Intro Plain text Simple format Simple rules
7. @Kartones Usage
8. @Kartones CSV 0101000020E610000000000000008049C000000000000038C0,1083 "alien",2014-11-04 15:24:40.43413+00 category 1, "jump jump up!", {""value"":""es""}
9. @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
10. @Kartones WKB: Well-Known Binary POINT(2.0 4.0) = 000000000140000000000000004010000000000000 https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary
11. @Kartones GeoJSON { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } } http://geojson.org/
12. @Kartones IMPORT ISSUES
13. @Kartones Typical Huge files (>1GB) Lots of rows (+2M) Lots of columns (~1600) XLS/XLSX -> CSV
14. @Kartones Typical Stream HTTP downloaded file Stream file between servers Stream data import to DB
15. @Kartones Typical
16. @Kartones CartoDB-specific Content guessing (e.g. lat/lon) Type guessing Geometry errors fixing Sync tables -> No downtime allowed
17. @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
18. @Kartones Questions? Thanks! kartones@cartodb.com