The PointNum attribute means the number of coordinates in the chain.
The coordinate chain starts at 1 and ends at 1 as well (inside each Parcel element).
However, within the sequences of these points, there is one more inner logic, which must be explicated. This is the logic of full coincidence of the X and Y coordinates. For example:
in the sequence within Parcel_ID = 1 there are equal coordinates with PointNum 1 and 4, 5 and 7
in the sequence within Parcel_ID = 2 there are equal coordinates with PointNum 1 и 3
GOAL: I need to number these sub-chains also. (in the SequenceNum column)
CODE
DECLARE @xml XML =
N'<Parcels>
<Parcel ID="1">
<EntitySpatial>
<SpatialElement>
<SpelementUnit PointNum="1">
<Ordinate X="100.1" Y="-100.1"/>
</SpelementUnit>
<SpelementUnit PointNum="2">
<Ordinate X="100.2" Y="-100.2"/>
</SpelementUnit>
<SpelementUnit PointNum="3">
<Ordinate X="100.3" Y="-100.3"/>
</SpelementUnit>
<SpelementUnit PointNum="4">
<Ordinate X="100.1" Y="-100.1"/>
</SpelementUnit>
<SpelementUnit PointNum="5">
<Ordinate X="100.5" Y="-100.5"/>
</SpelementUnit>
<SpelementUnit PointNum="6">
<Ordinate X="100.6" Y="-100.6"/>
</SpelementUnit>
<SpelementUnit PointNum="7">
<Ordinate X="100.5" Y="-100.5"/>
</SpelementUnit>
<SpelementUnit PointNum="8">
<Ordinate X="100.8" Y="-100.8"/>
</SpelementUnit>
<SpelementUnit PointNum="9">
<Ordinate X="100.9" Y="-100.9"/>
</SpelementUnit>
<SpelementUnit PointNum="10">
<Ordinate X="100.11" Y="-100.11"/>
</SpelementUnit>
<SpelementUnit PointNum="1">
<Ordinate X="100.1" Y="-100.1"/>
</SpelementUnit>
</SpatialElement>
</EntitySpatial>
</Parcel>
<Parcel ID="2">
<EntitySpatial>
<SpatialElement>
<SpelementUnit PointNum="1">
<Ordinate X="200.1" Y="-200.1"/>
</SpelementUnit>
<SpelementUnit PointNum="2">
<Ordinate X="200.2" Y="-200.2"/>
</SpelementUnit>
<SpelementUnit PointNum="3">
<Ordinate X="200.1"" Y="-200.1"/>
</SpelementUnit>
<SpelementUnit PointNum="4">
<Ordinate X="200.4" Y="-200.4"/>
</SpelementUnit>
<SpelementUnit PointNum="5">
<Ordinate X="200.5" Y="-200.5"/>
</SpelementUnit>
<SpelementUnit PointNum="1">
<Ordinate X="200.1" Y="-200.1"/>
</SpelementUnit>
</SpatialElement>
</EntitySpatial>
</Parcel>
</Parcels>';
SELECT base.value('@ID', 'VARCHAR(1000)') AS Parcel_ID
, DENSE_RANK() OVER(ORDER BY outr) as SpatialElement_Count
,outr2.value('(Ordinate/@X)[1]', 'NVARCHAR(1000)') AS Ordinate_X
,outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(1000)') AS Ordinate_Y
,outr2.value('@PointNum', 'NVARCHAR(1000)') AS PointNum
FROM @xml.nodes('Parcels/Parcel') as x(base)
OUTER APPLY base.nodes('EntitySpatial/SpatialElement') AS B(outr)
OUTER APPLY outr.nodes('SpelementUnit') AS C(outr2);
Desired output (“SequenceNum” column)
+-----------+----------------------+------------+------------+--------------------------+ | Parcel_ID | SpatialElement_Count | Ordinate_X | Ordinate_Y | PointNum | SequenceNum | +-----------+----------------------+------------+------------+--------------------------+ | 1 | 1 | 100.1 | -100.1 | 1 | 1 | | 1 | 1 | 100.2 | -100.2 | 2 | 1 | | 1 | 1 | 100.3 | -100.3 | 3 | 1 | | 1 | 1 | 100.1 | -100.1 | 4 | 1 | | | | | | | | | 1 | 1 | 100.5 | -100.5 | 5 | 2 | | 1 | 1 | 100.6 | -100.6 | 6 | 2 | | 1 | 1 | 100.5 | -100.5 | 7 | 2 | | | | | | | | | 1 | 1 | 100.8 | -100.8 | 8 | null | | 1 | 1 | 100.9 | -100.9 | 9 | null | | 1 | 1 | 100.11 | -100.11 | 10 | null | | 1 | 1 | 100.1 | -100.1 | 1 | null | | | | | | | | | | | | | | | | 2 | 2 | 200.1 | -200.1 | 1 | 1(or 3) | | 2 | 2 | 200.2 | -200.2 | 2 | 1(or 3) | | 2 | 2 | 200.1 | -200.1 | 3 | 1(or 3) | | | | | | | | | 2 | 2 | 200.4 | -200.4 | 4 | null | | 2 | 2 | 200.5 | -200.5 | 5 | null | | 2 | 2 | 200.1 | -200.1 | 1 | null | +-----------+----------------------+------------+------------+--------------------------+
Advertisement
Answer
This might help you. Please note that it works for your example but I can see it failing for cases like…
- More than two equal coordinates.
- Intertwined coordinates groups.
- PointNum attribute jumps (breaks in the ordinal sequence)
WITH parseXml AS (
SELECT
base.value('@ID', 'VARCHAR(1000)') AS Parcel_ID,
outr.value('for $se in . return count(//SpatialElement[. << $se]) + 1', 'INTEGER') AS SpatialElement_Count,
outr2.value('for $o in . return count(../SpelementUnit[. << $o]) + 1', 'INTEGER') AS Ordinate_position,
outr2.value('@PointNum', 'INTEGER') AS PointNum,
outr2.value('(Ordinate/@X)[1]', 'NVARCHAR(100)') AS Ordinate_X,
outr2.value('(Ordinate/@Y)[1]', 'NVARCHAR(100)') AS Ordinate_Y
FROM @xml.nodes('Parcels/Parcel') as x(base)
OUTER APPLY base.nodes('EntitySpatial/SpatialElement') AS B(outr)
OUTER APPLY outr.nodes('SpelementUnit') AS C(outr2)
),
groups AS (
SELECT
Parcel_ID, Ordinate_X, Ordinate_Y,
--Exclude last row in parcel_id
MIN(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END) AS StartSequence,
MAX(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END) AS EndSequence,
ROW_NUMBER() OVER(PARTITION BY Parcel_ID
ORDER BY MIN(CASE WHEN PointNum = Ordinate_position THEN Ordinate_position END)) AS SequenceNum
FROM
parseXml
GROUP BY
Parcel_ID, Ordinate_X, Ordinate_Y
HAVING COUNT(*) > 1
)
SELECT
ref.Parcel_ID, ref.SpatialElement_Count, ref.Ordinate_X, ref.Ordinate_Y, ref.PointNum,
groups.SequenceNum
FROM
parseXml ref
LEFT JOIN groups
ON groups.StartSequence <= ref.Ordinate_position
AND groups.EndSequence >= ref.Ordinate_position
AND groups.Parcel_ID = ref.Parcel_ID
ORDER BY
ref.Parcel_ID, Ordinate_position;