September 15, 2014 at 3:42 am
Hi,
I'm a bit of a newbie to GIS and I need to identify, geometrically, which of my company's customers are located within a particular area. In this case it is those who are within the M25 - the major motorway which encircles London, UK.
I have co-ordinates for the route of the M25 as a set of LINESTRING segments and can combine them into a single MULTILINESTRING using geometry::UnionAggregate. But what I need to do is convert this into a POLYGON so I can use (eg) STIntersects to identify which customers are "inside" and which are "outside" this polygon. I'm sure there must be an easy way to convert a MULTILINESTRING into a POLYGON but I can't seem to find it!
Many thanks.
September 15, 2014 at 12:56 pm
Unfortunately there is no built in way to convert from a linestring to a polygon, which is a bit of an oversite in my opinion.
One way to do this is
-- Set up a test linestring
DECLARE @g Geometry = Geometry::STGeomFromText('LINESTRING (0 0, 10 0, 10 10, 0 10, 0 0)',0);
SELECT
CASE
WHEN @g.STIsRing() = 1 THEN -- Check that it closes
-- convert to text and replace linestring with polygon. Fix brackets
Geometry::STGeomFromText(REPLACE(REPLACE(@g.ToString(),'LINESTRING (','POLYGON (('),')','))'),@g.STSrid)
ELSE @g
END Geom;
September 15, 2014 at 1:51 pm
Quick thought, don't ever use geometry for geographical data, use geometry with the appropriate SRID, normally 4326.
mickyT's solution is straight forward, just make certain that the direction is correct and that the start point is the same as the endpoint.
😎
September 15, 2014 at 2:35 pm
Eirikur Eiriksson (9/15/2014)
Quick thought, don't ever use geometry for geographical data, use geometry with the appropriate SRID, normally 4326.mickyT's solution is straight forward, just make certain that the direction is correct and that the start point is the same as the endpoint.
😎
+100 on the Geography for Lat/Longs.
And I just noticed that you said MULTILINESTRING not LINESTRING. This makes me think that potentially the lines aren't closing correctly. Here's a variation that will be a bit more robust. It is Geometry again, but should work for Geography as well
DECLARE @g Geometry = Geometry::STGeomFromText('MULTILINESTRING ((0 0, 10 0),(0 10, 10 10, 10 0),(0 10, 0 0),(20 20, 30 20, 30 30),(20 20, 20 30, 30 30))',0)
SELECT @g.STUnion(@g.STPointN(1)).ToString() cleanedResult,
CASE
WHEN
@g.STUnion(@g.STPointN(1)).STIsClosed() = 1 AND -- better check than STIsRing for this
@g.STUnion(@g.STPointN(1)).STGeometryType() like '%LINESTRING' THEN
Geometry::STGeomFromText(
CONCAT(
REPLACE(@g.STUnion(@g.STPointN(1)).ToString(),'LINESTRING (','POLYGON ((')
,')')
,@g.STSrid)
END
September 16, 2014 at 3:18 am
Thanks for the responses guys - but I wonder if I might be on "Mission Impossible" here! Couple of points:
First, geometry (rather than geography) is fine for the UK because (a) we're small 🙂 and (b) we have excellent mapping, provided by Ordnance Survey, that is based on a planar geometry with SRID 27700 and which is in general use throughout. (Everywhere in the UK has an "Ordnance Survey Grid Reference".)
Second, here is the dataset (provided by Ordnance Survey) used to derive the "line of the M25" (with a couple of corrections added by me.):
LINESTRING (503858 195242, 504000 195726)
LINESTRING (504157 179690, 504100 179870, 504097 179899, 504080 180090)
LINESTRING (504554 178588, 504590 178460)
LINESTRING (504590 178460, 504596 178424, 504614 178317, 504656 178067)
LINESTRING (545955 155393, 546157 155479, 546599 155683, 546907 155854)
LINESTRING (542854 154679, 543408 154688, 543892 154759)
LINESTRING (549026 163141, 549458 163059)
LINESTRING (550253 163543, 550260 163800, 550261 163819, 550303 164248)
LINESTRING (550259 162866, 550278 162658, 550284 162600, 550320 162214, 550351 161901, 550388 161593)
LINESTRING (540030 153830, 540510 153780, 540611 153796, 540700 153810, 541079 153914)
LINESTRING (552278 167068, 552320 167130, 552403 167302, 552470 167440, 552522 167599)
LINESTRING (537679 153591, 537840 153710, 537933 153765, 537960 153780, 538160 153880, 538435 153990, 538764 154015)
LINESTRING (554970 170910, 555245 171185, 555270 171210, 555417 171412)
LINESTRING (553250 168710, 553490 168930, 553647 169160, 553790 169370, 553792 169373, 553937 169600, 554071 169812)
LINESTRING (527230 153520, 527511 153626, 527600 153660, 527957 153713, 528338 153692, 528665 153577)
LINESTRING (558705 186526, 558790 187000, 558791 187008, 558821 187276, 558850 187520, 558846 187545, 558791 187941)
LINESTRING (558113 183802, 558160 184110, 558209 184508, 558220 184540, 558233 184653)
LINESTRING (516514 158376, 516637 158294, 516690 158260, 516840 158140, 516848 158131, 516904 158075, 517010 157970, 517076 157882, 517290 157600)
LINESTRING (514024 158377, 514240 158410, 514554 158434, 514839 158491)
LINESTRING (511010 157414, 511260 157370, 511535 157367, 511670 157400, 511890 157499)
LINESTRING (550240 197910, 550531 197681, 551402 197312)
LINESTRING (540914 199752, 541110 199810, 541173 199849, 541293 199922)
LINESTRING (525234 199974, 525390 199950, 525558 199919, 525683 199894)
LINESTRING (502183 172748, 502280 172970, 502350 173080, 502374 173119, 502466 173233, 502568 173356, 502615 173417)
LINESTRING (502773 166332, 502840 166260, 503232 165866, 503391 165661)
LINESTRING (505489 162179, 505550 161994, 505570 161930, 505590 161710, 505594 161556)
LINESTRING (505078 162879, 505192 162711, 505415 162339)
LINESTRING (501668 188619, 501916 190636)
LINESTRING (501568 187212, 501586 187363, 501620 187650, 501629 187687)
LINESTRING (501887 171983, 501950 172160, 502010 172315, 502141 172651)
LINESTRING (501504 170778, 501520 171034, 501576 171248, 501810 171770)
LINESTRING (501461 168372, 501594 168087)
LINESTRING (537291 199854, 537474 199837, 537678 199840)
LINESTRING (523386 199885, 523700 199790, 523940 199817, 524220 199850, 524386 199885)
LINESTRING (536545 200000, 536679 199991)
LINESTRING (503458 194554, 503534 194626, 503677 194824)
LINESTRING (502334 192862, 502399 193008, 502446 193092)
LINESTRING (519892 156086, 520217 155881, 520486 155667, 520618 155515)
LINESTRING (509940 203040, 510057 203033, 510290 203020)
LINESTRING (520255 202524, 520340 202460, 520522 202299, 520710 202130, 520778 202080)
LINESTRING (507937 201625, 508009 201684, 508045 201714, 508085 201747, 508161 201810)
LINESTRING (530415 200746, 530573 200794, 530652 200819, 530850 200880, 531153 200880)
LINESTRING (529926 200574, 530020 200590, 530110 200628, 530249 200686)
LINESTRING (542183 200571, 542711 200755)
LINESTRING (545640 200550, 545840 200417, 546147 200261)
LINESTRING (536357 200012, 536545 200000)
LINESTRING (540640 199670, 540914 199752)
LINESTRING (551402 197312, 551693 197148, 551863 197039, 552132 196866, 552440 196610, 552580 196460, 552600 196440, 553809 195074, 554430 194475, 554640 194320, 554640 194319, 554770 194220, 554870 194140, 554960 194060, 554976 194050, 554978 194048, 555510 193730, 555586 193675, 555955 193415, 555958 193412, 556090 193320, 556720 192480, 556786 192366)
LINESTRING (556786 192366, 556980 192030, 556992 192012, 557082 191886, 557120 191830, 557253 191495, 557280 191430, 557300 191250, 557290 190790, 557366 190353)
LINESTRING (511890 157499, 512155 157661, 512418 157819, 512890 158060, 513081 158135, 513203 158184, 513320 158230, 513620 158300, 513820 158345, 514024 158377)
LINESTRING (504000 195726, 504146 196252)
LINESTRING (504146 196252, 504167 196615, 504190 196990, 504260 197370, 504420 197670, 504630 197890, 504900 198040, 504977 198056, 505130 198090, 506299 198373, 506544 198481, 506795 198683, 507199 199091)
LINESTRING (502615 173417, 502870 173950, 502956 174197, 502976 174252, 503000 174320, 503140 174600, 503163 174631, 503330 174860, 503447 175004, 503620 175219, 503750 175380)
LINESTRING (504554 178588, 504460 178930, 504415 179074, 504370 179218, 504370 179220, 504273 179425, 504180 179620, 504157 179690)
LINESTRING (501916 190636, 501950 190860, 502050 191930, 502118 192201, 502217 192573, 502334 192862)
LINESTRING (501720 185840, 501857 185526, 501952 185309, 502040 185110, 502241 184762, 502370 184540, 502849 184149, 503022 184052, 503252 183964, 503612 183768, 503917 183468, 503999 183332, 504113 183098, 504160 182930, 504183 182755, 504290 181830, 504272 181373, 504264 181137, 504241 180861, 504214 180746, 504170 180550, 504148 180458, 504120 180340, 504104 180245, 504080 180090)
LINESTRING (546907 155854, 547151 155977, 547383 156052, 547939 156128, 548347 156211, 548579 156279, 548794 156364, 549038 156466, 549290 156590, 549480 156720)
LINESTRING (549480 156720, 549570 157000, 549620 157200, 549769 157593, 549890 157839, 550161 158191, 550370 158580, 550418 158997, 550420 159010, 550450 159640, 550480 160240, 550477 160396, 550470 160920, 550458 161015, 550410 161406, 550388 161593)
LINESTRING (541079 153914, 541292 154021, 541668 154263, 542023 154438, 542579 154640, 542854 154679)
LINESTRING (543892 154759, 544080 154800, 544181 154824, 544531 154911, 544750 154986, 544960 155060, 545310 155170, 545698 155298, 545955 155393)
LINESTRING (550303 164248, 550352 164643, 550530 165144, 550690 165450, 550896 165705, 551290 166100, 551540 166290, 551750 166460, 552030 166740, 552050 166763, 552150 166880, 552278 167068)
LINESTRING (550259 162866, 550246 162997, 550240 163070)
LINESTRING (550240 163070, 550253 163543)
LINESTRING (538764 154015, 539270 153975, 540030 153830)
LINESTRING (552522 167599, 552630 167930)
LINESTRING (552630 167930, 552980 168470, 553250 168710)
LINESTRING (535010 152800, 535430 152811, 536092 152711, 536371 152696, 536649 152742, 536858 152835, 537091 153013, 537331 153269, 537501 153455, 537650 153570, 537679 153591)
LINESTRING (555417 171412, 555540 171580, 555710 172160)
LINESTRING (554071 169812, 554140 169920, 554350 170260, 554690 170620, 554840 170780, 554970 170910)
LINESTRING (530650 153220, 531109 153165, 531160 153160, 531550 153050, 532110 152870, 532547 152744, 532600 152730, 532980 152660, 533510 152620, 534050 152630, 534255 152655, 534580 152720, 535010 152800)
LINESTRING (528665 153577, 528800 153520, 528847 153491, 529019 153430, 529201 153400, 529588 153330, 530185 153280, 530650 153220)
LINESTRING (558233 184653, 558329 185119, 558450 185590, 558580 185970, 558690 186440, 558705 186526)
LINESTRING (517290 157600, 517471 157353, 517710 157030, 517749 156984, 517895 156811, 518126 156625, 518321 156513, 518562 156392, 518851 156327, 519222 156281, 519529 156216, 519892 156086)
LINESTRING (557366 190353, 557470 190092, 557540 189950, 557687 189819, 557710 189800, 558050 189520, 558300 189120, 558550 188520)
LINESTRING (558550 188520, 558791 187941)
LINESTRING (514839 158491, 514913 158511, 515241 158599, 515500 158667, 515691 158678, 515983 158644, 516270 158540)
LINESTRING (516270 158540, 516514 158376)
LINESTRING (507960 159100, 508294 158908, 508830 158520, 510380 157650, 511010 157414)
LINESTRING (505594 161556, 505600 161340, 505616 161129, 505620 161070, 505740 160630, 505891 160321, 506040 160020, 506322 159800, 506440 159710, 506789 159562, 506820 159550, 507113 159474, 507130 159470, 507395 159378, 507620 159300, 507960 159100)
LINESTRING (524386 199885, 524740 199960, 525070 200000, 525234 199974)
LINESTRING (501780 167800, 501992 167334, 502187 166995, 502504 166586, 502773 166332)
LINESTRING (503391 165661, 503550 165430, 503580 165378, 503830 164960)
LINESTRING (503830 164960, 504225 164219, 505078 162879)
LINESTRING (505415 162339, 505480 162210, 505489 162179)
LINESTRING (501668 188619, 501670 188580, 501670 188140, 501629 187687)
LINESTRING (501568 187212, 501552 187079, 501550 187060, 501567 186869, 501588 186643, 501600 186500, 501696 185967, 501720 185840)
LINESTRING (502141 172651, 502173 172726, 502183 172748)
LINESTRING (501810 171770, 501852 171887, 501887 171983)
LINESTRING (501594 168087, 501780 167800)
LINESTRING (537678 199840, 537957 199850, 538356 199899, 538490 199890, 538731 199825, 539051 199741, 539632 199657, 540185 199627, 540400 199637, 540640 199670)
LINESTRING (558113 183802, 558029 183479, 557862 183059, 557747 182758, 557658 182445, 557591 182154, 557538 181819, 557516 181491, 557501 181059, 557523 180738, 557570 180101)
LINESTRING (557570 180101, 557576 180030, 557583 179740, 557561 179419, 557541 179257, 557524 179143, 557531 178972, 557536 178928)
LINESTRING (525683 199894, 525752 199882, 526030 199890, 526150 200000)
LINESTRING (541293 199922, 541420 200000)
LINESTRING (523000 200000, 523061 199983, 523386 199885)
LINESTRING (546908 200000, 546930 199990, 547027 199946, 547659 199705, 547882 199566, 548510 199092, 549388 198694, 549695 198506, 549911 198311, 550097 198052, 550240 197910)
LINESTRING (501461 168372, 501431 168467, 501409 168617, 501384 168720, 501400 169000, 501480 169420, 501521 170156, 501504 170778)
LINESTRING (536679 199991, 536777 199983, 536997 199905, 537291 199854)
LINESTRING (504656 178067, 504573 177843, 504308 177314, 504290 177290, 504257 177228, 504177 177081, 504157 177045, 504081 176880, 504040 176675, 504008 176504, 503750 175380)
LINESTRING (503050 194163, 503458 194554)
LINESTRING (503677 194824, 503858 195242)
LINESTRING (502446 193092, 503050 194163)
LINESTRING (520618 155515, 520719 155398, 520979 155036, 521217 154642, 521379 154329, 521574 154004, 521750 153790, 521930 153620, 523300 152710, 523719 152520, 524145 152412, 524548 152396, 525276 152474, 525875 152608)
LINESTRING (525875 152608, 526314 152783, 526515 152923, 526786 153155, 527084 153428, 527230 153520)
LINESTRING (510290 203020, 510872 203032, 511350 203140)
LINESTRING (508161 201810, 508210 201850, 508610 202210, 508757 202351, 508860 202450, 509060 202690, 509310 202910, 509590 202990, 509713 203009, 509780 203020, 509940 203040)
LINESTRING (511350 203140, 511920 203090, 512520 203040)
LINESTRING (512520 203040, 513000 202780, 513253 202682, 513411 202622, 513440 202610, 513628 202560, 513630 202560, 513748 202530, 513910 202490, 514240 202520, 514660 202610, 514798 202637, 514924 202662, 515060 202690, 515140 202708, 515200 202722, 515360 202759, 515540 202800, 515830 202836, 516020 202860, 516530 202930, 516660 202921, 516970 202900, 517320 202880, 517537 202906, 517650 202920, 518090 203060, 518570 203160, 519130 203180)
LINESTRING (519130 203180, 519530 203060, 519810 202900, 520040 202690, 520255 202524)
LINESTRING (507630 201310, 507837 201534, 507870 201570, 507937 201625)
LINESTRING (520778 202080, 521070 201870, 521340 201560, 521408 201466, 521700 201070, 521912 200743, 521960 200670, 522140 200420, 522510 200190)
LINESTRING (526480 200250, 526750 200440, 526975 200455, 527040 200460, 527350 200430, 527610 200320, 527840 200270, 528040 200290, 528310 200390, 528580 200510, 528800 200570, 529080 200610, 529410 200570, 529780 200550, 529926 200574)
LINESTRING (530249 200686, 530330 200720, 530415 200746)
LINESTRING (531153 200880, 531220 200880, 531560 200800, 531573 200800, 531890 200810, 532270 200820, 532570 200830, 532910 200730, 533160 200640, 533420 200500, 533670 200370, 533880 200250, 534070 200190, 534280 200170, 534538 200196, 534897 200209)
LINESTRING (534897 200209, 535129 200209, 535411 200152)
LINESTRING (545108 200899, 545194 200865, 545380 200730, 545640 200550)
LINESTRING (544355 201072, 544589 201088, 544791 201064, 545108 200899)
LINESTRING (507230 200000, 507239 200178, 507250 200420, 507350 200820, 507630 201310)
LINESTRING (522510 200190, 522572 200167, 523000 200000)
LINESTRING (526150 200000, 526480 200250)
LINESTRING (536357 200012, 536211 200033)
LINESTRING (541420 200000, 541660 200200, 541780 200310, 542183 200571)
LINESTRING (546147 200261, 546908 200000)
LINESTRING (542711 200755, 543168 200915, 543380 200960, 543911 200987, 544258 201056, 544355 201072)
LINESTRING (536211 200033, 535411 200152)
LINESTRING (557536 178928, 555710 172160)
LINESTRING (507199 199091, 507216 199616, 507230 200000)
Unfortunately, the line segments are in no particular order or "direction" (though I do think thet close) so the MULTILINESTRING that I get from UnionAggregate isn't easily able to be edited to a POLYGON. It could do with being sorted by some means so as to get the individual segments in the correct order and direction but thats beyond me.
September 16, 2014 at 10:43 am
A breakthrough! I've managed to find a way to sort the individual line segments into order, reversing those that were "backward" with respect to the others. In the process I found a "rogue" line which, once removed, allowed the shape to close. Thereafter, the UnionAggregate function returns a LINESTRING (instead of a MULTILINESTRING) and the original suggestion for using REPLACE to change the format from LINESTRING (..... to POLYGON ((... works a treat.
Thanks guys - I now have the polygon that I needed. :-):-)
September 16, 2014 at 10:47 am
andyscott (9/16/2014)
A breakthrough! I've managed to find a way to sort the individual line segments into order, reversing those that were "backward" with respect to the others. In the process I found a "rogue" line which, once removed, allowed the shape to close. Thereafter, the UnionAggregate function returns a LINESTRING (instead of a MULTILINESTRING) and the original suggestion for using REPLACE to change the format from LINESTRING (..... to POLYGON ((... works a treat.Thanks guys - I now have the polygon that I needed. :-):-)
Good stuff and good job,
and an ample reminder that it is as important to assert the data as the code;-)
😎
September 16, 2014 at 1:31 pm
andyscott (9/16/2014)
A breakthrough! I've managed to find a way to sort the individual line segments into order, reversing those that were "backward" with respect to the others. In the process I found a "rogue" line which, once removed, allowed the shape to close. Thereafter, the UnionAggregate function returns a LINESTRING (instead of a MULTILINESTRING) and the original suggestion for using REPLACE to change the format from LINESTRING (..... to POLYGON ((... works a treat.Thanks guys - I now have the polygon that I needed. :-):-)
Excellent news. Can you share how you did it? I used the following on the data you posted to create the polygon and find the spurious line.SELECT uLine.STGeometryN(N).STIsClosed() Closes, -- Indicate if polygon can be created
-- Return the polygon or the line
CASE
WHEN
uLine.STGeometryN(N).STIsClosed() = 1 AND
uLine.STGeometryN(N).STGeometryType() like '%LINESTRING' THEN
Geometry::STGeomFromText(
CONCAT(
REPLACE(uLine.STGeometryN(N).ToString(),'LINESTRING (','POLYGON ((')
,')')
,uLine.STGeometryN(N).STSrid)
ELSE
uLine.STGeometryN(N)
END
FROM ( -- Union up the lines into a multilinestring. Touching lines are dissolved
SELECT Geometry::UnionAggregate(Line) ULine -- Avoids the need to Union with start point
FROM Line
) u
CROSS APPLY ( -- Used to split the unioned line into components
SELECT TOP (uLine.STNumGeometries()) N
FROM Tally -- Table of numbers
) t
September 17, 2014 at 4:14 am
mickyT (9/16/2014)
Can you share how you did it? I used the following.........
Nothing like as elegant as that! I didn't appreciate the power of UnionAggregate and spent my time getting the rows into order, such that the end point of row-n matched with the start point of row-n+10 (and reversing the points in any rows which were "backward") because I thought that was what UnionAggregate needed. So you've done in one statement what took me a temp table, a function and a procedure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply