topic Re: best way to deal with sql geometry column with multi geo types? in Python Questions

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

ChrisPedrezuela — Sun, 06 Dec 2015 20:29:23 GMT

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

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

DanPatterson_Retired — Sun, 06 Dec 2015 21:53:01 GMT

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

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

ChrisPedrezuela — Sun, 06 Dec 2015 22:13:13 GMT

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

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

DanPatterson_Retired — Sun, 06 Dec 2015 23:01:59 GMT

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

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

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

ChristianWells — Mon, 07 Dec 2015 15:48:57 GMT

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.

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

JoshuaBixby — Tue, 08 Dec 2015 19:53:29 GMT

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.

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

KenHartling — Tue, 08 Dec 2015 21:06:00 GMT

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.

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

ChrisPedrezuela — Tue, 08 Dec 2015 22:59:47 GMT

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

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

ChristianWells — Tue, 08 Dec 2015 23:05:25 GMT

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

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

BruceHarold — Wed, 09 Dec 2015 19:53:46 GMT

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

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

SimonJackson — Thu, 04 May 2017 11:38:12 GMT

I have pretty much the same question.

Have a view which includes geometry column and is actually a combination of around 20 layers. Problem is these are mixed spatial types (point, line, polygon).

Looking to either create thee separate query layers that separate based upon geometry type or create 3 Separate db views from within SQL server management studio.

What is the best approach and anyone familiar with the optimal SQL to filter based on the geometry type.

Performance will be a concern and requirement is to serve this up to server

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

JoshuaBixby — Thu, 04 May 2017 16:03:24 GMT

I would say if you have a view that combines 20 layers and has a mixed geometry column, performance has been mostly written off already.

Plenty of thoughts/ideas come to mind. In random order:

I can't speak to SQL Server 2016, but I believe up through SQL Server 2012 that spatial indexes could not be applied to indexed views. Even if the underlying 20 layers have spatial indexes applied, having a mixed geometry view without spatial indexes will make the performance quite poor.
Are geometries mixed in each or any of the 20 layers that make up the current view? If not, it might be better to build new views or query layers directly from subsets of those 20 layers rather than combining them simply to filter them.
Functionally, since database views can be registered in the geodatabase starting at 10.5, I think that gives a slight edge to data views over query layers, especially since these views seem like they will stick around for a while and possibly support multiple services.

I don't know how to eke performance out of the situation you describe. Functionally, I would lean toward creating database views and registering them with the geodatabase, if you are using 10.5+.

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

SimonJackson — Thu, 04 May 2017 22:01:12 GMT

Thanks Joshua, on phone right now so excuse short response

"database views can be registered in the geodatabase starting at 10.5"

I need to do some reading on this and the pros and cons of registering a spatial view created at the DBMS level (or with the "create database view tool").

Performance is definitely going to be an issue and might have to come up with a plan B