[BM SS4] Registered SQL*Server Feature has no Text Sub-Feature

Offline Bruce Reeves SRNS over 7 years ago

I've got a table in SQL*Server intended to store polygon information. When I "register features", I set the feature to be a polygon-collection, and in the GSA I see the resultant "MyPolygon_Collection" and the sub-feature "MyPolygon". I can't figure out how to get a second sub-feature of the collection for "MyPolygon_CollectionText". When I right-click and "insert polygon placement metadata", there is no bottom section for the annotation modes....And the sub-feature only show "Schema Versioning" for the right-click operations. Is annotation not possible for "collections"?

Parents

0 Offline Jeff Bielefeld Tue, Mar 1 2016 5:35 PM

Bruce,

You will need to have a related table which contains locations of annotation sub-features. For example you might have...

CREATE TABLE BUILDINGS2
	(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CONSTRUCTED] [date] NULL,
	[VALUE] [int] NULL,
	[NAME] [varchar](50) NULL,
	[CLASSIFICATION] [int] NULL,
	GEOM geometry NULL
    );

...to create the polygon table, then for the annotation text table...

CREATE TABLE BUILDING_TEXT 
	(TEXT_ID INT IDENTITY (1,1) CONSTRAINT BUILDINGTEXT_PK PRIMARY KEY, 
	BUILDING_REF INT CONSTRAINT ST_FK REFERENCES BUILDINGS2(ID) ON DELETE CASCADE, 
	MS_ANGLE REAL,
	MS_XSCALE REAL,
	MS_YSCALE REAL,
	GEOM GEOMETRY);

...which when registered in the Bentley Geospatial Administrator yield the following:

Attached is a sample SQL script which demonstrates how a polygon table with annotation text table can be created.

Fullscreen sqlserver_sample_data3.sql Download

USE [sample1]
GO

IF OBJECT_ID ( 'dbo.BUILDING_TEXT', 'U' ) IS NOT NULL 
    DROP TABLE dbo.BUILDING_TEXT;
GO

IF OBJECT_ID ( 'dbo.BUILDINGS2', 'U' ) IS NOT NULL 
    DROP TABLE dbo.BUILDINGS2;
GO

IF OBJECT_ID ( 'dbo.CLASSIFICATIONS', 'U' ) IS NOT NULL 
    DROP TABLE dbo.CLASSIFICATIONS;
GO

CREATE TABLE CLASSIFICATIONS
	(
	ID int NOT NULL,
	[NAME] [varchar](50) NULL
	);

ALTER TABLE CLASSIFICATIONS
	WITH CHECK
	ADD CONSTRAINT CLASSIFICATIONS_PK
	PRIMARY KEY(ID);
GO

INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (1, 'CLASSIFICATION 1');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (2, 'CLASSIFICATION 2');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (3, 'CLASSIFICATION 3');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (4, 'CLASSIFICATION 4');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (5, 'CLASSIFICATION 5');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (6, 'CLASSIFICATION 6');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (7, 'CLASSIFICATION 7');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (8, 'CLASSIFICATION 8');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (9, 'CLASSIFICATION 9');
INSERT INTO CLASSIFICATIONS (ID, NAME) VALUES (10, 'CLASSIFICATION 10');
GO

CREATE TABLE BUILDINGS2
	(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CONSTRUCTED] [date] NULL,
	[VALUE] [int] NULL,
	[NAME] [varchar](50) NULL,
	[CLASSIFICATION] [int] NULL,
	GEOM geometry NULL
    );
GO

INSERT INTO BUILDINGS2 (NAME, VALUE, CLASSIFICATION, GEOM) VALUES ('BUILDING 1', 930264, 1, geometry::STGeomFromText('POLYGON ((-90 285, 9 285, 9 385, -90 385, -90 285))', 0));
INSERT INTO BUILDINGS2 (NAME, VALUE, CLASSIFICATION, GEOM) VALUES ('BUILDING 2', 829498, 2, geometry::STGeomFromText('POLYGON ((97 114, 197 114, 197 214, 97 214, 97 114))', 0));
INSERT INTO BUILDINGS2 (NAME, VALUE, CLASSIFICATION, GEOM) VALUES ('BUILDING 3', 51898, 1,  geometry::STGeomFromText('POLYGON ((101 -233, 201 -233, 201 -133, 101 -133, 101 -233))', 0));
INSERT INTO BUILDINGS2 (NAME, VALUE, CLASSIFICATION, GEOM) VALUES ('BUILDING 4', 984885, 7, geometry::STGeomFromText('POLYGON ((612 -173, 712 -173, 712 -73, 612 -73, 612 -173))', 0));
INSERT INTO BUILDINGS2 (NAME, VALUE, CLASSIFICATION, GEOM) VALUES ('BUILDING 5', 681766, 6, geometry::STGeomFromText('POLYGON ((500 0, 600 0, 600 100, 500 100, 500 0))', 0));
GO

