Need to update points and shift them west

Question

I have a MySQL spatial table with millions of points. Some of these points are off by some constant fractional number of degrees. I can select the points in question like so.

SELECT AsText(Point) as point FROM records WHERE ST_CONTAINS(GeomFromText(?), point);

Where my GeomFromText is a polygon containing the points I want to shift. What's the best way to go about shifting large numbers of points about a degree to the west? My first thought is to iterate over my result set and update each point individually, but I'm concerned about performance when I get into the millions of rows area.

Is there a better way to accomplish shifting a large number of geospatial points?

BTW, I'm currently accessing the database via Nashorn/Java but I could run something on the database terminal as well.

sol4me sol4me · Accepted Answer · 2015-01-04T22:25:53

You can use batch update i.e. rather then sending the updates one by one. just sent updates queries in batch E.g.

statement.addBatch("your update query1");
statement.addBatch("your update query2);
int[] recordsAffected = statement.executeBatch();

You can also use PreparedStatement (An object that represents a precompiled SQL statement.) to execute batch updates, which enables you to reuse same sql statement with different parameters.

You can also create a stored procedure and invoke it from java.

Need to update points and shift them west

1 Answers