best way to deal with sql geometry column with multi geo types?

4441
12
12-06-2015 12:29 PM
ChrisPedrezuela
Occasional Contributor III

hi guys,

I got this sql table im working with, it has a geometry column but contains multiple geometry types,

[u'POLYGON', u'LINESTRING', u'GEOMETRYCOLLECTION', u'POINT', u'MULTILINESTRING', u'MULTIPOLYGON']

Im using arcpy's arcsdesqlexecute and I seem to be hitting an error loading the column as is, I tried converting it to varchar but I don't feel its necessary.

Any thoughts guys?

Regards,

Chris P

12 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Do you mean this page with all the warnings and no examples relating to geometry? ArcSDESQLExecute—Help | ArcGIS for Desktop

ChrisPedrezuela
Occasional Contributor III

Sorry for the lack of detail earlier,

Here is the traceback report,

Traceback (most recent call last):

  File "Path\ExtractDataToCSV.py", line 37, in <module>

    extract = in_connection.execute(sql)

  File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\arcobjects\arcobjects.py", line 27, in execute

    return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))

AttributeError: ArcSDESQLExecute: SreamBindOutputColumn ArcSDE Error -65 \uda10

So this happens when I include the geometry column that has multiple geometry types. Without it, the table loads fine. I can load the geometry type but I have to do something like this,

"cast(GeoLocation as varchar(8000)) as GeoLocation"

This will load it as string but still I have to work a process to convert them as geometries. Wondering if there is a simpler or better way of reading sql tables like this.

Thanks Dan

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

from Stack only one with -65 error code using  ArcSDESQLExecute as query

arcpy - ArcSdeSqlExecute ArcSDE Error -65 - Geographic Information Systems Stack Exchange

0 Kudos
ChristianWells
Esri Contributor

Can you please post the SQL query and DBMS you are using? I found the same error when running STAsText() from SQL Server.

One option to work around this may be to turn your query into a Query layer and utilize the SearchCursor to loop through the rows.

JoshuaBixby
MVP Esteemed Contributor

Ever since ArcSDE faded away as a standalone product and got integrated into ArcGIS for Server, at least from a marketing and licensing perspective, I have found some of the worthwhile documentation from earlier releases doesn't move forward.  I believe the ArcSDE 10.0 C API Return codes are still mostly accurate for ArcGIS 10.2.x and ArcGIS 10.3.x:

SE_INVALID_POINTER (-65)

Returned by functions that are passed pointers to data that are either NULL or invalid.

If you are trying to export geographic data as geographic data from an enterprise geodatabase, ArcSDESQLExecute is the wrong tool.  The tool doesn't work with native spatial data types returned from various DBMSes.

KenHartling
Esri Contributor

Correct. ArcSDESQLExecute is written using the 'old' SDE libraries which requires the spatial table to be registered with SDE (Enterprise GDB) and for the shape field to only contain one geometry type.  From what I can tell from the info provided, Query Layers are probably the way to go.

ChrisPedrezuela
Occasional Contributor III

Thanks heaps for all the feedback guys. Since I don't want to create query layers in an mxd, I could just probably use arcpy.MakeQueryLayer_management and do something with the records while in_memory I guess. But I just want to validate if "GeometryCollection" type can be recognized? In my case this type is just composed of multiple polygon so pretty much like a "MultiPolygon".

0 Kudos
ChristianWells
Esri Contributor

Hi Chris,

Unfortunately, Esri does not supported the GeometryCollection type yet:

SQL Server data types supported in ArcGIS—Help | ArcGIS for Desktop

ArcGIS does not support the following geometry subtypes:

  • CircularString
  • CompoundCurve
  • CurvePolygon
  • GeometryCollection

0 Kudos
BruceHarold
Esri Regular Contributor

Hi

I would like to see if the Data Interoperability extension can solve your problem.

It doesn't care about mixed geometry types.

If you can get your data to us we'll take a look.

Regards

0 Kudos