Technology – Page 2 – Dušan Mulac

Browsing the Slovenian vehicle database

Slovenian “National Interoperability Framework” (NIO) published a database with data on every registered vehicle in Slovenia, so I decided to do some fun queries. (Query results are in Slovene, but i will try to translate the most interesting ones)

How many registered vehicles are in Slovenia?

+----------+
| count(*) |
+----------+
|  1516719 |
+----------+

What about different vehicle types?

+-------------------------------------+----------+
| J-Kategorija in vrsta vozila (opis) | count(*) |
+-------------------------------------+----------+
| osebni avtomobil                    |  1148673 | #(personal) cars
| traktor                             |   109677 | #tractors
| tovorno vozilo                      |    86719 | #trucks
| motorno kolo                        |    54778 | #motorcycles
| priklopno vozilo                    |    37240 | #trailers
| kolo z motorjem                     |    32215 | #motorized bicycles
| vlečno vozilo                       |    12266 |
| moped-dvokolo                       |    10833 |
| traktorski priklopnik               |     7252 |
| delovno vozilo                      |     6394 |
| štirikolo                           |     5391 |
| avtobus                             |     2687 |
| trikolo                             |     1471 |
| lahko štirikolo                     |      828 |
| Neznana vrednost                    |      235 |
| moped-trikolo                       |       19 |
| motorno kolo z visoko močjo         |       19 |
| kolo na motorni pogon               |        7 |
| dvokolesni moped                    |        6 |
| motorno kolo z nizko močjo          |        4 |
| motorno kolo s srednjo močjo        |        2 |
| gosenični traktor                   |        2 |
| NULL                                |        1 |
+-------------------------------------+----------+

Common manufacturers (cars only):

