How to read SDO_GEOMETRY

limor

hi,

I will create a webserver to fetch geometries out of my oracle database (11g Locator).

I am using node.js 4.4.7.

My query works fine for types NUMBER and VARCHAR!

But in addition I wanna read out SDO_GEOMETRY but if I try this the following error occurs:

NJS-010: unsupported data type in select list

Does it mean that I am not able to fetch SDO_GEOMETRY types in the current version?

The work around woudl be to transform the geometry in another type to read this out...

There exists a function inside the database "sdo2geojson3d" which converts the sdo_geometry to a json ( inside db = clob type). But I only get this as result:

  [ 1315274,

    Lob {

      _readableState: [Object],

      readable: true,

      domain: null,

      _events: [Object],

      _eventsCount: 2,

      _maxListeners: undefined,

      _writableState: [Object],

      writable: true,

      allowHalfOpen: true,

      iLob: [Object] },

    'BuildingGroundSurface' ],

Is there an easy way to read out sdo_geometries?

Christopher Jones-Oracle

There is no native support (as of node-oracledb 1.10) for object types.  You will have to write a wrapper module in PL/SQL and decompose the component parts.  Some of the techniques mentioned in the section "Using Oracle Locator for Spatial Mapping" of the free PHP & Oracle book apply to node-oracledb.

Also see https://github.com/oracle/node-oracledb/issues/147 where this enhancement request is being tracked.

limor

Does it make sense to create a new column for each polygon and convert the coordinates out of the sdo_geometry in an array.

Then I can fetch these coordinates directly. This will be faster I think. Am I right?

I tried it with cx_Oracle but this also have no native support. Now I will check the running time with node.js and then I can make a decision.

I am looking for a fast method to fetch only the coordinates based on the sdo_geometry.

Christopher Jones-Oracle

Python's cx_Oracle has object (e.g. SDO) support in the default branch: https://bitbucket.org/anthony_tuininga/cx_oracle This release hasn't been finalized or bundled yet but you could try it.

Regarding performance, you should benchmark with your environment and data sizes.  If I was just getting the point type for a small data set, I might convert them into 3 "index-by" arrays of x, of y, and of z values. Then I'd bind the three arrays using https://github.com/oracle/node-oracledb/blob/master/doc/api.md#plsqlindexbybinds   This may or may not be the best for your use case.