topic Re: Spatial View Not Displaying All Geometry in Mapping Questions

Spatial View Not Displaying All Geometry

WalterDziuba1 — Tue, 23 May 2017 19:27:49 GMT

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

Re: Spatial View Not Displaying All Geometry

JoshuaBixby — Tue, 23 May 2017 20:16:38 GMT

How do you know the view contains 149,400 records? Are you getting that count from within ArcGIS, from a different SQL client, or somewhere else? If within ArcGIS, does the table view show 149,000 records but you don't see or think all of those are being displayed on the map?

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Wed, 24 May 2017 12:03:46 GMT

Morning Joshua,

I can review the total number of records while in ArcCatalog > Preview > Table.

The view contains a 1:M cardinality, with one of our GIS data sets joined to three external database tables.

Yes, while in ArcMap, the attribute table lists the complete contents of the view, and I can see from the display that not all of the geometry is being displayed.

Walter

Re: Spatial View Not Displaying All Geometry

JoshuaBixby — Wed, 24 May 2017 12:28:43 GMT

Walter Dziuba wrote:
the attribute table lists the complete contents of the view, and I can see from the display that not all of the geometry is being displayed.

Have you looked at or scrolled through all 149,000 records or are you relying on the record count being reported in the table? How many records to you estimate are being displayed? 1,000; 10,000; 100,000? What if you click the "Move to the end of the table" button when viewing the table, do more records display?

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Wed, 24 May 2017 14:10:48 GMT

I can open the attribute table and scroll to end of table, but no additional point data is being displayed. Out of the 1,837 data points I have in my GIS related table, approx only half of the point appear either while looking at the attribute table, or not.

Walter

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Wed, 24 May 2017 14:17:51 GMT

Please clarify how the one-to-many cardinality is being manifested -- Does the feature class honor the absolute requirement that the objectid be unique? If it doesn't, you're in the realm of undefined behavior, where literally anything can happen (including the creation of a black hole to swallow your server).

- V

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Wed, 24 May 2017 14:40:23 GMT

I'm using a pre-tested sql statement and am using ArcCatalog > New > View with the following statement that creates the joins in the view:

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Wed, 24 May 2017 17:16:36 GMT

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). If the rowid result set values are non-unique, all manner of strange results will occur.

In this context, you might be able to use a materialized view (called "indexed views" in SQL Server) to create a table with a unique ID (probably ought to preserve the source table IDs as well), or you can use relates and/or joins to teach ArcGIS the relationships between the tables (likely to be slower).

- V

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Fri, 26 May 2017 13:44:51 GMT

Now that I've realized that not all of my point data has connections to the spatial view that I am creating, therefore, not all the point geometry will show up. Great so far.

Thanks Vince... Was successful in populating a unique number in a new column in my spatial view and have added it to my SDE feature class.

I've attempted to publish the map service, unfortunately, I get a 'SEVERE' error in ArcGIS server that states:

Error: DBMS table not found[42S02:[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'EDMS_GIS_Test_Deploy.xxxx\ARCGISDEV.GRWSPATIALRAD'.].

The spatial view attached in my .mxd document states that it's

"EDMS_GIS_Test_Deploy.dbo.GRWSPATIALRAD

My Questions:

1. So where do these erroneous names come from?

2. How can the map publishing be successful after no errors were generated during the analyze phase?

Walter

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Fri, 26 May 2017 14:03:38 GMT

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

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Fri, 26 May 2017 14:36:09 GMT

Please explain how you went about

populating a unique number in a new column in my spatial view

If you used a row number in the view, this could cause random results (and undefined behavior).

- V

Re: Spatial View Not Displaying All Geometry

WalterDziuba1 — Fri, 26 May 2017 14:47:28 GMT

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

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Fri, 26 May 2017 14:57:15 GMT

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.

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Fri, 26 May 2017 15:00:40 GMT

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

Re: Spatial View Not Displaying All Geometry

IreneEgbulefu2 — Tue, 08 Aug 2017 17:22:16 GMT

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

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Tue, 08 Aug 2017 18:42:33 GMT

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

Re: Spatial View Not Displaying All Geometry

IreneEgbulefu2 — Tue, 08 Aug 2017 18:53:50 GMT

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

Re: Spatial View Not Displaying All Geometry

VinceAngelo — Tue, 08 Aug 2017 21:31:54 GMT

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.