1

I am trying to find nearest road segment to a point in Oracle. I can do this with SDO_GEOMETRY type i.e

select 
/*+ LEADING(pois) INDEX(route ALL_ROUTES_SPTAIL_INDEX)  */  
route.*
from DEVICE_STATUS pois,ALL_ROUTES route where 
MDSYS.sdo_nn(route.shape, pois.shape,'sdo_num_res=1') = 'TRUE'
and pois.objectid=729415

How can I find nearest neighbor when both tables have ST_GEOMETRY type? or is there any way to convert ST_GEOMETRY to SDO_GEOMETRY

Improve this question
3
  • 1
    You accepted Asad's answer so I will guess he answered what you thought you were asking. However, I would read your question to be asking how to find the nearest neighbors for "Oracle's ST_GEOMETRY", e.g. MDSYS.ST_GEOMETRY. Asad is talking about "Esri's ST_GEOMETRY" or SDE.ST_GEOMETRY. They are two utterly different items from two different vendors. I am just putting this comment here for anyone else coming along with the docs link: docs.oracle.com/en/database/oracle/oracle-database/18/spatl/…
    – pauldzy
    Dec 19, 2019 at 11:45
  • 1
    @pauldzy, since there isn't any way to find nearest neighbor from st_geometry. Therefore, only option I am left with is to convert ST_GEOMETRY(Oracle's or Esri's, does not matter here) to sdo_geometry first and then find the neighbors.
    – Ayaz49
    Dec 19, 2019 at 12:47
  • The term "ST_GEOMETRY" is overloaded in the Oracle context, because there is a native type with that name. You should specify SDE.ST_GEOMETRY where you mean the Esri type and MDSYS.ST_GEOMETRY where you reference the native Oracle type. The alternative is to explicitly call out the vendor.
    – Vince
    Dec 19, 2019 at 14:12

1 Answer 1

Reset to default
2

First you will need to convert st_geom to wkt and then create sdo_geom from wkt, something like this should work.

select SDO_UTIL.FROM_WKTGEOMETRY(sde.st_astext(shape),SRID) from table;

you can also refer to this or you can also try

select sdo_geometry(sde.st_astext(shape),SRID) from table;

as described here

Improve this answer
1
  • 1
    The thing that makes nearest-neighbor work is the ability to leverage the spatial index. Casting between types will not utilize a spatial index, and therefore not perform adequately for tables of any significance. You need a "second" (or more) to pair with your "first".
    – Vince
    Dec 19, 2019 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.