topic Re: join oracle table and polyline layer into single layer to be used for webservice to AGO in Python Questions

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

GeorgeMcQuary — Tue, 05 Apr 2016 22:00:31 GMT

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?

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

AndrewKeith3 — Wed, 06 Apr 2016 20:02:17 GMT

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

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

GeorgeMcQuary — Thu, 07 Apr 2016 01:17:37 GMT

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")

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

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

AndrewKeith3 — Thu, 07 Apr 2016 13:20:47 GMT

Can you post your error.

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

GeorgeMcQuary — Thu, 07 Apr 2016 18:15:40 GMT

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).

>>>

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

AndrewKeith3 — Sat, 11 Dec 2021 10:25:21 GMT

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")

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