sql.html 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235
  1. <!-- meta page description: SQL support in GRASS GIS -->
  2. <!-- this file is lib/db/sqlp/sql.html -->
  3. Vector points, lines and areas usually have attribute data that are
  4. stored in DBMS. The attributes are linked to each vector object using a
  5. category number (attribute ID, usually the "cat" integer column). The
  6. category numbers are stored both in the vector geometry and the
  7. attribute table.
  8. <p>
  9. GRASS GIS supports various RDBMS
  10. (<a href="http://en.wikipedia.org/wiki/Relational_database_management_system">Relational
  11. database management system</a>) and embedded databases. SQL
  12. (<a href="http://en.wikipedia.org/wiki/Sql">Structured Query
  13. Language</a>) queries are directly passed to the underlying database
  14. system. The set of supported SQL commands depends on the RDMBS and
  15. database driver selected.
  16. <h2>Database drivers</h2>
  17. The default database driver used by GRASS GIS 7 is SQLite. GRASS GIS
  18. handles multiattribute vector data by default. The <em>db.*</em> set of
  19. commands provides basic SQL support for attribute management, while the
  20. <em>v.db.*</em> set of commands operates on vector maps.
  21. <p>
  22. Note: The list of available database drivers can vary in various binary
  23. distributions of GRASS GIS:
  24. <p>
  25. <table class="border">
  26. <tr><td><a href="grass-sqlite.html">sqlite</a></td><td>Data storage in SQLite database files (default DB backend)</td>
  27. <td><a href="http://sqlite.org/">http://sqlite.org/</a></td></tr>
  28. <tr><td><a href="grass-dbf.html">dbf</a></td><td>Data storage in DBF files</td>
  29. <td><a href="http://shapelib.maptools.org/dbf_api.html">http://shapelib.maptools.org/dbf_api.html</a></td></tr>
  30. <tr><td><a href="grass-pg.html">pg</a></td><td>Data storage in PostgreSQL RDBMS</td>
  31. <td><a href="http://postgresql.org/">http://postgresql.org/</a></td></tr>
  32. <tr><td><a href="grass-mysql.html">mysql</a></td><td>Data storage in MySQL RDBMS</td>
  33. <td><a href="http://mysql.org/">http://mysql.org/</a></td></tr>
  34. <!--
  35. <tr><td><a href="grass-mesql.html">mesql</a></td><td>Data are stored in MySQL embedded database</td>
  36. <td><a href="http://mysql.org/">http://mysql.org/</a></td></tr>
  37. -->
  38. <tr><td><a href="grass-odbc.html">odbc</a></td><td>Data storage via UnixODBC (PostgreSQL, Oracle, etc.)</td>
  39. <td><a href="http://www.unixodbc.org/">http://www.unixodbc.org/</a></td></tr>
  40. <tr><td><a href="grass-ogr.html">ogr</a></td><td>Data storage in OGR files</td>
  41. <td><a href="http://gdal.org">http://gdal.org/</a></td></tr>
  42. </table>
  43. <h2>NOTES</h2>
  44. <h3>Database table name restrictions</h3>
  45. <ul>
  46. <li> No dots are allowed as SQL does not support '.' (dots) in table names.</li>
  47. <li> Supported table name characters are only: <br>
  48. <div class="code"><pre>
  49. [A-Za-z][A-Za-z0-9_]*
  50. </pre></div></li>
  51. <li> A table name must start with a character, not a number.</li>
  52. <li> Text-string matching requires the text part to be 'single quoted'.
  53. When run from the command line multiple queries should be contained
  54. in "double quotes". e.g.<br>
  55. <div class="code"><pre>
  56. d.vect map where="individual='juvenile' and area='beach'"
  57. </pre></div></li>
  58. <li> Attempts to use a reserved SQL word (depends on database backend) as
  59. column or table name will cause a "SQL syntax error".</li>
  60. <li> An error message such as &quot;<tt>dbmi: Protocol
  61. error</tt>&quot; either indicates an invalid column name or an
  62. unsupported column type (then the GRASS SQL parser needs to be
  63. extended).</li>
  64. <li> DBF column names are limited to 10 characters (DBF API definition).</li>
  65. </ul>
  66. <h2>EXAMPLES</h2>
  67. <h3>Display of vector feature selected by attribute query</h3>
  68. Display all vector points except for <i>LAMAR</i> valley
  69. and <i>extensive trapping</i> (brackets are superfluous in this
  70. example):
  71. <div class="code"><pre>
  72. g.region vector=schools_wake -p
  73. d.mon wx0
  74. d.vect roadsmajor
  75. # all schools
  76. d.vect schools_wake fcol=black icon=basic/diamond col=white size=13
  77. # numerical selection: show schools with capacity of above 1000 kids:
  78. d.vect schools_wake fcol=blue icon=basic/diamond col=white size=13 \
  79. where="CAPACITYTO &gt; 1000"
  80. # string selection: all schools outside of Raleigh
  81. # along with higher level schools in Raleigh
  82. d.vect schools_wake fcol=red icon=basic/diamond col=white size=13 \
  83. where="ADDRCITY &lt;&gt; 'Raleigh' OR (ADDRCITY = 'Raleigh' AND GLEVEL = 'H')"
  84. </pre></div>
  85. <p>
  86. Select all attributes from table where <i>CORECAPACI</i> column values are
  87. smaller than 200 (children):
  88. <div class="code"><pre>
  89. # must be run from the mapset which contains the table
  90. echo "SELECT * FROM schools_wake WHERE CORECAPACI &lt; 200" | db.select input=-
  91. </pre></div>
  92. <p>
  93. <p>
  94. Example of subquery expressions from a list (not supported for DBF driver):
  95. <div class="code"><pre>
  96. v.db.select schools_wake where="ADDRCITY IN ('Apex', 'Wendell')"
  97. </pre></div>
  98. <h3>Example of pattern matching</h3>
  99. <div class="code"><pre>
  100. # field contains string:
  101. # for DBF driver:
  102. v.extract schools_wake out=elementary_schools where="NAMELONG LIKE 'ELEM'"
  103. # for SQLite driver:
  104. v.extract schools_wake out=rivers_noce where="DES LIKE '%NOCE%'"
  105. v.extract schools_wake out=elementary_schools where="NAMELONG LIKE '%ELEM%'"
  106. # match exactly number of characters (here: 2), does not work for DBF driver:
  107. v.db.select mysites where="id LIKE 'P__'"
  108. #define wildcard:
  109. v.db.select mysites where="id LIKE 'P%'"
  110. </pre></div>
  111. <h3>Example of null handling</h3>
  112. <div class="code"><pre>
  113. v.db.addcolumn map=roads col="nulltest int"
  114. v.db.update map=roads col=nulltest value=1 where="cat &gt; 2"
  115. d.vect roads where="nulltest is null"
  116. v.db.update map=roads col=nulltest value=2 where="cat &lt;= 2"
  117. </pre></div>
  118. <h3>Update of attributes</h3>
  119. Examples of complex expressions in updates (using <tt>v.db.*</tt>
  120. modules):
  121. <div class="code"><pre>
  122. v.db.addcolumn map=roads column="exprtest double precision"
  123. v.db.update map=roads column=exprtest value="cat/nulltest"
  124. v.db.update map=roads column=exprtest value="cat/nulltest+cat" where="cat=1"
  125. # using data from another column
  126. v.db.update map=roads column=exprtest qcolumn="(cat*100.)/SHAPE_LEN."
  127. </pre></div>
  128. <p>
  129. Examples of more complex expressions in updates (using <tt>db.*</tt>
  130. modules):
  131. <div class="code"><pre>
  132. echo "UPDATE roads SET exprtest=null"
  133. echo "UPDATE roads SET exprtest=cat/2" | db.execute
  134. echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
  135. echo "UPDATE roads SET exprtest=NULL WHERE cat&gt;2" | db.execute
  136. echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"
  137. </pre></div>
  138. <p>
  139. Instead of creating and updating new columns with an expression, you
  140. can use the expression directly in a command:
  141. <div class="code"><pre>
  142. d.vect roads where="(cat/3*(cat+1))&gt;8"
  143. d.vect roads where="cat&gt;exprtest"
  144. </pre></div>
  145. <h3>Example of changing a SQL type (type casting)</h3>
  146. <i>Note: not supported for DBF driver.</i>
  147. <p>
  148. North Carolina data set: convert string column to double precision:
  149. <p>
  150. <div class="code"><pre>
  151. # first copy map into current mapset
  152. g.copy vect=geodetic_pts,mygeodetic_pts
  153. v.db.addcolumn mygeodetic_pts col="zval double precision"
  154. # the 'z_value' col contains 'N/A' strings, not to be converted
  155. v.db.update mygeodetic_pts col=zval \
  156. qcol="CAST(z_value AS double precision)" \
  157. where="z_value &lt;&gt; 'N/A'"
  158. </pre></div>
  159. <h3>Example of concatenation of fields</h3>
  160. <i>Note: not supported for DBF driver.</i>
  161. <div class="code"><pre>
  162. v.db.update vectormap column=column3 qcolumn="column1 || column2"
  163. </pre></div>
  164. <h3>Example of conditions</h3>
  165. Conditions (like if statements) are usually written as CASE statement in SQL:
  166. <div class="code"><pre>
  167. v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 12 THEN cat else NULL end"
  168. # a more complex example with nested conditions
  169. v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
  170. </pre></div>
  171. <h2>SEE ALSO</h2>
  172. <em>
  173. <a href="db.select.html">db.select</a>,
  174. <a href="db.execute.html">db.execute</a>,
  175. <a href="v.db.select.html">v.db.select</a>,
  176. <a href="v.db.update.html">v.db.update</a>
  177. </em>
  178. <p>
  179. <a href="databaseintro.html">Database management in GRASS GIS</a>,
  180. <a href="database.html">Help pages for database modules</a>
  181. <p>
  182. <i>Last changed: $Date$</i>