topic Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry in Data Management Questions

Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

DEWright_CA — Fri, 08 Oct 2010 01:18:23 GMT

Ok, I am trying to create the stub of a spatial view in 9.3.1 ArcSDE running on top of SQL2008 using the GEOMETRY option versus the SDEBINARY.

So I can run this command:

sdetable -o create_view -T v_Parcels -t "PARCEL" -c "PARCEL.OBJECTID,PARCEL.POLYID" -i 5151 -s DEVGISSQL -u <username> -p <password>

This will create a view just fine, so I know I am talking to the SDE Engine and SQL Server just fine.

But if I try to add the SHAPE field like this:

sdetable -o create_view -T v_Parcels -t "PARCEL" -c "PARCEL.OBJECTID,PARCEL.POLYID,PARCEL.SHAPE" -i 5151 -s DEVGISSQL -u <username> -p <password>

I get a 'Error: Invalid entity type (-29).' with the error unable to create view v_Parcels.

Is there some switch I am missing? Or am I not able to create a view like this because I am using SQL2008 with MSGeometry?

I can create a view no problem using the SDE objects, but we want to access our data from more than just ESRI.

Any thoughts?

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

VinceAngelo — Fri, 08 Oct 2010 11:18:51 GMT

When using native geometry, use SQL to create the view, then 'sdelayer -o register'
to make it available through ArcSDE.

- V

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

DEWright_CA — Fri, 08 Oct 2010 17:04:01 GMT

Ok, so I have done this; but I am getting kicked back a underlying DBMS layer error and finally this:
Microsoft SQL Server Native Client 10: Cannet alter 'V_PARCELADDRESS' because it is not a table.

Thoughts?

When using native geometry, use SQL to create the view, then 'sdelayer -o register'
to make it available through ArcSDE.

- V

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

VinceAngelo — Fri, 08 Oct 2010 17:14:13 GMT

What does 'sdetable -o describe' report for the table?

What 'sdelayer -o register' command are you using (less password)?

Are you specifying an existing NOT NULL unique integer rowid column with the '-C' flag?

- V

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

DEWright_CA — Fri, 08 Oct 2010 18:38:08 GMT

What does 'sdetable -o describe' report for the table?

When I run the describle, the View I have built does not even show up in the list, only the three true geometry layers.

What 'sdelayer -o register' command are you using (less password)?

sdelayer -o register -l v_ParcelAddress,OBJECTID -e a -t GEOMETRY -C OBJECTID -i 5153 -s devgissql

Are you specifying an existing NOT NULL unique integer rowid column with the '-C' flag?
Yup, you can see that above, just using the OBJECT ID from the spatial layer.

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

VinceAngelo — Fri, 08 Oct 2010 19:32:19 GMT

Does 'sdetable -o describe -t {view_name}' return the view's description? I know
this works with Oracle, PostgreSQL, and DB2, even if the table isn't registered
with ArcSDE.

The error message indicates that layer registration is attempting to add or alter
the rowid column, which makes me think that the column isn't NOT NULL.

You really ought to specify coordinate reference parameters (-R or -x/-G) and
an envelope (-E) with the register operation, instead of allowing ArcSDE to
choose values which may be incompatible with your data. If the features are
ever multi-part, you'll need '-e a+'.

- V

Re: Creating Spatial View in 9.3.1 with SQL2008 using MSSQL Spatial Geometry

KimPeter — Wed, 13 Oct 2010 17:35:17 GMT

Hi David,

Is the table (parcels) on which you are trying to create your view already registered with ArcSDE and/or the geodatabase? For example, is "parcels" a feature class you created in ArcGIS, or is it a spatial table you created in SQL Server using SQL?

-Kim