Offset to geometry field

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am working with MySQL 4.1 and I got a table with a point field. I
need to do an "offset" in all the points, that is, adding some deltaX
to the X coordinate and a deltaY to the Y coordinate.

How can I do this using the update statement?

Re: Offset to geometry field

Quoted text here. Click to load it

Yes, in SQL you can write expressions in UPDATE statements that reference
the fields you're changing:

UPDATE tableName
  SET xcoord = xcoord + 47,
          ycoord = ycoord - 96;

The example above adds the _same_ offset to all points.  If you want to
change each point by a different offset, the easiest way to do it is to
write n UPDATE statements, each with WHERE clauses to affect only the
respective points.

Bill K.

Re: Offset to geometry field

OK, Bill, but what I got is a 'point' data type (a descendant of
'geometry' data type). The x and y components are not in separate

Suppose I create the following table:

CREATE TABLE geotest (
  id integer not null primary key,
  coord point

To insert a new record with coordinates x=12, y=49, for example, I'd

INSERT INTO geotest VALUES (1, pointfromtext('point(12 49)'));

What I am trying to figure out is how to use the UPDATE statement with
geometry fields, like this one. MySQL provides spatial data support,
but it is still not well documented.

Re: Offset to geometry field

Quoted text here. Click to load it

The only way that is apparent to me is (for example, to add +10,+19 to a

UPDATE geotest
  SET coord = PointFromText(CONCAT('point(', X(coord)+10, ' ', Y(coord)+19,
  WHERE id = 12345;

I tried the above and it works.

I also tried a test of something like:

UPDATE geotest SET x(coord) = x(coord)+10;

But that doesn't work.  Apparently one cannot use a spatial function as an
lvalue in an UPDATE.

Bill K.

Site Timeline