+-----------------------------+----------+
| D1-Znamka                   | count(*) |
+-----------------------------+----------+
| RENAULT                     |   195620 |
| VOLKSWAGEN                  |   167546 |
| OPEL                        |    90443 |
| CITROEN                     |    80923 |
| PEUGEOT                     |    72295 |
| FORD                        |    60756 |
| FIAT                        |    47954 |
| AUDI                        |    46237 |
| ŠKODA                       |    43864 |
| BMW                         |    37414 |
| HYUNDAI                     |    36942 |
| TOYOTA                      |    33153 |
| SEAT                        |    27598 |
| KIA                         |    26660 |
| MERCEDES BENZ               |    22120 |
| NISSAN                      |    20200 |
| MAZDA                       |    17181 |
| HONDA                       |    16269 |
| CHEVROLET                   |    14401 |
| SUZUKI                      |    13145 |
| VOLVO                       |    11045 |
| MERCEDES-BENZ               |     8233 |
| ALFA ROMEO                  |     8202 |
| DACIA                       |     7578 |
| MITSUBISHI                  |     7189 |
| DAEWOO                      |     7088 |
| SUBARU                      |     3546 |
| LAND ROVER                  |     2256 |
| LANCIA                      |     2248 |
| MINI                        |     1986 |
| ZASTAVA                     |     1770 |
| JEEP                        |     1498 |
| ROVER                       |     1438 |
| CHRYSLER                    |     1277 |
| SAAB                        |     1070 |
| SMART                       |     1051 |
| PORSCHE                     |      980 |
| UNIS                        |      963 |
| DAIHATSU                    |      789 |
| LADA                        |      611 |
| JAGUAR                      |      608 |
| LEXUS                       |      570 |
| SSANGYONG                   |      567 |
| VOLKSWAGEN, VW              |      461 |
| PROTON                      |      408 |
| FIAT-ADRIA                  |      400 |
| IMV                         |      380 |
| MCC                         |      349 |
| ADRIA                       |      292 |
| DODGE                       |      251 |
| CIMOS                       |      202 |
| MARUTI                      |      183 |
| AUSTIN                      |      113 |
| MG                          |      100 |
| IVECO                       |       96 |
| HYMER                       |       77 |
| IDA                         |       68 |
| CADILLAC                    |       65 |
| STEYR DAIMLER PUCH          |       60 |
| PUCH                        |       53 |
| TRIUMPH                     |       53 |
| SUN LIVING                  |       52 |
| WILLYS                      |       51 |
| MASERATI                    |       50 |
| BMW I                       |       49 |
| HUMMER                      |       47 |
| MAHINDRA                    |       46 |
| PONTIAC                     |       43 |
| FERRARI                     |       42 |
| TRABANT                     |       40 |
| TESLA                       |       37 |
| FORD-CHALLENGER             |       36 |
| AUTOCARAVANS RIMOR          |       36 |
| UAZ                         |       35 |
| CAPRON                      |       34 |
| NSU                         |       33 |
| TAM                         |       32 |
| RENAULT-ADRIA               |       31 |
| FSM                         |       29 |
| QUATTRO                     |       29 |
| CARTHAGO                    |       28 |
| ELNAGH                      |       27 |
| WARTBURG                    |       27 |
| INFINITI                    |       27 |
| BENIMAR                     |       27 |
| DETHLEFFS                   |       24 |
| TRIGANO                     |       23 |
| CHALLENGER                  |       22 |
| SIMCA                       |       21 |
| LINCOLN                     |       21 |
| KNAUS                       |       19 |
| DS                          |       19 |
| STEYR-PUCH                  |       19 |
| AUTOBIANCHI                 |       17 |
| GM DAEWOO                   |       17 |
| BUICK                       |       16 |
| SEA                         |       16 |
| PIAGGIO                     |       15 |
| ASTON MARTIN                |       15 |
| FIAT-JOINT                  |       14 |
| DANGEL                      |       14 |
| LOTUS                       |       13 |
| TATA                        |       13 |
| LAIKA                       |       12 |
| ISUZU                       |       12 |
| MORRIS                      |       12 |
| BENTLEY                     |       12 |
| STEYR-DAIMLER               |       12 |
| VAUXHALL                    |       11 |
| DAIMLER-ADRIA               |       11 |
| SHUANGHUAN                  |       11 |
| GMC                         |       11 |
| ROLLS ROYCE                 |       11 |
| HYNDAI                      |       10 |
| FIAT-ADRIA MOBIL            |       10 |
| DKW                         |       10 |
| INNOCENTI                   |        9 |
| OLDSMOBILE                  |        9 |
| VAZ                         |        9 |
| TALBOT                      |        9 |
| MOBILVETTA                  |        9 |
| EURA MOBIL                  |        8 |
| DAIMLER BENZ                |        8 |
| DAIMLERCHRYSLER             |        8 |
| WEINSBERG                   |        8 |
| ROLLER TEAM                 |        8 |
| RAPIDO                      |        8 |
| GM DAEWOO AUTO&TECHN        |        7 |
| GIOTTILINE                  |        7 |
| MORGAN                      |        7 |
| BUERSTNER GMBH              |        7 |
| CARAVANS INTERNATIONAL      |        6 |
| BUERSTNER                   |        6 |
| STEYR-DAIMLER-PUCH          |        6 |
| MCLOUIS                     |        6 |
| FORD-CHAUSSON               |        6 |
| HOBBY                       |        5 |
| PLYMOUTH                    |        5 |
| DAF                         |        5 |
| WESTFALIA                   |        5 |
| FIAT-CARTHAGO               |        5 |
| SUN ROLLER                  |        5 |
| BURSTNER                    |        5 |
| FIAT-TRIGANO                |        5 |
| AUTO UNION-DKW              |        5 |
| LUAZ                        |        5 |
| BEDFORD                     |        5 |
| PLA                         |        5 |
| SUNBEAM                     |        5 |
| CARAVAN INTERNATIONAL       |        5 |
| ARO                         |        4 |
| PANTHER                     |        4 |
| TATRA                       |        4 |
| ROLLS-ROYCE                 |        4 |
| DAIMLER                     |        4 |
| BERTONE                     |        4 |
| LAMBORGHINI                 |        4 |
| RIMOR                       |        4 |
| MC LOUIS                    |        4 |
| ELNAGH MARLIN               |        4 |
| BRAVIA                      |        4 |
| T.E.C.                      |        4 |
| JOINT                       |        3 |
| FIAT-CHAUSSON               |        3 |
| ASIA                        |        3 |
| ABARTH                      |        3 |
| ARCA                        |        3 |
| AUSTIN HEALEY               |        3 |
| BMW ALPINA                  |        3 |
| ALFA- ROMEO                 |        3 |
| RELIANT                     |        2 |
| BORGWARD                    |        2 |
| SCION                       |        2 |
| ADRIA CARAVAN               |        2 |
| FIAT-LAIKA                  |        2 |
| AERO                        |        2 |
| FIAT ARCA                   |        2 |
| GREAT WALL                  |        2 |
| TOMOS-CITROEN               |        2 |
| HYMERMOBIL                  |        2 |
| AUTO-UNION                  |        2 |
| ZAZ                         |        2 |
| CONCORDE                    |        2 |
| MERCURY                     |        2 |
| BINZ                        |        2 |
| JBA                         |        2 |
| MATRA SIMCA                 |        2 |
| KTM                         |        2 |
| CHRYSLER-SIMCA              |        2 |
| FSO                         |        2 |
| A.C. CARS                   |        1 |
| ALFA-ROMEO                  |        1 |
| CHAUSSON                    |        1 |
| PHOENIX                     |        1 |
| DONNET ZEDEL                |        1 |
| FIAT-AUTOCARAVANS RIMOR     |        1 |
| STEYER-PUCH                 |        1 |
| CALVY MITCHEL               |        1 |
| LMC                         |        1 |
| GREAT WALL MOTOR            |        1 |
| HUPMOBILE                   |        1 |
| PORCSHE                     |        1 |
| AC COBRA LTD                |        1 |
| BSA                         |        1 |
| CATERHAM                    |        1 |
| TOMOS CITROEN               |        1 |
| LDV                         |        1 |
| FORD-GB                     |        1 |
| LAFER                       |        1 |
| ITASCA                      |        1 |
| KUTSENITS                   |        1 |
| SHELBY                      |        1 |
| IVECO-LAIKA                 |        1 |
| ACURA                       |        1 |
| VOLKSWAGWN                  |        1 |
| GLAS-ISARIA                 |        1 |
| IRENAULT                    |        1 |
| STEYR-FIAT                  |        1 |
| API                         |        1 |
| VOLKSVAGEN                  |        1 |
| DAIMLER-LA STRADA           |        1 |
| JEPP                        |        1 |
| K1                          |        1 |
| AMC                         |        1 |
| V0LKSWAGEN                  |        1 |
| BOMBARDIER                  |        1 |
| LASTNA IZDELAVA             |        1 |
| FERGUSON                    |        1 |
| AHORN                       |        1 |
| GAZELLE                     |        1 |
| ROSENGART                   |        1 |
| OVERLAND                    |        1 |
| FORD-D                      |        1 |
| MOSKVICH                    |        1 |
| WILLIS                      |        1 |
| CIMOS CITROEN               |        1 |
| ROVER RH(600)               |        1 |
| VOLKWAGEN                   |        1 |
| VOLSKWAGEN                  |        1 |
| BUGATTI                     |        1 |
| LEA-FRANCIS                 |        1 |
| STANDARD                    |        1 |
| MAN                         |        1 |
| MERCEDES  BENZ              |        1 |
| ARSENAL                     |        1 |
| M-R                         |        1 |
| FORD GPW                    |        1 |
| MERCEDES                    |        1 |
| ADLER                       |        1 |
| AUSTIN MORIS                |        1 |
| LTI                         |        1 |
| NSU-FIAT                    |        1 |
| ITINEO                      |        1 |
| TRIGANO CHAUSSON            |        1 |
| MITSUBISHI-NL               |        1 |
| PHILLIPS                    |        1 |
| FORD-USA                    |        1 |
| FRANKIA                     |        1 |
| ASTRA                       |        1 |
| GAZ                         |        1 |
| ESSEX                       |        1 |
| AUTOMIRAGE                  |        1 |
| POESSL                      |        1 |
| VOLGA                       |        1 |
| REUTER                      |        1 |
| MAYBACH                     |        1 |
| DAIMLERCHRYSLER-ADRIA MOBIL |        1 |
| BLUCAMP                     |        1 |
| FORD-BURSTNER               |        1 |
| TOMOS                       |        1 |
| AMILCAR                     |        1 |
| ZAPOROŽEC-ZAZ               |        1 |
| FIAT-MOOVEO                 |        1 |
| STEMMER                     |        1 |
| PILGRIM                     |        1 |
| FENDT                       |        1 |
| GERMAN E CARS               |        1 |
| IFA                         |        1 |
| VW                          |        1 |
| WOLSELEY                    |        1 |
| OLTCIT                      |        1 |
| IKOR-B                      |        1 |
| AUTOZODIACO                 |        1 |
| FOELLNER                    |        1 |
+-----------------------------+----------+

