Skip to Content.
Sympa Menu

freegeocz - [FreeGeoCZ] statistiky prostorových dat

freegeocz AT fsv.cvut.cz

Předmět: Svobodná geoinformační infrastruktura

List archive

[FreeGeoCZ] statistiky prostorových dat


Chronological Podle vláken 
  • From: "Jan Helebrant" <JHelebrant AT seznam.cz>
  • To: <freegeocz AT fsv.cvut.cz>
  • Subject: [FreeGeoCZ] statistiky prostorových dat
  • Date: Sun, 13 Nov 2016 16:29:01 +0100 (CET)
  • List-archive: <http://mailman.fsv.cvut.cz/pipermail/freegeocz>
  • List-id: Svobodná geoinformační infrastruktura <freegeocz.fsv.cvut.cz>

Ahojky,
mám tu databázi - cca 7 GB CSV soubor (vzorek dat v příloze) z
https://api.safecast.org/system/measurements.tar.gz

Rád bych z toho udělal poměrně podrobné vyhodnocení dat.
Část z toho jsou prostorové operace, zbytek by se eventuelně mohl provést jinde, pokud datům přiřadím kódy zemí podle polohy bodů..

Hranice bych asi vzal z Natural Earth - Admin 0 – Countries:
http://www.naturalearthdata.com/downloads/50m-cultural-vectors/

nebo z OSM boundaries - i když tam bych to asi musel vzít po jednotlivých zemích:
https://osm.wno-edv-service.de/boundaries/
pokud to nejde vyexportovat jednodušeji...

Rád bych získal tato data:

1) počet nahraných bodů dle "Uploaded Time" pro určité dané časové úseky - konkrétní rok, nebo měsíc
2) statistiky pro jednotlivé země, nebo kontinenty
3) základní statistiky typu medián, aritmetický průměr, min, max pro pole "Value" přičemž budu muset nejdřív z DB odstranit data, kde "Unit" je "Celsius" a ne "CPM"
4) počet unikátních zařízení ("Device ID") pro jednotlivé země

Stačí mi číselný výstup, grafy si z toho pak klidně udělám v Calcu nebo QTIPlotu...

Jak byste to udělali vy? Mám to celé naimportovat do PostGIS DB spolu s těmi hranicemi a celé to udělat tam, nebo je vhodnější si jen podle vrstvy hranic přiřadit kódy jednotlivých zemí a pak to spíš zpracovat v nějakém statistickém softu? Kolega umí v R-ku, tak by se třeba část analýzy dala udělat tam...

dík

Honza




