Toad World® Forums

oracle spatial update SQL


#1

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! :slight_smile:


#2

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


#3

even better answer. I made it work today. below is the complete code to make it work. Never mind the table names. :slight_smile:

–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