(* Getting list of plants *) maintainList := ( SetDirectory[dirwork]; filelist = "plantlist.txt"; data = Import[filelist, "Lines"]; data = Select[data, ! StringMatchQ[#, "'*'"] &]; data1 = Select[data, ! StringFreeQ[#, "'"] &]; If[data1 =!= {}, Print["Warning: ", filelist, ": some lines contain ' character: ", data1]]; data = Split[ data, ! StringMatchQ[#1, Whitespace ...] && ! StringMatchQ[#2, Whitespace ...] &]; data = Select[data, ! StringMatchQ[#[[1]], Whitespace ...] &]; md = Length[data]; Print["Using ", md, " plant names ..."]; names0 = #[[1]] & /@ data; dups = Tally[names0]; dups = Select[dups, #[[2]] =!= 1&]; If[dups =!= {}, Print["Warning: There are duplicate names: ", dups // TableForm]]; openSQL; datalist = Table[ name = data[[n, 1]]; synon = Drop[data[[n]], 1]; If[Length[synon] > 0, nameid = synon[[1]]; If[StringMatchQ[nameid, RegularExpression["[0-9]+|[a-z]+[-][0-9]+"]] || (StringFreeQ[nameid, " "] && StringLength[nameid] >= 30), synon = Drop[synon, 1], Print["Warning: wrong format for name ID: ", name, " -> ", synon]; ]; ]; synon = Flatten[StringSplit[#, {Whitespace ... ~~ "," ~~ Whitespace ...}] & /@ synon]; datasql = SQLExecute[conn, "SELECT * FROM pictures WHERE (MATCH (caption) AGAINST ('+\"qatar\" +\"" <> name <> "\"' IN BOOLEAN MODE));"]; datasql = { ToString[#[[2]]], (* year *) ToString[#[[3]]], (* roll *) #[[4]],(* pic *) StringReplace[#[[5]], RegularExpression["(\\w+) (s\\W)"] -> "$1'$2"], (* caption *) #[[6]], (* x-size *) #[[7]], (* 7-size *) #[[13]][[1]], (* date *) #[[14]] (* pop *) } & /@ datasql; {name, synon, datasql}, {n, md}]; CloseSQLConnection[conn]; datalist = Select[datalist, #[[3]] =!= {} &]; mdatalist = Length[datalist]; names = #[[1]] & /@ datalist; Print["There are ", mdatalist, " plants from the list with pictures: ", Short[names, 5]]; names1 = Complement[names0, names]; m1 = Length[names1]; Print["There are ", m1, " plants for which pictures are not available: ", Short[names1, 50]]; alldatasql = Flatten[#[[3]] & /@ datalist, 1] // Union; (picsize[#[[2]], #[[3]]] = {#[[5]], #[[6]]})& /@ alldatasql; qplantpictures = (Take[#, 3] & /@ alldatasql) // Union; qplantpictures = Sort[qplantpictures, 100 ToExpression[#1[[2]]] + ToExpression[#1[[3]]] < 100 ToExpression[#2[[2]]] + ToExpression[#2[[3]]]&]; Print["There are ", Length[qplantpictures], " pictures showing Qatar plants: ", Short[qplantpictures, 5]]; qyears = ToExpression[#[[1]]] & /@ qplantpictures; qyearmin = Min[qyears]; qyearmax = Max[qyears]; Print["Years span: ", qyearmin, " - ", qyearmax]; Do[ names = Select[datalist, MemberQ[Transpose[Take[Transpose[#[[3]]], 3]], picture] &]; names = #[[1]] & /@ names; If[Length[names] > 1, names = Complement[names, { "Arthrocnemum macrostachyum", "Ziziphus nummularia", "Zygophyllum qatarense", "Tripleurospermum auriculatum", "Suaeda maritima" }]]; If[Length[names] > 1, Print["Warning: ambigous: plantsonpicture[", picture, "] = ", names]]; plantsonpicture[picture] = names, {picture, qplantpictures}]; Do[ {name, names, datasql} = data; names = StringReplace[#, RegularExpression["(\\w+) (s\\W)"] -> "$1'$2"]& /@ names; plantnames[name] = names; plantdatasql[name] = datasql, {data, datalist}]; (* plantdatasql["Suaeda maritima"] = Complement[plantdatasql["Suaeda maritima"], plantdatasql["Salsola soda"]]; *) );