Skip to main content

I 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)

 

 

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.


@s.jager 

Thank you for your detailed explanation.

I can get by with this.


Reply