topic Problem with sql view union between two feature classes in Geodatabase Questions

Problem with sql view union between two feature classes

KevinDunlop — Tue, 31 Jan 2017 21:29:50 GMT

Hi all,

I am having a problem with a view I created in our database.

Background: We have a section of county that is on a military base. They maintain their own roads but we have a agreement that we can assist with EMS services as needed. A few years ago, they re-addressed their roads but allow residents to use either the new or the old address. For our 911 system we need to create a feature class that shows all roads and with duplicated records for roads with old names. All other applications do not need the old names so we do not want to include in our normal road centerline feature class. So we have our normal road centerline (CENPWC) feature class and a separate feature class for the old names (CENQMCB_old). I want to union (via a view) them together so that I have single view with both sets of roads. Both feature classes are versioned.

Environment: Oracle 11g with SDE 10.3.1

Code:

CREATE OR REPLACE VIEW CENPWC_NG911_VIEW AS
SELECT cenpwc_evw.globalid,
cenpwc_evw.shape
FROM cenpwc_evw
UNION ALL
SELECT cenqmcb_old_evw.globalid,
cenqmcb_old_evw.shape
FROM cenqmcb_old_evw

*Note, I will add the other attributes in later after I get it to work in ArcMap.

Error: When I bring it into ArcMap, and open the attribute table it reads about half the records then I get the following error:

"Error reading OID from table. Reading rows has been stopped. Check that the datasource is valid.

The given coordinate references are incompatible

The operation is not supported by this implementation."

Both feature classes have the same coordinate system and extents. The view works fine when I view it in Oracle's SQL Developer.

What am I missing? Thanks.

Re: Problem with sql view union between two feature classes

AdamZiegler1 — Wed, 01 Feb 2017 16:04:11 GMT

Hi Kevin - Are you using ST_GEOMETRY? There is a chance that the spatial references are slightly different, even though they are the same reference system. Check out the SDE_geometry_columns table and grab the 'srid' of each feature class. Then find those srids in the SDE_spatial_references table and validate that they are the same.

-Adam Z

Re: Problem with sql view union between two feature classes

KevinDunlop — Thu, 02 Feb 2017 14:59:18 GMT

Thanks that was the problem. I had CENPWC at SRID 5, CENQMBC_OLD at 2283. When I used the project tool from ArcGIS Desktop toolbox on CENQMBC_OLD it set the SRID for it 113 even though I imported the CENPWC coordinate system. I finally can to copy both of them down to a FGDB but them in a dataset then copy them back into SDE. This gave them both SRID 5 and the view works just fine now.