+ Reply to Thread
Results 1 to 4 of 4

Thread: Spatial View, one to many relationships, unexpected results

  1. #1
    Brad Fisher
    Join Date
    Jul 2010
    Posts
    93
    Points
    11
    Answers Provided
    1


    0

    Default 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.

    Brad
    Brad Fisher
    -------------
    Sopra Group
    @gisfish

  2. #2
    Vince Angelo

    Join Date
    Feb 2010
    Posts
    2,712
    Points
    755
    Answers Provided
    108


    0

    Default 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

  3. #3
    Brad Fisher
    Join Date
    Jul 2010
    Posts
    93
    Points
    11
    Answers Provided
    1


    0

    Default 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.

    Cheers

    Brad
    Brad Fisher
    -------------
    Sopra Group
    @gisfish

  4. #4
    Vince Angelo

    Join Date
    Feb 2010
    Posts
    2,712
    Points
    755
    Answers Provided
    108


    0

    Default 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

+ Reply to Thread

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts