topic Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes in Data Management Questions

Oracle SQL - Spatial View With Union of Polygon Feature Classes

MatthewMiles — Thu, 24 Jul 2014 23:21:39 GMT

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)

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?

Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes

VinceAngelo — Thu, 24 Jul 2014 23:29:12 GMT

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

Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes

MatthewMiles — Sat, 26 Jul 2014 23:06:32 GMT

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.

Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes

VinceAngelo — Sun, 27 Jul 2014 13:27:47 GMT

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

Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes

MatthewMiles — Wed, 30 Jul 2014 14:22:22 GMT

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!

Re: Oracle SQL - Spatial View With Union of Polygon Feature Classes

ModyBuchbinder — Thu, 13 Nov 2014 06:06:18 GMT

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