Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: March 31, 2017 - 3:02 am UTC

Last updated: April 03, 2017 - 12:51 am UTC

Version: 11.2.0.4.7

Viewed 10K+ times! This question is

You Asked

Hello there,

I'm using Oracle 11.2.0.4 and I wanted to create sdo geometric column by using longitude and latitude. I got longitude and latitude stored in another table. All I have to do is generate the geometry for that coordinates.

for instance:

latitude: -37.52279481
longitude: 145.305951204

SRID: 4283 (Geodetic coordinate system)

output should be something like this:

MDSYS.SDO_GEOMETRY(2001,4283,MDSYS.SDO_POINT_TYPE(145.516531118182,-37.8744300272727,NULL),NULL,NULL)

Can you please help me by throwing light on oracle function that can be used to generate the geometry

Joseph

and Connor said...

Sorry - am I missing something ? You appear to have answered your own question ?

SQL> create table t ( s sdo_geometry);

Table created.

SQL>
SQL> insert into t values  ( SDO_GEOMETRY(2001,4283,MDSYS.SDO_POINT_TYPE(145.516531118182,-37.8744300272727,NULL),NULL,NULL) ) ;

1 row created.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joseph Swamidass, April 03, 2017 - 12:42 am UTC

Hello Connor,



ha ha Yes sound easy isn't? that was an example. Thanks for your reply. I want to construct a geometry like that.

I have a table which has got latitude and longitude from which I have to populate table b which has got sdo_geometry column and that should be in the below format


"MDSYS.SDO_GEOMETRY(2001,4283,MDSYS.SDO_POINT_TYPE(145.516531118182,-37.8744300272727,NULL),NULL,NULL)"

So I was wondering if I could use any Oracle function to generate the above geometric field.

Thanks



Connor McDonald
April 03, 2017 - 12:51 am UTC 

SQL> create table source ( lat number, lng number);

Table created.

SQL> insert into source values (145.516531118182,-37.8744300272727);

1 row created.

SQL> insert into source values (123.345,-23.35634);

1 row created.

SQL> insert into source values (90.134,-76.234524);

1 row created.

SQL>
SQL>
SQL> create table t ( s sdo_geometry);

Table created.

SQL> insert into t
  2  select SDO_GEOMETRY(2001,4283,MDSYS.SDO_POINT_TYPE(lat,lng,NULL),NULL,NULL)
  3  from source;

3 rows created.


Joseph Swamidass, April 03, 2017 - 3:07 am UTC

Hello Connor,



ha ha Yes sound easy isn't? that was an example. Thanks for your reply. I want to construct a geometry like that.

I have a table which has got latitude and longitude from which I have to populate table b which has got sdo_geometry column and that should be in the below format


"MDSYS.SDO_GEOMETRY(2001,4283,MDSYS.SDO_POINT_TYPE(145.516531118182,-37.8744300272727,NULL),NULL,NULL)"

So I was wondering if I could use any Oracle function to generate the above geometric field.

Thanks


PS: If I'm reposting it coz I cannot able to view the review I posted.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Spatial/Graph

Need more information on Spatial? Check out the Spatial dev guide for the Oracle Database