Since data entry is done at registration points by hand, you see “MERCEDES BENZ” and “MERCEDES-BENZ” etc. as different manufacturers.

How many Lamborghinis (all vehicle types, all spellings)?

+----------------+----------+
| D1-Znamka      | count(*) |
+----------------+----------+
| LAMBORDINI     |        1 |
| LAMBORDŽINI    |        1 |
| LAMBORGHINI    |     2417 |
| LAMBORGINI     |        5 |
| S. LAMBORGHINI |        1 |
+----------------+----------+

Nice! Must be alot of rich people here.. but lets check the vehicle type first:

+-------------------------------------+----------+
| J-Kategorija in vrsta vozila (opis) | count(*) |
+-------------------------------------+----------+
| traktor                             |     2419 | #tractors
| osebni avtomobil                    |        4 | #cars
| Neznana vrednost                    |        2 | #unknown
+-------------------------------------+----------+

Okay, maybe not. Lets check the not-tractors:

+-------------+-----------------+------------------------+
| D1-Znamka   | D2-Tovar oznaka | D3-Komerc oznaka       |
+-------------+-----------------+------------------------+
| LAMBORGHINI | 140-R-45-A-P    | GALLARDO               |
| LAMBORGHINI | 140-R-45-A-P    | GALLARDO               |
| LAMBORGHINI | NULL            | R 235                  | #unknown type, actually a tractor
| LAMBORGHINI | L147            | LAMBORGHINI MURCIELAGO |
| LAMBORGHINI | NULL            | LDA 90                 | #unknown type, actually a bulldozer-like vehicle
| LAMBORGHINI | 147F-704S       | MURCIELAGO /           |
+-------------+-----------------+------------------------+

Nice, 4 actual Lamborhini cars.

What about some demographic data – how old are the owners (Starost == age)?

+----------------------------+----------+
| C2-Starost lastnika vozila | count(*) |
+----------------------------+----------+
| NULL                       |   319475 | #company owned cars
| 1                          |        1 |
| 2                          |        3 |
| 3                          |        1 |
| 4                          |        2 |
| 5                          |        6 |
| 6                          |        9 |
| 7                          |        9 |
| 8                          |        3 |
| 9                          |        2 |
| 10                         |        7 |
| 11                         |        9 |
| 12                         |       14 |
| 13                         |       17 |
| 14                         |       24 |
| 15                         |       70 |
| 16                         |      173 |
| 17                         |      434 |
| 18                         |      965 |
| 19                         |     2360 |
| 20                         |     3890 |
| 21                         |     5298 |
| 22                         |     6746 |
| 23                         |     8147 |
| 24                         |     9243 |
| 25                         |    11035 |
| 26                         |    12429 |
| 27                         |    14028 |
| 28                         |    16177 |
| 29                         |    17011 |
| 30                         |    17357 |
| 31                         |    18643 |
| 32                         |    19350 |
| 33                         |    20415 |
| 34                         |    21860 |
| 35                         |    22956 |
| 36                         |    23990 |
| 37                         |    24682 |
| 38                         |    25084 |
| 39                         |    25136 |
| 40                         |    26056 |
| 41                         |    25898 |
| 42                         |    25784 |
| 43                         |    26283 |
| 44                         |    26737 |
| 45                         |    26501 |
| 46                         |    25600 |
| 47                         |    26802 |
| 48                         |    27546 |
| 49                         |    28138 |
| 50                         |    29551 |
| 51                         |    29234 |
| 52                         |    28672 |
| 53                         |    28220 |
| 54                         |    27573 |
| 55                         |    27435 |
| 56                         |    26243 |
| 57                         |    25566 |
| 58                         |    25066 |
| 59                         |    25115 |
| 60                         |    24906 |
| 61                         |    24058 |
| 62                         |    22796 |
| 63                         |    22614 |
| 64                         |    21458 |
| 65                         |    19926 |
| 66                         |    19542 |
| 67                         |    17366 |
| 68                         |    16118 |
| 69                         |    14913 |
| 70                         |    13200 |
| 71                         |     9067 |
| 72                         |    10557 |
| 73                         |    11165 |
| 74                         |    10985 |
| 75                         |     9805 |
| 76                         |     9143 |
| 77                         |     8248 |
| 78                         |     7308 |
| 79                         |     6522 |
| 80                         |     5949 |
| 81                         |     5125 |
| 82                         |     4345 |
| 83                         |     3621 |
| 84                         |     3138 |
| 85                         |     2508 |
| 86                         |     2121 |
| 87                         |     1461 |
| 88                         |     1162 |
| 89                         |      811 |
| 90                         |      574 |
| 91                         |      441 |
| 92                         |      230 |
| 93                         |      173 |
| 94                         |      120 |
| 95                         |       73 |
| 96                         |       39 |
| 97                         |       27 |
| 98                         |        6 |
| 99                         |        4 |
| 100                        |        1 |
| 101                        |        2 |
| 102                        |        4 |
| 103                        |        1 |
| 105                        |        1 |
| 108                        |        3 |
| 109                        |        1 |
+----------------------------+----------+

In Slovenia it is legal for a minor to own or inherit a vehicle, but to register it, an adult “user” with the appropriate licence must be assigned.

