topic Load Point Feature Class through SQL Insert in Data Management Questions

Load Point Feature Class through SQL Insert

JPMurphy — Wed, 14 May 2014 15:55:24 GMT

What I am trying to accomplish:
Being able to automate data loading into a spatial table/feature class so that the data is viewable in the ArcGIS service after loading. I know how to do this with just a SQL table with a geometry column but am wanting to make sure it is ok to do once registered with the geodatabase.

When reviewing the documentation about registering it says that you can specify an existing integer field to be the ObjectID field and that from then on the database manages the field starting at the highest integer + 1. If I don't want to use arcobjects to load the data but instead use a sql insert statement is there a problem if a) I increment the objected field my self or b) specify the objected field to be an identity field and let sql auto increment it?

This data does not need to be versioned or edits tracked.

Is this the right way or am I missing something in the documentation to do this. I have attempted this in a test environment and either option a or b above seem to work, but I wanted to see if there are any pitfalls to doing it this way.

Thanks!

Re: Load Point Feature Class through SQL Insert

VinceAngelo — Wed, 14 May 2014 16:31:56 GMT

Implementing max(rowidcol)+1 population results in *very* slow INSERTs.

ArcGIS will honor a USER-set rowid column on a table registered with ArcSDE --
all you need to do is make sure the rowid values are: 32-bit positive, non-zero
integers, unique, and repeatable on subsequent queries (not rownum). Such a
table could not be registered with the geodatabase (ArcObjects wants SDE-set
rowids), but registration is not necessary for mapping.

- V

Re: Load Point Feature Class through SQL Insert

JPMurphy — Mon, 19 May 2014 15:23:50 GMT

vangelo,

Thank you for your reply, yes I would probably not go the route Max(rowed) + 1 but instead use the built in SQL IdentitySpecification so that Sql Server generates.

It sounds like you are saying I am ok to proceed, I'm just concerned about your last comment "Such a
table could not be registered with the geodatabase (ArcObjects wants SDE-set
rowids), but registration is not necessary for mapping."

Is the table you are referring to if I tried to use rownum or am I missing something?

Thank you for your assistance.

Re: Load Point Feature Class through SQL Insert

VinceAngelo — Mon, 19 May 2014 16:33:39 GMT

All ArcSDE-registered tables to be resigtered with the geodatabase MUST have an
SDE-set rowid column. This is a requirement. ArcGIS will enforce this by adding
an OBJECTID_1 column which is SDE-set if necessary.

Lack of geodatabase registration means the table must remain a simple feature class
(no versioning, no archive, no behaviors,...). This is the opportunity cost of choosing
SQL loading with an identity column.

You can still view the simple feature class with realtime updates in Server, so I'm not
sure where your concern lies.

- V

Re: Load Point Feature Class through SQL Insert

JPMurphy — Mon, 19 May 2014 16:55:15 GMT

vangelo,

When I tried to publish an mxd in arcmap 10.2, a dialog box came up saying the table was not registered and the data had to be copied to the server. The words copied to the server make it sound like a one time upload of the data, meaning any inserts after the publication would not be viewable. Maybe I am just misreading the dialog box. Would you explain how you would publish a map service of an unregistered table, so that the sql inserts will be seen after the initial publication?

Thank you.

Re: Load Point Feature Class through SQL Insert

VinceAngelo — Mon, 19 May 2014 17:28:47 GMT

Ouch. I thought you could access simple feature classes from Server, but it seems
as if it can't. Working around that will wander into unsupported realms. How urgent
is your SQL insert requirement?

- V

Re: Load Point Feature Class through SQL Insert

JPMurphy — Mon, 19 May 2014 19:24:04 GMT

Vangelo,

Thanks for you quick responses, the need is probably a 2-3 on a 1(urgent)-5(not urgent) scale.

I'll keep searching, unless you dig something else up.

Thanks again for the help.

Re: Load Point Feature Class through SQL Insert

MarcoBoeringa — Tue, 20 May 2014 17:24:51 GMT

Ouch. I thought you could access simple feature classes from Server, but it seems
as if it can't. Working around that will wander into unsupported realms. How urgent
is your SQL insert requirement?

- V

Are you sure? I included, based on Help documentation, an option for creating a web service based on a Query Layer in my Geodatabase Framework document. The web service uses the Query Layer in ArcMap, and must be published as a Map Service from within ArcMap.

According to this Help page, the requirements for a web service based on a Query Layer are:

"The following describe feature service data requirements specific to data stored in a database:

All data must be from a single database.

Write permissions on the data are required if you plan to allow edits to the data. When using operating system authentication, these permissions must be granted to the ArcGIS Server account.

When you add database data to ArcMap, a query layer is created. If you alter the query layer definition, be sure the query contains only one table, does not have duplicate columns, and does not include virtual or merged columns.

The data that is published is determined by the query layer defined for the table in ArcMap. For example, tables that contain data types that are not supported by ArcGIS can be published, but the unsupported data types are not accessible through ArcGIS or the feature service. See Viewing database data in ArcGIS for information on how the query layer is initially defined when a database table is added to ArcMap.

The table must contain a unique integer column maintained by the database. If you create tables and load the data to the database using ArcGIS for Desktop, a database-maintained unique integer ObjectID is added automatically. If you create data outside of ArcGIS, be sure to include a database-maintained unique integer column in the table. If such a column does not exist, you cannot publish a feature service. You can use the Add Incrementing ID Field geoprocessing tool to add a database-maintained integer column to your table if it is in an IBM DB2, Microsoft SQL Server, Oracle, or PostgreSQL database."

Re: Load Point Feature Class through SQL Insert

VinceAngelo — Tue, 20 May 2014 17:47:00 GMT

I don't think the OP is using a Query Layer. I make Desktop layers from unregistered
tables all the time (through ArcSDE connections), but don't publish them (and don't
have the time to try it right now).

- V

Re: Load Point Feature Class through SQL Insert

MarcoBoeringa — Thu, 22 May 2014 19:33:01 GMT

vangelo,

When I tried to publish an mxd in arcmap 10.2, a dialog box came up saying the table was not registered and the data had to be copied to the server. The words copied to the server make it sound like a one time upload of the data, meaning any inserts after the publication would not be viewable. Maybe I am just misreading the dialog box. Would you explain how you would publish a map service of an unregistered table, so that the sql inserts will be seen after the initial publication?

Thank you.

It may not be entirely relevant, but I do think you should start reading this recent (April 2014) Support Services Blog article:

The Evolution of Query Layers

(Please note right-click and choosing "Open in new TAB" does not always succeed in opening the right Blog article, use left click instead to open in current page if you have problems, or search the ESRI site with the title of the Blog article)

Re: Load Point Feature Class through SQL Insert

CherylCleghorn — Fri, 23 May 2014 13:59:27 GMT

In terms of the publishing, was it an analyzer warning that you got saying 'Layer's data source is not registered with the server and data will be copied'? If so then you can register your connection with the server to directly access the data source. Server uses either a managed or registered data store:Determining how the server will access service data, About registering your data with the server and Registering a SQL Server database with the server. I am not sure if you are using SQLServer but you can navigate to the relevant link.

Cheryl