Change srid in mysql

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开发者_如何学Python 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?

---

This is one way of doing it in MySQL:

   UPDATE table SET shape = ST_GeomFromText(ST_AsText(shape), SRID);

Where SRID should be the new SRID code (for example 4326 for WGS84). Keep in mind that this only changed the SRID code and not the actual coordinates stored in the shape.

---

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.

---

I was able to do this in MySQL 5.7 using the following technique:

update location_polygons
set multipoly = ST_GeomFromGeoJSON(ST_AsGeoJSON(multipoly), 2, 0)
where SRID(multipoly) <> 0

Based on this documentation URL: https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html