Difference between revisions of "CartoDB"

From ecology
Jump to: navigation, search
(Created page with "CartoDB is an open source tool that allows for the storage and visualization of geospatial data on the web. See http://www.cartodb.com Our own CartoDB server is at http://cartod...")
 
(Update url)
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
CartoDB is an open source tool that allows for the storage and visualization of geospatial data on the web. See http://www.cartodb.com
 
CartoDB is an open source tool that allows for the storage and visualization of geospatial data on the web. See http://www.cartodb.com
  
−
Our own CartoDB server is at http://cartodb.e-ecology.cloudlet.sara.nl .
+
Our own CartoDB server is at http://cartodb.e-ecology.vm.surfsara.nl .
 
Accounts can be made on request by s.verhoeven@esciencecenter.nl after being OK-ed by Willem Bouten.
 
Accounts can be made on request by s.verhoeven@esciencecenter.nl after being OK-ed by Willem Bouten.
 +
 +
Installation documentation of CartoDB server can be found at https://github.com/NLeSC/eEcology-CartoDB
  
 
== GPS tables ==
 
== GPS tables ==
  
−
The e-ecology gps.ee*limited tables are available on the CartoDB server.
+
The following tables are available on the CartoDB server:
−
The tables available are the same as on https://services.e-ecology.sara.nl/wiki/index.php/DB_Views_2015, but with following changes to tables with a location column:
 
  
−
* Added cartodb_id column, as primary key
+
* gps.ee_species_limited
−
* Added the_geom_webmercator column
+
* gps.ee_individual_limited
−
* Renamed location column to the_geom column
+
* gps.ee_project_limited
 +
* gps.ee_tracker_limited
 +
* gps.ee_track_session_limited
 +
* gps.ee_tracking_speed_limited
 +
* gps.ee_acceleration_limited
 +
* gps.ee_acc_start_limited
 +
* gps.ee_nest_limited
 +
* gps.ee_nest_inhabitant_limited
 +
 
 +
The tables have the same colums as described on [[DB_Views_2015]]
 +
 
 +
Tables with a location column have been made CartoDB compliant by
 +
 
 +
* Adding cartodb_id column, as primary key
 +
* Adding the_geom_webmercator column
 +
* Renaming the location column to the_geom column
 +
 
 +
Only data from the following projects is available on the CartoDB server:
 +
 
 +
* lbbg_texel
 +
* hg_texel
 +
* hb_veluwe
 +
* ef_alegranza
  
−
Only data from the gull projects is available on the CartoDB server.
 
 
The data is not synced.
 
The data is not synced.
 +
 +
== Limitations ==
 +
 +
* Disk space is limited, please do not copy data from gps.* tables to your own account
 +
* Some links will redirect back to http://cartodb.com
 +
* Whenever you see an error or have a support question please don't contact support at cartodb, but contact s.verhoeven@esciencecenter.nl
 +
* Geocoding (eg. converting city name to lat,lon) is not working
 +
* Login with Google+ does not work
 +
* Making visualization of a lot of rows (more than 65000 rows per tile) can be very slow
 +
* Export image does not work. Make screenshot of web page as a workaround
 +
 +
== Create CartoDB map visualization based on eEcology data ==
 +
 +
# Press `NEW MAP` button
 +
# Press `CREATE NEW MAP` button
 +
# Press `CREATE EMPTY MAP` button
 +
# Enter a SQL query in the SQL tab (SQL icon left side of screen)
 +
# Press `Apply query` button
 +
# Switch to `MAP VIEW` to see the map
 +
 +
=== Example queries ===
 +
 +
A time window of a single tracker
 +
 +
<pre>
 +
SELECT *
 +
FROM gps.ee_tracking_speed_limited
 +
WHERE
 +
device_info_serial = 344
 +
AND
 +
date_time BETWEEN '2013-01-01' AND '2014-01-01'
 +
</pre>
 +
 +
Compare 2 species
 +
 +
<pre>
 +
