last updated: 2018-11-03



Understanding TopoGeo_FromGeoTableExt dustbin results

Used Table:

The Goal of this topic

Is to show. when using TopoGeo_FromGeoTableExt:
  • dustbin tables: to resolve a problem
  • Explain the meaning of known RTTOPO error messages
dustbin_middle_earth_farthings as Table (not a SpatalTable)
  • ROWID contains the value of the ROWID of the original table
  • id_rowid (name differs for each table) and contains the value of the Primary Key of the original table
  • message the messase returned from rttopo function rtt_AddLine (set in _rtt_AddEdge)
  • tolerance the tolerance used in TopoGeo_FromGeoTableExt
  • failing_geometry the geometrycausing the error (will be either a POINT or a LINESTRING)

Notes::
  • The second columns (here: id_rowid) will differ in every TABLE, since it is the name of the Primary Key of the original table
  • failing_geometry is not registered as a geometry, which is the reason why dustbin_middle_earth_farthings is not a SpatialTable
  • The error message, will reference a faulty Edge or geometry, which has not been added to the Topology

dustbin_middle_earth_farthings_view as a SpatalView
  • Will contain all columns of the original source row (including the registered geometry)
  • as well as all columns of dustbin_middle_earth_farthings

Notes::
  • The source geometry can be viewed in QGis, where the error message can be shown as a lable

 The columns ROWID, tolerance and failing_geometry will not be listed

id_rowid message
10 Side-location conflict: new edge starts in face 15 and ends in face 16
16 SQL/MM Spatial exception - geometry crosses edge 44
19 Side-location conflict: new edge starts in face 0 and ends in face 21
25 SQL/MM Spatial exception - geometry crosses edge 83
26 SQL/MM Spatial exception - geometry crosses edge 17

Notes:
  • The value of id_rowid: tells us which geometry is causing problems
  • message : is telling us what the cause of the error is

Conclusion:
 all we have to do, if figure out what the message means and how to deal with it.
SELECT TopoGeo_FromGeoTableExt
( --topo-name to use
 'topology_admin_borders',
 -- db-prefex (of an ATTACHed Database,
 -- if NULL=MAIN/local-table]
 'db_import',
 -- table-name of external (non-topo) TABLE
 'middle_earth_farthings',
 -- column-name of external TABLE
 -- (NULL can be used of ONLY 1 Geometry,
 -- otherwise column-name of geometry]
 'eur_polygon',
 -- name of the table intended to store PK values,
 -- with error message
 -- corresponding to features failing to be imported
 'dustbin_middle_earth_farthings',
 -- name of the Spatial View supporting
 'dustbin_middle_earth_farthings_view',
 -- lines-max-points
 NULL,
 -- lines-max-length
 NULL,
 -- tolerance [default=0]
 0
);

Note:
UpdateLayerStatistics
  should be called to display the SpatialView in QGis.

id_rowid=19
Lebennin


Side-location conflict: new edge starts in face n1and ends in face n1 :
  the first questions to be resolved are: what is the rowid if
  • Face 0 and Face21 id_rowid=19 [Lebennin] has no face since it failed.
    • Face (0+1)=1, id_rowid=1: [Andrast (Ras Morthil)}
    • Face (21+1)=22, id_rowid=3: [Anórien}

    Cause: id_rowid=19 crosses over id_rowid=18 [Lamedon] Faces 6,9,19,20
    • A new edge for [id_rowid=19[Lebennin]] and
    • starts in face [face_id=1, id_rowid=1, name[Andrast (Ras Morthil)]] and
    • ends in face [face_id=22, id_rowid=3, name[Anórien]]
id_rowid=10
Eastfold

  • Face 15 and Face16 id_rowid=10 [Eastfold] has no face since it failed.
    • Face (15+1)=16, id_rowid=14: [Forlindon}
    • Face (16+1)=17, id_rowid=15: [Harlindon}

    Cause: id_rowid=10 [makes no since at all]
    • A new edge for [id_rowid=10, name[Eastfold]] and
    • starts in face [face_id=16, id_rowid=14, name[Forlindon]] and
    • ends in face [face_id=17, id_rowid=15, name[Harlindon]]