ALTER TABLE BUILDINGS2
	WITH CHECK
	ADD CONSTRAINT BUILDINGS2_PK
	PRIMARY KEY(ID)
GO

ALTER TABLE BUILDINGS2
	WITH CHECK
	ADD CONSTRAINT BUILDINGS2_FK
	FOREIGN KEY (CLASSIFICATION)
	REFERENCES CLASSIFICATIONS(ID)
GO

CREATE SPATIAL INDEX BUILDINGS2_SPX 
	ON BUILDINGS2(GEOM)
	WITH ( BOUNDING_BOX = ( 0.0, 0.0, 5000.0, 5000.0 ) );
GO

-- The following code will fill the StartDate column of the FiscalYear table with random dates between two given dates: 
-- First, let's declare the date range.
DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;

-- Set the start and date dates. In this case, we are using
-- the month of october, 2006.
SET @date_from = '1985-10-14';
SET @date_to = '2009-04-27';

UPDATE BUILDINGS2 SET CONSTRUCTED =  
(
    -- Remember, we want to add a random number to the
    -- start date. In SQL we can add days (as integers)
    -- to a date to increase the actually date/time
    -- object value.
    @date_from +
    (
    	-- This will force our random number to be >= 0.
    	ABS
    	(
    		-- This will give us a HUGE random number that
    		-- might be negative or positive.
    		CAST(CAST(NewID() AS BINARY(8)) AS INT)
    	)

    	-- Our random number might be HUGE. We can't have
    	-- exceed the date range that we are given.
    	-- Therefore, we have to take the modulus of the
    	-- date range difference. This will give us between
    	-- zero and one less than the date range.
    	%

    	-- To get the number of days in the date range, we
    	-- can simply substrate the start date from the
    	-- end date. At this point though, we have to cast
    	-- to INT as SQL will not make any automatic
    	-- conversions for us.
    	CAST((@date_to - @date_from) AS INT)
    )
);
GO

-- CREATE BUILDING TEXT ANNOTATION TABLE

CREATE TABLE BUILDING_TEXT 
	(TEXT_ID INT IDENTITY (1,1) CONSTRAINT BUILDINGTEXT_PK PRIMARY KEY, 
	BUILDING_REF INT CONSTRAINT ST_FK REFERENCES BUILDINGS2(ID) ON DELETE CASCADE, 
	MS_ANGLE REAL,
	MS_XSCALE REAL,
	MS_YSCALE REAL,
	GEOM GEOMETRY);
GO

INSERT INTO BUILDING_TEXT (BUILDING_REF, GEOM) VALUES (1, geometry::STGeomFromText('POINT (32 354)', 0));
INSERT INTO BUILDING_TEXT (BUILDING_REF, GEOM) VALUES (2, geometry::STGeomFromText('POINT (213 182)', 0));
INSERT INTO BUILDING_TEXT (BUILDING_REF, GEOM) VALUES (3, geometry::STGeomFromText('POINT (58 -246)', 0));
INSERT INTO BUILDING_TEXT (BUILDING_REF, GEOM) VALUES (4, geometry::STGeomFromText('POINT (333 -106)', 0));
INSERT INTO BUILDING_TEXT (BUILDING_REF, GEOM) VALUES (5, geometry::STGeomFromText('POINT (221 50)', 0));
GO

-- CREATE SPATIAL INDEX

CREATE SPATIAL INDEX BUILDINGTEXTSDX 
	ON BUILDING_TEXT(GEOM)
	WITH ( BOUNDING_BOX = ( 0.0, 0.0, 5000.0, 5000.0 ) );
GO

Regards,

Jeff Bielefeld [Bentley]

Reply