Updating Oracle Spatial geometry column using SQL?

Question

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?

• Mapperz: stackoverflow.com/questions/14707442/…
• user30184: @Mapperz, that answer about how to update SRID does not help much with this challenge.

Answer 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);

• Albert Godfrind: Just nitpicking: you don't need the MDSYS prefix. Also parentheses around SDO_GEOMETRY are not required. But that is the correct solution.
• user30184: Thanks, the cleaner the better so I edited my answer. I only tested that the statement gave correct result.