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

[ChrisPedrezuela]

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

[SimonJackson]

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 

[JoshuaBixby]

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

[SimonJackson]

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