topic Re: Register SQL Spatial View using Esri Binary in Data Management Questions

Register SQL Spatial View using Esri Binary

mpboyle — Mon, 28 Apr 2014 20:58:24 GMT

Does anyone know how to register a SQL spatial view using a feature class that has Esri binary as its shape?

I have tried sdetable -o register and sdetable -o create_view but have been unsuccessful. This could be because of my own lack of knowledge with the command line.

ArcSDE = 10.1
DBMS = SQL Server 2008 R2

Feature class name = lcbase.dbo.Parcels
Joined table name = gis.dbo.vw_parcels

Trying to create the view in a geodatabase called 'gisstaging'.

Below is the create_view command line prompt I've tried with an error (Error -162, Invalid Database Name)

sdetable -o create_view -T VWTest -t lcbase.dbo.PARCELS,gis.dbo.vw_parcelsweb -c lcbase.dbo.PARCELS.SHAPE,lcbase.dbo.PARCELS.GPNNUM,gis.dbo.vw_parcelsweb.GPN -w"lcbase.dbo.PARCELS.GPNNUM = gis.dbo.vw_parcelsweb.GPN" -i sde:sqlserver:lcgissql -D gisstaging -u **** -p ****

Below is the register command line prompt I've tried after creating the view in SSMS with an error (Error -144, Wrong Column Type, SE_table_get_shape_type failed) --- I'm assuming this is due to the Esri binary shape.

sdelayer -o register -l gisstaging.dbo.ParcelsSearch,Shape -e a -t geometry -i sde:sqlserver:lcgissql -s lcgissql -D gisstaging -u **** -p ****

Any ideas on how I can create a spatial view using an Esri binary feature class...?

Thanks in advance!

Re: Register SQL Spatial View using Esri Binary

VinceAngelo — Mon, 28 Apr 2014 21:21:04 GMT

You cannot register an existing view on an SDEBINARY layer -- 'sdelayer -o register'
is only used with native (or ST) geometry storage.

The 'sdetable -o create_view' utility is only used to create new views. It does not
support views that span databases.

It's not formally supported, but the work-around is to create a nominal table (with the same
columns in the same order) in the same database as the layer, then alter the view after it has
been created.

- V

Re: Register SQL Spatial View using Esri Binary

mpboyle — Mon, 28 Apr 2014 21:49:37 GMT

Vince,

Thanks for the info! Once the SDEBINARY feature class was in the same geodatabase as the view, using the sdetable -o create view worked and I was able to see the view as a 'feature' in ArcCatalog.

With feature classes that use GEOMETRY (SQL Server) as their shape, can those views span databases and use the sdetable -o register command to store the view within a different geodatabase than the feature class?

Also - what type of geometry is 'better', SDEBINARY or GEOMETRY for SQL Server?

Thanks again for the knowledge, been trying to figure what the issue was all day!

Re: Register SQL Spatial View using Esri Binary

VinceAngelo — Tue, 29 Apr 2014 00:21:05 GMT

With feature classes that use GEOMETRY (SQL Server) as their shape, can those views span databases and use the sdetable -o register command to store the view within a different geodatabase than the feature class?

Esri no longer supports the last version that permitted cross-database queries. There's a
significant performance cost, so their use is not recommended.

Also - what type of geometry is 'better', SDEBINARY or GEOMETRY for SQL Server?

The answer is different by use case and even implementation of use case.

- V