3 Replies Latest reply on May 1, 2012 6:29 AM by vangelo-esristaff

    Spatial View, one to many relationships, unexpected results

      I have a created a spatial view from the following input:  poly fc (unique ParcelID) & attribute table (not unique ParcelID resulting in a one 2 many relationship).

      Syntax below:

      sdetable -o create_view -T LR_Land_Owners_SV_noOLWands -t LR_Land_Owners,LR_Stakeholders -c "LR_Land_Owners.OBJECTID,LR_Land_Owners.PARCELID,LR_Land_Owners.Shape,LR_Stakeholders.NATURE,LR_Stakeholders.Stakeholder,LR_Stakeholders.Frequency,LR_Stakeholders.multi_stakeholders,LR_Stakeholders.order_limits" -a "OBJECTID,PARCELID,SHAPE,NATURE,STAKEHOLDER,FREQUENCY,MULTI_STAKEHOLDERS,ORDER_LIMITS" -w "LR_Land_Owners.PARCELID = LR_Stakeholders.PARCELID AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'" -u XXXXX -p XXXXX -i sde:sqlserver:database_instance -D LTTGISV

      The view is created as i would expect, the results are as i would expect whereby i get a list of stakeholders as defined by above. 

      However, the issue being that when i review the data in ArcGIS desktop, whereby i run a defn query against the stakeholder attribute, I get values for the stackholder attribute that SHOULD not be listed.  I can understand why its happening (the join on PARCELID is resulting in first 'random' record where the one2many relationship exist).  Interestingly if you run query against the attribute table, it will only let see the unique records (for stakeholder) as defined (restricted) by your query defn.  Would one agree its a bug? 

      The issue is avoided if i put the stakeholder query within the spatial view query definition but I'm trying to avoid this since this results in unique spatial views for each of my stakeholders (whereas if i keep the stakeholder query in the mxd (query definition) it enables me to have a single 'stakeholder' spatial view). 

      I note 'identifying' the parcels in question seems to always return the correct value for stakeholder; as does my web app which is dependent on the spatial view.

      I have played with the join type in the view (database level) but with no success. 

      Interested in any feedback anyone has to give as while i think i understand what is happening, it seems someone inconsistent.

        • Re: Spatial View, one to many relationships, unexpected results
          Actually, this is the expected result -- chaos!

          One-to-many views are not supported by ArcGIS because it *must* have a unique registered
          rowid column (objectid) to tie the table to the geometry.  This view would be supported if you
          had a unique NOT NULL integer column in the stakholders' table, and specified *that* in lieu
          of the non-unique OBJECTID (note a rownum is not permitted, because the value must be
          reproducible in subset queries).

          Relationship classes are the geodatabase methodology to provide full support for "many"
          relationships in ArcGIS.

          - V
          • Re: Spatial View, one to many relationships, unexpected results
            Vince thanks for prompt response.

            While i appreciate your comments Vince & agree partially, i still cannot help but think the sequence in which the order of the sql statement being run is varying between that which makes up the 'table view' in the desktop (seems that the defn query is fired after the spatial view is read), and that of an attribute query (or identify) via desktop upon which the firing the sql defn & spatial view is being considered as a single statement which will return an expected result (fyi parcelID & stakeholder for the join table results in a unique value; hence this is why if the spatial view includes the sql where statement for the stakeholder (e.g.  AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'") you end up with as expected results. 

            I can appreciate unexpected behaviour but i would expect it to be consistent (contradicting myself!) between desktop functionality (if you get what i am saying).  I'm not saying this is necessarily a bug, nor by design, but i suspect the underlying sql statements are NOT taking the same path (order) to return the end results & hence the inconsistency.

            One thing to note, the SV defn i provided, i inadvertently included the Stakeholder query (AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'") to make the record set for the join table unique.  This isn't an issue, its just this SV gives consistent results.

            W.R.T. your comments, are you inferring the join be based upon an objectid (i should think not (for obvious reasons) but i don't quite understand your statement re *must* have unique registered rowid).  Both tables in the spatial view are gdb registered with each having objectids.    You go on further to highlight *that* in lieu of the non-unique OBJECTID - can you elaborate a little more.

            Appreciate i'm obviously at an advantage of having greater understanding of my data than yourself.


            • Re: Spatial View, one to many relationships, unexpected results
              Yes, secondary queries do probably take a different path through the SQL engine, but if the
              registered rowid was unique (as it is required to be), it wouldn't matter.

              Some folks try to game the unique requirement by returning row number in the view rowid
              column, but since the value is used to refetch the row, this will result in random results without
              the previous WHERE constraint (and that's before you consider caching and other optimizer tricks
              that could reorder the return sequence).

              Since you do have an OBJECTID in LR_Stakeholders, you should use that in the create_view
              column list (vice the 'many' one).  I've done this extensively with GeoNames data that actually
              has distinct places and names (alternate spellings for the same location); so long as you fetch
              the rowid from the many table, ArcGIS won't notice that one-to-many isn't supported.

              - V