=
Captured Time,Latitude,Longitude,Value,Unit,Location Name,Device ID,MD5Sum,Height,Surface,Radiation,Uploaded Time,Loader ID
2020-02-03 17:00:00,37.507551666667,139.94117,72,cpm,,,6449bbf7ce3b30a8e05bc23a0bc40644,,,,2020-02-03 17:00:00,633
2020-02-03 11:00:00,37.50725,139.94,55,cpm,,,2fa8bccef282796bcdc297679c4db5b3,,,,2020-02-03 11:00:00,614
2020-02-03 11:00:00,37.505445,0.016666666666667,68,cpm,,,a166df14f60b61095693684fc0f89c54,,,,2020-02-03 11:00:00,614
2020-02-01 03:00:00,34.066486666667,-118.89521666667,50,cpm,,,da79c21520d3ff3f5ed010a70f4a6d29,,,,2020-02-01 03:00:00,507
2020-02-01 01:00:00,37.673233333333,140.06666666667,48,cpm,,,6cdf740a2304a850185f74c0c4a4b878,,,,2020-02-01 01:00:00,504
2020-02-01 01:00:00,37.674781666667,140.079895,52,cpm,,,6666348e83cbf76e3508f473c302df77,,,,2020-02-01 01:00:00,504
2020-01-12 19:00:00,37.737123333333,140.72685333333,454,cpm,,,42482c7028b1d843e2565b6c452057be,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.53961,140.11657666667,47,cpm,,,e6f1f188b207a47d5482a12a1494d694,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.754973333333,140.68766,384,cpm,,,4f1c5c2ab9c04f7c19f8060e07db4686,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.526595,139.93166666667,59,cpm,,,d8f99c989a46e3f41a0285f7ac9e8a96,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.75588,140.70341166667,463,cpm,,,29ab5e66f6bcd461f61db388616c3f39,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.550586666667,139.95,44,cpm,,,556541be00f1251e411dc4909c04b4a7,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.747668333333,140.707195,579,cpm,,,9f17d9c657291f929e85f8a6e45c9959,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.517901666667,139.92547833333,59,cpm,,,5399edc68f223aba0e651fd5c8bdd933,,,,2020-01-12 19:00:00,461
2020-01-12 19:00:00,37.737156666667,140.72683166667,441,cpm,,,bf708a03ba34b20b27890f90f5a14024,,,,2020-01-12 19:00:00,461
2020-01-12 14:00:00,37.552841666667,139.95877333333,62,cpm,,,f3bf949f96b6cca15463606969e72edd,,,,2020-01-12 14:00:00,456
2020-01-12 12:00:00,37.537458333333,139.95942833333,56,cpm,,,13312da8f50bfbb3bf1cbbc1941821e6,,,,2020-01-12 12:00:00,446
2020-01-12 09:00:00,37.539836666667,140.02078,62,cpm,,,3a7da957e3937913d1c5ee582e9beb4c,,,,2020-01-12 09:00:00,461
2020-01-12 09:00:00,37.737141666667,140.72682833333,430,cpm,,,d6528af9f739cc1f31bc5515a73d8194,,,,2020-01-12 09:00:00,461
2020-01-12 04:00:00,37.554983333333,139.95203,50,cpm,,,22fb5ba33ff448f98d6b2dc3f752bf1e,,,,2020-01-12 04:00:00,456
2016-11-04 01:00:07,37.4932,139.93305,38,cpm,,101,e40768be06169b47c412cdbc8b50ee51,,,,2016-11-04 01:00:07.805856,
2016-11-04 01:00:03,37.366713,140.53097,27,celcius,,100079,0654275d9d656aac8f1ee06eaf2ca8d0,462,,,2016-11-04 01:00:03.691745,
2016-11-04 01:00:02,37.366713,140.53097,11,cpm,,100072,b48a5a4b821d04d2071d233d99fc9bf5,462,,,2016-11-04 01:00:03.149019,
2016-11-04 01:00:01,37.366713,140.53097,56,cpm,,100071,654c26d9ad16aa776613db4913a6ee95,462,,,2016-11-04 01:00:01.932265,
2016-11-04 00:59:59,37.659,140.459,15,cpm,,100012,1bfef5dfa9f6a314b38ec53dc680f02d,,,,2016-11-04 00:59:59.143228,
2016-11-04 00:59:58,37.659,140.459,42,cpm,,100011,b9d8b2f48ea341add2f23340d99517db,,,,2016-11-04 00:59:58.653269,
2016-11-04 00:59:49,47.612268,-122.345039,25.2,celcius,,200079,b13c76578da986ee1338a91ef6aafdea,36,,,2016-11-04 00:59:49.24526,
2016-11-04 00:59:48,47.612268,-122.345039,8,cpm,,200072,27bf74e31881db9fa707b0429c0e4a2c,36,,,2016-11-04 00:59:48.886355,
2016-11-04 00:59:48,47.612268,-122.345039,24,cpm,,200071,32dedb292a20314d55aceef1e8881aa3,36,,,2016-11-04 00:59:48.560729,
2016-11-04 00:59:40,34.56,140.8,114.6,cpm,,80,18b26f751871dd91850d9d7a395f5180,,,,2016-11-04 00:59:40.364533,
2016-11-04 00:59:34,20.9109,-156.2887,29,cpm,,79,2dab27f91874b00ca742d1b2ac57c80f,,,,2016-11-04 00:59:34.855565,
2016-11-04 00:59:28,42.565013,-70.783765,25.6,celcius,,100239,5d5b7657081f9473fd41d82a099b708f,10,,,2016-11-04 00:59:29.376469,
2016-11-04 00:59:29,35.6617,139.742,27,celcius,,100149,7e9a30116ded1e6e3d50cdc89884702c,100,,,2016-11-04 00:59:29.299735,
2016-11-04 00:59:28,35.6617,139.742,12,cpm,,100142,1d49179ecd09e93f25df2d163b48a100,100,,,2016-11-04 00:59:28.835575,
2016-11-04 00:59:28,42.565013,-70.783765,11,cpm,,100232,446c925e4c8673e8f0d0aac14d795c7e,10,,,2016-11-04 00:59:28.666932,
2016-11-04 00:59:28,35.6617,139.742,31,cpm,,100141,a26504d2ecb7c140f5a6c1abe72b06ba,100,,,2016-11-04 00:59:28.391199,
2016-11-04 00:59:28,42.565013,-70.783765,29,cpm,,100231,edc553bd514c9f55bf12e21d81340bd5,10,,,2016-11-04 00:59:28.224507,
2016-11-04 00:59:20.89,37.442836,-122.128094,15,cpm,,106,318f70bc43938491be2f5d013770947b,,,,2016-11-04 00:59:21.234147,
2016-11-04 00:59:05.89,37.442836,-122.128094,24,cpm,,107,2371df56cd384830898796c98c55828f,,,,2016-11-04 00:59:06.232875,
2016-11-04 00:59:05,37.5635,140.9917,17,celcius,,48,a5b916f9b0d0b3c4fb87153a4c521aaa,5,,,2016-11-04 00:59:05.213965,
2016-11-04 00:59:04,37.5635,140.9917,0,cpm,,0,19de1b42148bd04476b04b370eabe7d7,5,,,2016-11-04 00:59:04.513859,
2016-11-04 00:59:03,37.5635,140.9917,0,cpm,,40,2a447e0691585aab5d430a016d438877,5,,,2016-11-04 00:59:03.757783,
2016-11-04 00:59:01,42.812292,-70.872505,35,cpm,,99,9edad30a23c886efd16b8a884409215b,,,,2016-11-04 00:59:01.257157,
2016-11-04 00:59:00,38.3151,-123.0752,86,cpm,,78,c8bac5a5597ccd0888fc8ddddcdd9d60,,,,2016-11-04 00:59:01.164709,
2016-11-04 00:58:37,34.995197,135.764331,42,cpm,,74,04b355813ef4e0a26895680663fa7226,,,,2016-11-04 00:58:37.902937,
2016-11-04 00:58:32,37.64444,140.797711,20,celcius,,100179,c8bbdd3b4f89d40d0e396ae554adb018,426,,,2016-11-04 00:58:32.473828,
2016-11-04 00:58:31,37.64444,140.797711,44,cpm,,100172,052df72791b1219609b5fa7dcc330e00,426,,,2016-11-04 00:58:31.974741,
2016-11-04 00:58:31,37.64444,140.797711,128,cpm,,100171,2214700762abd0bd5be7f36f0b7c3b30,426,,,2016-11-04 00:58:31.446916,
2016-11-04 00:58:21,42.381242,-71.111946,23.2,celcius,,200099,199db92d76b52b506bbec232408eb512,5,,,2016-11-04 00:58:21.57787,
2016-11-04 00:58:20,42.381242,-71.111946,10,cpm,,200092,e62125fcf59fdce1dd79e25f1ce4ca7c,5,,,2016-11-04 00:58:21.09446,
2016-11-04 00:58:20,42.381242,-71.111946,32,cpm,,200091,24a7b14cabc67dc25d33aabda0e30612,5,,,2016-11-04 00:58:20.219414,
2016-11-04 00:58:17,35.26828,136.2493,26.5,status,,1009,de89cf80a33aae05a90008b137895283,100,,,2016-11-04 00:58:17.485131,
2016-11-04 00:58:11,35.26828,136.2493,16,cpm,,1002,06ca337a4fb1b7e15e2a65d219dbf5d9,100,,,2016-11-04 00:58:11.958368,
2016-11-04 00:58:06,35.26828,136.2493,0,cpm,,1001,1816448c4e86aecc91a2f0264e04c57a,100,,,2016-11-04 00:58:06.37751,
2016-11-04 00:58:04,36.0438388,140.2153596,16,cpm,,63,8236b1c2b4e0af6b353dda8c8c3f6664,,,,2016-11-04 00:58:04.188913,
2016-11-04 00:57:46,46.524491,6.630666,15,cpm,,54,fc878fcfe8e823ce99e8b547576c4f71,,,,2016-11-04 00:57:46.695191,
2016-11-04 00:57:43,35.659491,139.72785,23,cpm,,100022,b066118783f903639de87481a0dea2ec,92,,,2016-11-04 00:57:43.943175,
2016-11-04 00:57:43,35.659491,139.72785,8,cpm,,100021,093b466959423c662e9e2bcdfa285c44,92,,,2016-11-04 00:57:43.455146,
2016-11-04 00:56:55,35.615814,139.625711,24.5,celcius,,100189,16b0c2d37f05b6f81f88ee3fc608a27d,24,,,2016-11-04 00:56:55.357962,
2016-11-04 00:56:50,35.615814,139.625711,17,cpm,,100182,12f776c31a9809c1980a0e581f90cca8,24,,,2016-11-04 00:56:50.269944,
2016-11-04 00:56:45,35.615814,139.625711,49,cpm,,100181,c2fc917e66d41ffc05d0909d367ae906,24,,,2016-11-04 00:56:45.539191,
2016-11-04 00:56:38,31.833193,130.301922,18.8,celcius,,100229,df63476545e22b725040eaace32aab7c,13,,,2016-11-04 00:56:38.306605,
2016-11-04 00:56:37,31.833193,130.301922,14,cpm,,100222,9c967580d75abeb50e67857781d7625f,13,,,2016-11-04 00:56:37.752601,
2016-11-04 00:56:36,31.833193,130.301922,41,cpm,,100221,9d87fcdeb2b929069133771f5c6cfc37,13,,,2016-11-04 00:56:37.153059,
2016-11-04 00:56:06,34.4883,136.165,15.6,status,,100039,95fda12b12e2fd3c9c49ea890ccec9fb,535,,,2016-11-04 00:56:06.44492,
2016-11-04 00:56:01,34.4883,136.165,14,cpm,,100032,1c339b8ae45a4c803099b84ac1fe275c,535,,,2016-11-04 00:56:01.248442,
2016-11-04 00:55:53,34.4883,136.165,52,cpm,,100031,cce5a116412ba636a469167eb0e9f644,535,,,2016-11-04 00:55:53.987599,
2016-11-04 00:55:52,37.563007,140.995446,20,celcius,,100089,a827d87218e16bd34b4c0a2990a57c9a,3,,,2016-11-04 00:55:52.941242,
2016-11-04 00:55:52,37.0105,140.9253,16,cpm,,100102,b7e96b7f0571b6c0ee25daa875518f83,100,,,2016-11-04 00:55:52.380452,
2016-11-04 00:55:52,37.563007,140.995446,16,cpm,,100082,c4293a79e2ba50b048b812b7c7493560,3,,,2016-11-04 00:55:52.362097,
2016-11-04 00:55:51,37.0105,140.9253,43,cpm,,100101,10b49f4e943107c5a93c26810d61ef6a,100,,,2016-11-04 00:55:51.945701,
2016-11-04 00:55:51,37.563007,140.995446,50,cpm,,100081,c6cb41685481686070db042baae09370,3,,,2016-11-04 00:55:51.780247,
2016-11-04 00:55:49,34.121726,-118.269582,37,cpm,,61,1cad585f43f7e4ffaeab14334f5f8fb6,,,,2016-11-04 00:55:49.953399,
2016-11-04 00:55:49,37.54562,140.398995,28,celcius,,100249,4bdd8085f8671648f23560bfe1dd2130,238,,,2016-11-04 00:55:49.240911,
2016-11-04 00:55:47,37.54562,140.398995,18,cpm,,100242,d0842229934600fe1d877d7040735fab,238,,,2016-11-04 00:55:48.104943,
2016-11-04 00:55:47,37.54562,140.398995,0,cpm,,100241,bb841901531e28e533ccb4f4c950fa18,238,,,2016-11-04 00:55:47.521359,
2016-11-04 00:55:29,25.0439,121.529,44,cpm,,75,843f2e36381dbcf8a27af2c56d36d1fb,,,,2016-11-04 00:55:29.558923,
2016-11-04 00:55:07,37.4932,139.93305,36,cpm,,101,af724682ae30ddb775a37d15a6785fae,,,,2016-11-04 00:55:07.626516,
2016-11-04 00:55:00,37.366713,140.53097,27,celcius,,100079,13143b8bfc3f01927b7eabbff6696d05,462,,,2016-11-04 00:55:00.209997,
2016-11-04 00:54:59,37.366713,140.53097,23,cpm,,100072,229b5294fad3ba10d1af60851b50d5c9,462,,,2016-11-04 00:54:59.644102,
2016-11-04 00:54:58,37.366713,140.53097,58,cpm,,100071,021e2c4efb976db28ed9880b81f1f07c,462,,,2016-11-04 00:54:59.040031,
2016-11-04 00:54:56,37.659,140.459,14,cpm,,100012,a6011f0c7e35f045ab0f28288faec9b4,,,,2016-11-04 00:54:56.266286,
2016-11-04 00:54:55,37.659,140.459,35,cpm,,100011,97e491e93124fda9b918fafa3879d103,,,,2016-11-04 00:54:55.832172,
2016-11-04 00:54:47,34.56,140.8,107.6,cpm,,80,45de7f710786b48345766db4b78e8484,,,,2016-11-04 00:54:47.852677,
2016-11-04 00:54:47,47.612268,-122.345039,25.2,celcius,,200079,75df69de33ae7255f8a4690ce6386dc3,36,,,2016-11-04 00:54:47.365874,
2016-11-04 00:54:46,47.612268,-122.345039,12,cpm,,200072,2e1611d2079a86eeeb30bc228b645b90,36,,,2016-11-04 00:54:46.940616,
2016-11-04 00:54:46,47.612268,-122.345039,26,cpm,,200071,27480ead06a752e9df5151295d4de969,36,,,2016-11-04 00:54:46.601281,
2016-11-04 00:54:34,20.9109,-156.2887,27,cpm,,79,6384bd32bfeb14b6e0c8e14acb9747a1,,,,2016-11-04 00:54:34.862745,
2016-11-04 00:54:25,35.6617,139.742,27,celcius,,100149,b19b937fc23c3b3de7c45c2a64f05b00,100,,,2016-11-04 00:54:25.938441,
2016-11-04 00:54:25,42.565013,-70.783765,25.6,celcius,,100239,90b5efb0fd33b6905fc68091fc533640,10,,,2016-11-04 00:54:25.927203,
2016-11-04 00:54:25,35.6617,139.742,16,cpm,,100142,a8fb83dc026f09507ebc89924bfca8b8,100,,,2016-11-04 00:54:25.499335,
2016-11-04 00:54:25,42.565013,-70.783765,10,cpm,,100232,bbe71dc38a5ab59463542d2feb6db76c,10,,,2016-11-04 00:54:25.450373,
2016-11-04 00:54:24,42.565013,-70.783765,25,cpm,,100231,89239644e7ac3ea3ac3ab6073afb8c8e,10,,,2016-11-04 00:54:24.962957,
2016-11-04 00:54:24,35.6617,139.742,31,cpm,,100141,aef301aa5b7f9daf2aea171df781ae15,100,,,2016-11-04 00:54:24.79355,
2016-11-04 00:54:05.862,37.442836,-122.128094,6,cpm,,106,c13b19591b72c123c8289c6465862f91,,,,2016-11-04 00:54:06.15633,
2016-11-04 00:54:01,38.3151,-123.0752,82,cpm,,78,7a448e9b5d3da3d83bdae028344fb1d6,,,,2016-11-04 00:54:01.360727,
2016-11-04 00:54:01,42.812292,-70.872505,38,cpm,,99,9187ac390d0b008c9fd04d2499dcaefa,,,,2016-11-04 00:54:01.353148,
2016-11-04 00:54:00,37.5635,140.9917,17,celcius,,48,515636999282fdc6f9ff00beddef5b2b,5,,,2016-11-04 00:54:01.068282,
2016-11-04 00:54:00,37.5635,140.9917,0,cpm,,0,c03b25cdf121f8a924c0e956f4dced93,5,,,2016-11-04 00:54:00.377734,
2016-11-04 00:53:59,37.5635,140.9917,0,cpm,,40,e43d440f4cd06a062d8b14caa8814094,5,,,2016-11-04 00:53:59.629188,
2016-11-04 00:53:50.862,37.442836,-122.128094,36,cpm,,107,ff573200d9b332cf0c73ea3b6c8a6f50,,,,2016-11-04 00:53:51.184713,
2016-11-04 00:53:37,34.995197,135.764331,40,cpm,,74,27a00ebd32acf52af9cd016d6d88ee31,,,,2016-11-04 00:53:37.951245,
2016-11-04 00:53:28,37.64444,140.797711,20,celcius,,100179,d35d7fbc5fe0a28fdf170c430eca9bd5,426,,,2016-11-04 00:53:28.942425,
2016-11-04 00:53:28,37.64444,140.797711,49,cpm,,100172,d06ce8ec48126c7c34e2d668de6fb5f9,426,,,2016-11-04 00:53:28.394258,
2016-11-04 00:53:27,37.64444,140.797711,136,cpm,,100171,9e8811d5c176a7d2edcf4481d359b40b,426,,,2016-11-04 00:53:27.881272,
2016-11-04 00:53:18,42.381242,-71.111946,23.2,celcius,,200099,8b96749f20b12081a7812d683fe2c76d,5,,,2016-11-04 00:53:18.857628,
2016-11-04 00:53:18,42.381242,-71.111946,11,cpm,,200092,d3107c1f4e8192d7b8f8d584b6dd618a,5,,,2016-11-04 00:53:18.340667,
2016-11-04 00:53:17,42.381242,-71.111946,31,cpm,,200091,d679d7bd72d8a2b010cc50274526d702,5,,,2016-11-04 00:53:17.842271,
2016-11-04 00:53:03,36.0438388,140.2153596,17,cpm,,63,60f033496367119faaeac01f5a8d0529,,,,2016-11-04 00:53:04.1359,
2016-11-04 00:52:59,35.26828,136.2493,26.4,status,,1009,ef35224c86e2d87588d9f65aa9c4b3a3,100,,,2016-11-04 00:52:59.514451,
2016-11-04 00:52:53,35.26828,136.2493,16,cpm,,1002,81f11d40541e1ecc35b14d9be96f63fc,100,,,2016-11-04 00:52:53.598173,
2016-11-04 00:52:47,35.26828,136.2493,0,cpm,,1001,4fb97727bb2a4ce543a896d3efbc06a6,100,,,2016-11-04 00:52:48.031755,




Archivace běží na MHonArc 2.6.19+.

Top of Page