SELECT cartodb_id, the_geom, the_geom_webmercator, date_time, english_name
 +
FROM
 +
gps.ee_species_limited s
 +
JOIN gps.ee_individual_limited i ON s.latin_name = i.species_latin_name
 +
JOIN gps.ee_track_session_limited USING (ring_number)
 +
JOIN gps.ee_tracking_speed_limited USING (device_info_serial)
 +
WHERE
 +
date_time BETWEEN '2015-01-01' AND '2015-06-02'
 +
AND
 +
english_name IN ('Herring Gull', 'Lesser Black-backed Gull')
 +
</pre>
 +
 +
== Share visualizations and datasets ==
 +
 +
Sharing has several different levels:
 +
 +
* public, visualization is listed on your public profile page
 +
* public if url is known
 +
* readable by logged in users
 +
* private

Latest revision as of 13:59, 11 September 2015

CartoDB is an open source tool that allows for the storage and visualization of geospatial data on the web. See http://www.cartodb.com

Our own CartoDB server is at http://cartodb.e-ecology.vm.surfsara.nl . Accounts can be made on request by s.verhoeven@esciencecenter.nl after being OK-ed by Willem Bouten.

Installation documentation of CartoDB server can be found at https://github.com/NLeSC/eEcology-CartoDB

GPS tables

The following tables are available on the CartoDB server:

  • gps.ee_species_limited
  • gps.ee_individual_limited
  • gps.ee_project_limited
  • gps.ee_tracker_limited
  • gps.ee_track_session_limited
  • gps.ee_tracking_speed_limited
  • gps.ee_acceleration_limited
  • gps.ee_acc_start_limited
  • gps.ee_nest_limited
  • gps.ee_nest_inhabitant_limited

The tables have the same colums as described on DB_Views_2015

Tables with a location column have been made CartoDB compliant by

  • Adding cartodb_id column, as primary key
  • Adding the_geom_webmercator column
  • Renaming the location column to the_geom column

Only data from the following projects is available on the CartoDB server:

  • lbbg_texel
  • hg_texel
  • hb_veluwe
  • ef_alegranza

The data is not synced.

Limitations

  • Disk space is limited, please do not copy data from gps.* tables to your own account
  • Some links will redirect back to http://cartodb.com
  • Whenever you see an error or have a support question please don't contact support at cartodb, but contact s.verhoeven@esciencecenter.nl
  • Geocoding (eg. converting city name to lat,lon) is not working
  • Login with Google+ does not work
  • Making visualization of a lot of rows (more than 65000 rows per tile) can be very slow
  • Export image does not work. Make screenshot of web page as a workaround

Create CartoDB map visualization based on eEcology data

  1. Press `NEW MAP` button
  2. Press `CREATE NEW MAP` button
  3. Press `CREATE EMPTY MAP` button
  4. Enter a SQL query in the SQL tab (SQL icon left side of screen)
  5. Press `Apply query` button
  6. Switch to `MAP VIEW` to see the map

Example queries

A time window of a single tracker 

SELECT * 
FROM gps.ee_tracking_speed_limited 
WHERE
device_info_serial = 344
AND
date_time BETWEEN '2013-01-01' AND '2014-01-01'

Compare 2 species 

SELECT cartodb_id, the_geom, the_geom_webmercator, date_time, english_name
FROM
gps.ee_species_limited s
JOIN gps.ee_individual_limited i ON s.latin_name = i.species_latin_name
JOIN gps.ee_track_session_limited USING (ring_number)
JOIN gps.ee_tracking_speed_limited USING (device_info_serial)
WHERE
date_time BETWEEN '2015-01-01' AND '2015-06-02'
AND
english_name IN ('Herring Gull', 'Lesser Black-backed Gull')

Share visualizations and datasets

Sharing has several different levels:

  • public, visualization is listed on your public profile page
  • public if url is known
  • readable by logged in users
  • private
Retrieved from "https://wiki.e-ecology.nl/index.php?title=CartoDB&oldid=1266"