Which car does to youngest car owner have? (not sure if it’s inherited or a data entry error – age:=1)

+------------+--------------------+----------------------------+
| D1-Znamka  | D3-Komerc oznaka   | C2-Starost lastnika vozila |
+------------+--------------------+----------------------------+
| VOLKSWAGEN | TOURAN / 2.0 / TDI | 1                          |
+------------+--------------------+----------------------------+

How many cars are registered per administrative unit (geographical)?

+----------+--------------------------------------------+
| count(*) | C13-Upravna enota uporabnika vozila (opis) |
+----------+--------------------------------------------+
|   203373 | LJUBLJANA                                  |
|    74347 | MARIBOR                                    |
|    45983 | KRANJ                                      |
|    37767 | CELJE                                      |
|    37697 | PTUJ                                       |
|    36522 | NOVA GORICA                                |
|    36510 | NOVO MESTO                                 |
|    34164 | DOMŽALE                                    |
|    33235 | KOPER                                      |
|    29689 | MURSKA SOBOTA                              |
|    23703 | ŽALEC                                      |
|    22915 | ŠKOFJA LOKA                                |
|    22661 | VELENJE                                    |
|    22344 | GROSUPLJE                                  |
|    19867 | SLOVENSKA BISTRICA                         |
|    19278 | RADOVLJICA                                 |
|    19103 | KAMNIK                                     |
|    17929 | ŠMARJE PRI JELŠAH                          |
|    16023 | KRŠKO                                      |
|    15804 | SEŽANA                                     |
|    15752 | JESENICE                                   |
|    14860 | AJDOVŠČINA                                 |
|    13762 | VRHNIKA                                    |
|    13339 | BREŽICE                                    |
|    13236 | POSTOJNA                                   |
|    13144 | RAVNE NA KOROŠKEM                          |
|    12830 | SLOVENSKE KONJICE                          |
|    11957 | TREBNJE                                    |
|    11811 | LENDAVA                                    |
|    11739 | SLOVENJ GRADEC                             |
|    11409 | GORNJA RADGONA                             |
|    11220 | LITIJA                                     |
|    11185 | ŠENTJUR PRI CELJU                          |
|    10816 | LENART                                     |
|    10691 | PESNICA                                    |
|    10550 | TOLMIN                                     |
|    10140 | SEVNICA                                    |
|     9860 | ČRNOMELJ                                   |
|     9687 | PIRAN                                      |
|     9571 | LAŠKO                                      |
|     9395 | CERKNICA                                   |
|     9316 | LJUTOMER                                   |
|     9163 | IDRIJA                                     |
|     8950 | MOZIRJE                                    |
|     8845 | IZOLA                                      |
|     8838 | ORMOŽ                                      |
|     8783 | ZAGORJE OB SAVI                            |
|     8605 | RADLJE OB DRAVI                            |
|     8347 | KOČEVJE                                    |
|     8318 | ILIRSKA BISTRICA                           |
|     8170 | TRBOVLJE                                   |
|     8127 | TRŽIČ                                      |
|     7908 | RUŠE                                       |
|     7407 | LOGATEC                                    |
|     7364 | RIBNICA                                    |
|     4936 | DRAVOGRAD                                  |
|     4534 | HRASTNIK                                   |
|     4493 | METLIKA                                    |
|      701 | Sistem                                     |
+----------+--------------------------------------------+

What about fuel type (gasoline vs diesel vs others)?

+----------+-----------------------------+
| count(*) | P13-Vrsta goriva (opis)     |
+----------+-----------------------------+
|   621159 | Bencin                      | #gasoline
|   517443 | Dizel                       | #diesel
|     8692 | Bencin/Utek. naft. plin     | #gasoline + LPG
|      662 | Utekočinjeni naftni plin    | #LPG
|      353 | Ni goriva                   | #no gas? maybe electric
|       97 | Dizel/Biodizel in komb.     |
|       94 | Komprimiran zemeljski plin  |
|       68 | Bencin/Komp. zem. plin      |
|       57 | Mešanica                    |
|       16 | Bencin/Etanol               |
|       14 | Dizel/Utek. naft. plin.     |
|       10 | Vodik                       | #hydrogen
|        5 | Utekočinjeni naftni plin*   |
|        3 | Diesel, nafta, plinsko olje |
+----------+-----------------------------+

Most common engine size/displacement (ccm):

+-------------------------+----------+
| P11-Delovna prostornina | count(*) |
+-------------------------+----------+
| 1598                    |   113932 | 
| 1896                    |    85995 |
| 1149                    |    71910 |
| 1968                    |    60370 |
| 1390                    |    49126 |
| 1560                    |    48783 |
| 1461                    |    47872 |
| 1997                    |    39966 |
| 1995                    |    38076 |
| 49                      |    34013 | #most common for mopeds
| -1                      |    31052 |
| 1360                    |    29413 |
| 1242                    |    22095 |
| 1870                    |    19239 |
| 1587                    |    19183 |
| 1998                    |    18516 |
| 1197                    |    18451 |
| 1910                    |    16872 |
| 1396                    |    14560 |
| 0                       |    14301 |
| 1364                    |    13973 |
| 1596                    |    13717 |
| 998                     |    12881 |
| 2500                    |    12685 |
| 1398                    |    12437 |
| 999                     |    11584 |
| 1595                    |    11523 |
| 1199                    |    10962 |
| 1686                    |    10720 |
| 1248                    |    10545 |
| 1198                    |     9736 |
| 1399                    |     9205 |
| 2198                    |     8630 |
| 1124                    |     8099 |
| 1368                    |     8067 |
| 1299                    |     7689 |
| 1397                    |     7653 |
| 1229                    |     7485 |
| 2148                    |     7377 |
| 2360                    |     7296 |
| 1796                    |     7276 |
| 1388                    |     6722 |
| 1781                    |     6601 |
| 1108                    |     6599 |
| 1991                    |     6406 |
| 1498                    |     6333 |
| 1298                    |     6236 |
| 2993                    |     6025 |
| 1591                    |     5859 |
| 1582                    |     5673 |
+-------------------------+----------+

