Spatial Queries Return Duplicate Rows after upgrade to 12c (Doc ID 2275024.1)

Last updated on JUNE 20, 2017

Applies to:

Oracle Spatial and Graph - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

After upgrade to 12c,, spatial queries return duplicate rows from the same data that returned single rows in 11g.

SELECT
  NODE_NUM,
  T.Y AS LATITUDE,
  T.X AS LONGITUDE
FROM
  SPDATA N,
  TABLE(SDO_UTIL.GETVERTICES(SDO_CS.TRANSFORM(N.LOCATION, 8307))) T;
  
  SELECT
  (CASE WHEN LENGTH(N.NODE_NUM) > 8 THEN SUBSTR(N.NODE_NUM,6) ELSE NODE_NUM END) NODE_NAME,
  T.Y AS LATITUDE,
  T.X AS LONGITUDE
FROM
  SPDATA N,
  TABLE(SDO_UTIL.GETVERTICES(SDO_CS.TRANSFORM(N.LOCATION, 8307))) T
WHERE
  N.LOCATION.SDO_POINT.Z is null
/


NODE_NUM  LATITUDE LONGITUDE
---------- ---------- ----------
EL19 29.9665456 -90.15257
EL19 -.02646733 1
CL57 29.998382 -90.192411
CL57 -.06721721 1
CL49 29.9968849 -90.178122
CL49 -1 .177634278
EL28 29.9482084 -90.189116
EL28 .792701432 -1
EL31 29.961672 -90.194642
EL31 -.30489663 -1

The 2nd row for each node_num is not returned in 11g.

Changes

 Upgrading database to 12c (12.1.0.2) from 11.2.0.4

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms