Hi

If you are working with Lat/Lon coordinates, you probably should use the Geography data type rather than the Geometry. Unfortunately these data types do use float to store the coordinates with the inherent issues that they have. Basically any method that returns a geometry will likely cause slight shifts to the points. The one that annoys me the most is MakeValid(). The good news is 2012 uses a higher precision float than 2008, but you still get the shift all be it smaller.

Zerko, another thing you may want to consider when breaking apart your polygons is polygons with interior rings (donuts). I usually break the polygons into parts and then into rings.

Here's an example

CREATE TABLE #rings (RegisterID int not null, PartNum int not null, RingNum int not null, Ring Geometry, NumPoints int);

ALTER TABLE #rings ADD CONSTRAINT rings_pk PRIMARY KEY (RegisterId, PartNum, RingNum);

;WITH

e1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

e2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

--e3(N) AS (SELECT 1 FROM E2 a, E2 b), --10000

cteTally(N) AS (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM e2

),

-- Get Individual polygons from Geometry

GetPolygonParts AS (

SELECT RegisterID,

RegisteredExtent.STGeometryN(t.N) Part,

RegisteredExtent.STGeometryN(t.N).STNumPoints() NumPoints,

t.N PartNum

FROM Register a

CROSS APPLY (select TOP(a.RegisteredExtent.STNumGeometries()) n from cteTally z ) t

WHERE RegisteredExtent is not null

),

-- Split to internal and external rings

GetRings AS (

SELECT a.RegisterID, a.PartNum, 0 RingNum,

Part.STExteriorRing() Ring, Part.STExteriorRing().STNumPoints() NumPoints

FROM GetPolygonParts a

UNION ALL

SELECT a.RegisterID, a.PartNum, t.N RingNum,

Part.STInteriorRingN(t.N) Ring, Part.STInteriorRingN(t.N).STNumPoints() NumPoints

FROM GetPolygonParts a

CROSS APPLY (select TOP(a.Part.STNumInteriorRing()) n from cteTally z ) t

)

INSERT INTO #rings SELECT * FROM GetRings

CREATE TABLE #coords (RegisterID int not null, PartNum int not null, RingNum int not null, X float not null, Y float not null)

ALTER TABLE #coords ADD CONSTRAINT int_pk PRIMARY KEY (RegisterId, PartNum, RingNum, x, y);

;WITH

e1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

e2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

e3(N) AS (SELECT 1 FROM E2 a, E2 b), --10000

cteTally(N) AS (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM e3

),

GetRingCoords AS (

SELECT RegisterID, p.PartNum, p.RingNum

,p.Ring.STPointN(t.N).STX X, p.Ring.STPointN(t.N).STY Y

FROM #rings p

CROSS APPLY (select TOP (p.NumPoints - 1) n from cteTally z) t

)

INSERT INTO #coords SELECT * FROM GetRingCoords

Putting them together into another format from that should be relatively easy. I found it worked better with the TOP clause in the cross apply.

I use a double cast to put the coordinates into a varchar ... CAST(CAST(@x AS NUMERIC(10,6)) AS VARCHAR(11))

-Micky