mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+----------------+ | database | size in GB | +--------------------+----------------+ | information_schema | 0.000183105469 | | mysql | 0.007525796071 | | performance_schema | 0.000000000000 | | sector | 0.124526977539 | | sys | 0.000015258789 | +--------------------+----------------+ 5 rows in set (0.02 sec) mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES WHERE table_schema='sector' GROUP BY table_schema; +----------+--------------+ | database | size in MB | +----------+--------------+ | sector | 127.51562500 | +----------+--------------+ 1 row in set (0.00 sec) mysql> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "sector" ORDER BY (data_length+ index_length) DESC; +--------------+-----------+ | Table | Size (MB) | +--------------+-----------+ | Observation | 75.14 | | Measurement | 52.09 | | Sensitivity | 0.14 | | Device | 0.08 | | CoverageLora | 0.03 | | City | 0.02 | | Subscription | 0.02 | +--------------+-----------+ 7 rows in set (0.00 sec) mysql> select id, urn, ST_AsText(location), city_id from Device limit 50; +----+----------------------------------------+--------------------------+---------+ | id | urn | ST_AsText(location) | city_id | +----+----------------------------------------+--------------------------+---------+ | 1 | urn:x-iot:smartsantander:u7jcfa:t3248 | POINT(-3.81165 43.46727) | 1 | | 2 | urn:x-iot:smartsantander:u7jcfa:t10013 | POINT(-3.8001 43.47207) | 1 | | 3 | urn:x-iot:smartsantander:u7jcfa:t513 | POINT(-3.80987 43.464) | 1 | | 4 | urn:x-iot:smartsantander:u7jcfa:t514 | POINT(-3.80939 43.464) | 1 | | 5 | urn:x-iot:smartsantander:u7jcfa:t10020 | POINT(-3.83115 43.46016) | 1 | | 6 | urn:x-iot:smartsantander:u7jcfa:t250 | POINT(-3.80368 43.4622) | 1 | | 7 | urn:x-iot:smartsantander:u7jcfa:t10003 | POINT(-3.80033 43.47191) | 1 | | 8 | urn:x-iot:smartsantander:u7jcfa:t371 | POINT(-3.8107 43.46354) | 1 | | 9 | urn:x-iot:smartsantander:u7jcfa:t277 | POINT(-3.79764 43.46324) | 1 | | 10 | urn:x-iot:smartsantander:u7jcfa:t3247 | POINT(-3.81145 43.46715) | 1 | | 11 | urn:x-iot:smartsantander:u7jcfa:t10012 | POINT(-3.80008 43.472) | 1 | | 12 | urn:x-iot:smartsantander:u7jcfa:t10011 | POINT(-3.80008 43.47194) | 1 | | 13 | urn:x-iot:smartsantander:u7jcfa:t519 | POINT(-3.80885 43.46475) | 1 | | 14 | urn:x-iot:smartsantander:u7jcfa:t594 | POINT(-3.81282 43.45431) | 1 | | 15 | urn:x-iot:smartsantander:u7jcfa:t10004 | POINT(-3.80042 43.47188) | 1 | | 16 | urn:x-iot:smartsantander:u7jcfa:t368 | POINT(-3.81118 43.46352) | 1 | | 17 | urn:x-iot:smartsantander:u7jcfa:t403 | POINT(-3.79711 43.46327) | 1 | | 18 | urn:x-iot:smartsantander:u7jcfa:t397 | POINT(-3.80292 43.4627) | 1 | | 19 | urn:x-iot:smartsantander:u7jcfa:t10009 | POINT(-3.80015 43.47204) | 1 | | 20 | urn:x-iot:smartsantander:u7jcfa:t363 | POINT(-3.8105 43.46327) | 1 | | 21 | urn:x-iot:smartsantander:u7jcfa:t3230 | POINT(-3.81217 43.46769) | 1 | | 22 | urn:x-iot:smartsantander:u7jcfa:t365 | POINT(-3.8105 43.46324) | 1 | | 23 | urn:x-iot:smartsantander:u7jcfa:t10006 | POINT(-3.80026 43.47201) | 1 | | 24 | urn:x-iot:smartsantander:u7jcfa:t276 | POINT(-3.79737 43.46326) | 1 | | 25 | urn:x-iot:smartsantander:u7jcfa:t356 | POINT(-3.80901 43.46293) | 1 | | 26 | urn:x-iot:smartsantander:u7jcfa:t254 | POINT(-3.8028 43.46197) | 1 | | 27 | urn:x-iot:smartsantander:u7jcfa:t609 | POINT(-3.81121 43.45606) | 1 | | 28 | urn:x-iot:smartsantander:u7jcfa:t370 | POINT(-3.81114 43.46367) | 1 | | 29 | urn:x-iot:smartsantander:u7jcfa:t183 | POINT(-3.79634 43.46315) | 1 | | 30 | urn:x-iot:smartsantander:u7jcfa:t2502 | POINT(-3.81021 43.46368) | 1 | | 31 | urn:x-iot:smartsantander:u7jcfa:t256 | POINT(-3.80235 43.46221) | 1 | | 32 | urn:x-iot:smartsantander:u7jcfa:t10001 | POINT(-3.80023 43.47182) | 1 | | 33 | urn:x-iot:smartsantander:u7jcfa:t361 | POINT(-3.8088 43.46334) | 1 | | 34 | urn:x-iot:smartsantander:u7jcfa:t599 | POINT(-3.81211 43.45571) | 1 | | 35 | urn:x-iot:smartsantander:u7jcfa:t3241 | POINT(-3.81178 43.4677) | 1 | | 36 | urn:x-iot:smartsantander:u7jcfa:t374 | POINT(-3.80995 43.46369) | 1 | | 37 | urn:x-iot:smartsantander:u7jcfa:t10000 | POINT(-3.80014 43.47171) | 1 | | 38 | urn:x-iot:smartsantander:u7jcfa:t612 | POINT(-3.80866 43.4584) | 1 | | 39 | urn:x-iot:smartsantander:u7jcfa:t351 | POINT(-3.80938 43.4631) | 1 | | 40 | urn:x-iot:smartsantander:u7jcfa:t518 | POINT(-3.80884 43.46442) | 1 | | 41 | urn:x-iot:smartsantander:u7jcfa:t372 | POINT(-3.81079 43.46366) | 1 | | 42 | urn:x-iot:smartsantander:u7jcfa:t606 | POINT(-3.81316 43.45397) | 1 | | 43 | urn:x-iot:smartsantander:u7jcfa:t249 | POINT(-3.80347 43.46232) | 1 | | 44 | urn:x-iot:smartsantander:u7jcfa:t280 | POINT(-3.79848 43.46308) | 1 | | 45 | urn:x-iot:smartsantander:u7jcfa:t604 | POINT(-3.81019 43.45714) | 1 | | 46 | urn:x-iot:smartsantander:u7jcfa:t453 | POINT(-3.80402 43.46212) | 1 | | 47 | urn:x-iot:smartsantander:u7jcfa:t10002 | POINT(-3.80033 43.47181) | 1 | | 48 | urn:x-iot:smartsantander:u7jcfa:t515 | POINT(-3.80893 43.46399) | 1 | | 49 | urn:x-iot:smartsantander:u7jcfa:t516 | POINT(-3.8088 43.4639) | 1 | | 50 | urn:x-iot:smartsantander:u7jcfa:t367 | POINT(-3.81131 43.46336) | 1 | +----+----------------------------------------+--------------------------+---------+ 50 rows in set (0.01 sec) mysql> select id, device_id, timestamp, ST_AsText(location) from Observation limit 50; +---------+-----------+----------------------------+--------------------------+ | id | device_id | timestamp | ST_AsText(location) | +---------+-----------+----------------------------+--------------------------+ | 4117697 | 22 | 2020-02-19 20:23:08.141431 | POINT(-3.8105 43.46324) | | 4117703 | 1 | 2020-02-19 20:23:18.574884 | POINT(-3.81165 43.46727) | | 4117704 | 44 | 2020-02-19 20:23:18.658657 | POINT(-3.79848 43.46308) | | 4117706 | 50 | 2020-02-19 20:23:24.729112 | POINT(-3.81131 43.46336) | | 4117712 | 1 | 2020-02-19 20:23:37.924805 | POINT(-3.81165 43.46727) | | 4117713 | 154 | 2020-02-19 20:23:14.000000 | POINT(-3.834 43.4552) | | 4117717 | 1 | 2020-02-19 20:23:57.269911 | POINT(-3.81165 43.46727) | | 4117718 | 35 | 2020-02-19 20:23:58.309146 | POINT(-3.81178 43.4677) | | 4117719 | 142 | 2020-02-19 20:23:42.000000 | POINT(-3.80826 43.4615) | | 4117721 | 14 | 2020-02-19 20:24:07.379409 | POINT(-3.81282 43.45431) | | 4117722 | 175 | 2020-02-19 20:24:00.000000 | POINT(-3.83522 43.4647) | | 4117723 | 46 | 2020-02-19 20:24:09.715222 | POINT(-3.80402 43.46212) | | 4117724 | 66 | 2020-02-19 20:24:10.174605 | POINT(-3.81203 43.45517) | | 4117725 | 51 | 2020-02-19 20:24:10.676334 | POINT(-3.81107 43.46369) | | 4117726 | 67 | 2020-02-19 20:24:11.596631 | POINT(-3.80917 43.45778) | | 4117727 | 36 | 2020-02-19 20:24:12.898236 | POINT(-3.80995 43.46369) | | 4117728 | 147 | 2020-02-19 20:23:30.000000 | POINT(-3.83037 43.4575) | | 4117731 | 6 | 2020-02-19 20:24:13.455544 | POINT(-3.80368 43.4622) | | 4117732 | 10 | 2020-02-19 20:24:16.321841 | POINT(-3.81145 43.46715) | | 4117733 | 1 | 2020-02-19 20:24:16.620912 | POINT(-3.81165 43.46727) | | 4117734 | 163 | 2020-02-19 20:23:21.000000 | POINT(-3.79446 43.4721) | | 4117735 | 28 | 2020-02-19 20:24:24.968854 | POINT(-3.81114 43.46367) | | 4117736 | 148 | 2020-02-19 20:23:52.000000 | POINT(-3.85226 43.4603) | | 4117737 | 150 | 2020-02-19 20:23:23.000000 | POINT(-3.77914 43.4712) | | 4117738 | 160 | 2020-02-19 20:23:06.000000 | POINT(-3.80403 43.4617) | | 4117739 | 60 | 2020-02-19 20:24:33.546893 | POINT(-3.81054 43.46368) | | 4117740 | 145 | 2020-02-19 20:23:56.000000 | POINT(-3.85122 43.4553) | | 4117741 | 1 | 2020-02-19 20:24:35.972346 | POINT(-3.81165 43.46727) | | 4117742 | 164 | 2020-02-19 20:23:47.000000 | POINT(-3.86931 43.4574) | | 4117743 | 161 | 2020-02-19 20:23:55.000000 | POINT(-3.83378 43.4551) | | 4117744 | 143 | 2020-02-19 20:24:14.000000 | POINT(-3.8107 43.4594) | | 4117746 | 151 | 2020-02-19 20:24:09.000000 | POINT(-3.8129 43.4618) | | 4117747 | 149 | 2020-02-19 20:24:16.000000 | POINT(-3.84946 43.4609) | | 4117748 | 1 | 2020-02-19 20:24:55.324379 | POINT(-3.81165 43.46727) | | 4117749 | 158 | 2020-02-19 20:24:08.000000 | POINT(-3.83389 43.4551) | | 4117750 | 9 | 2020-02-19 20:25:00.613044 | POINT(-3.79764 43.46324) | | 4117751 | 41 | 2020-02-19 20:25:01.225703 | POINT(-3.81079 43.46366) | | 4117752 | 162 | 2020-02-19 20:23:55.000000 | POINT(-3.81055 43.4617) | | 4117753 | 154 | 2020-02-19 20:24:34.000000 | POINT(-3.83401 43.4552) | | 4117755 | 143 | 2020-02-19 20:24:30.000000 | POINT(-3.8107 43.4594) | | 4117756 | 8 | 2020-02-19 20:25:09.258150 | POINT(-3.8107 43.46354) | | 4117757 | 173 | 2020-02-19 20:23:38.000000 | POINT(-3.83719 43.4573) | | 4117758 | 1 | 2020-02-19 20:25:15.073910 | POINT(-3.81165 43.46727) | | 4117759 | 142 | 2020-02-19 20:25:02.000000 | POINT(-3.8094 43.4615) | | 4117760 | 148 | 2020-02-19 20:24:41.000000 | POINT(-3.85355 43.4602) | | 4117761 | 21 | 2020-02-19 20:25:19.782240 | POINT(-3.81217 43.46769) | | 4117762 | 168 | 2020-02-19 20:24:56.000000 | POINT(-3.83427 43.4556) | | 4117763 | 165 | 2020-02-19 20:24:00.000000 | POINT(-3.9084 43.3912) | | 4117764 | 147 | 2020-02-19 20:24:52.000000 | POINT(-3.83222 43.4565) | | 4117765 | 152 | 2020-02-19 20:24:43.000000 | POINT(-3.80285 43.4619) | +---------+-----------+----------------------------+--------------------------+ 50 rows in set (0.01 sec) mysql> select id, observation_id, phenomenon, value, uom from Measurement limit 50; +---------+----------------+---------------------+---------+---------------+ | id | observation_id | phenomenon | value | uom | +---------+----------------+---------------------+---------+---------------+ | 4117697 | 4117697 | temperature:ambient | 10.900 | degreeCelsius | | 4117703 | 4117703 | temperature:ambient | -39.700 | degreeCelsius | | 4117704 | 4117704 | temperature:ambient | 12.060 | degreeCelsius | | 4117706 | 4117706 | temperature:ambient | 11.930 | degreeCelsius | | 4117712 | 4117712 | temperature:ambient | -39.700 | degreeCelsius | | 4117713 | 4117713 | temperature:ambient | 16.400 | degreeCelsius | | 4117717 | 4117717 | temperature:ambient | -39.700 | degreeCelsius | | 4117718 | 4117718 | temperature:ambient | 11.710 | degreeCelsius | | 4117719 | 4117719 | temperature:ambient | 200.000 | degreeCelsius | | 4117721 | 4117721 | temperature:ambient | 14.320 | degreeCelsius | | 4117722 | 4117722 | temperature:ambient | 18.400 | degreeCelsius | | 4117723 | 4117723 | temperature:ambient | 10.320 | degreeCelsius | | 4117724 | 4117724 | temperature:ambient | 12.000 | degreeCelsius | | 4117725 | 4117725 | temperature:ambient | 11.090 | degreeCelsius | | 4117726 | 4117726 | temperature:ambient | 12.190 | degreeCelsius | | 4117727 | 4117727 | temperature:ambient | 115.160 | degreeCelsius | | 4117728 | 4117728 | temperature:ambient | 13.900 | degreeCelsius | | 4117731 | 4117731 | temperature:ambient | 11.870 | degreeCelsius | | 4117732 | 4117732 | temperature:ambient | 10.640 | degreeCelsius | | 4117733 | 4117733 | temperature:ambient | -39.700 | degreeCelsius | | 4117734 | 4117734 | temperature:ambient | 200.000 | degreeCelsius | | 4117735 | 4117735 | temperature:ambient | 9.290 | degreeCelsius | | 4117736 | 4117736 | temperature:ambient | 200.000 | degreeCelsius | | 4117737 | 4117737 | temperature:ambient | 200.000 | degreeCelsius | | 4117738 | 4117738 | temperature:ambient | 10.800 | degreeCelsius | | 4117739 | 4117739 | temperature:ambient | 11.030 | degreeCelsius | | 4117740 | 4117740 | temperature:ambient | 200.000 | degreeCelsius | | 4117741 | 4117741 | temperature:ambient | -39.700 | degreeCelsius | | 4117742 | 4117742 | temperature:ambient | 10.400 | degreeCelsius | | 4117743 | 4117743 | temperature:ambient | 14.400 | degreeCelsius | | 4117744 | 4117744 | temperature:ambient | 12.000 | degreeCelsius | | 4117746 | 4117746 | temperature:ambient | 12.000 | degreeCelsius | | 4117747 | 4117747 | temperature:ambient | 12.800 | degreeCelsius | | 4117748 | 4117748 | temperature:ambient | -39.700 | degreeCelsius | | 4117749 | 4117749 | temperature:ambient | 14.600 | degreeCelsius | | 4117750 | 4117750 | temperature:ambient | 11.610 | degreeCelsius | | 4117751 | 4117751 | temperature:ambient | -30.250 | degreeCelsius | | 4117752 | 4117752 | temperature:ambient | 17.100 | degreeCelsius | | 4117753 | 4117753 | temperature:ambient | 16.400 | degreeCelsius | | 4117755 | 4117755 | temperature:ambient | 11.900 | degreeCelsius | | 4117756 | 4117756 | temperature:ambient | -48.640 | degreeCelsius | | 4117757 | 4117757 | temperature:ambient | 200.000 | degreeCelsius | | 4117758 | 4117758 | temperature:ambient | -39.700 | degreeCelsius | | 4117759 | 4117759 | temperature:ambient | 200.000 | degreeCelsius | | 4117760 | 4117760 | temperature:ambient | 200.000 | degreeCelsius | | 4117761 | 4117761 | temperature:ambient | 11.650 | degreeCelsius | | 4117762 | 4117762 | temperature:ambient | 200.000 | degreeCelsius | | 4117763 | 4117763 | temperature:ambient | 14.600 | degreeCelsius | | 4117764 | 4117764 | temperature:ambient | 13.800 | degreeCelsius | | 4117765 | 4117765 | temperature:ambient | 10.700 | degreeCelsius | +---------+----------------+---------------------+---------+---------------+ 50 rows in set (0.00 sec)