hi there,
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?
muchas gracias!
it would be something like this:
update geo_table a set a.geometry=
(select
SDO_GEOMETRY(2001,
8311,MDSYS.sdo_point_type(b.X,b.Y, NULL),NULL, NULL)
from coord_table b
where a.id=b.id);
even better answer. I made it work today. below is the complete code to make it work. Never mind the table names.
–this SQL will take the spatial LON and LAT from a non spatial table and
–insert them into a spatial geometry column on another table
–making the second table a spatial table
DELETE FROM user_sdo_geom_metadata
WHERE table_name = ‘KEN2’;
DROP TABLE KEN2;
COMMIT;
CREATE TABLE ken2
AS
(SELECT *
FROM ken
WHERE lat IS NOT NULL);
COMMIT;
ALTER TABLE ken2 ADD geom SDO_GEOMETRY;
INSERT INTO user_sdo_geom_metadata
VALUES (‘ken2’,
‘geom’,
MDSYS.sdo_dim_array (MDSYS.sdo_dim_element (‘Longitude’,
-180,
180,
.05),
MDSYS.sdo_dim_element (‘Latitude’,
-90,
90,
.05)),
8311);
COMMIT;
UPDATE ken2 a
SET a.geom =
(SELECT SDO_GEOMETRY (2001,
8311,
MDSYS.sdo_point_type (b.lon, b.lat, NULL),
NULL,
NULL)
FROM ken b
WHERE a.combined_id IS NOT NULL AND a.combined_id = b.combined_id);
COMMIT;
CREATE INDEX KEN2_spx
ON KEN2 (geom)
INDEXTYPE IS