By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Offset to geometry field

P: n/a
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?

Feb 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Otavio Macedo" <ot**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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.
Feb 3 '06 #2

P: n/a
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.

Feb 3 '06 #3

P: n/a
"Otavio Macedo" <ot**********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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(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.

Regards,
Bill K.
Feb 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.