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