Research on cross-user and cross-database table Association of ArcSDE

Source: Internet
Author: User
ArcSDE provides multiple Join operations, such as Join, Relate, ArcSDE view, QueryLayer, and RelationshipClass. Each method has its own unique applicability and methods. However, in general, when we introduce the use of these methods, it is undoubtedly under the same database (SQLServer) and the same user (Oracle)

ArcSDE provides multiple Join operations, such as Join, Relate, ArcSDE view, QueryLayer, and RelationshipClass. Each method has its own unique applicability and methods. However, in general, when we introduce the use of these methods, it is undoubtedly under the same database (SQLServer) and the same user (Oracle)

ArcSDE provides multiple Join operations, such as Join, Relate, ArcSDE view, QueryLayer, and RelationshipClass. Each method has its own unique applicability and methods.


However, we generally use these methods in the same database (SQLServer) and the same user (Oracle) environments, however, many users use associations in the actual process. They are actually associations between spatial data and attribute data. These attribute data are often not the same as spatial data. In this case, can ArcSDE be implemented?


The general method is Join. you can load data from different storage locations to ArcMap for Join and save MXD. It is okay to directly operate the associated object or publish the ArcGIS Server service, however, what this method brings to users is that the performance is not low or there are obvious problems when publishing services.

For example, if the data source is joined when the service is published, a problem occurs in the ArcGIS10.1 environment.

1: When ArcGIS10.1 SP1 is patched, the labels after Join cannot be seen, but the SP1 Patch Is Not required. This problem is a Bug after communication with the United States:

NIM087311 Map labels do not work in a map service if the feature class resides in

ArcSDE database and is joined to a table.

Http://support.esri.com/en/bugs/nimbus/TklNMDg3MzEx

This problem has been solved in ArcGIS 10.1 SP1 for (Desktop, Engine, Server) Quality Improvement Patch and ArcGIS10.2.


2: Use Join to publish the service. You can check the results in the query.


We can only solve this problem through the ArcSDE view or QueryLayer.

----------------------------------------------------------------------------------


Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------


SQLServer Database

For example, if my test data, spatial data pl, and attribute data owner have a common correlation field pid, then pl is stored in the database of sde101 ), the owner is stored in the test database.




Note: because cross-database operations are involved, I set the sde user to have operation permissions on the sde101 database by default. I also need to set the sde user to have relevant permissions on the test database, if more rigorous, the sde user has operation permissions on the owner table in the test database.


1: Use the ArcSDE command view Method

C:\Program Files\ArcGIS\ArcSDE\sqlexe\bin>sdetable -o create_view -T myview123 -t sde101.dbo.place,test.dbo.owner -c sde101.dbo.place.objectid,sde101.dbo.place.shape,sde101.dbo.place.pid,test.dbo.owner.name -a objectid,shape,pid,name -w "sde101.dbo.place.pid=test.dbo.owner.pid" -i sde:sqlserver:lishaohua -s lishaohua -D sde101 -u sde -p Super123ArcSDE 10.2Attribute        Administration Utility-----------------------------------------------------        Error: Invalid database name (-162).        Error: Unable to create view myview123
The database name is invalid.

If I directly copy the owner to the sde101 database, that is, the space data in the same database is associated with the attribute data, and there is no problem in creating the view.

C:\Program Files\ArcGIS\ArcSDE\sqlexe\bin>sdetable -o create_view -T myview -t sde101.dbo.place,sde101.dbo.owner -c sde101.dbo.place.objectid,sde101.dbo.place.shape,sde101.dbo.place.pid,sde101.dbo.owner.name -a objectid,shape,pid,name -w "sde101.dbo.place.pid=sde101.dbo.owner.pid" -i sde:sqlserver:lishaohua -s lishaohua -D sde101 -u sde -p Super123ArcSDE 10.2Attribute        Administration Utility-----------------------------------------------------Successfully created view myview.
Conclusion: the ArcSDE command does not support cross-database association between SQLServer.


2: Use QueryLayer for Association


Conclusion: You can use QueryLayer to implement cross-database association between SQLServer. You can use QueryLayer to publish the ArcGIS Server service.

----------------------------------------------------------------------------------


Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------


Oracle Database

For example, test data: the storage space data PL under the sde user, the storage property data owner under the aaa user, and the associated field is pid.

1: Use the ArcSDE command to create a view

C: \ Users \ Administrator> sdetable-o create_view-T myview-t sde. pl, aaa. owner-c sde. pl. objectid, sde. pl. shape, sde. pl. name, aaa. owner. name-a objectid, shape, name1, name2-w "sde. pl. pid = aaa. owner. pid "-I sde: oracle11g: 192.168.220.131/orcl-u sde-p sdeArcSDE 10.2 Attribute adminiity ity handle Error: Underlying DBMS error (-51 ). error: Unable to create view myviewORA-01720: 'aaa does not exist. OWNER's authorization options

The system prompts that the sde user does not have permissions for aaa. owner.

C: \ Users \ Administrator> sqlplus aaa/aaa @ orcl_131SQL * Plus: Release 11.2.0.1.0 Production on Thursday February 27 11:51:38 2014 Copyright (c) 1982,201 0, Oracle. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> grant select on owner to sde with grant option; authorization successful. SQL> exit disconnect C from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options: \ Users \ Administrator> sdetable-o create_view-T myview-t sde. pl, aaa. owner-c sde. pl. objectid, sde. pl. shape, sde. pl. name, aaa. owner. name-a objectid, shape, name1, name2-w "sde. pl. pid = aaa. owner. pid "-I sde: oracle11g: 192.168.220.131/orcl-u sde-p sdeArcSDE 10.2 Attribute adminiity ity extends Successfully created view myview.

Use the aaa user to assign the owner table to the sde user, and then create the view successfully.

Summary: Oracle databases support cross-User Creation of ArcSDE Space Views


2: QueryLayer


Conclusion: QueryLayer supports cross-User Association.


In oracle databases, if you want to use cross-database instances, see:

ArcSDE for cross-database Association
Http://blog.csdn.net/linghe301/article/details/7237049

However, this method has many problems and is not recommended for users.



For more information, see:


ArcGIS table association method (2)-ArcSDE view, QueryLayer, RelationshipClass
Http://blog.csdn.net/linghe301/article/details/6649717


ArcGIS table association method (1)-Join and Relate
Http://blog.csdn.net/linghe301/article/details/6649689

----------------------------------------------------------------------------------


Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.