1

I am trying to update a table B geom column with the coordinates from table A. Table A having coordinates in two separate columns x and y. Table B to have the coordinates from Table A into Table B's geom spatial column.

Table A and B have an ID in common for each record.

I usually use FME to do this but this time I'd like to do it using pure SQL.

Any suggestions on how to do it?

1

This should work if your spatial table is "geo_table" and using SRID=4326, geometry column is "geometry", and coordinates are in table "coord_table" in columns "X" and "Y"

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);
  • Just nitpicking: you don't need the MDSYS prefix. Also parentheses around SDO_GEOMETRY are not required. But that is the correct solution. – Albert Godfrind Jun 3 '15 at 9:39
  • Thanks, the cleaner the better so I edited my answer. I only tested that the statement gave correct result. – user30184 Jun 3 '15 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.