Oracle SQL - Spatial View With Union of Polygon Feature Classes

2420
5
Jump to solution
07-24-2014 04:21 PM
MatthewMiles
New Contributor II

Hi!

So at my office I curate a lot of datasets maintained by other (ArcGIS-compatible, but not ArcGIS-using) departments, fed into an Oracle-based SDE, for consumption by all departments in an intranet web mapping system used by all departments and all divisions at my company.

One of the groups of data I have contains two feature classes of point data that are functionally very similar, and for an unrelated project it looked like I needed both FCs merged into one FC, so I tried to accomplish this using a union in a Spatial View:

(Table names have been changed and some fields removed for anonymity's sake)

CREATE OR REPLACE FORCE VIEW POINTS_COMBINED

(OBJECTID, SHAPE)

AS

  SELECT CAST (ROWNUM AS INTEGER) AS OBJECTID, SHAPE

  FROM

  (

  SELECT SHAPE FROM POINTS_1

  UNION ALL

  SELECT SHAPE FROM POINTS_2

  )

  ORDER BY OBJECTID;

This seems to work perfectly - the total number of features in the Spatial View is the same as the sum of the features in the individual FCs.

With this, I decided to try the same method with a couple of polygon FCs, but for whatever reason I get the error "Stream Not Found" when I use literally the same SQL statement with the FC names changed to reflect the polygon FCs.

Any idea what I'm missing? I've checked the Spatial Reference for both and they're the same, so it's not that, as far as I can tell. Is it possibly just that polygon shape data is more complex than point shape data?

1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

You can't use ROWNUM as a registered rowid.  The value MUST be repeatable on subsequent

queries, or the graphic to table relationship will be random.

- V

View solution in original post

0 Kudos
5 Replies
VinceAngelo
Esri Esteemed Contributor

You can't use ROWNUM as a registered rowid.  The value MUST be repeatable on subsequent

queries, or the graphic to table relationship will be random.

- V

0 Kudos
MatthewMiles
New Contributor II

Is there another way to create a unique non-nullable integer field in the view using an expression, then? Most of the solutions I found by googling around online just suggested using the ROWNUM on the container level.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

No, I don't think it's possible to construct a UNION view without unique rowid values in the source tables. It's far easier to tear apart a single table than to join multiple tables in this manner (and the performance would be better as well).

-V

0 Kudos
MatthewMiles
New Contributor II

I was afraid of that. Oh, well. It's not too hard to just add another GP to my update scripts to accomplish the same thing; I was just mostly trying this out as an experiment.

Thank you for the help!

0 Kudos
ModyBuchbinder
Esri Regular Contributor

Hi V

I am running into the same problem.

If there is a field (for example uKey) that is unique between the two tables (my responsibility to manage it) can I use it as OBJECTID?

Should I register my view into the geodatabase once it created?

Thanks

Mody

0 Kudos