{"action":"created","issue":{"url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/14","labels_url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/14/labels{/name}","comments_url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/14/comments","events_url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/14/events","html_url":"https://github.com/UCL-ShippingGroup/shipviz/issues/14","id":90989714,"number":14,"title":"Test CartoDB performance","user":{"login":"raquel-ucl","id":10402246,"avatar_url":"https://avatars.githubusercontent.com/u/10402246?v=3","gravatar_id":"","url":"https://api.github.com/users/raquel-ucl","html_url":"https://github.com/raquel-ucl","followers_url":"https://api.github.com/users/raquel-ucl/followers","following_url":"https://api.github.com/users/raquel-ucl/following{/other_user}","gists_url":"https://api.github.com/users/raquel-ucl/gists{/gist_id}","starred_url":"https://api.github.com/users/raquel-ucl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raquel-ucl/subscriptions","organizations_url":"https://api.github.com/users/raquel-ucl/orgs","repos_url":"https://api.github.com/users/raquel-ucl/repos","events_url":"https://api.github.com/users/raquel-ucl/events{/privacy}","received_events_url":"https://api.github.com/users/raquel-ucl/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"raquel-ucl","id":10402246,"avatar_url":"https://avatars.githubusercontent.com/u/10402246?v=3","gravatar_id":"","url":"https://api.github.com/users/raquel-ucl","html_url":"https://github.com/raquel-ucl","followers_url":"https://api.github.com/users/raquel-ucl/followers","following_url":"https://api.github.com/users/raquel-ucl/following{/other_user}","gists_url":"https://api.github.com/users/raquel-ucl/gists{/gist_id}","starred_url":"https://api.github.com/users/raquel-ucl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raquel-ucl/subscriptions","organizations_url":"https://api.github.com/users/raquel-ucl/orgs","repos_url":"https://api.github.com/users/raquel-ucl/repos","events_url":"https://api.github.com/users/raquel-ucl/events{/privacy}","received_events_url":"https://api.github.com/users/raquel-ucl/received_events","type":"User","site_admin":false},"milestone":null,"comments":2,"created_at":"2015-06-25T15:03:19Z","updated_at":"2015-06-25T22:29:32Z","closed_at":null,"body":"Ingest different subsets of ShipViz data and test how long they take to be ingested, what's the maximum size for a file to be ingested through the web GUI and how the size of the DB impacts the data visualisation.\r\nWhat's the biggest set of data (i.e. number of points) we can store with CartoDB being reasonably usable?\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/comments/115418908","html_url":"https://github.com/UCL-ShippingGroup/shipviz/issues/14#issuecomment-115418908","issue_url":"https://api.github.com/repos/UCL-ShippingGroup/shipviz/issues/14","id":115418908,"user":{"login":"raquel-ucl","id":10402246,"avatar_url":"https://avatars.githubusercontent.com/u/10402246?v=3","gravatar_id":"","url":"https://api.github.com/users/raquel-ucl","html_url":"https://github.com/raquel-ucl","followers_url":"https://api.github.com/users/raquel-ucl/followers","following_url":"https://api.github.com/users/raquel-ucl/following{/other_user}","gists_url":"https://api.github.com/users/raquel-ucl/gists{/gist_id}","starred_url":"https://api.github.com/users/raquel-ucl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raquel-ucl/subscriptions","organizations_url":"https://api.github.com/users/raquel-ucl/orgs","repos_url":"https://api.github.com/users/raquel-ucl/repos","events_url":"https://api.github.com/users/raquel-ucl/events{/privacy}","received_events_url":"https://api.github.com/users/raquel-ucl/received_events","type":"User","site_admin":false},"created_at":"2015-06-25T22:29:32Z","updated_at":"2015-06-25T22:29:32Z","body":"The database backup I was given contains _8 months_ of data for around _10K vessels_. It has a total of 200 million points, although some of them are invalid since their location is NULL. The number of valid measurements (i.e. records, i.e positions) is 163 millions out of those mentioned 200 million points, that's 8% invalid records.\r\n\r\nThe whole set of data, as far as I know, is 4 years, and it's around 1 billion points. Taking into account about 8% might be invalid, we still have about 920K points. No GIS visualisation tool can handle gracefully that amount of data in the manner needed for this project with the requested functionality and the amount of time we have.\r\n\r\nAfter testing up to 5 million points in CartoDB, the performance when visualising is a little poor since some data in not displayed when the zoom is too far. Zooming in or filtering out unwanted data helps showing the right content (including the data not displayed in lower levels of zoom), but this can be a limitation for the users. However, CartoDB would immediately solve issues #5 #6 #7 and #10 with little extra effort:\r\n * Data loading interface\r\n * Custom SQL queries\r\n * Heatmaps\r\n * Timeslider\r\n * Various filtering methods and visualisation types\r\n\r\nPlus it also has several extra features that can be of help for studying the data. All in all I think this is a great tool and we should give it a try and spend some time customising or resampling the DB.\r\n\r\nThere's three unsolved questions at this point:\r\n \r\n* Can the DB be reduced or divided so that we handle less data? Since there's different kinds of users, perhaps each kind of user can have a custom DB with the data they are interested in. Is there any record of the use cases this project is trying to tackle? I remember a diagram with the different groups of users in this project. Perhaps that'd be useful to have.\r\n\r\n* I need a Ubuntu server available to be installed at UCL Institute of Energy with at least 8GB RAM. It will host the DB and CartoDB for different types of users. I need to find out how to configure it for external users to access without compromising the data. Note other alternatives like GeoServer+LeafLet would pose the same problem. QGIS wouldn't, though, since it's a Desktop tool that connects to any local PostGIS DB.\r\n\r\n* Still need to find out if QGIS or GeoServer+LeafLet would perform better, but I'm aware of the project time constraints and I might leave the GeoServer+LeafLet option out and focus on QGIS vs CartoDB.\r\n\r\n\r\n\r\n\r\n"}}