For each CoordGeom segment (Line, Curve or Spiral) inside /LandXML/Alignments/Alignment/CoordGeom , I'm trying to calculate the running total for @length in the following SQL query:
Declare @xml XML = '<?xml version="1.0"?>
<LandXML xmlns="http://www.landxml.org/schema/LandXML-1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.landxml.org/schema/LandXML-1.1 http://www.landxml.org/schema/LandXML-1.1/LandXML-1.1.xsd" date="2007-10-20" time="17:22:09" version="1.1" language="English" readOnly="false">
<Units>
<Imperial areaUnit="squareFoot" linearUnit="foot" volumeUnit="cubicYard" temperatureUnit="fahrenheit" pressureUnit="inchHG" diameterUnit="inch" angularUnit="decimal degrees" directionUnit="decimal degrees"></Imperial>
</Units>
<CoordinateSystem desc="HARN (HPGN) Florida State Planes, East Zone, US Foot" ogcWktCode="PROJCS["FLHP-EF",GEOGCS["LL-HPGN",DATUM["HPGN",SPHEROID["GRS1980",6378137.000,298.25722210]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Transverse_Mercator"],PARAMETER["false_easting",656166.667],PARAMETER["false_northing",0.000],PARAMETER["scale_factor",0.999941176471],PARAMETER["central_meridian",-81.00000000000000],PARAMETER["latitude_of_origin",24.33333333333333],UNIT["Foot_US",0.30480060960122]]" horizontalDatum="HPGN" horizontalCoordinateSystemName="FLHP-EF" fileLocation="AutoCAD Map Zone Name"></CoordinateSystem>
<Project name="C:\Documents and Settings\JorgeK\Desktop\CURRENT\UCF\SR-DGN-PROJ\UCF-ARBORETUM\dwg\UCF-ARB-FDGN.dwg"></Project>
<Application name="AutoCAD Civil 3D" desc="Civil 3D" manufacturer="Autodesk, Inc." version="2008" manufacturerURL="www.autodesk.com/civil" timeStamp="2007-10-20T17:22:09"></Application>
<Alignments name="">
<Alignment name="AL-1" length="54.055248541569" staStart="1000." desc="">
<CoordGeom>
<Line dir="332.184029560865" length="8.864727370067">
<Start>1550921.5827274956 592831.84063415369</Start>
<End>1550917.4461515071 592839.68105062458</End>
</Line>
<Spiral length="10." radiusEnd="30." radiusStart="INF" rot="cw" spiType="clothoid" theta="9.549296585514" totalY="0.554454236563" totalX="9.972257921783" tanLong="6.676392709492" tanShort="3.342176984846">
<Start>1550917.4461515071 592839.68105062458</Start>
<PI>1550914.3307244433 592845.58599368203</PI>
<End>1550912.3023774354 592848.24229678721</End>
</Spiral>
<Curve rot="cw" chord="1.295497212228" crvType="arc" delta="2.474409707229" dirEnd="320.160323270288" dirStart="322.634732977518" external="0.006995416978" length="1.295597893068" midOrd="0.006993786163" radius="30.000000000797" tangent="0.6478996485">
<Start>1550912.3023774354 592848.24229678733</Start>
<Center>1550888.4588973736 592830.03547228139</Center>
<End>1550911.494099478 592849.25471951056</End>
<PI>1550911.9091709286 592848.7572361991</PI>
</Curve>
<Spiral length="10." radiusEnd="INF" radiusStart="30." rot="cw" spiType="clothoid" theta="9.549296585514" totalY="0.554454236563" totalX="9.972257921783" tanLong="6.676392709492" tanShort="3.342176984846">
<Start>1550911.494099478 592849.25471951056</Start>
<PI>1550909.3529630154 592851.82097600971</PI>
<End>1550904.2846047955 592856.16677665606</End>
</Spiral>
<Line dir="310.611026682892" length="7.019159649992">
<Start>1550904.2846047955 592856.16677665606</Start>
<End>1550898.9560374692 592860.73569036077</End>
</Line>
<Line dir="300.875832153876" length="16.875763628442">
<Start>1550898.9560374692 592860.73569036077</Start>
<End>1550884.4718826693 592869.39598237514</End>
</Line>
</CoordGeom>
</Alignment>
<Alignment name="AL-2" length="103.912591379321" staStart="8000." desc="">
<CoordGeom>
<Line dir="17.57965069973" length="21.307738926737">
<Start>1550912.0899307244 592848.51720009069</Start>
<End>1550918.5255355434 592868.82982495264</End>
</Line>
<Spiral length="10." radiusEnd="33.501322221944" radiusStart="INF" rot="cw" spiType="clothoid" theta="8.551271369754" totalY="0.496701819232" totalX="9.977748041186" tanLong="6.674461833374" tanShort="3.340421001603">
<Start>1550918.5255355434 592868.82982495264</Start>
<PI>1550920.5414324952 592875.19257712457</PI>
<End>1550921.0656232478 592878.49161169864</End>
</Spiral>
<Line dir="9.028379326447" length="1.811956496263">
<Start>1550921.0656232478 592878.49161169864</Start>
<End>1550921.3499620936 592880.28111938655</End>
</Line>
<Spiral length="10." radiusEnd="36.82437422537" radiusStart="INF" rot="cw" spiType="clothoid" theta="7.779599887078" totalY="0.452003113127" totalX="9.981579620353" tanLong="6.673116056263" tanShort="3.339197204298">
<Start>1550921.3499620936 592880.28111938655</Start>
<PI>1550922.3971320367 592886.8715613574</PI>
<End>1550922.4699049771 592890.20996455965</End>
</Spiral>
<Line dir="1.248779438117" length="33.289436906511">
<Start>1550922.4699049771 592890.20996455965</Start>
<End>1550923.1954012257 592923.49149494432</End>
</Line>
<Line dir="2.196010733584" length="3.17722982984">
<Start>1550923.1954012257 592923.49149494432</Start>
<End>1550923.3171470633 592926.66639137454</End>
</Line>
<Spiral length="10." radiusEnd="30." radiusStart="INF" rot="cw" spiType="clothoid" theta="9.549296585514" totalY="0.554454236563" totalX="9.972257921783" tanLong="6.676392709492" tanShort="3.342176984846">
<Start>1550923.3171470633 592926.66639137454</Start>
<PI>1550923.5729746986 592933.3378822516</PI>
<End>1550923.145219343 592936.65257124556</End>
</Spiral>
<Curve rot="cw" chord="0.382669369498" crvType="arc" delta="0.730849615493" dirEnd="351.915864559344" dirStart="352.646714174837" external="0.000610167975" length="0.382671963827" midOrd="0.000610155565" radius="30.000000000672" tangent="0.191338576292">
<Start>1550923.145219343 592936.65257124568</Start>
<Center>1550893.3919440852 592932.81296039547</Center>
<End>1550923.0938230981 592937.03177339805</End>
<PI>1550923.1207304872 592936.84233622323</PI>
</Curve>
<Spiral length="10." radiusEnd="INF" radiusStart="30." rot="cw" spiType="clothoid" theta="9.549296585514" totalY="0.554454236563" totalX="9.972257921783" tanLong="6.676392709492" tanShort="3.342176984846">
<Start>1550923.0938230979 592937.03177339805</Start>
<PI>1550922.6238228632 592940.34073661186</PI>
<End>1550920.6013693318 592946.70343415416</End>
</Spiral>
<Line dir="342.366567947968" length="3.943557256143">
<Start>1550920.6013693318 592946.70343415416</Start>
<End>1550919.4067632062 592950.46169971651</End>
</Line>
</CoordGeom>
</Alignment>
</Alignments>
</LandXML>';
WITH XMLNAMESPACES (DEFAULT 'http://www.landxml.org/schema/LandXML-1.1')
SELECT
T.record.value('(../../@name)[1]', 'VARCHAR(32)') as AlignmentName,
T.record.value('(../../@length)[1]', 'FLOAT') as AlignmentLength,
T.record.value('(../../@staStart)[1]', 'FLOAT') as AlignmentStartM,
T.record.value('let $i := . return count(../*[. << $i]) + 1', 'int') as SegmentNum,
T.record.value('local-name(.[1])', 'VARCHAR(32)') as SegmentType,
T.record.value('@length[1]', 'FLOAT') as SegmentLength,
T.record.value('sum(for $x in . return $x/@length)', 'float') as SegmentStartM,
T.record.value('sum(for $x in . return $x/@length)', 'float') as SegmentEndM
FROM @xml.nodes('LandXML/Alignments/Alignment/CoordGeom/*') AS T(record);The goal is to get SegmentStartM to equal the sum of all the previous @length attribute values in a particular alignment.
T.record.value('sum(for $x in . return $x/@length)', 'float') as SegmentStartMSegmentEndM would then equal the sum of all the previous @length attribute values plus the current @length.
T.record.value('sum(for $x in . return $x/@length)', 'float') as SegmentEndMI've been banging my head on this for days, feel like I've tried everything but just not getting it:
T.record.value('let $i := . return count(../*[@length << $i]) + ../../@staStart[1]', 'float') as SegmentStartM,
T.record.value('let $i := . return count(../*[. << $i]) + ../../@staStart[1]', 'float') as SegmentStartM,
T.record.value('let $i := @length[1] return count(../*[. << $i]) + ../../@staStart[1]', 'float') as SegmentStartM,
T.record.value('let $i := . let $sum := sum(@length[$i]) return $sum', 'FLOAT') as SegmentStartM,
T.record.value('let $i := (1,2,3,4) let $sum := sum($i) return $sum', 'FLOAT') as SegmentStartM,
T.record.value('let $s := . for $i in $s return sum(@length[$i and $s])', 'float') as SegmentStartM,
T.record.value('let $s := . for $i in $s return sum(@length[$i])', 'float') as SegmentStartM,
T.record.value('let $s := . for $i in $s return sum(@length[$i and $s])', 'float') as SegmentStartM,
T.record.value('for $pos in . return sum(@length[$pos])', 'float') as SegmentStartM,
T.record.value('let $i := . return sum(for $x in $i return @length[. << $x])', 'float') as SegmentStartM,
T.record.value('let $i := . return sum(@length[. << $i])', 'float') as SegmentStartM,
T.record.value('let $i := . for $s in (@length[. << $i]) return sum($s)', 'float') as SegmentStartM,
T.record.value('let $i := . for $s in (../*[. << $i]) return sum(@length[$s])', 'float') as SegmentStartM,
T.record.value('let $i := . for $x in ../*[. << $i] return @length[$x]', 'float') as SegmentStartM,Anyone know how to caculate the running total of an attribute value (@length) for the current node plus all previous nodes within the same alignment?
4条答案
按热度按时间z4bn682m1#
If i'm understanding the question right, maybe something like this?
Basically just store the results in a temp table to make it easier to work with in sql, then join and group on the AlighmentName and SegmentNum
mwyxok5s2#
Looks like you just need a windowed function which sums SegmentLength partitioned by AlignmentName and Ordered by SegementNum?
ugmeyewa3#
You can put the
CoordGeomselection into anAPPLYand just calculate the running sum over that, which simplifies it a lot. It means you don't need to use the complex and slowcount(. <<hack to get a row-number, as you can just use the normalROW_NUMBERfunction.There are also other simplifications:
//axis, select directly from each level of the XML using separate.nodesfunctions.[1]on attributes orlocal-nameas they are guaranteed to return one value.ROW UNBOUNDED PRECEDINGfor running sum. It's faster and in some cases more accurate.decimalinstead offloatfor better accuracy.db<>fiddle
Note that you cannot select the
.nodesvalue directly out of a subquery, you must select values from it using.value.dgsult0t4#
I'm a big fan of the old hated OPENXML, so here's a potential solution to your problem:
The upside of OPENXML is that it returns positional IDs of elements which simplifies this kind of stuff. @mp:id is the magic id of each element ordered, and @mp:parentid contains the parent id, which is used to partition by.
Rest is just a single SUM OVER PARTITION running aggregation.
Important when handling openxml is to always close the document handle. Also, due to namespaces, one needs to create a pseudo namespace xml, but otherwise it's pretty smooth