Update values in an ST_Geometry spatial column

The SQL UPDATE statement alters the values in a spatial column just as it does any other type of attribute. Typically, spatial attribute data must be retrieved from the table, altered in a client application, then returned to the server.

The following SQL statements illustrate how to fetch and update the spatial data from one row in the hazardous_sites table for each of the supported databases:

Steps:
  1. Open an SQL editor and connect to your database.

    Connect using an account that has privileges to update the table.

  2. Update the value stored in an ST_Geometry column.
    • Oracle
      UPDATE hazardous_sites 
       SET location = sde.st_geometry('point (18 57)', 4326) 
       WHERE site_id = 102;
      
    • PostgreSQL
      UPDATE hazardous_sites 
       SET location = st_point('point (18 57)', 4326) 
       WHERE site_id = 102;
      
    • SQLite
      UPDATE hazardous_sites 
       SET location = st_point('point (18 57)', 4326) 
       WHERE site_id = 2;
      
    • IBM DB2
      UPDATE hazardous_sites 
       SET location = db2gse.st_pointfromtext('point(18 57)', 1) 
       WHERE site_id = 102
      
    • IBM Informix
      UPDATE hazardous_sites 
       SET location = st_pointfromtext('point(18 57)', 1) 
       WHERE site_id = 102
      
6/19/2015