Geometry STUnion using SQL Server 2008r2 and view the geometry data in ArcMap

3803
6
10-28-2013 10:28 AM
YangLiu2
New Contributor II
I am trying to union 254 counties' polygons in to 7 different branches (East, West, North, South, Northwest, South-Central. and Central). I have a state boundary shapefile including 254 counties. I create a stored procedure as following:

CREATE PROCEDURE [dbo].[Branch_Union] (@zone varchar(15))
AS
    
 SET NOCOUNT ON; 
      -- select columns from table and insert into a temporaty table 
      SELECT SHAPE, NewBranch INTO #tempZone FROM  BOUNDARIES_WEB 
      WHERE NewBranch = @zone 
      -- Union county polygons in every defined zones 
      DECLARE @g geometry SELECT @g=shape FROM #tempZone 
      SELECT @g=@g.STUnion(shape)FROM #tempZone 
      SELECT @g AS shape INSERT INTO NewBranchTable (shape, tfsBranch) 
            VALUES ((SELECT @g),@ZONE) 


And then, I create the 'NewBranchTable' to store every inserted value:

CREATE TABLE NewBranchTable 
(OID INT IDENTITY,
 NewBranch nvarchar(15),
 shape geometry) 


Finally, I execute my Stored procedure:

EXEC Branch_Union 'East'
EXEC Branch_Union 'West'
EXEC Branch_Union 'Central'
EXEC Branch_Union 'South'
EXEC Branch_Union 'South-Central'
EXEC Branch_Union 'Northwest'
EXEC Branch_Union 'North' 


All the procedures are running well; however, when I create a database connection and add the 'NewBranchTable' to ArcMap, error message pops up and shows that "Could not add the specified data object to the map. Invalid entity type." Could any one can give some advise that what the issue is? Thanks
[ATTACH=CONFIG]28660[/ATTACH]
0 Kudos
6 Replies
VinceAngelo
Esri Esteemed Contributor
An invalid entity type error occurs when a geography strays outside the types
originally requested at layer creation.  This can be degenerate lines (points)
in an line layer ("nsl+") or degenerate polygons in an area layer ("na+"), or
multi-part features (islands) in a single-part layer ("npsla").  How is your layer
defined?  Did you leave off the "+"?  'sdelayer -o describe_long' on the layer
might tell the tale.

It's also possible that some other corruption is occurring in the geometry, but
to detect that you'd need to run validation tests inside the database, and if
those pass, export the well-known text outside the database for independent
verification.

- V
0 Kudos
YangLiu2
New Contributor II
An invalid entity type error occurs when a geography strays outside the types
originally requested at layer creation.  This can be degenerate lines (points)
in an line layer ("nsl+") or degenerate polygons in an area layer ("na+"), or
multi-part features (islands) in a single-part layer ("npsla").  How is your layer
defined?  Did you leave off the "+"?  'sdelayer -o describe_long' on the layer
might tell the tale.

It's also possible that some other corruption is occurring in the geometry, but
to detect that you'd need to run validation tests inside the database, and if
those pass, export the well-known text outside the database for independent
verification.

- V


Thanks for your reply. The spatial column is geometry. All values should be polygon.

YL
0 Kudos
YangLiu2
New Contributor II
An invalid entity type error occurs when a geography strays outside the types
originally requested at layer creation.  This can be degenerate lines (points)
in an line layer ("nsl+") or degenerate polygons in an area layer ("na+"), or
multi-part features (islands) in a single-part layer ("npsla").  How is your layer
defined?  Did you leave off the "+"?  'sdelayer -o describe_long' on the layer
might tell the tale.

It's also possible that some other corruption is occurring in the geometry, but
to detect that you'd need to run validation tests inside the database, and if
those pass, export the well-known text outside the database for independent
verification.

- V


I created a spatial stable as following:
CREATE TABLE NewBranchTable 
(OID INT IDENTITY,
 NewBranch nvarchar(15),
 shape geometry)


The Shape column values should be poloygon. And its type is Geometry.

I am new on SDE geodatabase administration.
I run the 'sdelayer -o describe_long' to get details, but another error comes: "Entry for SDE instance not found in services file". I query that whether my data is registered with SDE. In the pass, I created a new database and drag Feature Classes and table into it. And then I play with data in the database (e.g. view, stored procedure, and trigger). Finally I use 'Enable Enterprise Geodatabase' tool on my database. May I said all data are registered with SDE now?
Beisdes, I am not sure how to run the validation test inside the database. Can any hint supplied? Thanks!

YL
0 Kudos
VinceAngelo
Esri Esteemed Contributor
Your data is likely *multi-part* polygon, not simply "polygon", which is one
possibility of where the trouble lies.

'sdelayer' can be executed without a service against any enterprise geodatabase.
You merely need to use the Direct Connect syntax ("-i sde:sqlserver:servername
and "-D databasename").

Registering a geodatabase within a database will not, of itself, register all
your native geometry with the geodatabase.

Esri didn't write Microsoft's geometry type, so I don't know much about the
tools for doing validation.  The documentation should be your starting point,
and after that a Microsoft forum.

- V
0 Kudos
YangLiu2
New Contributor II
Your data is likely *multi-part* polygon, not simply "polygon", which is one
possibility of where the trouble lies.

'sdelayer' can be executed without a service against any enterprise geodatabase.
You merely need to use the Direct Connect syntax ("-i sde:sqlserver:servername
and "-D databasename").

Registering a geodatabase within a database will not, of itself, register all
your native geometry with the geodatabase.

Esri didn't write Microsoft's geometry type, so I don't know much about the
tools for doing validation.  The documentation should be your starting point,
and after that a Microsoft forum.

- V


Good Morning,

Since the feature class cannot be added in ArcMap, I could not figure out how to change my multi-part feature class to simple feature class using SQL Server 2008R2. Do you have any idea on it? Besides, I unioned same 254-county feature class into several regions using same method. In this time, the unioned feature class is able to be added, and its feature type is simple. However, 3 of 6 polygons cannot be seen in ArcMap. When I zoom into a large scale, the missed polygons are shown. And then I zoom out back to full extent, they are gone again. By the way, I found that there are a lot cracks in each polygon. Any thought? Thanks!

Best,
YL
0 Kudos
VinceAngelo
Esri Esteemed Contributor
It appears that you have several issues overlapping here.  Tech Support is likely going
to be your best bet for untangling them.

- V
0 Kudos