STL ファイルを SQLite 3 に格納

1. テーブル定義
   • T(seq, nx, ny, nz, x1, y1, z1, x2, y2, z2, x3, y3, z3)
   • fredini(seq, name, nx, ny, nz, x1, y1, z1, x2, y2, z2, x3, y3, z3)

   ◆ bash プログラム

   #!/bin/bash
   
   rm -f /tmp/fredinidb
   #
   cat >/tmp/a.$$.sql <<-SQL
   drop table T;
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   #
   cat >/tmp/a.$$.sql <<-SQL
   drop table fredini;
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   #
   cat >/tmp/a.$$.sql <<-SQL
   create table T (
   seq           integer primary key not null, 
   nx            real, 
   ny            real, 
   nz            real, 
   x1            real, 
   y1            real, 
   z1            real, 
   x2            real, 
   y2            real, 
   z2            real, 
   x3            real, 
   y3            real, 
   z3            REAL 
   );  
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   #
   cat >/tmp/a.$$.sql <<-SQL
   create table fredini (
   name          TEXT not null, 
   seq           INTEGER not null, 
   nx            real, 
   ny            real, 
   nz            real, 
   x1            real, 
   y1            real, 
   z1            real, 
   x2            real, 
   y2            real, 
   z2            real, 
   x3            real, 
   y3            real, 
   z3            REAL 
   );  
   SQL
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   

   
2. テーブルの作成

   stl ファイルが /tmp/fredini 直下にあるとする．

   ◆ bash プログラム

   #!/bin/bash
   
   #
   cd /tmp
   rm -f readstl.c
   # binary STL to csv 
   wget https://www.kkaneko.jp/rinkou/od/readstl.c
   g++ -o /tmp/a.out readstl.c
   
   #
   cd /tmp/fredini
   for i in *.stl; do
   j=`basename $i .stl`
     echo $j
     /tmp/a.out $i | tail -n +2 > /tmp/$j.csv
     cat >/tmp/a.$$.sql <<-SQL
   .mode csv
   create table T1 as select * from T;
   .import /tmp/$j.csv T1 
   select * from T1 limit 10;
   insert into fredini select '$j' as name, T1.seq as seq, T1.nx as nx, T1.ny as ny, T1.nz as nz, T1.x1 as x1, T1.y1 as y1, T1.z1 as z1, T1.x2 as x2, T1.y2 as y2, T1.z2 as z2, T1.x3 as x3, T1.y3 as y3, T1.z3 as z3 from T1; 
   drop table T1;
   vacuum; 
   SQL
     cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   done
   

3. (オプション) データベースの再構成

   ◆ bash プログラム

   #!/bin/bash
   
   cd /tmp
   ls -la fredinidb
   
   # dump
   cat >/tmp/a.$$.sql <<-SQL
   .output /tmp/fredinidb.sql
   .dump 
   .exit
   SQL
   #
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   
   #
   rm /tmp/fredinidb
   
   # resutor 
   cat >/tmp/a.$$.sql <<-SQL
   .read /tmp/fredinidb.sql
   .exit
   SQL
   #
   cat /tmp/a.$$.sql | sqlite3 /tmp/fredinidb
   #
   cd /tmp
   ls -la fredinidb
   

   SQLiteman で確認

4. R で使ってみる

   ◆ bash プログラム

   #!/bin/bash
   cat >/tmp/a.$$.r <<-RCOMMAND
   require(RSQLite)
   require(sqldf)
   require(rgl)
   dbname="/tmp/fredinidb"
   driver=dbDriver("SQLite")
   conn=dbConnect(driver,dbname)
   #
   T <- dbGetQuery(conn, "SELECT * from fredini");
   #
   D <- sqldf("select x1, y1, z1 from T")
   plot3d( x = D\$x1, y = D\$y1, z = D\$z1 )
   Sys.sleep(100)
   RCOMMAND
   cat /tmp/a.$$.r | r