Populate feature class via SQL

[JasonPlummer]

Afternoon everyone!  Just wanting to check and see how others are doing this.  I have a sql statement that joins multiple tables that I would like to use to populate data into a non versioned point feature class.  What would be my best method to load the data?  It would be super nice if I could automate this sql to populate the data on a schedule as well.   Any information will be greatly appreciated!  Thanks!

[VinceAngelo]

The ability to do this depends on the geometry storage and the RDBMS in use and what you're planning to do with the results (neither of which are mentioned in your question, nor is the ArcGIS software version).

Creating a new table with SQL is actually very simple, BUT it's also a potential way to corrupt your geodatabase.  The issues include:

• Will the table be accessed through a Query Layer, or does the resulting table need to be registered with ArcSDE and/or the geodatabase
• Does the SQL query result in multiple geometry classes in the same table (e.g. polygons and lines)?
• Will the table change schema in subsequent construction runs? (Truncate vs Drop/Create)
• How are you planning on preserving the registered rowid (ObjectID) column?

- V

[JasonPlummer]

I probably should of just started off with what i wanted to do and then see what y'all thought!  I'm wanting to utilize the collector application for some inspections.  I have constructed my feature class to be used by collector, now i just need to populate it with some data (some data is in feature classes while some is in tables).  The inspection data set would change on a monthly basis, so that is where i was hoping i could schedule this.  So i need to join these different sources together to condense the data for collector.  What would you suggest?  Please forgive me if i have missed tutorial somewhere for this exact issue, i'm new to the collector scene.  Thank you so much for your help!

PS

Using ArcMap 10.2.1 with Oracle 12.1.0.2.0

[AndrewKeith3]

Jason, whats up, long time, no see?  I think you could accomplish this easily by using python and Task Scheduler.  If you already have the view built in the geodatabase (make sure it includes the SHAPE), you could just call arcpy.CopyFeatures_management (in_features, out_feature_class, {config_keyword}, {spatial_grid_1}, {spatial_grid_2}, {spatial_grid_3}).  I would need more details on your workflow to provide more specifics and clarity if this would be the best approach or not.

[JasonPlummer]

Andrew!  Whats up man!?!?  How have things been going?  I have a sql statement that pretty much pulls all the information i need, just would like to be able to run that in python to populate a feature class.   I started to look at some cursors and python, but just wanted to make sure i was doing it the best/recommended way?  Any suggestions?  I can give you my personal email.  Thanks man!  Tell everyone i said hey!

[DrewSwayne]

I am currently doing different types of geoprocessing tasks via Transact-SQL in a SQL Server Database using the geometry methods provided here: geometry Data Type Method Reference . A lot of what I am doing occurs using triggers in the database, so when a feature is inserted in a particular feature class via a web service provided through a web app, a trigger then fires to create and insert data in other feature classes to produce on the fly analysis. You definitely have to be careful with ObjectIDs. I don't believe this is necessarily best-practice, but it is currently working without issue, and it's fun!