<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="populating-spatial-columns">
      </a>
      13.4.7 Populating Spatial Columns
     </h3>
    </div>
   </div>
  </div>
  <p>
   After you have created spatial columns, you can populate them
        with spatial data.
  </p>
  <p>
   Values should be stored in internal geometry format, but you can
        convert them to that format from either Well-Known Text (WKT) or
        Well-Known Binary (WKB) format. The following examples
        demonstrate how to insert geometry values into a table by
        converting WKT values to internal geometry format:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Perform the conversion directly in the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40367248"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token string">'POINT(1 1)'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'POINT(1 1)'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Perform the conversion prior to the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60588488"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token string">'POINT(1 1)'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The following examples insert more complex geometries into the
        table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3958881"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'LINESTRING(0 0,1 1,2 2)'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span>
<span class="token string">'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The preceding examples use
   <a class="link" href="gis-wkt-functions.html#function_st-geomfromtext">
    <code class="literal">
     ST_GeomFromText()
    </code>
   </a>
   to create
        geometry values. You can also use type-specific functions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86706678"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'POINT(1 1)'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_PointFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'LINESTRING(0 0,1 1,2 2)'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_LineStringFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span> <span class="token string">'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_PolygonFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token variable">@g</span> <span class="token operator">=</span>
<span class="token string">'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomCollFromText</span><span class="token punctuation">(</span><span class="token variable">@g</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A client application program that wants to use WKB
        representations of geometry values is responsible for sending
        correctly formed WKB in queries to the server. There are several
        ways to satisfy this requirement. For example:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Inserting a
      <code class="literal">
       POINT(1 1)
      </code>
      value with hex
            literal syntax:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15629375"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token function">ST_GeomFromWKB</span><span class="token punctuation">(</span>X<span class="token string">'0101000000000000000000F03F000000000000F03F'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      An ODBC application can send a WKB representation, binding
            it to a placeholder using an argument of
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      type:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa24970565"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> geom <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">ST_GeomFromWKB</span><span class="token punctuation">(</span>?<span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      Other programming interfaces may support a similar
            placeholder mechanism.
     </p>
    </li>
    <li class="listitem">
     <p>
      In a C program, you can escape a binary value using
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-escape-string-quote.html" target="_top">
       <code class="literal">
        mysql_real_escape_string_quote()
       </code>
      </a>
      and include the result in a query string that is sent to the
            server. See
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-escape-string-quote.html" target="_top">
       mysql_real_escape_string_quote()
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
