I am trying to do what seems to be a simple operation: 1. Iterate through a feature class 2. Insert found rows to an MSSQL spatial table. However I keep getting the following error No support for this geometry type. Strangely I get this error even if I only try to insert and int or string with no shapes involved. It seems that Arc does not understand the table which is strange because I can run select operations and Describe() on the table without issue. Does Arc support inserting spatial data to MSSQL in this manner? In the code snipped below: selected_grid_fc: File Geodatabase Feature Class, Geometry Type: Polygon selected_grids_mssql: MSSQL table with a geometry column. The geometry column stores Polygon grid squares from a 100m Fishnet in EPSG:2157 try : # Start an edit session. Must provide the workspace. edit_session = arcpy . da . Editor ( env . workspace ) # Edit session is started without an undo/redo stack for versioned data (for second argument, use False for unversioned data) edit_session . startEditing ( False , False ) # Start an edit operation edit_session . startOperation ( ) search_cols = [ 'OBJECTID' , 'GridId' , 'SHAPE@' ] insert_cols = [ 'ObjectId' , 'GridId' , 'Shape' ] # If I user 'Shape@', the error is the same. selected_grids_mssql = os . path . join ( self . sde_path , 'dbo.SelectedGrids' ) with arcpy . da . SearchCursor ( selected_grid_fc , search_cols ) as search_cursor : with arcpy . da . InsertCursor ( selected_grids_mssql , insert_cols ) as insert_cursor : for row in search_cursor : insert_cursor . insertRow ( row ) except Exception as ex : arcpy . AddError ( "Something went wrong." ) raise else : # Stop the edit operation. edit_session . stopOperation ( ) # Stop the edit session and save the changes edit_session . stopEditing ( True ) arcpy . AddMessage ( "Import successful." ) For reference, the following works fine via pyodbc, but I wanted to see if it could be done without the need for an external library. selected_grids = [ ] selected_grid_cols = [ 'OBJECTID' , 'GridId' , 'SHAPE@WKT' ] with arcpy . da . SearchCursor ( selected_grid_fc , selected_grid_cols ) as cursor : for row in cursor : selected_grids . append ( row ) with Helpers . db_connect ( ) . cursor ( ) as cursor : query = "INSERT INTO [dbo].[SelectedGrids](ObjectId, GridId, Shape) VALUES (?,?,geometry::STGeomFromText(?, 2157));" cursor . executemany ( query , selected_grids )

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; } } }

Insert geometry from ESRI feature class to MSSQL spatial table using ArcPy InsertCursor()

BarryO__Neill

I am trying to do what seems to be a simple operation:

1. Iterate through a feature class
2. Insert found rows to an MSSQL spatial table.

However I keep getting the following error No support for this geometry type.

Strangely I get this error even if I only try to insert and int or string with no shapes involved. It seems that Arc does not understand the table which is strange because I can run select operations and Describe() on the table without issue.

Does Arc support inserting spatial data to MSSQL in this manner?

In the code snipped below:

selected_grid_fc: File Geodatabase Feature Class, Geometry Type: Polygon
selected_grids_mssql: MSSQL table with a geometry column. The geometry column stores Polygon grid squares from a 100m Fishnet in EPSG:2157

