+6I have a SPATIAL table with the following columns: X_coord, Y_coord and a SPATIAL column GEOMETRY
The X and Y coordinates are adjusted. With which attribute can I adjust the GEOMETRY columns?
It has data_type SDO_GEOMETRY (Oracle).
Contents of a record:
MDSYS.SDO_GEOMETRY(2001, 28992, MDSYS.SDO_POINT_TYPE(201234.5678, 47123.4567, NULL), NULL, NULL)
Best answer by s.jager
First of all: if you can, change your datamodel. You are duplicating data now, which is not necessary. It’s easy enough using a view some application needs the X and Y separately in columns instead of as sdo_geometry, or even virtual columns (this being Oracle).
Secondly: That’s really easy to do in just sql:
update geo_table a set a.geometry=
(select
SDO_GEOMETRY(2001,
4326,MDSYS.sdo_point_type(b.X,b.Y, NULL),NULL, NULL)
from coord_table b
where a.id=b.id);This of course assumes you have a column id that uniquely identifies every row. If you don’t, then you really should add one, because if you don’t have a unique identifier then you will end up either updating all rows in the table, or making the above sql a bit more complicated and less reliable/performant.
Third: if you can only use FME, and have no other options, then have a look at the VertexCreator. That will do what you want.
+27First of all: if you can, change your datamodel. You are duplicating data now, which is not necessary. It’s easy enough using a view some application needs the X and Y separately in columns instead of as sdo_geometry, or even virtual columns (this being Oracle).
Secondly: That’s really easy to do in just sql:
update geo_table a set a.geometry=
(select
SDO_GEOMETRY(2001,
4326,MDSYS.sdo_point_type(b.X,b.Y, NULL),NULL, NULL)
from coord_table b
where a.id=b.id);This of course assumes you have a column id that uniquely identifies every row. If you don’t, then you really should add one, because if you don’t have a unique identifier then you will end up either updating all rows in the table, or making the above sql a bit more complicated and less reliable/performant.
Third: if you can only use FME, and have no other options, then have a look at the VertexCreator. That will do what you want.