0

When I use QGIS to show Oracle spatial data, only the last line of an Multiline is shown. For instance, in this multiline geometry, only the segment from 20,10,0 to 30,10,0 is shown

insert into rr.DB_ORIGIN_PATHS( id, geom) select 3, SDO_GEOMETRY( 3002, NULL, NULL, 
          SDO_ELEM_INFO_ARRAY( 1,4,2,  1,2,1,   4,2,1)
        , SDO_ORDINATE_ARRAY(          10,10,0, 20,10,0, 30,10,0 )) from dual;

What should I do to make QGIS show all the lines in my multiline geometry ?

This is a sample from the actual production data as a response to user30184:

select
        SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT( SDO_GEOMETRY( 3002, NULL, NULL,  
        SDO_ELEM_INFO_ARRAY(1,4,3, 1,2,2, 7,2,1, 10,2,2), 
        SDO_ORDINATE_ARRAY(193438.746000003, 326577.692999998, 0, 193437.195826577, 326578.284037557, 0, 193435.539999961, 326578.387000003, 0, 193433.818000015, 326577.163000011, 0, 193433.67635475, 326577.068722151, 0, 193433.522895758, 326576.995223623, 0 ))
        ,0.005) from dual;

Also gives the following:

54530 Point:0,Edge:1,

I see all kind of gaps when I use QGIS to look at this production data, but the software used to draw this data (Intergraph G/Technology) doesn't.

1

The problem with the validation is because the geometry is 3D, which comes with limitations in the structures.

Here the validation rejects the line because of the (1,4,2) triplet. That is used to represent composite lines, i.e. lines formed by a mix of circular arcs and straight lines. In your case all the number 2 means a composite line formed by two sections: (1,2,1) and (4,2,1). But those sections are straight lines, and this encoding is not needed. The proper encoding is:

SDO_GEOMETRY( 3002, NULL, NULL,  SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(10,10,0, 20,10,0, 30,10,0 )

I imagine that QGIS is getting confused by the composite-line encoding. Maybe it simply does not understand it at all.

This is not a multi-line. It is a single line formed by one composite element. A true multi-line would be like this:

SDO_GEOMETRY( 3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,2,1, 4,2,1), SDO_ORDINATE_ARRAY(10,10,0, 20,10,0, 30,10,0 )

Notice that the geometry type is 3006 where 6 indicates a multi-line.

EDIT: I just noticed your second example:

SDO_ELEM_INFO_ARRAY(1,4,3, 1,2,2, 7,2,1, 10,2,2), 
    SDO_ORDINATE_ARRAY(193438.746000003, 326577.692999998, 0, 193437.195826577, 326578.284037557, 0, 193435.539999961, 326578.387000003, 0, 193433.818000015, 326577.163000011, 0, 193433.67635475, 326577.068722151, 0, 193433.522895758, 326576.995223623, 0 )

That once is definitely invalid for 3D lines in Oracle. We only allow straight lines in 3D shapes, and that composite element contains arcs. Here the validation properly rejects the data.

If you validate the shape in 2D, then you will get no error:

select  SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT( 
          sdo_cs.make_2d (
            SDO_GEOMETRY( 3002, NULL, NULL,  
              SDO_ELEM_INFO_ARRAY(1,4,3, 1,2,2, 7,2,1, 10,2,2), 
              SDO_ORDINATE_ARRAY(
                193438.746000003, 326577.692999998, 0, 193437.195826577, 326578.284037557, 0, 193435.539999961, 326578.387000003, 0, 193433.818000015, 326577.163000011, 0, 193433.67635475, 326577.068722151, 0, 193433.522895758, 326576.995223623, 0 
              )
            )
          ),
          0.005
        ) 
from dual;

Now this is just Oracle's validation complaining about the shape. I don't think that is what troubles QGIS - I mean it does not validate each and every shape it fetches: that would make it unbearably slow. I suspect that it just does not understand the composite types.

One last possibility is that it complains because the shapes are 3D. Or the combination of 3D and composites ? Maybe it does understand composites in 2D ?

You can easily turn the data into 2D by running it through the SDO_CS.MAKE_2D() function. It has the effect of stripping out the Z values. Maybe QGIS would be happy with that ?

Either way, your data is not really 3D, just some shapes with all Z set to 0. They should properly be encoded as 2D.

  • Thanks for your answer, but this is just the way the data is and I need to handle it. So I don't have 3006 gtypes but 3002, and the first triplet is 1,2,4. According to the Oracle documentation this construct is allowed : docs.oracle.com/database/121/SPATL/… – Steef Aug 12 '17 at 6:10
  • We are probably too restrictive with compound elements when used in 3D. In your case, that compound element contains straight lines, so should technically be valid. I will take a look into that. – Albert Godfrind Aug 14 '17 at 9:26
  • However, the fact that Oracle's validation rejects the shape presently is unlikely to have anything to do with QGIS not accepting it. I suspect QGIS simply does not understand composite elements. – Albert Godfrind Aug 14 '17 at 9:28
  • Curve geometries are supported by GDAL since version 2.0 trac.osgeo.org/gdal/wiki/rfc49_curve_geometries but it may be that combination 3D and compound curves is not supported with Oracle. At least it is not tested in the autotest suite trac.osgeo.org/gdal/browser/trunk/autotest/ogr/ogr_oci.py. I would have a try by converting geometries into 2D. Because you do not have meaningful Z coordinates it would mean no loss of data. – user30184 Aug 14 '17 at 13:46
  • We introduced support for full 3D in 11.1. Before that you could have 3D shapes, but the Z were just ignored. One of the main restrictions with 3D shapes is that only straight lines are allowed. Circular arcs / curves are not. allowed in 3D. This is why the validation fails on finding something like "n,2,2" in the element info array. – Albert Godfrind Aug 15 '17 at 7:23
0

Are you sure that your SDO_GEOMETRY is valid? I tried

select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(
SDO_GEOMETRY( 3002, NULL, NULL,  SDO_ELEM_INFO_ARRAY(
1,4,2,  1,2,1,   4,2,1), SDO_ORDINATE_ARRAY(
10,10,0, 20,10,0, 30,10,0 )),0.005) from dual;

The result was

54530 Point:0,Edge:1,

Which seems to mean

ORA-54530: invalid etype for element at element offset
    Cause: An invalid etype was encountered.
    Action: Correct the etype of the geometry.

I am surprised that QGIS is drawing anything.

  • I'm getting the same validation error as you, but it is data I'm seeing in a production system although I simplified it to make it more readable. This is a actual geometry found in this system (see edit) – Steef Aug 11 '17 at 17:12
  • If software is made to write invalid data they are most probably made also to handle such in reading. Does Intergraph have a bug tracker? – user30184 Aug 11 '17 at 18:05
  • They do have software to read this, but I am supposed to merge some of these geometries and its very cumbersome to view the result, so I'd rather use QGIS. – Steef Aug 12 '17 at 6:03
  • Try to find a validation function from Oracle for making a fixed table or view. Or export data with Intergraph to some other format. – user30184 Aug 12 '17 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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