<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="comment token"># Start an edit session. Must provide the workspace.</SPAN>
    edit_session <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>Editor<SPAN class="punctuation token">(</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace<SPAN class="punctuation token">)</SPAN>
    <SPAN class="comment token"># Edit session is started without an undo/redo stack for versioned data (for second argument, use False for unversioned data)</SPAN>
    edit_session<SPAN class="punctuation token">.</SPAN>startEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="comment token"># Start an edit operation</SPAN>
    edit_session<SPAN class="punctuation token">.</SPAN>startOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

    search_cols <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'GridId'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SHAPE@'</SPAN><SPAN class="punctuation token">]</SPAN>
    insert_cols <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'ObjectId'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'GridId'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Shape'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># If I user 'Shape@', the error is the same.</SPAN>
    selected_grids_mssql <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">.</SPAN>sde_path<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'dbo.SelectedGrids'</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>selected_grid_fc<SPAN class="punctuation token">,</SPAN> search_cols<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> search_cursor<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN>selected_grids_mssql<SPAN class="punctuation token">,</SPAN> insert_cols<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> insert_cursor<SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> search_cursor<SPAN class="punctuation token">:</SPAN>
                insert_cursor<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">except</SPAN> Exception <SPAN class="keyword token">as</SPAN> ex<SPAN class="punctuation token">:</SPAN>
    arcpy<SPAN class="punctuation token">.</SPAN>AddError<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Something went wrong."</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="keyword token">raise</SPAN>
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="comment token"># Stop the edit operation.</SPAN>
    edit_session<SPAN class="punctuation token">.</SPAN>stopOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    <SPAN class="comment token"># Stop the edit session and save the changes</SPAN>
    edit_session<SPAN class="punctuation token">.</SPAN>stopEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>
    arcpy<SPAN class="punctuation token">.</SPAN>AddMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Import successful."</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

For reference, the following works fine via pyodbc, but I wanted to see if it could be done without the need for an external library.

selected_grids <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
selected_grid_cols <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'GridId'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SHAPE@WKT'</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>selected_grid_fc<SPAN class="punctuation token">,</SPAN> selected_grid_cols<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
        selected_grids<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">with</SPAN> Helpers<SPAN class="punctuation token">.</SPAN>db_connect<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
    query <SPAN class="operator token">=</SPAN> <SPAN class="string token">"INSERT INTO [dbo].[SelectedGrids](ObjectId, GridId, Shape) VALUES (?,?,geometry::STGeomFromText(?, 2157));"</SPAN>
    cursor<SPAN class="punctuation token">.</SPAN>executemany<SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">,</SPAN> selected_grids<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Encuentra más publicaciones etiquetadas con

Accepted answers

ModyBuchbinder

You cannot write directly from Desktop to enterprise tables that are not register in the Geodatabase.

You cannot start edit in ArcMap and you cannot do it in arcpy too.

You must register the table or use external software (pyodbc)

Have fun

All comments

jas_eagle

I know this is an older post, but I just wanted to reply that the suggestion above has serious security implications.

Writing SQL statements in Python (or any language) without binding input parameters opens your database and tool to SQL injection vulnerabilities.

Parameter binding is included in libraries for a reason. I would always recommend to use them.

MarcoBoeringa

If you intend to only create read-only feature classes that do not need any ESRI Geodatabase functionality, then I second Mody Buchbinder‌ suggestion of using pyodbc to insert the records into the MSSQL table.

This means you need to replace the nested arcpy.InsertCursor with an appropriate pyodbc Cursor object.

I have written a Python multi-threaded application doing just that, and successfully used it to insert > 100M(!) records from a File Geodatabase into a PostgreSQL table.

Note that pyodbc is available in the ArcGIS Pro Conda environment. Just select it from there and install it to start working with it.

By the way, I recommend inserting as WKT (Well Know Text) geometries using the "SHAPE@WKT" token, and not use pyodbc "parameter binding" but just concatenate all fields to transfer into a long INSERT string statement. I have seen some instabilities with WKB and using parameter binding on ultra large datasets, that maybe related to some known unresolved issues with pyodbc and parameter binding (but this is speculation from my side at this point in time... you can try WKB and parameter binding on smaller datasets and see if it works for you).

BarryO__Neill

Thanks very much for letting me know. I suspected as much but the error message was unclear and I had wondered if I was making a mistake in some manner. Not to worry, I will continute with pyODBC.

Thanks again!

ModyBuchbinder

You cannot write directly from Desktop to enterprise tables that are not register in the Geodatabase.

You cannot start edit in ArcMap and you cannot do it in arcpy too.

You must register the table or use external software (pyodbc)

Have fun

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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