Updating 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:
    • Oracle
      UPDATE hazardous_sites 
      SET location = sde.st_pointfromtext('point(18 57)', 1) 
      WHERE site_id = 102;
    • PostgreSQL
      UPDATE hazardous_sites 
      SET location = st_point('point (18 57)', 1) 
      WHERE site_id = 102;
    • 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

2/5/2013