sql.html 7.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217
  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/ogr">http://gdal.org/ogr/</a></td></tr>
  42. </table>
  43. <h2>NOTES</h2>
  44. <h3>Database table name restrictions</h3>
  45. <ul>
  46. <li> No dots 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. d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
  73. where="valley &lt;&gt; 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"
  74. </pre></div>
  75. <p>
  76. Select all attributes from table where <i>str1</i> column values are not 'No
  77. Name':
  78. <div class="code"><pre>
  79. echo "SELECT * FROM archsites WHERE str1 &lt;&gt; 'No Name'" | db.select
  80. </pre></div>
  81. <p>
  82. <p>
  83. Example of subquery expressions from a list (not supported for DBF driver):
  84. <div class="code"><pre>
  85. v.db.select mysites where="id IN ('P04', 'P05')"
  86. </pre></div>
  87. <h3>Example of pattern matching</h3>
  88. <div class="code"><pre>
  89. # field contains string:
  90. # for DBF driver:
  91. v.extract rivers out=rivers_noce where="DES LIKE 'NOCE'"
  92. # for SQLite driver:
  93. v.extract rivers out=rivers_noce where="DES LIKE '%NOCE%'"
  94. # match exactly number of characters (here: 2), does not work for DBF driver:
  95. v.db.select mysites where="id LIKE 'P__'"
  96. #define wildcard:
  97. v.db.select mysites where="id LIKE 'P%'"
  98. </pre></div>
  99. <h3>Example of null handling</h3>
  100. <div class="code"><pre>
  101. v.db.addcolumn map=roads col="nulltest int"
  102. v.db.update map=roads col=nulltest value=1 where="cat &gt; 2"
  103. d.vect roads where="nulltest is null"
  104. v.db.update map=roads col=nulltest value=2 where="cat &lt;= 2"
  105. </pre></div>
  106. <h3>Update of attributes</h3>
  107. Examples of complex expressions in updates (using <tt>v.db.*</tt>
  108. modules):
  109. <div class="code"><pre>
  110. v.db.addcolumn map=roads col="exprtest double precision"
  111. v.db.update map=roads col=exprtest value=cat/nulltest
  112. v.db.update map=roads col=exprtest value=cat/nulltest+cat where=cat=1
  113. </pre></div>
  114. <p>
  115. Examples of complex expressions in updates (using <tt>db.*</tt>
  116. modules):
  117. <div class="code"><pre>
  118. echo "UPDATE roads SET exprtest=null"
  119. echo "UPDATE roads SET exprtest=cat/2" | db.execute
  120. echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
  121. echo "UPDATE roads SET exprtest=NULL WHERE cat&gt;2" | db.execute
  122. echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"
  123. </pre></div>
  124. <p>
  125. Instead of creating and updating new columns with an expression, you
  126. can use the expression directly in a command:
  127. <div class="code"><pre>
  128. d.vect roads where="(cat/3*(cat+1))&gt;8"
  129. d.vect roads where="cat&gt;exprtest"
  130. </pre></div>
  131. <h3>Example of changing a SQL type (type casting)</h3>
  132. <i>Note: not supported for DBF driver.</i>
  133. <p>
  134. North Carolina data set: convert string column to double precision:
  135. <p>
  136. <div class="code"><pre>
  137. # first copy map into current mapset
  138. g.copy vect=geodetic_pts,mygeodetic_pts
  139. v.db.addcolumn mygeodetic_pts col="zval double precision"
  140. # the 'z_value' col contains 'N/A' strings, not to be converted
  141. v.db.update mygeodetic_pts col=zval \
  142. qcol="CAST(z_value AS double precision)" \
  143. where="z_value &lt;&gt; 'N/A'"
  144. </pre></div>
  145. <h3>Example of concatenating fields</h3>
  146. <i>Note: not supported for DBF driver.</i>
  147. <div class="code"><pre>
  148. v.db.update vectormap column=column3 qcolumn="column1 || column2"
  149. </pre></div>
  150. <h3>Example of conditions</h3>
  151. Conditions (like if statements) are usually written as CASE statement in SQL:
  152. <div class="code"><pre>
  153. v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 12 THEN cat else NULL end"
  154. # a more complex example with nested conditions
  155. v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
  156. </pre></div>
  157. <h2>SEE ALSO</h2>
  158. <em>
  159. <a href="db.select.html">db.select</a>,
  160. <a href="db.execute.html">db.execute</a>,
  161. <a href="v.db.select.html">v.db.select</a>,
  162. <a href="v.db.update.html">v.db.update</a>
  163. </em>
  164. <p>
  165. <a href="databaseintro.html">Database management in GRASS GIS</a>,
  166. <a href="database.html">Help pages for database modules</a>
  167. <p>
  168. <i>Last changed: $Date$</i>