nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

join oracle table and polyline layer into single layer to be used for webservice to AGO

GeorgeMcQuary

Clerks are creating polylines representing construction project locations in a polyline layer located in a file based .gdb

Attribute Table here is in the format

OBJECTID, SHAPE, PROJECTID, SHAPE_Length, etc.

With PROJECTID being the unique key.

This is currently being joined by PROJECTID through “JOIN AND RELATES” to additional text data coming from an oracle view SDEPCONSTR .CONSTRINFO in the format:

PROJECTID, PROJECTNAME, DESCRIPTION, DESIGNENGINEER, DESIGNPHONE, PLANNINGENGINEER, PLANNINGPHONE, etc.

Creating webservices with this join is raising a lot of medium errors about it being possibly slow since I’m accessing the Oracle server through the ESRI server and the join to an Oracle view is causing other strange artifacts in AGO.

I want to create a python script that picks up everything the clerks have entered either through editing the polyline layer on their workstation, combine the polylines with the oracle descriptor data through the join, and save it into one Feature Class that is local on the server that can be used to create the webservice for AGO, and just run the script as a cron job so I don’t have to do this by hand anymore.

I’m already hitting a problem with arcpy.CopyFeatures_management() only writing to a **new** Feature Class. So I have to delete the target FeatureClass each time before copying?

So far my code looks like:

# ---------------

# Import arcpy module

import arcpy

# Local variables:

ActiveProjectsInField = "Z:\\APF.gdb\\ConstructionProjects\\ActiveProjectsInField"

SDEPUSER_FieldProjectsCopy = "Database Connections\\ArcSDE4.sde\\SDEPCONSTR.RPP\\SDEPCONSTR.ProjectCopy"

data_type = ""

# Process: Delete temporary geodatabase

arcpy.Delete_management(SDEPUSER_FieldProjectsCopy, data_type)

# Process: Copy Features to a temporary geodatabase FieldProjectsCopy

arcpy.CopyFeatures_management(ActiveProjectsInField, SDEPUSER_FieldProjectsCopy, "", "0", "0", "0")

# ----------------

Which is copying the polyline layer from the file based geodatabase into the server’s geodatabase as a new layer "ProjectCopy". But not sure how to access from within Python the additional fields needed for the Oracle data to be joined to the polylines. And should I be dropping this built table each time instead of just truncating it?

Am I on the right path, or is there a better practice for combining desktop editing of polylines with oracle data into a single feature to be exported as a webservice for display on AGO?

Find more posts tagged with

Accepted answers

All comments

AndrewKeith3

You have to make a Feature Layer and a Table View to run the AddJoin function. I would probably get rid of line 31 and replace it with the arcpy.AddIndex_management function on the clerks layer "PROJECTID" field. This should work:

# ---------------
# Import arcpy module
import arcpy

# Variables (really, constants)
# ------------
# Clerks' version
ActiveProjectsInField = "Z:\\APF.gdb\\ConstructionProjects\\ActiveProjectsInField"

# Copy of the Clerk's data in the main geodatabase that we can drop and rebuild.
SDEPUSER_FieldProjectsCopy = "Database Connections\\ArcSDE4.sde\\SDEPCONSTR.RPP\\SDEPCONSTR.ProjectCopy"

#Oracle table from other part of agency with details to display on AGO.
SDEPUSER_ConstrOracle = "Database Connections\\ArcSDE4.sde\\SDEPUSER.CONSTRINFO"

#Final version used to create the webservice to AGO.
SDEPUSER_ConstrAGO =  "Database Connections\\ArcSDE4.sde\SDEPCONSTR.RPP\\SDEPCONSTR.CONSTRAGO"


arcpy.MakeFeatureLayer_management(SDEPUSER_FieldProjectsCopy, "clerk_lyr")
arcpy.MakeTableView_management(SDEPUSER_ConstrOracle, "oracle_table")
# Code
#------------
# Process: Delete temporary geodatabase from last use.
arcpy.Delete_management(SDEPUSER_FieldProjectsCopy, data_type)

# Process: Copy Clerk data to temporary geodatabase FieldProjectsCopy
arcpy.CopyFeatures_management(ActiveProjectsInField, SDEPUSER_FieldProjectsCopy, "", "0", "0", "0")

