Offset to geometry field

[Otavio Macedo]

Hi,

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?

[Bill Karwin]

"Otavio Macedo" <ot**********@g mail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Hi,

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?

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.

Regards,
Bill K.

[Otavio Macedo]

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
fields.

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
do:

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.

[Bill Karwin]

"Otavio Macedo" <ot**********@g mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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.

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

UPDATE geotest
SET coord = PointFromText(C ONCAT('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.

Regards,
Bill K.