SELECT
 'A new edge for ['||
 (SELECT 'id_rowid='||id_rowid||', name['||name||']]' FROM topology_admin_borders_topofeatures_1 WHERE (id_rowid=19))||
 ' and starts in face ['||
  (SELECT 'face_id='||topo_feature.face_id||', id_rowid='||layer_feature.id_rowid||', name['||layer_feature.name||']]' FROM topology_admin_borders_topofeatures_1 layer_feature
  JOIN topology_admin_borders_topofeatures topo_feature ON (topo_feature.fid=layer_feature.id_rowid) WHERE topo_feature.face_id IN ((0+1)))||
 ' and ends in face ['||
 (SELECT 'face_id='||topo_feature.face_id||', id_rowid='||layer_feature.id_rowid||', name['||layer_feature.name||']]' FROM topology_admin_borders_topofeatures_1 layer_feature
  JOIN topology_admin_borders_topofeatures topo_feature ON (topo_feature.fid=layer_feature.id_rowid) WHERE topo_feature.face_id IN ((21+1))) AS message;


A new edge for [id_rowid=19, name[Lebennin]] and starts in face [face_id=1, id_rowid=1, name[Andrast (Ras Morthil)]] and ends in face [face_id=22, id_rowid=3, name[Anórien]]

id_rowid=10
Eastfold

SQL/MM Spatial exception - geometry crosses edge n1 :
  the first question to be resolved is: to which geometry does the edge belong to
id_rowid=25 SQL/MM Spatial exception - geometry crosses edge 83
  • Face 15 and Face16 id_rowid=25 [Eastfold] has no face since it failed.
    • Face 15=16, id_rowid=14: [Forlindon}
    • Face 16=17, id_rowid=15: [Harlindon}

    Cause: id_rowid=10 [makes no since at all]
    • A new edge for [id_rowid=10, name[Eastfold]] and
    • starts in face [face_id=16, id_rowid=14, name[Forlindon]] and
    • ends in face [face_id=17, id_rowid=15, name[Harlindon]]
id_rowid=25 SQL/MM Spatial exception - geometry crosses edge 83 [feature 29]
SELECT
 'SQL/MM Spatial exception - geometry ['||
 (SELECT 'id_rowid='||id_rowid||', name['||name||']]' FROM topology_admin_borders_topofeatures_1 WHERE (id_rowid=25))||
 ' crosses edge ['||
 (SELECT 'face_id='||features.face_id||', id_rowid='||farthings.id_rowid||', name['||farthings.name||']]' FROM middle_earth_farthings farthings
  JOIN topology_admin_borders_topofeatures features ON (features.fid=farthings.id_rowid) WHERE features.face_id IN ((0+1)))||
 ' and ends in face ['||
 (SELECT 'face_id='||features.face_id||', id_rowid='||farthings.id_rowid||', name['||farthings.name||']]' FROM middle_earth_farthings farthings
  JOIN topology_admin_borders_topofeatures features ON (features.fid=farthings.id_rowid) WHERE features.face_id IN ((21+1))) AS message;

SELECT source_edge.edge_id, source_edge.geom AS edge_geom,source_edge.left_face,source_edge.right_face, source_face.rowid, source_face.geom AS face_geom, topo_feature.face_id,topo_feature.fid AS feature_id, layer_feature.name FROM topology_admin_borders_edge source_edge JOIN topology_admin_borders_face_geoms source_face ON ((source_face.rowid=source_edge.left_face) OR (source_face.rowid=source_edge.right_face)) JOIN topology_admin_borders_topofeatures topo_feature ON (source_face.rowid=topo_feature.face_id) JOIN topology_admin_borders_topofeatures_1 layer_feature ON (topo_feature.fid=layer_feature.fid) WHERE (source_edge.edge_id=(83+1))
A new edge for [id_rowid=19, name[Lebennin]] and starts in face [face_id=1, id_rowid=1, name[Andrast (Ras Morthil)]] and ends in face [face_id=22, id_rowid=3, name[Anórien]]


last updated: 2018-11-03