Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm building simple views against an ArcSDE Oracle RAC 10g. My SHAPE columns are of SDO_GEOMETRY datatype. ArcSDE 9.3.1.

I have several point tables, with the following outline:

ID (Guid) Name (string) Shape (SHAPE)

I've built a simple view liek this in Oracle!

CREATE OR REPLACE VIEW VW_POINTS AS

    SELECT ID, Name, Shape from TABLE_A

        UNION ALL

    SELECT ID, Name, Shape from TABLE_B

        -- etc

The view in Oracle is created just fine. I've registered this view with the command line:

sdetable -o create_view -T POINTS -t VW_POINTS -c Id, Name, SHAPE etc

The ArcSDE view is created again, just fine. I can use ArcObjects to query it, etc. I need now to create a Layer. I've tried the command line tools, sdelayer -o register, but I had no success. It gives me a message, saying that views are not suported.

I will need to do this several times, sometimes using complex syntax for building the view.

What is the problem? How can visualize these spatial views as SDE layers (I don't need to edit them, just view them spatially inside ArcMap)

Thanks for the input!

EDIT

This is a screenshot in ArcMap, for your deligth :P

ArcMap Screenshot for your happiness:

As requested, here comes the describe_long

// create_view C:\Users\H>sdetable -o create_view -s server -i port -u user -p pass -T view_airspace_points -t vw_airspace_points_oracle -c "*"

at this point I altered the view on oracle, including a cast as numeric(38,0) before registration.

// register layer C:\Users\H>sdelayer -o register -l view_airspace_points,shape -C OBJECTID,USER - g RTREE -s server -i port -u user -p pass -e p -P HIGH

ArcSDE 9.3.1 for Oracle10g Build 2784 Tue Oct 27 10:51:14 2009

Layer Administration Utility

Layer Description ....:

Table Owner ..........: SIGMAGIS2

Table Name ...........: VIEW_AIRSPACE_POINTS

Spatial Column .......: SHAPE

Layer Id .............: 1398

SRID .................: 79

Minimum Shape Id .....: 1

Offset ...............:

falsex: -180.000000

falsey: -90.000000

System Units .........: 994200.000000

Z Offset..............: 0.000000

Z Units ..............: 1.000000

Measure Offset .......:

Measure Units ........:

XY Cluster Tolerance .: 2.0

Spatial Index ........:

parameter: SPIDX_RTREE

exist: Yes

array form: -2,0,0

Layer Envelope .......:

minx: -180.00000, miny: -90.00000

maxx: 180.00000, maxy: 90.00000

Entities .............: p

Layer Type ...........: In-Line Spatial Type

Creation Date ........: 06/28/11 09:28:45

I/O Mode .............: NORMAL

Autolocking ..........: Enabled

Precision.............: High

User Privileges ......: SELECT, UPDATE, INSERT, DELETE

Coordinate System ....: GEOGCS["Longitude / Latitude [WGS 84]",DATUM["WGS 84",SPHEROID["WGS 84",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Decimal

Degree",0.0174532925199433]]

Layer Configuration ..: DEFAULTS

share|improve this question
    
So you need to create a layer file? –  D.E.Wright Jun 21 '11 at 0:53
    
No, I need to create a view inside the geodatabase, that acts like a featureclass. –  George Jun 21 '11 at 2:36
    
Then you are needing to create the spatial view as you say and I mention below; The only reason I can see it not showing up as a Spatial layer is do to permissions. Either ArcGIS/ArcSDE didn't register it properly; since that is just the purpose of a spatial layer, to join spatial and tabular data in a uniform manner. –  D.E.Wright Jun 21 '11 at 3:42
    
When you run the describe_long like 'sdetable -o describe_long -t victoria -u sasha -p polar.bear -i esri_40' on your view, what results do you get? We are needing to see if the spatial column and entity type are getting set and referenced right. –  D.E.Wright Jun 28 '11 at 0:06
1  
D.E. thanks for the alert. I've done everything correctly, but still does not work. :( very frustating. This can be done with an OpenSource stack quite easily. –  George Jun 28 '11 at 19:21
show 3 more comments

7 Answers

up vote 2 down vote accepted

I had this (and similar problems) in the past. It always came down to two things:

  1. The spatial table had some corrupt/invalid geometries. Problem with ArcMap is that as soon as it finds an invalid geometry it would stop rendering.

  2. The layer registration is incorrect. What I also found is that the sdelayer -o register doesn't always work correctly and you have to 'massage' the registration entries.

So to check if the layer is valid use FIRST the SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT function. Then use the ArcSDE validation function sdelayer -o feature_info -r invalid ... to get invalid features according to ArcSDE. Probably along with sdelayer -o list (see here)

For checking the registration, you have to go back into the ArcSDE schema. The tables that control the registration are: SDE.LAYERS, SDE.TABLE_REGISTRY, SDE.COLUMN_REGISTRY, SDE.SPATIAL_REFERENCES and SDE.GEOMETRY_COLUMNS. One trick I use is that i usually have a 'base' layer that is similar (as far as geometry types are concerned and primary key columns) that DISPLAYS in ArcMap. I then compare the registration records, betweeen the layer that displays and then one that doesn't. You will be amazed to find what a difference a change by 1 in an integer column makes! :-)

A tip on COLUMN_REGISTRY - all you have to do is to make sure that the Primary key column (what is defined as ROWID_COLUMN in the SDE.TABLE_REGISTRY) and the Spatial Column are correct (or same as the layer that displays). Ignore all other columns. If you add the layer in ArcMap logged in as the table owner the missing entries in the COLUMN_REGISTRY table will be added automatically.

Let me know if this helps!

share|improve this answer
    
mapholic thanks for the input! I'll try it first thing tomorrow! I hope you are right! Thanks! –  George Jul 5 '11 at 0:01
    
answered mapoholic. This worked for different types of registrations, but not for UNIONED one. Nevertheless, this is an amazing tip. Thanks for the answer. –  George Aug 3 '11 at 16:25
add comment

I've sort of scanned down the replies and no one seems to have observed that union queries are a bit of a problem for spatial views with ArcGIS. this is because you will almost certainly end up with duplicate objectids. The same is true of inner join queries where the joining predicates do not guarantee a unique "parent" record. in either case although the view may conform to all the registration requirements it will not behave correctly

share|improve this answer
    
Thanks for the input Rob. If you take a close look at the view I wrote, it generates OIDs on the fly using Oracles ROWNUM. THey are guaranteed to be unique. –  George Jul 19 '11 at 22:31
    
I'm not 100% convinced about the rownum usage if the data is being displayed in arcmap (for instance). Rownum is a function of the oracle query set. so each time you execute a query you get a new set of rownums starting at 1. This may mean that each pan/zoom etc will produce an objectid of 1 and any process expecting to use cached objectids will fail since subsequent queries will produce a new result. Have you looked at make query table tool as an option to get ArcGIS to add the objectid column for you? –  Rob McPherson Jul 20 '11 at 11:43
add comment

In my experience, when a layer isn't displaying in Arc, it's because the spatial index is missing. Views use the spatial index of the source table, but it looks like you are creating a spatial index for the view in your registration statement.

Do either of the source tables have spatial indexes? If not, try creating them in Oracle and re-registering the view without the -g.

Another possibility is that you are combining geometry from two tables into a single column, not sure if that's a no-no, haven't tried it before.

share|improve this answer
    
I will try that Matt. I'm also combining multiple geometries into one in another view and it's kinda crucial that this works. We are building linestrings on a view and that unioned view was supposed to be the single point of support for those layers. I'm trying other approaches, since I'm not seeing this happening. –  George Jun 28 '11 at 19:18
add comment

Please try to run following command: sdelayer -o alter -l [-S ] [-k ] [-i | | ] [-s ] [-D ] -E calc -u [-p ]

share|improve this answer
    
Thanks for the comment. Already tried to recalc the extent of the layer. –  George Jun 28 '11 at 12:03
add comment

One thing I see missing is a unique integer column that ArcObjects can use for the object ID. Try creating a single sequence that will maintain unique values for this id column. Then create insert triggers on each of the tables to populate the id column with the sequence.nextval.

In your sdelayer -o register command, be sure to set the -C flag to your integer id column with the key word USER so that esri doesn't try to manage the column for you.

share|improve this answer
1  
Hello jrockers, the registration process just works out fine. read my own answer to the thread. I'm using an Oracle generated column (ROWNUM) casted as numeric(38,0). Thanks for the tip, but I've already tried that. –  George Jun 27 '11 at 19:11
add comment

Thanks to all that viewed this thread.

Well the process is a bit complicated, but it is possible to register a complex query as a layer, it just takes some crafty thought. I'm not sure what are the implications on perfomance on this yet, but I'll try to keep this updated. I'm also with problems for viewing the points in ArcMap, but I'll investigate further.

First of all, create of complex spatial view in your RDBMS. Just be sure that you have a INTEGER UNIQUE NOT NULL column in the view, that can be used later for registration with ArcSDE.

My queries were just a bunch of UNION ALL, similar to this:

    SELECT OBJECTID, ID_UNIQUE_ID, AIRSPACE_ID, NM_INDICATIVE, 'XXX' AS "ELEMENT_TYPE",SHAPE FROM tb_XXX    
        UNION ALL

    SELECT OBJECTID, ID_UNIQUE_ID, AIRSPACE_ID, NM_INDICATIVE, 'YYY' AS "ELEMENT_TYPE",SHAPE FROM TB_YYY

In this schema, all my objects have as PK ID_UNIQUE_ID, which is a GUID. My ObjectIDs repeated themselves across feature classes, so, not a good choice for our surrogate ObjectID.

I used (in my case) Oracle's rownum virtual column to wrap all my queries and create a view that had an effective "objectID". Take a look:

SELECT 
    CAST(ROWNUM AS NUMBER(38,0)) AS "FID",
    OBJECTID,
    ID_UNIQUE_ID,
    AIRSPACE_ID,
    NM_INDICATIVE,
    ELEMENT_TYPE,
    SHAPE 
FROM 
(
    SELECT OBJECTID, ID_UNIQUE_ID, AIRSPACE_ID, NM_INDICATIVE, 'ZZZ' AS "ELEMENT_TYPE",SHAPE FROM TB_ZZZ

        UNION ALL

    SELECT OBJECTID, ID_UNIQUE_ID, AIRSPACE_ID, NM_INDICATIVE, 'XXX' AS "ELEMENT_TYPE",SHAPE FROM tb_XXX

        UNION ALL

    SELECT OBJECTID, ID_UNIQUE_ID, AIRSPACE_ID, NM_INDICATIVE, 'YYY' AS "ELEMENT_TYPE",SHAPE FROM TB_YYY
)

I created the view with the query above, and created a view on top of it. This view will become the spatial layer inside arcsde. This is a fairly easy process.

After that, I've tried and tried to register my spatial view as a layer, but always failed. When you create a view on top of it, ArcSDE assumes that FID is a NUMBER(38,10), a floating point number, according to the sdetable -o describe utility program.

In Oracle, this was the definition for the view:

  CREATE OR REPLACE FORCE VIEW "schema"."layer_name" ("FID", "OBJECTID", "ID_UNIQUE_ID", "AIRSPACE_ID", "NM_INDICATIVE", "SHAPE") AS 
  SELECT  CAST(fid AS NUMBER(38,0)) AS FID, objectid, id_unique_id, airspace_id, nm_indicative, shape FROM  schema.VW_AIRSPACE_POINTS_ORACLE;

After I've replaced the fid column with a cast, everything worked fine. I proceeded to registration of the layer and it went fine.

EDIT

The layer displays in ArcCatalog as a point layer and I can see and select the table in ArcMap as usual (the points are even highlighted), but I cannot simbolize them and view them as a regular layer. Any thougths?

Anyone has any ideas why this happens? Everything is fine, except that I cannot see the spatial objects inside ArcMap. Any ideas?

share|improve this answer
1  
You can do a lot with your performance of that view if you keep your indexes up to date, I have set scheduled jobs to run on systems with high numbers of transactions to keep the indexes rebuilt every night. Then when you are doing your joins they are better tuned. –  D.E.Wright Jun 21 '11 at 15:57
    
What happens if you drag and drop from arccatalog into a new empty map in arcmap? –  Kirk Kuykendall Jun 27 '11 at 18:44
    
ArcMap adds it to the TOC, but it renders nothing. The weird thing is that I can select the rows, and the geometry of that row will draw (selected). When rows are not selected nothing is drawn. Querying via ArcMap also works. –  George Jun 27 '11 at 19:11
1  
Interesting. If you write arcobjects code what does IGeoDataset.Extent look like for the featurelayer? And for the featureclass? –  Kirk Kuykendall Jun 27 '11 at 20:32
    
I'll try that! Let's see. –  George Jun 28 '11 at 12:03
add comment

What you are looking for is a Spatial-View, now the only major issue you will see here is that you need to be careful with the fields you are joining on. This is a pretty nice process, I use it all over the place where I have generalized my DB to talk with thin spatial data.

    sdetable -o create_view -T emp_region_view -t "employees,world.regions" 
-c "employees.name,employees.emp_id,world.regions.reg_id,world.regions.rname,world.regions.region"
-a "employee,eid,rid,region,area" -w "employees.emp_id = world.regions.emp_id"
-s myodbserver -i sde:oracle11g:ora1 -u gdb -p gdb.bdg

This should get you well on your way to what you want. The key is getting your fields to map correct. I tend to just do the view with the minimum I need, then use a UI to edit that view once registered in SDE to do my more fine-grain tweaks.

share|improve this answer
    
Hello D.E. I've tried this method but it does not work. For what I understand is that ArcSDE creates a "CREATE FORCE VIEW" command, stating the columns before hand and only register the columns you declare in the clause. I've tried creating an empty view (using just a feature class, to get the geometric definition) and replacing it for another definition. Ok, I can query this via ArcMap, but the geometry is NOT shown in the map. –  George Jun 22 '11 at 13:38
1  
If you drag the spatial view from ArcCatalog to ArcMap, it does not show up with geometric data? –  Seth P. Jun 27 '11 at 18:50
    
As stated before, the layer does not display in ArcMap or ArcCatalog preview, but it is listed as a layer of correct geometry type and it's queryable in ArcMap. –  George Jun 27 '11 at 19:52
1  
When you run the describe_long like 'sdetable -o describe_long -t victoria -u sasha -p polar.bear -i esri_40' on your view, what results do you get? We are needing to see if the spatial column and entity type are getting set and referenced right. –  D.E.Wright Jun 28 '11 at 0:06
    
I'll post the results here! –  George Jun 28 '11 at 12:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.