Spatial View Not Displaying All Geometry

[WalterDziuba1]

Afternoon,

I've created a spatial view with ArcCatalog and have now added it as the data source to my project *.mxd

The view contains 149,400 records. lt's been constructed from one of our data sets and inner joined to three other tables from an external database. The view has a 1:M cardinality to the remaining external data.

When I attempt to update my map extent in ArcMap, not all of the geometry is being displayed. But only a small fraction of the points display correctly.

My Question : Why is the entire collection of points not being displayed?

Walter

[WalterDziuba1]

Checked the "Data Store" on the server and have validated all registered connections. Also, reviewed database connection properties with the database name being correct, USER = sde and version=dbo.DEFAULT.

Walter

[VinceAngelo]

Best practice is to avoid ever registering a connection with the 'sde' USER.  The 'sde' user should only be used for instance administration, not for table creation, and certainly not for web-based table access.

- V

[WalterDziuba1]

I created a stand alone table and populated the table with 200,000 auto incremental numbers.

From there, I used this table as a temporary table in my next script and used those number and then assigned them to be a value in my spatial view with the column name "AutoID'

Walter

[VinceAngelo]

Without the SQL it's hard to be positive, but I can't imagine how that wouldn't have the same issue as a row number. The value must be organic to the table/view, so that a query by ID returns the same row it did on a previous query.  This is why a row number doesn't work, since row 5 returned with a WHERE clause of "color = 'RED'" might return a row with "color = 'BLUE'" when queried by ID.

[IreneEgbulefu2]

Hi Vince, Please how do I go about inserting this rowID because I'm having exactly the same issue here but imj yet to try including the rowid that would help tie the geometry

"The purpose of the rowid column is to tie the geometries in the map canvas to the attributes in the table view (and vice-versa)"

I strongly believe this could be the answer to my issue unfortunately, I dont know where to fix in the rowid

This is what  my SQL query looks like.

CREATE VIEW PARKVIEW AS

SELECT TC_User.TX_COUNTY_PARKS.ParkID, TC_User.TX_COUNTY_PARKS.Name, TC_User.TX_COUNTY_PARKS.Park_Acres,

                  TC_User.TX_COUNTY_PARKS.YearOpen, TC_User.TX_COUNTY_PARKS.ParkStatus, TC_User.TX_COUNTY_PARKS.StatusCmnt,

                  TC_User.TX_COUNTY_PARKS.ParkType, TC_User.TX_COUNTY_PARKS.Shape, TC_User.TX_COUNTY_PARK_FEATURES.FEATURE_NAME,

                  TC_User.TX_COUNTY_PARK_FEATURES.FEATURE_TYPE, TC_User.TX_COUNTY_PARK_FEATURES.FEATURE_LABEL,

                  TC_User.TX_COUNTY_PARK_FEATURES.GIS_Acres, TC_User.TX_COUNTY_PARK_FEATURES.Shape AS Expr1,

                  TC_User.TX_COUNTY_PARK_FEATURES.GIS_SqFt, TC_User.TX_COUNTY_PARK_TRAILS.LENGTH, TC_User.TX_COUNTY_PARK_TRAILS.WIDTH,

                  TC_User.TX_COUNTY_PARK_TRAILS.Trail_Name, TC_User.TX_COUNTY_PARK_TRAILS.Trail_Type,

                  TC_User.TX_COUNTY_PARK_TRAILS.Shape AS Expr2

FROM     TC_User.TX_COUNTY_PARKS INNER JOIN

                  TC_User.TX_COUNTY_PARK_FEATURES ON TC_User.TX_COUNTY_PARKS.OBJECTID = TC_User.TX_COUNTY_PARK_FEATURES.OBJECTID INNER JOIN

                             TC_User.TX_COUNTY_PARK_TRAILS ON TC_User.TX_COUNTY_PARKS.OBJECTID = TC_User.TX_COUNTY_PARK_TRAILS.OBJECTID

[VinceAngelo]

No one can answer this without information on the cardinality of the relationships between TX_COUNTY_PARKS and TX_COUNTY_PARK_FEATURES and TX_COUNTY_PARKS with TX_COUNTY_PARK_TRAILS.  If either is one-to-many, you cannot use TX_COUNTY_PARKS.OBJECTID as a rowid.

- V

[IreneEgbulefu2]

Yes Vince, The Criminality is  one to Many. Because there are a number of trails and features in one Park area.. Im was  trying to use the TX_COUNTY_PARKS.OBJECTID as a rowid to see if it will work but since it didnt work out, i am kind of stuck there. The PARKID which is the table primary key, though it is unique but it is not an integer it is a string (Combination of letters and numbers) couldn't serve as the ROWID (which I presume should be an Integer). The PARKS.OBJECTID was system generated. In one of the posting I read, there was a recommendation to create an imaginary rowid to enable arcmap process the column, now my problem is how to create this imaginary rowid and where to fix it without destabilizing the table

[VinceAngelo]

Adding an imaginary rowid to a view is a terrible idea, and won't work (for the reasons explained above)

Query Layers will use a string field as a registered column, but your column isn't unique across rows in the view, and therefore not applicable.

You have two choices: Use the ArcGIS paradigm (relates and/or joins) or use the database paradigm (materialized view).

- V

PS: I certainly hope criminality isn't a factor, only out-of-control auto-correct.