And colors?

+---------------------------------+----------+
| R-Barva vozila (opis)           | count(*) |
+---------------------------------+----------+
| kovinski - SIVA - SVETLA        |   299205 | #metallic gray light
| navaden - BELA - SREDNJA        |   135132 | #white
| navaden - RDEČA - SREDNJA       |   104414 | #red
| kovinski - SIVA - TEMNA         |    97520 | #metallic gray dark
| kovinski - SIVA - SREDNJA       |    85646 | #metallic gray medium
| kovinski - ČRNA - SREDNJA       |    66214 | #metallic black medium
| navaden - BELA - TEMNA          |    64781 | #white dark (???)
| kovinski - ČRNA - TEMNA         |    61789 | #metallic black dark(?)
| kovinski - MODRA - TEMNA        |    60884 | #metallic blue dark
| kovinski - RDEČA - TEMNA        |    45224 | #metallic red dark
| kovinski - MODRA - SVETLA       |    41295 | 
| navaden - RDEČA - TEMNA         |    37676 |
| navaden - BELA - SVETLA         |    37414 |
| kovinski - MODRA - SREDNJA      |    31500 |
| kovinski - RJAVA - SVETLA       |    30638 |
| navaden - ČRNA - SREDNJA        |    27241 |
| kovinski - ZELENA - TEMNA       |    24184 |
| navaden - ZELENA - SREDNJA      |    20368 |
| kovinski - ZELENA - SVETLA      |    20096 |
| kovinski - RDEČA - SREDNJA      |    17849 |
| navaden - MODRA - TEMNA         |    17846 |
| navaden - MODRA - SREDNJA       |    16393 |
| navaden - ČRNA - TEMNA          |    13610 |
| navaden - ORANŽNA - SREDNJA     |    11157 |
| navaden - ZELENA - SVETLA       |     9743 |
| kovinski - RJAVA - TEMNA        |     9408 |
| navaden - RUMENA - SREDNJA      |     8159 |
| kovinski - ZELENA - SREDNJA     |     7212 |
| navaden - RDEČA - SVETLA        |     7122 |
| kovinski - RJAVA - SREDNJA      |     7018 |
| navaden - MODRA - SVETLA        |     6953 |
| kovinski - BELA - SVETLA        |     6755 |
| navaden - SIVA - SREDNJA        |     6530 |
| navaden - ZELENA - TEMNA        |     6384 |
| kovinski - BELA - SREDNJA       |     5737 |
| kovinski - RUMENA - TEMNA       |     5470 |
| navaden - SIVA - TEMNA          |     5014 |
| kovinski - ČRNA - SVETLA        |     4996 |
| kovinski - BELA - TEMNA         |     4836 |
| navaden - RUMENA - SVETLA       |     4758 |
| kovinski - ORANŽNA - SREDNJA    |     4345 |
| navaden - RUMENA - TEMNA        |     3938 |
| kovinski - VIJOLIČNA - TEMNA    |     3741 |
| navaden - RJAVA - SVETLA        |     3471 |
| kovinski - RUMENA - SVETLA      |     3392 |
| navaden - SIVA - SVETLA         |     3067 |
| navaden - ORANŽNA - TEMNA       |     3023 |
| kovinski - ORANŽNA - TEMNA      |     2529 |
| navaden - ORANŽNA - SVETLA      |     2525 |
| kovinski - RUMENA - SREDNJA     |     2300 |
| navaden - RJAVA - SREDNJA       |     2204 |
| kovinski - RDEČA - SVETLA       |     2067 |
| NULL                            |     1501 |
| kovinski - ORANŽNA - SVETLA     |      905 |
| navaden - RJAVA - TEMNA         |      827 |
| kovinski - VIJOLIČNA - SREDNJA  |      701 |
| navaden - VIJOLIČNA - TEMNA     |      696 |
| kovinski - VIJOLIČNA - SVETLA   |      635 |
| navaden - ČRNA - SVETLA         |      360 |
| navaden - VIJOLIČNA - SREDNJA   |      210 |
| navaden - VIJOLIČNA - SVETLA    |      111 |
+---------------------------------+----------+

kovinski = metallic
temna = dark
srednja = medium
svetla = light/bright
siva = gray
bela = white
črna = black
rdeča = red
modra = blue
zelena = green
oranžna = orange
rjava = brown

I will probably add more queries/results, when I get some new ideas and/or questions.

Converting data from Microsoft Access (mdb) format to MySQL

Slovenian “National Interoperability Framework” (NIO) publishes some interesting data every now and then, but most data is usually in MS Access ‘mdb’ format and/or ‘csv’. Working with csv files is painful, since you have to do everything by hand (no selects, filters etc, you have to go through each and every line and check the conditions in a giant loop), and using Microsoft Access on Linux is an even bigger NO.

So, i decided to download some data (anonymized data on drivers licences in Slovenia), and convert it from mdb to MySQL (and insert it into the database). I could have done the same with parsing the csv file, but doing it from mdb autogenerates the database schema, (with csv, you have to do that yourself, by hand).

For converting the data i used mdbtools. The data i used can be found here (zipped mdb).

The first thing after downloading and unzipping the data, was to check which (and how many) tables are inside – you can do this with the “mdb-tables” command.

mulaz@x ~/vd $ mdb-tables Vozniki2013.mdb 
Vozniki_2013

So, there is one table in the database named “Vozniki_2013” (old data, I know).

To generate the schema I used the “mdb-schema” command (with MySQL output):

mulaz@x ~/vd $ mdb-schema Vozniki2013.mdb mysql
-- ----------------------------------------------------------
-- MDB Tools - A library for reading MS Access database files
-- Copyright (C) 2000-2011 Brian Bruns and others.
-- Files in libmdb are licensed under LGPL and the utilities under
-- the GPL, see COPYING.LIB and COPYING files respectively.
-- Check out http://mdbtools.sourceforge.net
-- ----------------------------------------------------------

-- That file uses encoding UTF-8

