From 99e6caf968d03c0ecb1645c3f8e3ab4470973b04 Mon Sep 17 00:00:00 2001 From: Nicolas Boisselier Date: Thu, 4 Jan 2018 18:08:50 +0000 Subject: [PATCH] lib/php/db/table.php --- lib/php/db/table.php | 17 ++++++++------ share/db/country.sql | 10 ++++----- share/db/nb.db | Bin 906240 -> 927744 bytes share/sql/country | 18 --------------- share/sql/country.sql | 51 ------------------------------------------ share/sql/geo.sql | 49 ++++++++++++++++++++++++++++++++++++++++ 6 files changed, 64 insertions(+), 81 deletions(-) delete mode 100755 share/sql/country delete mode 100644 share/sql/country.sql create mode 100644 share/sql/geo.sql diff --git a/lib/php/db/table.php b/lib/php/db/table.php index 72c9f009..1805f5d0 100644 --- a/lib/php/db/table.php +++ b/lib/php/db/table.php @@ -737,9 +737,6 @@ Class Table extends nb { foreach ($this->fields() as $k => $field) { - $name = $field->sql_name(); - if (!preg_match('/^(char|varchar|text|blog)/i',$field->type)) $name = $field->sql_name_cast_text(); - // No empty values $v = isset($values[$k]) ? $values[$k] : null; if (strcmp($v,'')==0 or $v=='!' or $v=='~') continue; @@ -792,14 +789,20 @@ Class Table extends nb { } - if (preg_match("/(LIKE|$regexp) ..$/","$equal$v") and $field->null) { - $k = 'COALESCE('.$name.','.$this->db()->quote('').")"; + $name = $field->sql_name(); + if (!preg_match('/^(char|varchar|text|blog)/i',$field->type)) $name = $field->sql_name_cast_text(); + +# NB 04.01.18 if (preg_match("/(LIKE|$regexp) ..$/","$equal$v") and $field->null) { + if (preg_match("/(LIKE|$regexp) ..$/","$equal$v")) { + if ($field->null) $k = 'COALESCE('.$name.','.$this->db()->quote('').")"; } elseif ($field->extras) { $k = $this->extras[$k]->sql_name(); - } elseif ($field->numeric() and $field->null) { - $k = 'COALESCE('.$name.",0)"; +# NB 04.01.18 } elseif ($field->numeric() and $field->null) { + } elseif ($field->numeric()) { + $name = $field->sql_name(); + if ($field->null) $k = 'COALESCE('.$name.",0)"; } elseif (!$field->numeric() and $field->null) { $k = 'COALESCE('.$name.",'')"; diff --git a/share/db/country.sql b/share/db/country.sql index f5d13655..ecef737d 100644 --- a/share/db/country.sql +++ b/share/db/country.sql @@ -19,10 +19,10 @@ -- EquivalentFipsCode DROP TABLE IF EXISTS country; CREATE TABLE IF NOT EXISTS country ( - id varchar(2), - iso varchar(2), - iso3 varchar(3), - isonum varchar(3), + id varchar(2) PRIMARY KEY, + iso varchar(2) UNIQUE, + iso3 varchar(3) UNIQUE, + isonum varchar(3) UNIQUE, fips varchar(2), name varchar(50), capital varchar(30), @@ -39,8 +39,8 @@ CREATE TABLE IF NOT EXISTS country ( geonameid integer, neighbours varchar(60), eqfips varchar(2) - -- PRIMARY KEY(id,country) ); +CREATE INDEX IF NOT EXISTS country_geonameid_idx ON country (geonameid); BEGIN TRANSACTION; DELETE FROM country; .import "country.csv" country diff --git a/share/db/nb.db b/share/db/nb.db index 9ba4eb4536ef7010605654af370de7ba1ecde0f8..09ecf6ffe5229ccd593703027a63c1e2f7321e66 100644 GIT binary patch delta 21133 zcmeHvcUY6x`}Y}17>NTxSq|J<1wsfRxC4YRl7LA7!Kw^VsRQ@c7(kGvK-xZ?YWJ?T zw56@J+HJR5+uB{bhi&b)wd1|-`)T`r`~AJ|@7v#Xz5e%dUEH6~xzEXSa`xvr=XtX2 z=Jahxt(|cLLPjzsTiIWKx{9uera15>X*0Fp)yUHrH%gw9Kgh4;>7Iz}b+Uh49QYz5 z!Sw9|rcclB($Jr$^%$aVl1{T5T&WH)rs-f(GQb#3V3L!-B#i@;2)VL}!@x|yD_sx` zW_%Qwas9!J)q@$M12ekk_h^HL`+5w6J<{(DYy_VtumbGNKrPs!Kn2*sKq**vpb)Gp zfEhajm~%lO9c+Fe8Ejr)4A|VjP_T}`K(ILh16X@Ng3ZPYXW?}-F9Ktm4`#+}Fw;F? zra8e(wWoreG8N2ZGnh#ez+_-KtXPEf2r!l~0anOQ!Ln-kz5Eoa>rFYTDnv#IUlw~6 z+?6^R!Sn5P|B|xeuaqO_{Y%QZe@po>UUQE8PAU;cwXgIfWNYQ<3qxh3@Y>z%MTnan zA|rXOFRxm?GNmv7Tlzs|nBHQsuzMl>EKHwjHqYx$hzjRVa)mGA89W2N@vFS&`{Dh> zpb%EZ_-Fh8Z|8G(5^IOxI5sXgJRu{LV>PjqKEe3WMos^~Fhj6#^ibX}xNP(`U7}tW zZHo^M8yyo}UAJc0>XjSjng1Y$#PI(Tm-&fR7{&?d@7Rt#xqW*K*tYH1TU#5k2emZL0lTBo1-7{n z`%6>fM6lZ%vA;Jq_66I}umx;=!xFH8h6}-NYrs+CZ?J*g+JIecO9OVU%?&srHZ@?Q zZmeGkc0>IFuzXX>bfo1!I{0O?r8pi61*@# zjurMbE9;imBp0o$T)3u&SAhF@{D5uEs?{qim(*4!XRTSevbKg}Z+b35juy66;B?nk z*8y@Y!n1>9val^%4MSyhZFOaGN!_Z|b&J_?hDZSf>cipl*!z-fZRn;X?2P2y0vviyta_vf#oOU&+W5`XA{-zDUx32|*qe$Y=kT}Q7ZP4ANzXr@!n3m-h=*Dt$1(;1x$#O$E znDsFK%XL05YrSCBKp&T@p{nJo0x&Cc!7R@PQwO6nDU3_GWHOk=usq3&ta$xe z3z$V_Fbko|~?d#eTo->Qt_!-8|F;(~^%5j-}ST$LDnygEL(u4){QffN@6Z?1?5 zzE?F$R7D5dXN(I*R>lOMn=ul){h1k~d3^7f0A)&wRfk7x~XO8*F5c+gpr!ha5KEh*!tgC|S2>E4{F35*oV;klXuo#=ti z@J4W7>4oxHc>ub?J}JaA!9%m1{H7kmg&ER+qgIBAun2pS-NGM5lD0lOF(yBTXPQf7h`LA5m9R2mpwq6?Yk8(^Kt z*zLe};m5*;0NaTybO*2wWFg-JYbORHU$i09y$r0C7>sDqf=qiqupPw40c%F4xeQnn zvA$TE?MNleC!!IV_!w9NF_<<)Ju-d-Sb*4cVB3(fEB~c5ezi1PsWb_YWD7F!EwIg) zI1JWdu?bn|gTOWty8ze*WFdQittS=(Y#lP)E?{ek>42?4rfmSW8ky!7V5^8>TdzbW zKgZInAd1;7M<$*HR!7VVY#B1XAJ|f2gMckT#=8HdG>g^JT!du^g_%m!Vv?{bV2hB2 zz6oq0u`*x_kcGSitcI8e*nDI<>=@O=uwzsq(>?*LlGrq0705LA0GmfF8Q5H8@^&oE z9HLlJ7a|kbO=lAW-6P78@!i16h=E8Evyic%TAD2EX{9}e<&&hpgxaPB9K}?wn}PX| zg<#`)i46nhL8gO6K+Hs@g{4FkA=8WiR)|buCvYPZ&A?p9ILr~kiHyAstUx2fxE@G8 zexK0o%)vw+(vZI3%tfX>2h4#?^Et2_Wb!v)c4VR#ST-__>%^bKCQEIaOw1?*6WcIJ zSRJq#$U;v8n@(&IuxZFb-T^k1820EX$aMI1ClkZLG6|XXXiV1uKIUcJR#Ro$;Hkecy7QSXx4M>`rWvho%WK!iQSa`gZ zNj!nVS5=O`cKu<0IOJ8qjxzb9{8D}>-;yWfbMkR{K;A8HmDkEEWVdXQ0l8i-mkZ@Q zSt4E1E~m;gIZ=+1gJqP|!&-Vyd@DW^?}^vMQE@~Z61`%dxJm35mxvCrU2GAn#bQw{ z%Ee5PCuWFrku1iDAtFlXgcxAQ67%qQ}ZJdQ{3FwWUe>}ytbH<8s3u@5mxZ1xC0;Q(h1 zM#OB3WN#peKKUg3hRAb6*@KuQ`l{t)RWwyl#O1%R*T_-$*lI%ml%A~=6M2@MMiQ0z z6Ry2dLqNBfcB(B%eFZvz7gY89EzrDlQk4XCNY+z@J*e_uRiDW&%ULj(; zm|cn2={x;{k>r@{W7~<0xr|*+F%fy|(9!3%=h;t07J1pv7-P88wFw=DM@_+BOI={M@tiF-7=y=U5k!)`je2iizFWk8nU@4r18u8KW>k*wSgNog5YI zh2+SpV9yd6(!-iCCiMFy{n4TOZ2;SW4&CV*{|q8mUmk;~{ozNrxtBtF*>&s*a;&|` zMmVlKOGT$06KHl#VDF(rv-QkE!eze-!lmCvQ-Z~pv4@CMe9P{jnA!E4$uZ_*whbNf z*)uCb{ctgP=K%INd2R_Ek4faJIQ9uSF3O!m7-t-ZD89aL72$17A(&8Xlk8<8xh3o) zikb37IwJq=$t4)UcfGKJj>-*AQS)h zEYJh9z%}wRxl1<5EpoM7EURU?^vFDElUA85$I4+cTK1JX@vHb=oE9I5H^och8S$ui zP~0tU5qpIqc8MmjRjd(9#C&m~@Cm2L7E?s3xIl~${x~r}z}mpi!YuG9f0v)+NBI$c zi1+e4`3?Lkelc(3^?V~=$uHuSd=@X_xqJqGHmxOh3C{Hi=e`XNGonTrndm!A#8PntKEvCoOD9?A|- zO!g@sIWn(j_aaH%{R%sVF{zi|SVY*da4zB2JI#cfH3JBjuNpxgSoS-6nuzHcb`8ah zc7y`@xk-+WK!@?IK~QG&(ctX?N&SW@i}^!t;-Z!Exw#5+=08+sD09Sg`Y z;Q`i+j)~pA48#i_eISZ()inl8F#g9U*iA^rUHuz-3S-7~)=wZz+v6fkn7ouwXPrza zm**pnZRnVW9~>KZ+|RB+HsQSbuhb* z$mr+TNlY^8%U|j4Zq!R{#T0%n(~Aj4J*{KMiQK7U&r{4b{rh0V(4V^4#YB#jv%^IAeD-z- z%s+haxzp%~5ksjC}SIAmfA!o@V z>5$WcF!Ne>(&J#e46LtHPe6hYA{0&tJLTr3jvM2T>TK6aQ2 z%wnP#DPl#xp5X~W?hlh1!2(Y{&CdP7j<8?w8?V1>AA1rDwYuWMp(^^)Fk^Rx zX{O$D;zIOHz4aG%DUnNeu-k}qx!LzbIuEk%h&0EuQ$*^k*jx1Jz^S$5*!DTQPbG)h zek4++LGTueIG7LF0t<0q;yrM13^-ARsVZ>J!pAHQNc zMx@U?!M-AgbF+yYrpMWh@UB|3-c$--E4+<8Xuj7%0&mc^mY+D`il&$p*Pe zuA)_Fw)DyZnI$L7R5?M8lyNdbhQShfR-6%^z)sFd(xQjO{h&o}1TA{$A6j&cSo*hh z2s_^>F&NaSL1_3d{98Z&oWIZC;4krK`D3tc-@|VsJsRY#Jis^b6}(6KnM+_uA~DiIL~4rV zLW)Q+LDPWWXoaRiH2YLEmLZxlENfLXc?qpnn54j;Zppx$P3dXue$`=wIT9i))H;gjDGeSu{0~&Pf=-RSmPi`nz4Y;Z0)C_B}YY*gD^D%7F0-=YK8R_(G0Ua zV(JVPO`xGLoMI<5S!Pj2CKL8ch)A(c2ZSXt#XMDYn4srCgb~*dM02c)saC|43`@F- zCRpMzoKi(-wVG5kC#s0@3F6bOFfm7p6sV0KRfl<NoX>|gacudF_|#MvPMPIC_7(Lk7L>7$|vrYZPfYD#}X zlNH81giYp9os0}KnyA){DHc7sQ%uuTG=>oxtvVIWQY-s$<0g=yel`bA;$ZdpM7}Fe z%A@iKY+2qf_sg5*UQnW)a);bT_oItw{V9|=a6f8>Ibej0mHlOi_>Gk4`{H%+_tmFc zw1{nDomeJnK!0~$xw+ypZ>7jES6N9N0FFtZj9s> zixH+iz_bjQ6Cona%$`;qCO^!*;4xd+BPvP71sgpkyln!KYQ@_!BxbzHKw@OQL`)V~ z)bYY5Gw2zJF&WtdL{cp59+jAIIf5u7-Ps~Bvwf<^$PU0QL8M5_u(G?*m6nmh?j&Nh zupX79u?JORMy(Oa)2yi=J|L|%1(#AJMqDp}q+8j|DlwzHL5~qvP#|!AdV@+#sOP`~ zW9>#Deqd%)Mc{%_1{w&7kzGr9rKSxf2NWHae8@aCwXcdPpamc$R0XI7ASNryQzT|~ zi%N_rq`(7pag9n$><%JO7jP2{QAV?$wW`dBYhI)gQN6YnpkW>M)|jgc#_~ zpAms>{1GKdO@;e3NSbP@L-boyQ)ZIGWN{IuSZ(wPV><4Lz%^jheykEB`vgfk^iQ}y zKw?2Hh9r%>qY@K)kBHTZ_f?TR-D(!9!^qwz0tGpx5)*ri%Aanrve(IFG2<2&To4NR zrb>+LWx7t9Wdb?UOzbHlFlJv-i5WLw;6qT_W0W@4lt~U#275v!R#f!xZA|8Da=^&^ z8HqnV#fY0w@M)t3_9=*@v1e3b#(g%7N@dTf#Kevuu|SJGuM#7BoCp;7kV-7PhUJ1>!S-EZ_CCqgusc4@-m{l;A(B>YaV$OKP%u*Yv!ORjL zsl!a4dpP+$?!hW%lKRW^*hocY7CMK}3X$n5CYhM&EGLGY6f&2+lr=!1E=5rnzk z1Qk7`7Bd|l60n($Y*LY#IktfmZ}$#W(M>u&)9#F-aCTk{qOIKCpRm~HH>kwhhtTKH z;P|xpa&Z3~Qu=IRgkG1P5IT*D4$`AGkDX>Wo7=~JK_O~$Qzx)FGj){CY3oOrXCobE za}1XXdsT($DWr}0Na3*;s|s_b z&8I5NqOu&^yNDD;UOVon0P&Q8SR~@ZeJ>a>MV^8UswYpCqeX>IRdg1)9jcxza+a#< zu_)Jx=PGa=yGK=@MOkiDm=@XGs^%=R6{woC(B)Qjn!DVoDnqxYSk-dwBB!eE{O&@J zDmLBDY*qQW^NLlm>CVd{X(`+eo2ok9IR&a9bwloRfo>vS*oUV`ifOe>GT$=deK>6!&9Y5;motED$|)| zV*&DHmZ@^nndwpGrZdy+SJkD{R!DP4L8*^aWkI2bRAqtNwjM`TL7rzVVXkWpp`)Bs zXo1~LVza=d+R6%=3B{QrPcxE+yibxmZOv zsm(l(a{+}5eRR(ZPtlO-%yYR(aph&ZNsZ(!q z1P){yN%*YIg`X_*m;T+;h;IYRn1vmYF8Cdxhqo@pXJbeq=d@sF7^W!WmdtgP1p{lUY9CD zOPx8Y2rVi0sW(g|ZkMW&O7c9a<}7h!s=Ej!@T4mV)sie9ZstXb5*X!Qgu)0G#pRi* zb}jbWRE1g$!~AnhRP4%A@9v5VJgV?4hQUO_(^qa|A7L6_shfRBq_{+tqP}8}Dm;C~ zZuTxk`3hCR=__=rg45^pvbQ11FMI_qRj>LAoa`-1onN2|PG7D~m7qRHp(;v!S=s6q zj1T%H$z5-m2am&G)+@_W)t`5ki=7}(X}PLOy->fTQ@tf_RZe}8}{9C=i?FiAnbqME^mPDr@-Dv3*7Z= zf^)c~asdd>S#qY#hdt0Ka2}r|$3i!Zl>^{JUIRPuKY;lBM4W5i06Qca z_AC1Vm^0=Qw5Kgn<8=kfzMiGKyo-~N3bI6t?a{(rgu zJP)^@IP$S#yf70Jmf;QpglFMS9-tqlPa-~coY0G#0}uhTGGQS*N(gUi5W3h4gibu& zg>*2F6XvsL3G?v$9Q?UB7;^7OR2hSA&LFf{~QbGtS8PG3^K<|C zB80F5&$a<`*!7^`f!Nt~R_j&@?I)c2l1i5Pn9IFZJ zs}S;wRw9(;7p|b7YdOvXqr|EI`K_l_8s#|90WokAf~TQBiLb?{La{l;KzP|{i=Z-= zm*N9i5u)71ej>E7vxs=VLuh9|;1`)yunB{uUiLj9h)l$iV)iYen|(u=$G#?X;E^!I z+wnviFpGUjXv0&_2oRU-H056ml9C)=Jd=kA7yFdZfsZ{w*oMz~!Mk4NxH$?~iidE3 zASwxccz_IH(3XUS>=YqLOhQnRgibsRfpi6UA`h6){BIJ;Wp5BV@L>;#plt;}P?V|` zJ6}@F{|~j;`I2J(f2hT<`SZ?$6!Yi)Gc5+$pBE|S|A$)ad`U6?Kh$F9ON#mbp%yz| zQj8|g%>S&#&X*LU{6UCg|F-=%TI_sDG5`PkDB%C86!X7|GK~Ik;`_g9vGXOx{Qppk zoi8co|A$)ad`U6?Kh$F9ONxQUgYAm{ti_;joEIsE8V|Nf{-YMd&Urqhm@3}>vlcsF zQjGt9E%uid8~z`)Sd*3CXBGN!{C34qAsqZR#aAIlYHv-@q{S+yydqNRe2$0e1iZ|} zZc(nT5EJ;#%F`8M9KT8F8YTuRm%qmQ>O?n0->4+m#1N(boxHz}!+UV-2IU%?7^Kua z#rrCsJqc!8C7iijuT;ju=dP?25&Sykj!H3AcWt7^J5VTH*KtUFEsxOgTjAr^DAQ8K zNM%Nq7|Qo5%ag@mWmA>V^F7L4nQ(!Mjl7?Z$3nv0%3W1rG{0JTXCnAdSBbv-D&Uwb;*lNmK6MFSkgP1PcIbkFMCff)C_Cq znZ1VZG?mMq$YR^lxH*z z0Bdamh0N2N;FT+G*}fTU+ID!uj+?hH0h_wL2CQlOg%`!ukL z+u;piKGDA&Gn=se0G0PlJ7ehrve-v-RAPXoN)#SINufBJ^0 zV8a`rZFpEi64=m&FUk1NLkBt&cN-RlvUrECu^zU?JEq0u>DMKOKO#6WQnVYX+YM@_~OE$OijK z05khIfc@>G0A}`K05kg_FbwSb0emO;y+8!mQvs+y_AXqDy|WI?+snYbg;~5=0p^WT zFt5A8yasi}PQt6=>{TqnD~SwR~&v~n+7#<38*r`K>+18*sG zw_+1?wZhlsovmK5yISF@d}pfzY)9)1uTRuXdEVM;0^5SmweuaV*qxhOu?IA@ zVxw$tjRo7-3VHH|R_q4#t=QWFty-|#TH!HX?r+%yc5BNTuv`2s*c&#tU~kyeQVn(^ zycWRthL#er>syMzu4~ByyS4=yi?3&)=kX$+xg{fSb>+dK{w<|8*~?bX`2S@C9T+U7q{Y<2(~T; zJF^)tTGWj5L18n_2kvHU4p;L~u+C=a&b*)*uan=5MapZ&9+2CF*KsthW-N;5G+}$# zn-+l0Zo-PnYTkWL)4ThecIhuXSuvc|o%M^bE~pp$69c-S0!2g@luY#RngO<77kmlP zw+os{^yz|*DGXikC4{~UzJv(xf_Xyyya?@rVu+B=Ensz>>%nR}SAf-YLScmLtN|-J z=Yi#&FunxqEQIg#dnagX#((RC%=oXJ>0p29OagnZ6Q`q}JF(_|>Wl?@wiElrkDb^j ze(1!qe!mNw@4H>t+rQm~4foA1Eb7<0ARB&W7dF{fyRhedxeIgoVpk^E)4Q;nf4&Rz z`fL|GIL`Q|yT*b2WLE;%k9T2@`Dj-kupjP%THzn;gv#Xa@5Bt=+lf8n)J~kp-`zPI z>^nPsVBg+}ed4X1P;>mvomiMRc4G6rzB3iiE$P><2G)ECKsM2X@2fJ8&X@t^+&ZvmMY({F#nyuupeP0ehqa z8i+sDfxYC(4$S6>4lK;$9axyhI-evLY{ckps6a8Q+Y5GNH+H=d-7pb};oVRs zk=nY~>H}nQExErcY#CP`t8`ljzU&M~Wx7^-L(?zpe`tl z7}y1~o`~wgY$BU~JEw#H5cm)6{q0;>pA-Bk#c(uyC4W-!9}OSQpHQwj8lKA^SBmZr zAEWd?7OshT41a26RZ>Fb68KRe+scJ&R)stb*LqZ$cq}}VKcdtf3s2&QmEFg}^Z6m= z(_`U&{;*PeJUopbRQ4PXkB)u_e|ja|l$2pkGMO`yQqBB!`1}Fo`Qzc^;vS5W6W{^q zB(pIoB_%a!_WC*N*J~ev_y?4JFNF{E-ybQ*3lqec(o&LAQqz*wuMgV+(Y^SsOva3) zlni51T9Rq~di-4;aUUj6$;e1DTOh^?sn>^C!GAA)hm_RxB&#XOY6iFVGq%QS&hq0Fn3UgfJOy_UbCd>^IX%U@P59jFiR6H3e= z{jK~Z<*`Bf+5EUNELtBEdyIa^X31jsd7Nc6nT5XOl%!Oc3dB*RC|W<6zo@iF>;3#Q z#So)6^G_993^+egw#4X5`Nzu3G5V?eBPB8x;6r6*tiFVQpxhj*pUdA@LgMt(wC}~s z453W_Q4Ufn;`CbWsW_P~l>KkYfr=l)?@pAng>vX68LjM%(+||WCo{m z_!@63q4D}U?OT)NI-%5EEz@)yrY`Y%Z*RQbH-(>7472oh?T<-vo=`eRX~!%6S$b{g z4;EP`T-jAhN~u0d**i-=jDN2j#+2VthJCqqtX>|63w^sq6WAn_M`Ephbmh-v}40HFGB>Y)C97WQ@88ml+JFwga59a?AE9A z-<1AAeXjObs%igP?I4}z7WiVnC>w(MiQ03ivKDT5inU2P4a}b6r{1@N`b0Ays2Gmu zhYA^`Jp8jhS~;^p7q84cq8|m%qy(6zo-38#=wp@5OLSG?I+!ixfK8gfI&=d^5M7c^+XUf$O58Jgi;#U4&og@X0f#<` za*kSxaUW~@>9l!}Mz0h-3RiA^PdhYB3zMA;PnRo{Q~UM9l(sjtQ97*!d|}G;d-Wrg z{g3LkO6L>W{>tWO^@&1;V150od(Pw9_?v!vR&Nl}aNkM2ej*>G7&L}r?Z~Nei%>Q_ zuCwZNx4{KRDA#KY)3n1U$TFeqy-PPnuY(mpCipag@j}^mxz4TA%>&0UWr@}>oDWrY zY7I$zh;mSCsN{o{VLC&uHoim?NK}9BW`;*+P|kRC6T-D;;8SrGnt)9?HOLUJyr(nR zd8~3Q+%Qymewr>ZOnU+n#3ai~^^=!|7zXh{y?2Bd=1$?MiXq!j!A;82Y=e`hC=X^E zyxgetwHpS5IGJEK6!Rn{U^h(UiOQXJLoT1F{Af2M>n0Rv0@1J~ks2~WapxGu@e7o7 zIfm7IymC6nFocg&!X1X`e2kLsFwEtndv9?Vs?0n~F>E%}@l0j&Wk7?!tqn_XSPaf zSJjl(uB=J)E{8kUm8LLQ>*bV7w;JO3WF>y9VS;uN4dc_lg`_Flwi>iNLuuM-7|X57 zom&m_c)HTpZ*b@=YG&6w5wbuR3X7LC_dew}WKZGVUc*E1p`o4_nJP9^!Vh^Zt6W=G zyD|xW3@zE1WC~jgiDxRw2Msa2NXb2DDAN_e7F%}^OQFqHsrw*nqv+b zhH;0IbI6dS%du$!Fk-*V(M;BfMo5+2d*F~EH=EB=e!IHQ;NApHpLyZDymwpQK7P(i v@9W>k9M3D2v+}0CUVr2LeFpz8?RTt& delta 7663 zcmYkB2Y3|K+Q;Xdvoo7yCcP(+gc3+dvLTgTLJc*c6KM${A%QefAV>+byXifv9Eu{A ztEeDqkf^AD<=PcduUz%2Sipjc-D|<`|1RJ6J?44x%Wuz|oipcs-*aYmv*+B{Jx9j$ zL#_+9)gN8h3k?vIHn6uN%^8ohMDv;Qw) z__5@EBj_G6-?k7+ zy8o|G!@?H!8-o^$h6U!0Xq#_t0q2>e;9N5woMWy4v&|fEwn+oeGHK+Q<`{5>nG8-h zM}X7JC@{+m2B(?^IK>p;WIFLAI`70yz=RFJ_*KBT#lYD4z?hkI&6$&cjC3G90Z5~^ zRGLDHKQP*}Z*&kBzxMmbe8~ELO7Z1>sxnn!->~^CTq#wivP&-im_$BOcfFA|$fv5Z zx^`U((Z@{ka(y=^h1(xy zMnRK{#As4grisE$jG|KFCh|8GkBzov@1^1n6qw`Oi-qhex` zl45cf&smVQI4@@2%)GeL4T*)7brrSM+vC0BDgUDcB}CcYZ;a)lp}vtaxUDe^+}cQ4 zs&i80YMpDq8s`GA+DUQR;!FgqoRsHErw>@+*aen5%D^(mYH+iIs%4WS3oLb{fh7(K z>P81uLa~E`MfPfNgS{9mY_QWX>+O`70{b*D-%j~hXCDHtwFiJ}>Gzt zrwn0-`;DoWMfh#za2z~T1>8D@x(jgzRPO3GW(qLjg0>nr@v#z?bswg?Px?p2n5z9yOqx6QXx|-VRg0j+r z3W>)~LL%(9Bz&AC`Kl}QULw^ok!#v!ZH zrie+xU_UY^o2`6b{q?IIkRU9d$5n|tGQ7uz~wdXXP)fs>Y8J-_?c4;*DKkBn6K-L z>^rfz6@`l*L3UdFCVq4mMsKbj!lrZ#MdReA`P3yr#Dk)`-&h$V8n$eQS8W9^I$i;ppaz+9RV}S*@elZWb zrI>@764_W*Vm9hU%tE}y3{M~na}!fhNn#2E5s3?+s1sj^3y8!^B2d&CVlb<#xUsaV zuDT41Ow_o(UKi?N75AX0V)h#)hs63l9wLx$t1jcTH#As9AgkF{Ov`vT9jM+=%tP^F z7rm(5P|8l@#ZKx~R#ZcA-9ZO778EM4;l+*gqOiJ5`JJxFgVA;`M)w51ERk z$GgeI6R<8-cyLd|gLjg7dL$*j-9+T1o4)=H)!YP`j59=650 z{xL24ScJRbQqfQr%*vB zyf_?3!(F9a{;tg8NY~9>Lk!AppqK{o-Pa?&%N=plINwQT`5v}|OgRd>k*4N37`C0v zqXkw^X8j4a&23nId@GsuHmuHVSXR82Onnqq<2Ky>cr}@2A8d=;e6aL*6{*0Y=9Ogp zGOWUFSi8KOjJ*IWa~qye_+~QY;NNDm>H2I+-Lr{7ml87m18gI8_Qc(f7n6BB4l8oo zDA)!v>usT|I9Zkr67M`k$zo9i|_^zk`l;wa4~+pQF+*<_rS(k!>(X@k!s zV~1cf+=fR3KAlW)U7yW#s(s z-(jg_makwbWa1iZG#MX&C6lpbe@l(+dTNqrAS-oFq%NM7umm!XE3i>+D}lw6SwDou zxs57%B$-B+JHl$N~?V6B#&h+^20r7zjBiMux5F+4DL6;16Y&H zD#3X!9A<`BKDswfu+#)Hd3-O+^cs%W&S=ZHKrRjYZ(##?|1U#${k+;{q_EaTXZfI2jCU!~+ToZD^#yLK?AZS#To_7Su?C z1vUnP0gW__e2nD4ah8JK&h?<-M4d9-iH(GLIkQ1e=Ty+ci5ms8I#WQ+ znED>WBW+( zqWgoM&t*!SI^soyhe;P0B{;J?j{-~}^Z!T9f(*q4=mxj#L< zZO(5{Y3pB*@ z`M^I{0M9K1o~2klgC|wxX_~^>Bn25c6AL{Z2|Ptt{-h7^gr{jf zLsD2(TR2$RhA~-18tDp<+mb!Y+Wm@skN=i;F?xUm91_a0M zTEP`92qRnGvK3s`f@HC!Ek)pxmIfMZaSIK$sD%d0ZJ~MRw9vd4w$Qv6w9t&_x6oYY zwa_qgTf)FOEq-8jiw8Knc{ez#c{@0>xeA=oya}A%yaAlnOp7+FnW8(j8I{K-HD`gh zb_}NLx}}3ocd&yN=YbBo>ir$aJ=@noOH{V+1oyV12H4H*C^vRfdj;rfF9rMBX@K5# znnq9iO0c_~7HwBM))DJ$p9yxfPXXK8X)U|ch$V{CI2IHnop z%rcwlJQ>Y2rSxX1fV3t$Pij-Gq6D#&Cd$X?reZL;i2`M7K9r*l9Lmwk|6mc)m~Z{H zf&JKvxX=&#vzG$+M=#Cn_ui@CZ@qL8|LvtT{n|@~`AaWd#LvBS5kK`}pSb>h_CqgC zeIIsHwSUk}iTh7CP4)e58s@!jO4+;J zRC)jIrco|*PXpiSrZWFmH;wgn_vi-vd8<1Pe6u?SJl{O%l|U>^d=1Nurp|2_=E{rV6{`w%*=_4$HoA1a?)hST#s7riJ4&U>pt){EJ2rI&WSzj`T8 z*P5^8Sn=zDU(bPSIbPmxv4^EGT(4#i$%f(j5cZ(FeYl>@j>}v_A0ho>bxYU-^fgnB zEvBFhUuI@iZ>+1azKHW4lZmnVGCx zC4EQg!3}o?ig=!iBdHlFwv@CCTYbG}3l85wSCu+?oGoSCXj_IYwZ5Ld2jsU?_mpwt zZ0VUeG8Vn-tz*%Cn64ouZH#Sfs%>mKn$;_4y-gL~+_MpOh|J>+*sX4(@ZJI|<<`~E zgVd;yW!NVDKK7J+W|O{(Jt?C%>tW$fxWB_I+l;RUD~hJ& zEKN?arC}@Jr)2JCeFA%2c5Fue-j+t0p3dHqS!HN^Q|>C$SFrQ)?J|85dqW16NBX9oBeGG3G(m3pQ6!g#Tb%gXOWhQ_dU@#h8xD)prk*(cIC zs?StEwuyCI_SCEKvf-$%dR)vDm3+aB^)lstJxCrtszoX3ei z*kv(yaCx>&-KL2@(eNMn*fG72y)WlKpfAwgE3%mLxcsO}gvyg>P~V*(wsX1Z8%w4p zDAWIzGmh&y?1H@MxIUEqYhd8Gz9gOV@1*gmp3nJJS@Eg8l|A8_pWGR3Bys*dMeFa)Yr?e1d#*(r2Is$=Of>XF<|~br#xm7s zlGw%Njxg(3O}ii8DSFGhRvMF4BSGYGd04fM&^7G;LSJezV}OTCJBsreQ&OJ3Y$M9PnE zFlI7~d}A%L`D&k*4QOqe;SkV;KvV=6)lQg~qaR;TjX`^S&yo&I`|_`>$wLez)N>=zjq6_?+JW diff --git a/share/sql/country b/share/sql/country deleted file mode 100755 index 57b0dca3..00000000 --- a/share/sql/country +++ /dev/null @@ -1,18 +0,0 @@ -#!/usr/bin/env perl -use strict; -use warnings; -@ARGV = ('curl -s http://download.geonames.org/export/dump/countryInfo.txt |'); -#exec($ARGV[0]); exit; - -while (<>) { - #s/^#ISO/ISO/; - next if /^#/; - chomp($_); - s,\\,\\\\,g; - my @r = split("\t",$_); - unshift @r,$r[0]; - while (@r<20) { - push @r,''; - } - print join("\t",@r)."\n"; -} diff --git a/share/sql/country.sql b/share/sql/country.sql deleted file mode 100644 index 34cd69b2..00000000 --- a/share/sql/country.sql +++ /dev/null @@ -1,51 +0,0 @@ --- ISO --- ISO3 --- ISO-Numeric --- fips --- Country --- Capital --- Area(in sq km) --- Population --- Continent --- tld --- CurrencyCode --- CurrencyName --- Phone --- Postal Code Format --- Postal Code Regex --- Languages --- geonameid --- neighbours --- EquivalentFipsCode -DROP TABLE IF EXISTS country; -CREATE TABLE IF NOT EXISTS country ( - id varchar(2), - iso varchar(2), - iso3 varchar(3), - isonum varchar(3), - fips varchar(2), - name varchar(50), - capital varchar(30), - area_km varchar(9), - population bigint, - continent varchar(2), - tld varchar(10), - currency_code varchar(3), - currency_name varchar(20), - phone varchar(30), - zip_format varchar(60), - zip_regexp varchar(150), - languages varchar(100), - geonameid integer, - neighbours varchar(60), - eqfips varchar(2) - -- PRIMARY KEY(id,country) -); -GRANT ALL ON ALL TABLES IN SCHEMA public TO www; -GRANT ALL ON ALL TABLES IN SCHEMA public TO nico; -GRANT ALL ON ALL TABLES IN SCHEMA public TO root; -BEGIN TRANSACTION; -DELETE FROM country; -copy country from stdin with (format 'text'); -COMMIT; - diff --git a/share/sql/geo.sql b/share/sql/geo.sql new file mode 100644 index 00000000..655caf0b --- /dev/null +++ b/share/sql/geo.sql @@ -0,0 +1,49 @@ +-- geonameid : integer id of record in geonames database +-- name : name of geographical point (utf8) varchar(200) +-- asciiname : name of geographical point in plain ascii characters, varchar(200) +-- alternatenames : alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000) +-- latitude : latitude in decimal degrees (wgs84) +-- longitude : longitude in decimal degrees (wgs84) +-- feature class : see http://www.geonames.org/export/codes.html, char(1) +-- feature code : see http://www.geonames.org/export/codes.html, varchar(10) +-- country code : ISO-3166 2-letter country code, 2 characters +-- cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters +-- admin1 code : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20) +-- admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) +-- admin3 code : code for third level administrative division, varchar(20) +-- admin4 code : code for fourth level administrative division, varchar(20) +-- population : bigint (8 byte int) +-- elevation : in meters, integer +-- dem : digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat. +-- timezone : the iana timezone id (see file timeZone.txt) varchar(40) +-- modification date : date of last modification in yyyy-MM-dd format +DROP TABLE IF EXISTS geo; +CREATE TABLE IF NOT EXISTS geo ( + id bigint PRIMARY KEY, + name varchar(200), + asciiname varchar(200), + altnames varchar(10000), + latitude numeric(7,4), + longitude numeric(7,4), + feature_class char(1), + feature_code varchar(10), + country char(2), + cc2 varchar(200), + admin1 varchar(20), + admin2 varchar(80), + admin3 varchar(20), + admin4 varchar(20), + population bigint, + elevation integer, + dem integer, + timezone varchar(40), + updated date +); +GRANT ALL ON ALL TABLES IN SCHEMA public TO www; +GRANT ALL ON ALL TABLES IN SCHEMA public TO nico; +GRANT ALL ON ALL TABLES IN SCHEMA public TO root; +CREATE INDEX IF NOT EXISTS geo_country_idx ON geo (country); +BEGIN TRANSACTION; +DELETE FROM geo; +COPY geo FROM PROGRAM 'curl -s http://download.geonames.org/export/dump/allCountries.zip | zcat' WITH (format 'text', NULL ''); +COMMIT; -- 2.47.3