Change srid in mysql

Question

Is it possible to change the srid of a column of geometry type? I just want to create a view of geometry type data from the raw latlon data and use it in the geoserver. However after using the pointfromtext function, the type of data I generate is geometry rather than point and geoserver would treat it as an feature typ of byte array which can not be used in the geoserver. However if I use the 'point' function directly in the mysql, I can get the exact type of point however the srid is not right.

So my question is can I set the srid for the geometry type of data?

Answer 1

Actually to do what you want in SQL Server 2008, I had to do the following (change all the data in EPGS:4326):

update TestGeom set geom = geometry::STGeomFromText(geom.STAsText(), 4326)

I don't know if in MySQL you can do the same kind of thing. Otherwise, you can rebuild your table with something similar to this:

update TestGeom 
set geom = geometry::STGeomFromText('POINT ('+ REPLACE(CONVERT(nvarchar, TestGeom.Lon), ',','.')+' '+REPLACE(CONVERT(nvarchar, TestGeom.Lat), ',','.')+' )', 4326)

I hope it can help you.