# Process: Add Spatial Index to temporary table "FieldProjectsCopy" to stop "no index" warning.
arcpy.AddSpatialIndex_management(SDEPUSER_FieldProjectsCopy, "0", "0", "0") 


# Process: Join Field --Join the two tables. **FAILS HERE**
arcpy.AddJoin_management("clerk_lyr", "PROJECTID", "oracle_table", "PROJECTID", "KEEP_ALL")

# Process: Copy all this (with joins) to a "SDEPUSER_ConstrAGO" to be used to build the webservice.
rcpy.CopyFeatures_management(SDEPUSER_FieldProjectsCopy, SDEPUSER_ConstrAGO, "", "0", "0", "0")

# ----------------

GeorgeMcQuary

Traceback (most recent call last):

File "C:\Users\geouser\Desktop\JoinToOracle.py", line 34, in <module>

arcpy.AddJoin_management(SDEPUSER_FieldProjectsCopy, "PROJECTID", SDEPUSER_ConstrOracle, "PROJECTID", "KEEP_ALL")

File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 5632, in AddJoin

raise e

ExecuteError: Failed to execute. Parameters are not valid.

The value cannot be a feature class

ERROR 000840: The value is not a Raster Layer.

ERROR 000840: The value is not a Raster Catalog Layer.

ERROR 000840: The value is not a Mosaic Layer.

WARNING 000970: The join field PROJECTID in the join table SDEPUSER.ProjectCopy is not indexed. To improve performance, we recommend that an index be created for the join field in the join table.

Failed to execute (AddJoin).

>>>

AndrewKeith3

Can you post your error.

GeorgeMcQuary

I'm finding I can create a join between the polyline layer and oracle table in modelbuilder, but when I try to do the same thing with exported python code, it fails.

# ---------------

# Import arcpy module

import arcpy

# Variables (really, constants)

# ------------

# Clerks' version

ActiveProjectsInField = "Z:\\APF.gdb\\ConstructionProjects\\ActiveProjectsInField"

# Copy of the Clerk's data in the main geodatabase that we can drop and rebuild.

SDEPUSER_FieldProjectsCopy = "Database Connections\\ArcSDE4.sde\\SDEPCONSTR.RPP\\SDEPCONSTR.ProjectCopy"

#Oracle table from other part of agency with details to display on AGO.

SDEPUSER_ConstrOracle = "Database Connections\\ArcSDE4.sde\\SDEPUSER.CONSTRINFO"

#Final version used to create the webservice to AGO.

SDEPUSER_ConstrAGO = "Database Connections\\ArcSDE4.sde\SDEPCONSTR.RPP\\SDEPCONSTR.CONSTRAGO"

# Code

#------------

# Process: Delete temporary geodatabase from last use.

arcpy.Delete_management(SDEPUSER_FieldProjectsCopy, data_type)

# Process: Copy Clerk data to temporary geodatabase FieldProjectsCopy

arcpy.CopyFeatures_management(ActiveProjectsInField, SDEPUSER_FieldProjectsCopy, "", "0", "0", "0")

# Process: Add Spatial Index to temporary table "FieldProjectsCopy" to stop "no index" warning.

arcpy.AddSpatialIndex_management(SDEPUSER_FieldProjectsCopy, "0", "0", "0")

# Process: Join Field --Join the two tables. **FAILS HERE**

arcpy.AddJoin_management(SDEPUSER_FieldProjectsCopy, "PROJECTID", SDEPUSER_ConstrOracle, "PROJECTID", "KEEP_ALL")

# Process: Copy all this (with joins) to a "SDEPUSER_ConstrAGO" to be used to build the webservice.

rcpy.CopyFeatures_management(SDEPUSER_FieldProjectsCopy, SDEPUSER_ConstrAGO, "", "0", "0", "0")

# ----------------

AndrewKeith3

I would recommend a spatial view in Oracle. Once you get your data populated in the Oracle database, you can create another view that combines the data from the existing Oracle view and the data being copied to Oracle from the local File Geodatabase. You can create this in ArcCatalog by right clicking on your Oracle database connection, click New, then click View. Then you can type a SQL statement to join the view and table into a new view.

example:

select * from oracleview_name

inner join gdb_table_name_in_oracle b

b.projectid = a.projectid

You can replace the * with the field names you want to show in the new view.

As far as your process to get the data from FGDB to Oracle, you could use the Truncate and Append tools in python rather than Delete, Copy

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.