CREATE TABLE `Vozniki_2013`
 (
        `VIR_PODATKOV`                  varchar (510), 
        `STAROST`                       varchar (510), 
        `SPOL`                  varchar (510), 
        `PODVRSTA_VD`                   varchar (510), 
        `DATUM_IZDAJE`                  varchar (510), 
        `DATUM_VELJAVNOSTI`                     varchar (510), 
        `OMEJITEV_220`                  varchar (510), 
        `OMEJITEV_70`                   varchar (510), 
        `OMEJITEV_71`                   varchar (510), 
        `AM_DATUM_PRIDOBITVE`                   varchar (510), 
        `AM_DATUM_VELJAVNOSTI`                  varchar (510), 
        `A1_DATUM_PRIDOBITVE`                   varchar (510), 
        `A1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `AM2_DATUM_PRIDOBITVE`                  varchar (510), 
        `AM2_DATUM_VELJAVNOSTI`                 varchar (510), 
        `A_DATUM_PRIDOBITVE`                    varchar (510), 
        `A_DATUM_VELJAVNOSTI`                   varchar (510), 
        `B1_DATUM_PRIDOBITVE`                   varchar (510), 
        `B1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `B_DATUM_PRIDOBITVE`                    varchar (510), 
        `B_DATUM_VELJAVNOSTI`                   varchar (510), 
        `C1_DATUM_PRIDOBITVE`                   varchar (510), 
        `C1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `C1_95`                 varchar (510), 
        `C_DATUM_PRIDOBITVE`                    varchar (510), 
        `C_DATUM_VELJAVNOSTI`                   varchar (510), 
        `C_95`                  varchar (510), 
        `D1_DATUM_PRIDOBITVE`                   varchar (510), 
        `D1_DATUM_VELJAVNOSTI`                  varchar (510), 
        `D1_95`                 varchar (510), 
        `D_DATUM_PRIDOBITVE`                    varchar (510), 
        `D_DATUM_VELJAVNOSTI`                   varchar (510), 
        `D_95`                  varchar (510), 
        `BE_DATUM_PRIDOBITVE`                   varchar (510), 
        `BE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `C1E_DATUM_PRIDOBITVE`                  varchar (510), 
        `C1E_DATUM_VELJAVNOSTI`                 varchar (510), 
        `C1E_95`                        varchar (510), 
        `CE_DATUM_PRIDOBITVE`                   varchar (510), 
        `CE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `CE_95`                 varchar (510), 
        `D1E_DATUM_PRIDOBITVE`                  varchar (510), 
        `D1E_DATUM_VELJAVNOSTI`                 varchar (510), 
        `D1E_95`                        varchar (510), 
        `DE_DATUM_PRIDOBITVE`                   varchar (510), 
        `DE_DATUM_VELJAVNOSTI`                  varchar (510), 
        `DE_95`                 varchar (510), 
        `F_DATUM_PRIDOBITVE`                    varchar (510), 
        `F_DATUM_VELJAVNOSTI`                   varchar (510), 
        `G_DATUM_PRIDOBITVE`                    varchar (510), 
        `G_DATUM_VELJAVNOSTI`                   varchar (510), 
        `ID`                    int
);

Every field is “varchar (510)”, and that’s long. I’ve checked the data, and it fits into “varchar (50)”, so you can do a sed replace, to change the field size in the output. After that, you connect to the database, paste this output into the console (or pipe it directly from the command), and you will have an empty table created for your data.

This would also be a good time to create any indexes you need.

Now all we need is the actual data; we can get that using the “mdb-export” command (we have to tell it the table we want to export, and the output syntax).

mulaz@x ~/vd $ mdb-export -I mysql Vozniki2013.mdb Vozniki_2013
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","42","Ž","S","06.12.2002","21.04.2051",NULL,NULL,NULL,"02.06.1987","21.04.2051",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"21.12.1989","21.04.2051",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"02.06.1987","21.04.2051","02.06.1987","21.04.2051",1);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","44","Ž","S","12.07.2000","29.10.2049",NULL,NULL,NULL,"12.07.2000","29.10.2049",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"12.07.2000","29.10.2049",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"12.07.2000","29.10.2049",2);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","55","Ž","S","29.06.1994","05.11.2038",NULL,NULL,NULL,"07.06.1990","05.11.2038",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"07.06.1990","05.11.2038",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"07.06.1990","05.11.2038",3);
INSERT INTO `Vozniki_2013` (`VIR_PODATKOV`, `STAROST`, `SPOL`, `PODVRSTA_VD`, `DATUM_IZDAJE`, `DATUM_VELJAVNOSTI`, `OMEJITEV_220`, `OMEJITEV_70`, `OMEJITEV_71`, `AM_DATUM_PRIDOBITVE`, `AM_DATUM_VELJAVNOSTI`, `A1_DATUM_PRIDOBITVE`, `A1_DATUM_VELJAVNOSTI`, `AM2_DATUM_PRIDOBITVE`, `AM2_DATUM_VELJAVNOSTI`, `A_DATUM_PRIDOBITVE`, `A_DATUM_VELJAVNOSTI`, `B1_DATUM_PRIDOBITVE`, `B1_DATUM_VELJAVNOSTI`, `B_DATUM_PRIDOBITVE`, `B_DATUM_VELJAVNOSTI`, `C1_DATUM_PRIDOBITVE`, `C1_DATUM_VELJAVNOSTI`, `C1_95`, `C_DATUM_PRIDOBITVE`, `C_DATUM_VELJAVNOSTI`, `C_95`, `D1_DATUM_PRIDOBITVE`, `D1_DATUM_VELJAVNOSTI`, `D1_95`, `D_DATUM_PRIDOBITVE`, `D_DATUM_VELJAVNOSTI`, `D_95`, `BE_DATUM_PRIDOBITVE`, `BE_DATUM_VELJAVNOSTI`, `C1E_DATUM_PRIDOBITVE`, `C1E_DATUM_VELJAVNOSTI`, `C1E_95`, `CE_DATUM_PRIDOBITVE`, `CE_DATUM_VELJAVNOSTI`, `CE_95`, `D1E_DATUM_PRIDOBITVE`, `D1E_DATUM_VELJAVNOSTI`, `D1E_95`, `DE_DATUM_PRIDOBITVE`, `DE_DATUM_VELJAVNOSTI`, `DE_95`, `F_DATUM_PRIDOBITVE`, `F_DATUM_VELJAVNOSTI`, `G_DATUM_PRIDOBITVE`, `G_DATUM_VELJAVNOSTI`, `ID`) VALUES ("SVD","34","Ž","S","03.12.2007","17.03.2059",NULL,NULL,NULL,"15.11.1999","17.03.2059",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"15.11.1999","17.03.2059",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"15.11.1999","17.03.2059",4);
...many more lines (~1.3M)

All you have to do now, is to redirect this to a file, and copy it to te server with the database, and import it (or pipe it directly, if you can use the MySQL client on the machine you’re using now).

If you have a large table with alot of lines/INSERTs, you will notice that the import takes alot of time. The main slowdown for me was because MySQL commits the data after every INSERT statement, and that takes time. If you want to speed it up, you can disable the autocommit, and manually commit at the end. Since i’ve exported the data to a text file first, I just added the two lines in the beginning and the end of the file:

SET autocommit=0;
INSERT ....;
INSERT ....;
....
COMMIT;

And voila, you have all the data in your MySQL database now.

Fixing video tearing on Intel Broadwell graphics

I have a Thinkpad X250 laptop (Core i7 5600U CPU) with integrated graphics. When I watch video, in some cases (usually window mode + flash player) causes very noticable video tearing effect (almost like interlacing). It usually didn’t bother me (I mostly watch video fullscreen, where there is no tearing).

And the fix? More obvious then i though. Create a file /etc/X11/xorg.conf.d/15-intel.conf with this inside:

 Section "Device"
   Identifier  "Intel Graphics"
   Driver      "intel"
   Option      "TearFree"    "true"
EndSection

Yeah, who would have thought I had to enable the "TearFree" option…

Merging multiple ESRI SHP files into one

I’ve been playing around with GIS software for a couple of weeks now, and i’ve seen alot of datasources using tiled .shp files (instead of all the data in one huge .shp file, the data is split into “square” tiles). If you want to do stuff with that data, you sometimes want it in one .shp file, so here is a very simple script to merge the files to one:

#!/usr/bin/bash
for i in *shp; 
 do ogr2ogr -f 'ESRI Shapefile' -update -append merge.shp $i -nln merge; 
done

It just does a for loop over all shp files, and uses GDAL to merge them.

This will create a merge.shp file with a layer named “merge” containing all the data from other shapefiles.

PS: this works with shapefiles of the same type (eg. all polygon or all points).
PPS: this is mostly a reminder for me, since i will probably forget the merge command, and I dont want to lose any more time googling it.

gkrellm2 – changing the next/prev theme shortcut

Gkrellm2 is a “widget” that displays different system/etc. monitors at the side of the screen. It has keyboard shortcuts (PageUp and PageDown) to change the selected theme. So, if you change desktops and widow focus alot, it somethimes happens that gkrellm is focused by mistake, and by using the PageUp/Down keys, you change the theme when you didn’t want to.

I have no idea why, but the key combination is hardcoded in src/gui.c.

Here is the patch to change the combination to Control+PageUp:

diff -Naur gkrellm-2.3.5-old/src/gui.c gkrellm-2.3.5-new/src/gui.c
--- gkrellm-2.3.5-old/src/gui.c 2010-09-14 23:01:17.000000000 +0200
+++ gkrellm-2.3.5-new/src/gui.c 2016-07-20 11:31:48.256463308 +0200
@@ -2476,8 +2476,8 @@
        {
 {"/-",                            NULL,        NULL,                                   0, "<Separator>"},
 {N_("/Configuration"), "F1",   create_config_window,   0, "<Item>"},
-{N_("/Theme/Prev"), "Page_Up", cb_load_theme,  THEME_ALT_PREV, "<Item>"},
-{N_("/Theme/Next"), "Page_Down", cb_load_theme,        THEME_ALT_NEXT, "<Item>"},
+{N_("/Theme/Prev"), "<control><shift>Page_Up", cb_load_theme,  THEME_ALT_PREV, "<Item>"},
+{N_("/Theme/Next"), "<control><shift>Page_Down", cb_load_theme,        THEME_ALT_NEXT, "<Item>"},
 {"/-",                            NULL,        NULL,                                   0, "<Separator>"},
 {N_("/Quit"),             NULL,        gtk_main_quit,                  0, "<Item>"},
 {"/-",                            NULL,        NULL,                                   0, "<Separator>"},

If you’re using gentoo. just put the file in /etc/portage/patches/app-admin/gkrellm and reemerge the package.

Aligator clips fail

Recently I’ve found a cheap USB boost converter (small device that turns a lower voltage (~1V-5V) source to a regulated 5V output – so you can charge your USB devices from a range of batteries with different (lower) voltages).

I’ve also received a pack of cables, with aligator clips on both ends, some time ago, so I’ve decided to use them instead of soldering/”improvising”.

For input power, I’ve used a TrustFire 18650 battery in a holder, and the aligator clip cables to connect the boost converter. On the output, I’ve also connected a USB power meter to measure the output voltage and current (I wasn’t sure if it was the 500mA or 1A module).

The voltage was 4.94 volts (somewhat low, but with such a small load, it could be the voltage regulator issue), and zero exit current. Everything is OK!

I’ve connected my powerbank to the USB port (to charge it, and check the current), and the situation got bad:

Current was around 120mA (well below useful), and the output voltage was 4.68V (also too low). So ok, it’s a <$1 module, from eBay, probably no quality control whatsoever.. But just in case, let’s check the battery. I’ve connected a small 7segment LED display voltmeter to the boost module (under the clips), and nothing. The voltmeter requires ~3V to turn light up, so either it’s not working at all, or the voltage is too low. So I’ve used a proper multimeter, and measured the voltage of only 0.588 volts at the boost module! The batteries have protection circuits, so this isn’t a battery issue, since protection steps in at around 2.5 volts and shuts down the power completly. Voltage at the battery was around 3.8V when under load. Then I’ve touched the aligator clip cable with my hand (by accident), and it was warm to the touch – so here’s the problem!

Next thing, I’ve measured the voltage drop on the cables, and was surprised by the results (well, not really that much, since the cables heating up quite a bit). The voltage drop on the red cable was 1.657 volts and 1.378 volts on the black cable. So, on a simple circuit with ~200mA of current, i was losing around 3 volts (~80% of the voltage) on the connecting cables.

What have I learned today? Always check/verify which cheap Chinese manufacturer to blame, so you don’t blame the wrong one!

Battery testing #1 – TrustFire 2400mAh

After setting up my battery capacity tester, I’ve decided to do a test on a TrustFire 18650 lithium battery I’ve bought ~1.5 year ago on DealExtreme (url). The battery specs (and label) say it’s a 2400mAh battery, but with cheap chinese batteries, it’s better to measure then believe!

The battery i have is not new, but has seen very little use (probably about 4-5 charge/discharge cycles), and has been fully charged (not good, I know) in a small battery box at room temperature for most of that time, so i was expecting some loss in capacity.

I’ve charged the battery to full using a XTAR MP15 battery charger (USB power, very slow, but seems to work well), and started the discharge measurement immediately after the LED on the charger turned green (the battery should be at 95%+ capacity then, and the charger stays in CV mode for some time after that).

And the results? With a cutoff voltage of 3.0 volts (the protection circuit should kick in at about 2.5 volts, but I wasn’t willing to risk overdischarging it), the measured battery capacity was 2.271mAh! Which is great for that price!

So now, on my TODO list: testing AA/AAA batteries, brand name vs. much cheaper, store branded ones.

Another cheap gadget: battery capacity meter

Recently, I’ve been browsing ebay for random electronics, and I’ve found a cheap ($4.33) battery capacity meter. Since it was below my $5 “I wont impulse-buy it, I’ll think about it”, i immediately ordered it.

It came today, packed in bubble wrap, and contained a (micro) USB powered meter and a 5W 7.5Ohm load/resistor (measured 7.8Ohm at room temperature). On the left side are four terminals, outer two for the resistor and inner two for connecting the battery. In the middle is 4-digit 7-segment display, cutoff voltage adjustment buttons and three display leds, and on the right side is a micro USB connector, suppling power to the circuit. The microcontroller is probably hidden under the LED display.

The meter is rated for 1.5-12V with a maximum load of 3.1A, so I’ve decided to try it out with a 18650 battery which should power a current of about 0.5A through the provided load. I’v also connected the load and the USB power connector. After powering on it showed the voltage of about 3.79V on the LED display.

Using the (+) and (-) keys, you can adjust the cutoff voltage – this is the voltage when the battery is considered “empty”. Usually, most lithium cells have extra protection circuits, which cut off the power, when the cell voltage is too low, to prevent over-discharging – so you have to consider that when setting the voltage limit.

Pressing OK starts the discharge process. The display loops between capacity (Ah), current (A) and voltage (V), and shows the current values.

I’ve verified the values with my multimeter – the voltage was the same on both meters in all three digits, and the current differed only slightly with the last digit (<1mA difference compared to my multimeter).

Warning: the resistor gets HOT (>100°C). I’ll probably replace it with something larger or add some heatsinks to it.

I’ve tweaked the cutoff voltage (so I could see what happens when it’s finished measuring), and the display started flashing rapidly and stayed in the capacity display mode.

The meter also shows a few error codes, if you mess something up (set up cutoff voltage below the current battery voltage, etc.):

Err1: the battery voltage higher than 15V Err2: the battery voltage is lower than the stop voltage Err3: the battery is unable to withstand the load discharge current Err4: the current is too large (current is more than 3.1A)

Considering the price, the accuracy (within reason of course, my multimeter hasn’t been calibrated in sime time too), and the overinflation of battery capacities in the specifications (eBay sellers, I’m looking at you!), I consider it a nice gadget to have, to test your purchases, before relying on the written spec (eg. “20000mAh” 18650 cell)

Copying a file increasing in size

This is a quick hack, for when I used udpxrec (part of udpxy) to record an IPTV stream to an mpeg file, but instead of saving it to a network share (to watch it with some delay on my OpenElec box), i saved it to a local drive (by mistake). So here was a file, gradually increasing in size, which i wanted on my network drive (to start watching before the actual show/recording is finished). cp of course wont work, since it stops when it detects the end of file (does not detect new data being added, and wait for it), so you need to use something else.

When you think of a file with data being appended at the end, the first thought is “tail -f” (-f = follow and print the data being appended). Since tail only prints the last few lines (or bytes), you need to set it to output from the beginning with the “-c +0” (output bytes starting at the zero-th byte). I also pipe it through pv to follow the progress and copy rate.

So the command is:

tail -f -c +0 /path/source.mpg | pv > /destinationpath/destination.mpg

Playing with a cheap ($10) logic analyzer

A long time ago I ordered (eBay) and recieved a very cheap logic analyzer (it came out to be a Saleae clone). I havent really played with it enough, but I decided to test it.

It connects to PC via mini(!)-USB cable, and has 8 (+2 gnd) pins on the opposite end. The label marks it as a 24MHz 8 channel analyzer. I’ve connected it to a USB->RS232 adapter (from an old mini/nano Arduino kit).

I expected it needed some weird Chinese software, but it worked with original software from Saleae (which is nice).

Logic1 — Screenshot with data already captured

Capturing data is also very easy – the only two options are sample rate and data capture duration.

After the capture timer is over, the program shows logical levels on a time graph (first screenshot) – it does not seem to autodetect the protocol used. After selecting the protocol and protocol options (mainly the correct pin/s) it also decodes the actual data (“test123” in this case).

As it can be seen on the first screenshot, it correctly decoded the data sent via the RS232 adapter.

For $10, it’s a great toy. It probably has some timing/sync issues, and the casing isn’t that great, but for that little money, who cares!