1

I am trying to find the part of a LINESTRING between two points in SQL Server 2012 and am looking for how to approach it.

Here's one sample:

declare @point1 geometry, @point2 geometry, @line geometry, @pointbox geometry

set @point1 = geometry::STGeomFromText('POINT (-123.058499 38.918219)', 4326)
set @point2 = geometry::STGeomFromText('POINT (-123.097966 38.952692)', 4326)

set @line = geometry::STGeomFromText('LINESTRING (-123.057896 38.916655, -123.058085 38.916981, -123.058191 38.917187, -123.05834 38.917549, -123.058439 38.917878999999992, -123.05851100000001 38.918196999999992, -123.05852900000001 38.918371999999991, -123.05853 38.919007, -123.058507 38.919596999999996, -123.05840500000001 38.920535, -123.058318 38.921137, -123.058271 38.92132999999999, -123.058027 38.921942, -123.057883 38.922229999999992, -123.05775200000001 38.922447999999996, -123.057574 38.92269799999999, -123.05721100000001 38.923138999999992, -123.05684500000001 38.923541999999991, -123.055985 38.924552999999996, -123.055856 38.92472999999999, -123.055779 38.924872999999991, -123.05567700000002 38.925111999999991, -123.05563000000001 38.925221999999991, -123.05552800000001 38.925550999999992, -123.05545000000001 38.926024999999996, -123.055461 38.926546999999985, -123.05548 38.92671099999999, -123.05550900000001 38.926845, -123.05567100000002 38.927299999999995, -123.05605200000001 38.92789599999999, -123.060857 38.931855999999996, -123.061912 38.932839999999992, -123.062141 38.93324299999999, -123.062585 38.933886, -123.063613 38.93517099999999, -123.06504800000002 38.936489999999992, -123.065573 38.936972, -123.065974 38.93731, -123.066633 38.937771, -123.066931 38.937944, -123.06922399999999 38.939147999999996, -123.07014 38.93965, -123.070645 38.939865, -123.070882 38.939947, -123.071358 38.94005, -123.071444 38.940065, -123.071697 38.940104999999996, -123.07209399999999 38.940138999999995, -123.072876 38.940146, -123.073149 38.940141, -123.073685 38.940079, -123.07417 38.940056, -123.074686 38.940058, -123.074892 38.940062999999995, -123.075349 38.940107, -123.076005 38.940239999999996, -123.076525 38.940374, -123.07682299999999 38.94048, -123.077213 38.940638, -123.07753 38.940777, -123.077879 38.940945, -123.078031 38.941026, -123.07826 38.941165, -123.07865 38.941432, -123.078824 38.941565, -123.079113 38.94182, -123.079278 38.941983, -123.079427 38.942152, -123.079645 38.942429, -123.079742 38.942569999999996, -123.07998 38.942972999999995, -123.08018 38.943438, -123.080329 38.943884, -123.080498 38.944339, -123.080647 38.944651, -123.081219 38.945661, -123.081721 38.946442999999995, -123.081845 38.946622999999995, -123.082146 38.947009, -123.082752 38.947645, -123.083501 38.948364999999995, -123.08372299999999 38.948508, -123.083863 38.948589999999996, -123.084358 38.948847, -123.084593 38.948958, -123.08497299999999 38.949104, -123.087031 38.949740999999996, -123.088145 38.950072, -123.091341 38.951045, -123.092277 38.951304, -123.092667 38.9514, -123.093276 38.951526, -123.09485699999999 38.951826, -123.095502 38.951966999999996, -123.09664000000001 38.952273, -123.097878 38.952662, -123.098431 38.952836999999995, -123.101246 38.953711, -123.101383 38.953753999999996, -123.101519 38.953796, -123.101555 38.953806999999991, -123.102837 38.954225, -123.103544 38.954465999999989, -123.104761 38.954896999999995, -123.10485200000001 38.954937999999991, -123.105327 38.955131999999992, -123.10562200000001 38.955252999999992, -123.106221 38.955486, -123.106644 38.955678999999996, -123.10699000000001 38.95585899999999, -123.107292 38.956034999999986, -123.10745500000002 38.956130999999992, -123.108089 38.956580999999986, -123.10867 38.957090999999991, -123.108852 38.957246, -123.109121 38.957532, -123.109429 38.957837999999995, -123.11003099999999 38.958507999999995, -123.111364 38.959935, -123.111863 38.96047699999999, -123.112855 38.96152399999999, -123.11305 38.961736999999985, -123.11343600000001 38.962250999999995, -123.113589 38.96246399999999, -123.11388400000001 38.962958, -123.114154 38.963468999999996, -123.114545 38.964483999999992, -123.114895 38.96542, -123.114957 38.965585999999995, -123.115265 38.96644899999999, -123.115693 38.967596, -123.115814 38.967945, -123.115972 38.968354999999995, -123.116279 38.969280999999995, -123.11628400000001 38.969301999999992, -123.116445 38.970036, -123.116458 38.970136, -123.116525 38.970372999999995, -123.116565 38.97066199999999, -123.116586 38.970992999999986, -123.11659 38.97124199999999, -123.116603 38.971516, -123.116622 38.972121999999992, -123.116623 38.972312999999986, -123.116593 38.972552, -123.116534 38.97275299999999, -123.11638 38.973096999999996, -123.116163 38.973569999999995, -123.116074 38.973742, -123.116022 38.973841, -123.11553100000002 38.974845999999985, -123.115402 38.975087999999985, -123.114942 38.976023999999995, -123.114847 38.97626799999999, -123.114762 38.97658899999999, -123.114745 38.976684999999996, -123.11469500000001 38.976953999999992, -123.114664 38.97720799999999, -123.114669 38.977368999999996, -123.114729 38.977648999999985, -123.114862 38.977972, -123.115041 38.978312999999986, -123.115187 38.978549999999991, -123.11598400000001 38.97966199999999, -123.116162 38.97992099999999, -123.116234 38.980024, -123.11640200000001 38.980382999999996, -123.116535 38.98072299999999, -123.116638 38.981034, -123.116714 38.981305999999989, -123.11692 38.98236399999999, -123.116954 38.982555999999995, -123.116971 38.982653, -123.11748399999999 38.984978, -123.117856 38.986715, -123.118271 38.989011, -123.11854 38.99093, -123.118683 38.99238, -123.11871099999999 38.992743999999995, -123.118706 38.993036, -123.118637 38.993721, -123.118484 38.994505, -123.11821499999999 38.995304, -123.118049 38.995843, -123.11779 38.996792, -123.117713 38.997261, -123.11771 38.997285999999995, -123.117666 38.997659, -123.117643 38.997932, -123.11762999999999 38.99836, -123.117658 38.998872999999996, -123.11771999999999 38.999317, -123.117749 38.999486, -123.117867 39.000177999999991, -123.117887 39.000281, -123.117928 39.000419, -123.11808500000001 39.000833, -123.11812000000002 39.001001999999986, -123.118142 39.001142, -123.118181 39.00124499999999, -123.118369 39.001568999999996, -123.11849300000002 39.001875999999996, -123.11857200000001 39.00204, -123.118682 39.002182999999995, -123.118852 39.002504999999985, -123.119095 39.002845, -123.119174 39.002969999999991, -123.119518 39.003440999999995, -123.119693 39.003730999999995, -123.119913 39.004013, -123.120047 39.004219999999989, -123.120405 39.004734999999989, -123.120722 39.005152999999986, -123.121471 39.00621499999999, -123.12155400000002 39.006374999999991, -123.121641 39.006510999999996, -123.12194000000001 39.006861, -123.122197 39.007244, -123.12237600000002 39.007483, -123.122564 39.00776599999999, -123.123074 39.008448, -123.123824 39.009526999999991, -123.123998 39.009727999999996, -123.12432 39.010159999999992, -123.124738 39.010777999999995, -123.125009 39.011110999999985, -123.12511 39.011252999999996, -123.125257 39.011435999999996, -123.125592 39.011825, -123.1259 39.012208, -123.126015 39.012392999999996, -123.126314 39.012721999999989, -123.126484 39.012868999999995, -123.126681 39.013058, -123.126777 39.013170999999986, -123.127398 39.013891999999991, -123.127837 39.014430999999995, -123.128717 39.015457999999995, -123.130523 39.017632999999996, -123.131886 39.019259999999996, -123.133288 39.020948, -123.13349099999999 39.02117, -123.133547 39.021229999999996, -123.133563 39.021246999999995, -123.135295 39.023283, -123.135591 39.023641999999995, -123.137434 39.025855, -123.138267 39.026841, -123.138372 39.026969, -123.138655 39.027315, -123.139353 39.02820899999999, -123.140131 39.029336999999991, -123.140262 39.02955, -123.14164600000001 39.031650999999989, -123.14452 39.03590299999999, -123.145242 39.036943, -123.145833 39.037801, -123.147907 39.040788, -123.149676 39.043364, -123.150462 39.044498, -123.152545 39.047523999999996, -123.15258299999999 39.047578, -123.153638 39.049109, -123.156585 39.05336299999999, -123.157055 39.054023999999991, -123.157435 39.054603, -123.158511 39.056115999999996, -123.158823 39.056588999999995, -123.158864 39.056709999999995, -123.158942 39.056838999999989, -123.15928800000002 39.057528999999995, -123.15967500000001 39.058203999999996, -123.16044800000002 39.05936899999999, -123.160798 39.059857999999991, -123.162031 39.061658999999992, -123.162413 39.062173999999992, -123.162818 39.062677, -123.163163 39.06306, -123.163425 39.06331, -123.163528 39.063393999999995, -123.1641 39.063943999999985, -123.16568000000001 39.065379, -123.16604000000001 39.065695999999996, -123.16617 39.065816999999996, -123.16688800000001 39.066486999999995, -123.16730500000001 39.066901999999992, -123.167599 39.067229999999995, -123.167853 39.067585, -123.168124 39.06802299999999, -123.168924 39.069489999999995, -123.169489 39.070296999999989, -123.169624 39.070457999999995, -123.169776 39.07062899999999, -123.170191 39.071039999999996, -123.170392 39.07121999999999, -123.17074000000001 39.071512999999996, -123.172276 39.072725999999996, -123.173465 39.073676999999996, -123.174359 39.074383999999995, -123.175508 39.075282, -123.176104 39.075773999999996, -123.17741 39.076788, -123.17793100000002 39.077263999999985, -123.17846000000002 39.07782499999999, -123.178763 39.07819099999999, -123.179179 39.078775, -123.17938200000002 39.079104999999991, -123.179573 39.079439999999991, -123.18057 39.081265999999985, -123.18059199999999 39.081309999999995, -123.180633 39.081385999999995, -123.181648 39.083207, -123.181913 39.083704999999995, -123.182189 39.084182, -123.182527 39.084793999999995, -123.183104 39.085879999999996, -123.183393 39.086386999999995, -123.183519 39.086607999999991, -123.183883 39.08726, -123.184203 39.08782, -123.18448000000001 39.08834199999999, -123.185759 39.090658999999995, -123.18660300000002 39.092151999999992, -123.187138 39.093142, -123.187442 39.093675999999995, -123.188081 39.094865999999996, -123.18916000000002 39.096809999999991, -123.18948400000001 39.097394, -123.19000100000001 39.098244, -123.19081200000001 39.099578999999991, -123.191191 39.100141999999991, -123.19123 39.100197999999992, -123.19266400000001 39.102264, -123.19310999999999 39.10293, -123.193239 39.103138, -123.193524 39.103625, -123.193743 39.104093, -123.193965 39.104590999999992, -123.194128 39.104995999999986, -123.19428400000001 39.105426999999992, -123.194387 39.105769999999985, -123.194519 39.10621299999999, -123.194554 39.106349, -123.194671 39.106923999999985, -123.194853 39.108043999999985, -123.195017 39.109203, -123.195021 39.109238999999995, -123.195139 39.109922999999995, -123.19519 39.110279999999996, -123.195354 39.111432, -123.195369 39.111539, -123.19546700000001 39.112184, -123.195563 39.112845, -123.195726 39.113974999999996, -123.195824 39.114688999999991, -123.195866 39.115277999999996, -123.195847 39.115709999999986, -123.195759 39.11636, -123.1956 39.116973, -123.195463 39.117391999999995, -123.195433 39.117484999999995, -123.195375 39.117683, -123.195307 39.117883999999989, -123.19518 39.118254, -123.194894 39.119147, -123.194704 39.119786999999995, -123.19463999999999 39.120017, -123.194551 39.120453999999995, -123.194513 39.120996999999996, -123.194515 39.121112, -123.194522 39.121569, -123.194563 39.122147999999996, -123.19494 39.127150999999991, -123.195013 39.128327999999996, -123.195081 39.129201999999985, -123.195205 39.130776999999995, -123.195325 39.132455, -123.19533799999999 39.132628, -123.195334 39.132691999999992, -123.195399 39.133572999999991, -123.195507 39.135034999999995, -123.19555000000001 39.135616999999996, -123.19556800000001 39.135943999999995, -123.195612 39.136434999999992, -123.19561399999999 39.13646099999999, -123.195626 39.136717999999995, -123.19565 39.13698, -123.195713 39.137807999999985, -123.195714 39.137833999999991, -123.19572099999999 39.138054, -123.195739 39.138306, -123.195966 39.141186999999995, -123.196027 39.142148999999996, -123.196137 39.143401, -123.196181 39.144044, -123.196202 39.144346999999996, -123.196237 39.144867, -123.196251 39.145161, -123.196266 39.145479, -123.19640000000001 39.147269, -123.196472 39.148161, -123.19648400000001 39.148313999999992, -123.196521 39.148843, -123.196612 39.150197, -123.196661 39.150815, -123.196692 39.151207, -123.196717 39.151582999999995, -123.196766 39.152257999999996, -123.196781 39.152473, -123.196793 39.15257299999999, -123.196872 39.153218999999986, -123.196994 39.15390699999999, -123.19778800000002 39.157672999999996, -123.197989 39.15864599999999, -123.198088 39.159165999999992, -123.198138 39.159386, -123.198181 39.15957199999999, -123.198261 39.159922, -123.198465 39.160578999999991, -123.198741 39.16129, -123.198851 39.161529, -123.19894800000002 39.16173899999999, -123.19897600000002 39.161798999999995, -123.199198 39.162210999999992, -123.199397 39.162534, -123.199922 39.16330099999999, -123.20036500000002 39.163854, -123.200759 39.164293, -123.20112000000002 39.164654999999996, -123.201793 39.165267, -123.202358 39.165714, -123.202975 39.166140999999996, -123.20333700000002 39.16637399999999, -123.20504800000002 39.167417999999991, -123.20522199999999 39.167524, -123.205272 39.167553999999996, -123.205532 39.167704999999991, -123.206308 39.168163, -123.206722 39.168411999999996, -123.20689899999999 39.168518, -123.207768 39.169052, -123.210488 39.170679, -123.210856 39.17091, -123.211135 39.171116, -123.211383 39.171321999999996, -123.21151 39.171428999999996, -123.211743 39.171630999999991, -123.211926 39.171828999999995, -123.21221500000001 39.172243999999992, -123.21234 39.172443999999992, -123.212461 39.172677, -123.212575 39.172965999999995, -123.212641 39.173183, -123.21268 39.17338, -123.21272100000002 39.173583, -123.21275000000001 39.173881, -123.21275100000001 39.174203999999996, -123.21274100000001 39.174368, -123.21273800000002 39.174390999999986, -123.21265700000001 39.175145999999991, -123.212632 39.175373999999991, -123.212558 39.176158, -123.212525 39.176538, -123.212464 39.177116, -123.212434 39.177406999999995, -123.212397 39.177776999999992, -123.212374 39.178021, -123.21228400000001 39.178869999999996, -123.212172 39.180043999999995, -123.212165 39.180118, -123.212098 39.180649999999986, -123.211992 39.18168, -123.211913 39.182465999999991, -123.21182200000001 39.183251999999996, -123.21173 39.183931, -123.21146000000002 39.18545799999999, -123.211453 39.185507, -123.21143 39.18566899999999, -123.211137 39.187366999999995, -123.21107599999999 39.187659, -123.210977 39.188221999999996, -123.210436 39.191334, -123.21010400000002 39.193245999999995, -123.209991 39.19389799999999, -123.20998 39.193956, -123.209903 39.194376999999996, -123.209764 39.195212999999995, -123.20974900000002 39.195304, -123.20974700000001 39.195318, -123.209467 39.196902, -123.209341 39.197620999999991, -123.209316 39.19776199999999, -123.209171 39.198402999999992, -123.209125 39.198543999999991, -123.208941 39.199106, -123.208864 39.199301, -123.208137 39.201136, -123.207178 39.203551, -123.205651 39.207395999999996, -123.205525 39.207730999999995, -123.205377 39.208082999999995, -123.204586 39.210088999999996, -123.204487 39.21041799999999, -123.204403 39.210822999999991, -123.204373 39.211072, -123.204345 39.211427999999991, -123.204346 39.211787, -123.204391 39.21217699999999, -123.20443 39.212417999999985, -123.20455000000001 39.212877999999996, -123.204681 39.213256, -123.20507500000001 39.214249999999986, -123.205186 39.214556999999992, -123.206151 39.217016999999991, -123.20628200000002 39.217331999999992, -123.206455 39.2179, -123.206531 39.21820499999999, -123.20660500000001 39.218595, -123.20669600000001 39.219471999999996, -123.206753 39.220081, -123.206951 39.222181999999989, -123.207063 39.223375, -123.20714 39.224007, -123.207167 39.22437, -123.207206 39.224711, -123.207209 39.224739, -123.207241 39.225145999999995, -123.207291 39.225581, -123.20737 39.226362999999992, -123.207648 39.229112999999991, -123.207728 39.229939, -123.207835 39.231035999999996, -123.207862 39.231314, -123.207914 39.231854, -123.208028 39.233039, -123.20806999999999 39.233415, -123.208097 39.233782, -123.20809 39.234294999999996, -123.208063 39.234727, -123.207864 39.236207, -123.207792 39.236691, -123.207772 39.236820999999992, -123.207755 39.236934999999995, -123.207436 39.239004, -123.207392 39.239291999999992, -123.207317 39.239857, -123.207306 39.239947999999991, -123.207252 39.240262999999992, -123.20711100000001 39.241230999999992, -123.20709000000001 39.241433, -123.207072 39.24194399999999, -123.207088 39.242571999999996, -123.20710500000001 39.242736999999991, -123.20718200000002 39.24322999999999, -123.207246 39.24351699999999, -123.207354 39.243898999999992, -123.20752900000001 39.24436699999999, -123.207754 39.244869999999992, -123.208059 39.24543899999999, -123.20920900000002 39.247523999999991, -123.209416 39.2479, -123.209529 39.248111, -123.209625 39.248284, -123.209787 39.248546, -123.21090000000001 39.250260999999995, -123.211107 39.250633, -123.211284 39.250921, -123.212386 39.252911, -123.212707 39.253437, -123.212983 39.25383, -123.213116 39.254003999999995, -123.21343999999999 39.254388999999996, -123.213458 39.254411, -123.213559 39.254529, -123.213892 39.254901, -123.214202 39.25521, -123.21439 39.255387999999996, -123.214551 39.255534999999995, -123.214939 39.255891999999996, -123.21503799999999 39.255970999999995, -123.215278 39.256164999999996, -123.215645 39.256446, -123.216057 39.256738999999996, -123.216229 39.256853, -123.21651800000001 39.257042999999996, -123.21787499999999 39.257892, -123.218251 39.258114, -123.21886599999999 39.258497, -123.219153 39.258675, -123.219841 39.259147, -123.22026199999999 39.25949, -123.220525 39.259716, -123.22271 39.26191, -123.223148 39.26228, -123.223647 39.26266, -123.22423 39.263051999999995, -123.224508 39.263216, -123.225093 39.263531, -123.225387 39.263667999999996, -123.225729 39.263812, -123.226794 39.264222, -123.229258 39.265135, -123.229777 39.26536, -123.230197 39.26556699999999, -123.230859 39.265938999999996, -123.232662 39.267046999999991, -123.23384 39.267789999999991, -123.234098 39.267950999999989, -123.23418800000002 39.268003, -123.234267 39.268048, -123.234327 39.26808299999999, -123.234487 39.268166999999991, -123.234741 39.268301999999991, -123.235006 39.268436, -123.235575 39.268693999999996, -123.23587900000001 39.268822, -123.235965 39.268854999999995, -123.236452 39.269044999999991, -123.23668 39.269128999999985, -123.237311 39.269362, -123.23856599999999 39.269740999999996, -123.24124499999999 39.270534, -123.24145899999999 39.270598, -123.242456 39.270886, -123.243047 39.271062999999991, -123.245196 39.271703999999986, -123.247101 39.272289999999991, -123.247729 39.27250999999999, -123.248319 39.27274899999999, -123.24915200000001 39.273116999999992, -123.249705 39.27340199999999, -123.250177 39.273644999999995, -123.250919 39.274065999999991, -123.25116700000001 39.274195999999996, -123.25210900000002 39.274715, -123.253132 39.275269999999992, -123.253902 39.275680999999992, -123.254823 39.276209999999992, -123.256353 39.277042, -123.256511 39.277132999999992, -123.25681100000001 39.277336999999996, -123.257109 39.277568999999986, -123.25732 39.277763, -123.25754500000001 39.278007999999986, -123.257795 39.278332999999996, -123.257978 39.27864499999999, -123.258117 39.278936, -123.258236 39.279268999999985, -123.25844800000002 39.280335, -123.25858200000002 39.28090199999999, -123.258746 39.281418999999985, -123.258898 39.281818999999992, -123.259045 39.282136, -123.259184 39.282392999999992, -123.25931500000002 39.282596999999996, -123.259613 39.282966999999985, -123.26025700000001 39.283619999999992, -123.26102399999999 39.284383, -123.261534 39.284834, -123.261775 39.285095, -123.262054 39.285439, -123.262354 39.285837, -123.262887 39.286581999999996, -123.263363 39.287226, -123.263936 39.287957, -123.26418 39.288233999999996, -123.264288 39.28835, -123.264599 39.288633, -123.264843 39.288838, -123.265232 39.289131999999995, -123.265639 39.28939, -123.265979 39.289575, -123.266672 39.289918, -123.267943 39.290566999999996, -123.268553 39.290869, -123.26875799999999 39.290980999999995, -123.269095 39.291196, -123.270788 39.292459, -123.270993 39.292603, -123.271425 39.292871999999996, -123.271761 39.293046, -123.271961 39.293136, -123.272334 39.293271, -123.27266900000001 39.293382, -123.27308099999999 39.293476, -123.27372 39.293574, -123.274618 39.293665, -123.274822 39.293678, -123.275034 39.293701, -123.27551 39.293783, -123.27588399999999 39.293869, -123.276353 39.294042999999995, -123.276601 39.294153, -123.276624 39.294157999999996, -123.27678399999999 39.294236, -123.277118 39.294436999999995, -123.277529 39.294748999999996, -123.277953 39.295138, -123.278254 39.295442, -123.278482 39.295687, -123.2787 39.295905, -123.278864 39.296042, -123.279222 39.296261, -123.2797 39.296475, -123.279972 39.296558999999995, -123.280639 39.29673, -123.28183 39.296997, -123.28220999999999 39.297093, -123.282585 39.297225999999995, -123.282772 39.297314, -123.283135 39.297516, -123.283511 39.297754, -123.284319 39.298266999999996, -123.28616 39.299389999999995, -123.286419 39.299526, -123.286635 39.299613, -123.287129 39.299762, -123.287335 39.299807, -123.288381 39.299996, -123.289163 39.300125, -123.289545 39.300160999999996, -123.289602 39.300163999999995, -123.28973 39.300170999999992, -123.29021900000001 39.300156999999992, -123.291584 39.299965999999991, -123.29189300000002 39.299945, -123.292111 39.299943999999996, -123.292481 39.299981999999986, -123.29283 39.300049, -123.293357 39.300204999999991, -123.294309 39.300519, -123.29469 39.300654, -123.29493700000002 39.30075, -123.295227 39.300888999999991, -123.295558 39.301071999999991, -123.29582 39.301234, -123.29609500000001 39.301428, -123.296411 39.301670999999992, -123.298698 39.303798, -123.299227 39.304324, -123.29952800000001 39.304726999999986, -123.299664 39.304953999999995, -123.299791 39.305188, -123.300375 39.30645899999999, -123.30046800000001 39.306699999999985, -123.300502 39.306815999999991, -123.300531 39.306998, -123.300542 39.307162999999996, -123.300531 39.307379999999995, -123.30049300000002 39.307566, -123.30042600000002 39.307735999999991, -123.300279 39.308016999999985, -123.29969300000002 39.308980999999996, -123.299547 39.309221999999991, -123.299337 39.309539, -123.299021 39.310045999999986, -123.298727 39.310451999999991, -123.298518 39.310687, -123.298172 39.311046999999995, -123.297836 39.311379999999986, -123.297536 39.311710999999995, -123.29741 39.311896999999995, -123.29727 39.312166999999995, -123.297196 39.312376, -123.297138 39.312628, -123.29712 39.312898, -123.297153 39.313241, -123.297213 39.313483, -123.29727199999999 39.313638999999995, -123.29739000000001 39.313843999999996, -123.297423 39.313888999999996, -123.297462 39.313942, -123.2976 39.314122, -123.297696 39.314231, -123.297928 39.314431, -123.29818399999999 39.314609, -123.298414 39.31474, -123.298784 39.314915, -123.298967 39.314997999999996, -123.299574 39.315295, -123.300012 39.315487999999995, -123.300454 39.31567, -123.300658 39.315763, -123.300782 39.315830999999996, -123.300974 39.315961, -123.301093 39.316054, -123.30121199999999 39.31617, -123.30130199999999 39.316274, -123.301451 39.316503, -123.301541 39.316683, -123.30161799999999 39.316986, -123.30163999999999 39.317253, -123.301623 39.317519, -123.301581 39.317727999999995, -123.30090799999999 39.320066, -123.30085199999999 39.320321, -123.300794 39.32065, -123.30077 39.321056, -123.300784 39.321517, -123.300899 39.322317999999996, -123.301023 39.32307, -123.301139 39.32396, -123.301308 39.325125, -123.301355 39.325548999999995, -123.301412 39.325871, -123.301493 39.326207, -123.30165099999999 39.326737, -123.30192199999999 39.327739, -123.302058 39.328157999999995, -123.302197 39.328644, -123.302264 39.328844, -123.302386 39.32909, -123.302448 39.329192, -123.302611 39.3294, -123.302851 39.329609999999995, -123.303614 39.330141, -123.303958 39.330402, -123.304097 39.330551, -123.304208 39.330734, -123.304345 39.331016, -123.304379 39.331108, -123.304441 39.331379999999996, -123.30444 39.331674, -123.304428 39.331759999999996, -123.304271 39.332288999999996, -123.303951 39.333307999999995, -123.303904 39.333528, -123.30389199999999 39.333675, -123.303894 39.333822, -123.30393 39.334047, -123.304 39.334278999999995, -123.304053 39.334405, -123.304107 39.334534999999995, -123.304188 39.334665, -123.304323 39.334834, -123.304445 39.334955, -123.304746 39.335186, -123.304892 39.335271999999996, -123.305111 39.335386, -123.306416 39.335992, -123.308548 39.336979, -123.310563 39.337902, -123.311303 39.338254, -123.31173799999999 39.33848, -123.312044 39.338673, -123.312377 39.338918, -123.31240700000001 39.33894, -123.312598 39.339093999999996, -123.313099 39.33954, -123.313525 39.339954999999996, -123.314347 39.340745, -123.315377 39.341724, -123.317312 39.343554999999995, -123.319219 39.345386, -123.320757 39.346831, -123.321031 39.347099, -123.321242 39.347328999999995, -123.321537 39.347718, -123.321706 39.347978, -123.321804 39.34816, -123.32192599999999 39.348428999999996, -123.32206099999999 39.348794, -123.322194 39.349308, -123.322251 39.349703999999996, -123.322261 39.350096, -123.322235 39.350457, -123.322143 39.350954, -123.322046 39.351296999999995, -123.322021 39.351358999999995, -123.321859 39.351732, -123.321706 39.352005, -123.32067 39.353639, -123.319739 39.355056, -123.319467 39.355454, -123.31912299999999 39.356021, -123.31768600000001 39.358281999999996, -123.317402 39.358702999999991, -123.31705000000001 39.359252999999995, -123.316433 39.360253, -123.316224 39.360572999999995, -123.315875 39.361154999999989, -123.315624 39.36164999999999, -123.31542300000001 39.362169999999992, -123.315296 39.362594999999992, -123.31521 39.362949999999991, -123.315152 39.363345999999986, -123.315121 39.363852999999992, -123.315142 39.364238, -123.315182 39.364589999999986, -123.315269 39.36506099999999, -123.315375 39.36543799999999, -123.31551 39.365836999999992, -123.315653 39.366140999999992, -123.315816 39.366441999999985, -123.315982 39.366706, -123.31630600000001 39.36713499999999, -123.31645 39.36731799999999, -123.31679700000001 39.367699999999985, -123.317078 39.367986999999985, -123.318692 39.369527999999995, -123.31942 39.370191999999996, -123.32026 39.37104, -123.321655 39.372355999999996, -123.322081 39.372754999999991, -123.322573 39.373184999999985, -123.323899 39.374209999999991, -123.32402800000001 39.374296999999991, -123.324281 39.37448599999999, -123.324538 39.374656, -123.325009 39.37492799999999, -123.325465 39.375161, -123.326018 39.37537, -123.326381 39.375474, -123.326883 39.375596, -123.327316 39.375668, -123.327597 39.375696, -123.32782 39.375709, -123.328283 39.375709, -123.32919 39.375648999999996, -123.329678 39.375645, -123.329745 39.375626)', 4326)
set @pointbox = geometry::STGeomFromText('POLYGON((-123.097966 38.918219, -123.058499 38.918219, -123.058499 38.952692, -123.097966 38.952692, -123.097966 38.918219))', 4326)

select @point1.STBuffer(0.001)
UNION ALL
select @point2.STBuffer(0.001)
UNION ALL
select @line as line

-- uncomment this to see the intersecting box
--UNION ALL
--select @pointbox

-- this only gets a subset of the line
--UNION ALL
--select @pointbox.STIntersection(@line)

I have a source LINESTRING, and two points that intersect it somewhere. I would like to find and return only the part of the LINESTRING between those two points. Originally I thought I could do something like finding the minimum box containing those two points, then do the intersection of that, but as shown in the example above that doesn't take into account where the line goes beyond the limits of the box.

I've think I've seen that PostGIS has some sort of line substring function, but haven't been able to find the same for SQL Server. If it helps, I'm working in .NET as well and so if there's something in the SqlGeometry classes in C# I could work with that, too. Is there some sort of known algorithm I could follow to find what I'm looking for?

Improve this question
1

1 Answer 1

Reset to default
1

After some more thought and digging I think I've come up with a workable process for this that I wanted to share. I ended up walking my original linestring sublinestring by sublinestring, and testing if my points intersect that. Then I build a new linestring starting with my first point, adding each subsequent point along the original linestring, and ending with my second point. Here's the SQL implementation; I coded it in .NET, too, adding handling to determine which of my two points is "first" so that the order is correct. 

declare @point1 geometry, @point2 geometry, @line geometry, @pointbox geometry

set @point1 = geometry::STGeomFromText('POINT (-123.058499 38.918219)', 4326)
set @point2 = geometry::STGeomFromText('POINT (-123.097966 38.952692)', 4326)

set @line = geometry::STGeomFromText('LINESTRING (-123.057896 38.916655, -123.058085 38.916981, -123.058191 38.917187, -123.05834 38.917549, -123.058439 38.917878999999992, -123.05851100000001 38.918196999999992, -123.05852900000001 38.918371999999991, -123.05853 38.919007, -123.058507 38.919596999999996, -123.05840500000001 38.920535, -123.058318 38.921137, -123.058271 38.92132999999999, -123.058027 38.921942, -123.057883 38.922229999999992, -123.05775200000001 38.922447999999996, -123.057574 38.92269799999999, -123.05721100000001 38.923138999999992, -123.05684500000001 38.923541999999991, -123.055985 38.924552999999996, -123.055856 38.92472999999999, -123.055779 38.924872999999991, -123.05567700000002 38.925111999999991, -123.05563000000001 38.925221999999991, -123.05552800000001 38.925550999999992, -123.05545000000001 38.926024999999996, -123.055461 38.926546999999985, -123.05548 38.92671099999999, -123.05550900000001 38.926845, -123.05567100000002 38.927299999999995, -123.05605200000001 38.92789599999999, -123.060857 38.931855999999996, -123.061912 38.932839999999992, -123.062141 38.93324299999999, -123.062585 38.933886, -123.063613 38.93517099999999, -123.06504800000002 38.936489999999992, -123.065573 38.936972, -123.065974 38.93731, -123.066633 38.937771, -123.066931 38.937944, -123.06922399999999 38.939147999999996, -123.07014 38.93965, -123.070645 38.939865, -123.070882 38.939947, -123.071358 38.94005, -123.071444 38.940065, -123.071697 38.940104999999996, -123.07209399999999 38.940138999999995, -123.072876 38.940146, -123.073149 38.940141, -123.073685 38.940079, -123.07417 38.940056, -123.074686 38.940058, -123.074892 38.940062999999995, -123.075349 38.940107, -123.076005 38.940239999999996, -123.076525 38.940374, -123.07682299999999 38.94048, -123.077213 38.940638, -123.07753 38.940777, -123.077879 38.940945, -123.078031 38.941026, -123.07826 38.941165, -123.07865 38.941432, -123.078824 38.941565, -123.079113 38.94182, -123.079278 38.941983, -123.079427 38.942152, -123.079645 38.942429, -123.079742 38.942569999999996, -123.07998 38.942972999999995, -123.08018 38.943438, -123.080329 38.943884, -123.080498 38.944339, -123.080647 38.944651, -123.081219 38.945661, -123.081721 38.946442999999995, -123.081845 38.946622999999995, -123.082146 38.947009, -123.082752 38.947645, -123.083501 38.948364999999995, -123.08372299999999 38.948508, -123.083863 38.948589999999996, -123.084358 38.948847, -123.084593 38.948958, -123.08497299999999 38.949104, -123.087031 38.949740999999996, -123.088145 38.950072, -123.091341 38.951045, -123.092277 38.951304, -123.092667 38.9514, -123.093276 38.951526, -123.09485699999999 38.951826, -123.095502 38.951966999999996, -123.09664000000001 38.952273, -123.097878 38.952662, -123.098431 38.952836999999995, -123.101246 38.953711, -123.101383 38.953753999999996, -123.101519 38.953796, -123.101555 38.953806999999991, -123.102837 38.954225, -123.103544 38.954465999999989, -123.104761 38.954896999999995, -123.10485200000001 38.954937999999991, -123.105327 38.955131999999992, -123.10562200000001 38.955252999999992, -123.106221 38.955486, -123.106644 38.955678999999996, -123.10699000000001 38.95585899999999, -123.107292 38.956034999999986, -123.10745500000002 38.956130999999992, -123.108089 38.956580999999986, -123.10867 38.957090999999991, -123.108852 38.957246, -123.109121 38.957532, -123.109429 38.957837999999995, -123.11003099999999 38.958507999999995, -123.111364 38.959935, -123.111863 38.96047699999999, -123.112855 38.96152399999999, -123.11305 38.961736999999985, -123.11343600000001 38.962250999999995, -123.113589 38.96246399999999, -123.11388400000001 38.962958, -123.114154 38.963468999999996, -123.114545 38.964483999999992, -123.114895 38.96542, -123.114957 38.965585999999995, -123.115265 38.96644899999999, -123.115693 38.967596, -123.115814 38.967945, -123.115972 38.968354999999995, -123.116279 38.969280999999995, -123.11628400000001 38.969301999999992, -123.116445 38.970036, -123.116458 38.970136, -123.116525 38.970372999999995, -123.116565 38.97066199999999, -123.116586 38.970992999999986, -123.11659 38.97124199999999, -123.116603 38.971516, -123.116622 38.972121999999992, -123.116623 38.972312999999986, -123.116593 38.972552, -123.116534 38.97275299999999, -123.11638 38.973096999999996, -123.116163 38.973569999999995, -123.116074 38.973742, -123.116022 38.973841, -123.11553100000002 38.974845999999985, -123.115402 38.975087999999985, -123.114942 38.976023999999995, -123.114847 38.97626799999999, -123.114762 38.97658899999999, -123.114745 38.976684999999996, -123.11469500000001 38.976953999999992, -123.114664 38.97720799999999, -123.114669 38.977368999999996, -123.114729 38.977648999999985, -123.114862 38.977972, -123.115041 38.978312999999986, -123.115187 38.978549999999991, -123.11598400000001 38.97966199999999, -123.116162 38.97992099999999, -123.116234 38.980024, -123.11640200000001 38.980382999999996, -123.116535 38.98072299999999, -123.116638 38.981034, -123.116714 38.981305999999989, -123.11692 38.98236399999999, -123.116954 38.982555999999995, -123.116971 38.982653, -123.11748399999999 38.984978, -123.117856 38.986715, -123.118271 38.989011, -123.11854 38.99093, -123.118683 38.99238, -123.11871099999999 38.992743999999995, -123.118706 38.993036, -123.118637 38.993721, -123.118484 38.994505, -123.11821499999999 38.995304, -123.118049 38.995843, -123.11779 38.996792, -123.117713 38.997261, -123.11771 38.997285999999995, -123.117666 38.997659, -123.117643 38.997932, -123.11762999999999 38.99836, -123.117658 38.998872999999996, -123.11771999999999 38.999317, -123.117749 38.999486, -123.117867 39.000177999999991, -123.117887 39.000281, -123.117928 39.000419, -123.11808500000001 39.000833, -123.11812000000002 39.001001999999986, -123.118142 39.001142, -123.118181 39.00124499999999, -123.118369 39.001568999999996, -123.11849300000002 39.001875999999996, -123.11857200000001 39.00204, -123.118682 39.002182999999995, -123.118852 39.002504999999985, -123.119095 39.002845, -123.119174 39.002969999999991, -123.119518 39.003440999999995, -123.119693 39.003730999999995, -123.119913 39.004013, -123.120047 39.004219999999989, -123.120405 39.004734999999989, -123.120722 39.005152999999986, -123.121471 39.00621499999999, -123.12155400000002 39.006374999999991, -123.121641 39.006510999999996, -123.12194000000001 39.006861, -123.122197 39.007244, -123.12237600000002 39.007483, -123.122564 39.00776599999999, -123.123074 39.008448, -123.123824 39.009526999999991, -123.123998 39.009727999999996, -123.12432 39.010159999999992, -123.124738 39.010777999999995, -123.125009 39.011110999999985, -123.12511 39.011252999999996, -123.125257 39.011435999999996, -123.125592 39.011825, -123.1259 39.012208, -123.126015 39.012392999999996, -123.126314 39.012721999999989, -123.126484 39.012868999999995, -123.126681 39.013058, -123.126777 39.013170999999986, -123.127398 39.013891999999991, -123.127837 39.014430999999995, -123.128717 39.015457999999995, -123.130523 39.017632999999996, -123.131886 39.019259999999996, -123.133288 39.020948, -123.13349099999999 39.02117, -123.133547 39.021229999999996, -123.133563 39.021246999999995, -123.135295 39.023283, -123.135591 39.023641999999995, -123.137434 39.025855, -123.138267 39.026841, -123.138372 39.026969, -123.138655 39.027315, -123.139353 39.02820899999999, -123.140131 39.029336999999991, -123.140262 39.02955, -123.14164600000001 39.031650999999989, -123.14452 39.03590299999999, -123.145242 39.036943, -123.145833 39.037801, -123.147907 39.040788, -123.149676 39.043364, -123.150462 39.044498, -123.152545 39.047523999999996, -123.15258299999999 39.047578, -123.153638 39.049109, -123.156585 39.05336299999999, -123.157055 39.054023999999991, -123.157435 39.054603, -123.158511 39.056115999999996, -123.158823 39.056588999999995, -123.158864 39.056709999999995, -123.158942 39.056838999999989, -123.15928800000002 39.057528999999995, -123.15967500000001 39.058203999999996, -123.16044800000002 39.05936899999999, -123.160798 39.059857999999991, -123.162031 39.061658999999992, -123.162413 39.062173999999992, -123.162818 39.062677, -123.163163 39.06306, -123.163425 39.06331, -123.163528 39.063393999999995, -123.1641 39.063943999999985, -123.16568000000001 39.065379, -123.16604000000001 39.065695999999996, -123.16617 39.065816999999996, -123.16688800000001 39.066486999999995, -123.16730500000001 39.066901999999992, -123.167599 39.067229999999995, -123.167853 39.067585, -123.168124 39.06802299999999, -123.168924 39.069489999999995, -123.169489 39.070296999999989, -123.169624 39.070457999999995, -123.169776 39.07062899999999, -123.170191 39.071039999999996, -123.170392 39.07121999999999, -123.17074000000001 39.071512999999996, -123.172276 39.072725999999996, -123.173465 39.073676999999996, -123.174359 39.074383999999995, -123.175508 39.075282, -123.176104 39.075773999999996, -123.17741 39.076788, -123.17793100000002 39.077263999999985, -123.17846000000002 39.07782499999999, -123.178763 39.07819099999999, -123.179179 39.078775, -123.17938200000002 39.079104999999991, -123.179573 39.079439999999991, -123.18057 39.081265999999985, -123.18059199999999 39.081309999999995, -123.180633 39.081385999999995, -123.181648 39.083207, -123.181913 39.083704999999995, -123.182189 39.084182, -123.182527 39.084793999999995, -123.183104 39.085879999999996, -123.183393 39.086386999999995, -123.183519 39.086607999999991, -123.183883 39.08726, -123.184203 39.08782, -123.18448000000001 39.08834199999999, -123.185759 39.090658999999995, -123.18660300000002 39.092151999999992, -123.187138 39.093142, -123.187442 39.093675999999995, -123.188081 39.094865999999996, -123.18916000000002 39.096809999999991, -123.18948400000001 39.097394, -123.19000100000001 39.098244, -123.19081200000001 39.099578999999991, -123.191191 39.100141999999991, -123.19123 39.100197999999992, -123.19266400000001 39.102264, -123.19310999999999 39.10293, -123.193239 39.103138, -123.193524 39.103625, -123.193743 39.104093, -123.193965 39.104590999999992, -123.194128 39.104995999999986, -123.19428400000001 39.105426999999992, -123.194387 39.105769999999985, -123.194519 39.10621299999999, -123.194554 39.106349, -123.194671 39.106923999999985, -123.194853 39.108043999999985, -123.195017 39.109203, -123.195021 39.109238999999995, -123.195139 39.109922999999995, -123.19519 39.110279999999996, -123.195354 39.111432, -123.195369 39.111539, -123.19546700000001 39.112184, -123.195563 39.112845, -123.195726 39.113974999999996, -123.195824 39.114688999999991, -123.195866 39.115277999999996, -123.195847 39.115709999999986, -123.195759 39.11636, -123.1956 39.116973, -123.195463 39.117391999999995, -123.195433 39.117484999999995, -123.195375 39.117683, -123.195307 39.117883999999989, -123.19518 39.118254, -123.194894 39.119147, -123.194704 39.119786999999995, -123.19463999999999 39.120017, -123.194551 39.120453999999995, -123.194513 39.120996999999996, -123.194515 39.121112, -123.194522 39.121569, -123.194563 39.122147999999996, -123.19494 39.127150999999991, -123.195013 39.128327999999996, -123.195081 39.129201999999985, -123.195205 39.130776999999995, -123.195325 39.132455, -123.19533799999999 39.132628, -123.195334 39.132691999999992, -123.195399 39.133572999999991, -123.195507 39.135034999999995, -123.19555000000001 39.135616999999996, -123.19556800000001 39.135943999999995, -123.195612 39.136434999999992, -123.19561399999999 39.13646099999999, -123.195626 39.136717999999995, -123.19565 39.13698, -123.195713 39.137807999999985, -123.195714 39.137833999999991, -123.19572099999999 39.138054, -123.195739 39.138306, -123.195966 39.141186999999995, -123.196027 39.142148999999996, -123.196137 39.143401, -123.196181 39.144044, -123.196202 39.144346999999996, -123.196237 39.144867, -123.196251 39.145161, -123.196266 39.145479, -123.19640000000001 39.147269, -123.196472 39.148161, -123.19648400000001 39.148313999999992, -123.196521 39.148843, -123.196612 39.150197, -123.196661 39.150815, -123.196692 39.151207, -123.196717 39.151582999999995, -123.196766 39.152257999999996, -123.196781 39.152473, -123.196793 39.15257299999999, -123.196872 39.153218999999986, -123.196994 39.15390699999999, -123.19778800000002 39.157672999999996, -123.197989 39.15864599999999, -123.198088 39.159165999999992, -123.198138 39.159386, -123.198181 39.15957199999999, -123.198261 39.159922, -123.198465 39.160578999999991, -123.198741 39.16129, -123.198851 39.161529, -123.19894800000002 39.16173899999999, -123.19897600000002 39.161798999999995, -123.199198 39.162210999999992, -123.199397 39.162534, -123.199922 39.16330099999999, -123.20036500000002 39.163854, -123.200759 39.164293, -123.20112000000002 39.164654999999996, -123.201793 39.165267, -123.202358 39.165714, -123.202975 39.166140999999996, -123.20333700000002 39.16637399999999, -123.20504800000002 39.167417999999991, -123.20522199999999 39.167524, -123.205272 39.167553999999996, -123.205532 39.167704999999991, -123.206308 39.168163, -123.206722 39.168411999999996, -123.20689899999999 39.168518, -123.207768 39.169052, -123.210488 39.170679, -123.210856 39.17091, -123.211135 39.171116, -123.211383 39.171321999999996, -123.21151 39.171428999999996, -123.211743 39.171630999999991, -123.211926 39.171828999999995, -123.21221500000001 39.172243999999992, -123.21234 39.172443999999992, -123.212461 39.172677, -123.212575 39.172965999999995, -123.212641 39.173183, -123.21268 39.17338, -123.21272100000002 39.173583, -123.21275000000001 39.173881, -123.21275100000001 39.174203999999996, -123.21274100000001 39.174368, -123.21273800000002 39.174390999999986, -123.21265700000001 39.175145999999991, -123.212632 39.175373999999991, -123.212558 39.176158, -123.212525 39.176538, -123.212464 39.177116, -123.212434 39.177406999999995, -123.212397 39.177776999999992, -123.212374 39.178021, -123.21228400000001 39.178869999999996, -123.212172 39.180043999999995, -123.212165 39.180118, -123.212098 39.180649999999986, -123.211992 39.18168, -123.211913 39.182465999999991, -123.21182200000001 39.183251999999996, -123.21173 39.183931, -123.21146000000002 39.18545799999999, -123.211453 39.185507, -123.21143 39.18566899999999, -123.211137 39.187366999999995, -123.21107599999999 39.187659, -123.210977 39.188221999999996, -123.210436 39.191334, -123.21010400000002 39.193245999999995, -123.209991 39.19389799999999, -123.20998 39.193956, -123.209903 39.194376999999996, -123.209764 39.195212999999995, -123.20974900000002 39.195304, -123.20974700000001 39.195318, -123.209467 39.196902, -123.209341 39.197620999999991, -123.209316 39.19776199999999, -123.209171 39.198402999999992, -123.209125 39.198543999999991, -123.208941 39.199106, -123.208864 39.199301, -123.208137 39.201136, -123.207178 39.203551, -123.205651 39.207395999999996, -123.205525 39.207730999999995, -123.205377 39.208082999999995, -123.204586 39.210088999999996, -123.204487 39.21041799999999, -123.204403 39.210822999999991, -123.204373 39.211072, -123.204345 39.211427999999991, -123.204346 39.211787, -123.204391 39.21217699999999, -123.20443 39.212417999999985, -123.20455000000001 39.212877999999996, -123.204681 39.213256, -123.20507500000001 39.214249999999986, -123.205186 39.214556999999992, -123.206151 39.217016999999991, -123.20628200000002 39.217331999999992, -123.206455 39.2179, -123.206531 39.21820499999999, -123.20660500000001 39.218595, -123.20669600000001 39.219471999999996, -123.206753 39.220081, -123.206951 39.222181999999989, -123.207063 39.223375, -123.20714 39.224007, -123.207167 39.22437, -123.207206 39.224711, -123.207209 39.224739, -123.207241 39.225145999999995, -123.207291 39.225581, -123.20737 39.226362999999992, -123.207648 39.229112999999991, -123.207728 39.229939, -123.207835 39.231035999999996, -123.207862 39.231314, -123.207914 39.231854, -123.208028 39.233039, -123.20806999999999 39.233415, -123.208097 39.233782, -123.20809 39.234294999999996, -123.208063 39.234727, -123.207864 39.236207, -123.207792 39.236691, -123.207772 39.236820999999992, -123.207755 39.236934999999995, -123.207436 39.239004, -123.207392 39.239291999999992, -123.207317 39.239857, -123.207306 39.239947999999991, -123.207252 39.240262999999992, -123.20711100000001 39.241230999999992, -123.20709000000001 39.241433, -123.207072 39.24194399999999, -123.207088 39.242571999999996, -123.20710500000001 39.242736999999991, -123.20718200000002 39.24322999999999, -123.207246 39.24351699999999, -123.207354 39.243898999999992, -123.20752900000001 39.24436699999999, -123.207754 39.244869999999992, -123.208059 39.24543899999999, -123.20920900000002 39.247523999999991, -123.209416 39.2479, -123.209529 39.248111, -123.209625 39.248284, -123.209787 39.248546, -123.21090000000001 39.250260999999995, -123.211107 39.250633, -123.211284 39.250921, -123.212386 39.252911, -123.212707 39.253437, -123.212983 39.25383, -123.213116 39.254003999999995, -123.21343999999999 39.254388999999996, -123.213458 39.254411, -123.213559 39.254529, -123.213892 39.254901, -123.214202 39.25521, -123.21439 39.255387999999996, -123.214551 39.255534999999995, -123.214939 39.255891999999996, -123.21503799999999 39.255970999999995, -123.215278 39.256164999999996, -123.215645 39.256446, -123.216057 39.256738999999996, -123.216229 39.256853, -123.21651800000001 39.257042999999996, -123.21787499999999 39.257892, -123.218251 39.258114, -123.21886599999999 39.258497, -123.219153 39.258675, -123.219841 39.259147, -123.22026199999999 39.25949, -123.220525 39.259716, -123.22271 39.26191, -123.223148 39.26228, -123.223647 39.26266, -123.22423 39.263051999999995, -123.224508 39.263216, -123.225093 39.263531, -123.225387 39.263667999999996, -123.225729 39.263812, -123.226794 39.264222, -123.229258 39.265135, -123.229777 39.26536, -123.230197 39.26556699999999, -123.230859 39.265938999999996, -123.232662 39.267046999999991, -123.23384 39.267789999999991, -123.234098 39.267950999999989, -123.23418800000002 39.268003, -123.234267 39.268048, -123.234327 39.26808299999999, -123.234487 39.268166999999991, -123.234741 39.268301999999991, -123.235006 39.268436, -123.235575 39.268693999999996, -123.23587900000001 39.268822, -123.235965 39.268854999999995, -123.236452 39.269044999999991, -123.23668 39.269128999999985, -123.237311 39.269362, -123.23856599999999 39.269740999999996, -123.24124499999999 39.270534, -123.24145899999999 39.270598, -123.242456 39.270886, -123.243047 39.271062999999991, -123.245196 39.271703999999986, -123.247101 39.272289999999991, -123.247729 39.27250999999999, -123.248319 39.27274899999999, -123.24915200000001 39.273116999999992, -123.249705 39.27340199999999, -123.250177 39.273644999999995, -123.250919 39.274065999999991, -123.25116700000001 39.274195999999996, -123.25210900000002 39.274715, -123.253132 39.275269999999992, -123.253902 39.275680999999992, -123.254823 39.276209999999992, -123.256353 39.277042, -123.256511 39.277132999999992, -123.25681100000001 39.277336999999996, -123.257109 39.277568999999986, -123.25732 39.277763, -123.25754500000001 39.278007999999986, -123.257795 39.278332999999996, -123.257978 39.27864499999999, -123.258117 39.278936, -123.258236 39.279268999999985, -123.25844800000002 39.280335, -123.25858200000002 39.28090199999999, -123.258746 39.281418999999985, -123.258898 39.281818999999992, -123.259045 39.282136, -123.259184 39.282392999999992, -123.25931500000002 39.282596999999996, -123.259613 39.282966999999985, -123.26025700000001 39.283619999999992, -123.26102399999999 39.284383, -123.261534 39.284834, -123.261775 39.285095, -123.262054 39.285439, -123.262354 39.285837, -123.262887 39.286581999999996, -123.263363 39.287226, -123.263936 39.287957, -123.26418 39.288233999999996, -123.264288 39.28835, -123.264599 39.288633, -123.264843 39.288838, -123.265232 39.289131999999995, -123.265639 39.28939, -123.265979 39.289575, -123.266672 39.289918, -123.267943 39.290566999999996, -123.268553 39.290869, -123.26875799999999 39.290980999999995, -123.269095 39.291196, -123.270788 39.292459, -123.270993 39.292603, -123.271425 39.292871999999996, -123.271761 39.293046, -123.271961 39.293136, -123.272334 39.293271, -123.27266900000001 39.293382, -123.27308099999999 39.293476, -123.27372 39.293574, -123.274618 39.293665, -123.274822 39.293678, -123.275034 39.293701, -123.27551 39.293783, -123.27588399999999 39.293869, -123.276353 39.294042999999995, -123.276601 39.294153, -123.276624 39.294157999999996, -123.27678399999999 39.294236, -123.277118 39.294436999999995, -123.277529 39.294748999999996, -123.277953 39.295138, -123.278254 39.295442, -123.278482 39.295687, -123.2787 39.295905, -123.278864 39.296042, -123.279222 39.296261, -123.2797 39.296475, -123.279972 39.296558999999995, -123.280639 39.29673, -123.28183 39.296997, -123.28220999999999 39.297093, -123.282585 39.297225999999995, -123.282772 39.297314, -123.283135 39.297516, -123.283511 39.297754, -123.284319 39.298266999999996, -123.28616 39.299389999999995, -123.286419 39.299526, -123.286635 39.299613, -123.287129 39.299762, -123.287335 39.299807, -123.288381 39.299996, -123.289163 39.300125, -123.289545 39.300160999999996, -123.289602 39.300163999999995, -123.28973 39.300170999999992, -123.29021900000001 39.300156999999992, -123.291584 39.299965999999991, -123.29189300000002 39.299945, -123.292111 39.299943999999996, -123.292481 39.299981999999986, -123.29283 39.300049, -123.293357 39.300204999999991, -123.294309 39.300519, -123.29469 39.300654, -123.29493700000002 39.30075, -123.295227 39.300888999999991, -123.295558 39.301071999999991, -123.29582 39.301234, -123.29609500000001 39.301428, -123.296411 39.301670999999992, -123.298698 39.303798, -123.299227 39.304324, -123.29952800000001 39.304726999999986, -123.299664 39.304953999999995, -123.299791 39.305188, -123.300375 39.30645899999999, -123.30046800000001 39.306699999999985, -123.300502 39.306815999999991, -123.300531 39.306998, -123.300542 39.307162999999996, -123.300531 39.307379999999995, -123.30049300000002 39.307566, -123.30042600000002 39.307735999999991, -123.300279 39.308016999999985, -123.29969300000002 39.308980999999996, -123.299547 39.309221999999991, -123.299337 39.309539, -123.299021 39.310045999999986, -123.298727 39.310451999999991, -123.298518 39.310687, -123.298172 39.311046999999995, -123.297836 39.311379999999986, -123.297536 39.311710999999995, -123.29741 39.311896999999995, -123.29727 39.312166999999995, -123.297196 39.312376, -123.297138 39.312628, -123.29712 39.312898, -123.297153 39.313241, -123.297213 39.313483, -123.29727199999999 39.313638999999995, -123.29739000000001 39.313843999999996, -123.297423 39.313888999999996, -123.297462 39.313942, -123.2976 39.314122, -123.297696 39.314231, -123.297928 39.314431, -123.29818399999999 39.314609, -123.298414 39.31474, -123.298784 39.314915, -123.298967 39.314997999999996, -123.299574 39.315295, -123.300012 39.315487999999995, -123.300454 39.31567, -123.300658 39.315763, -123.300782 39.315830999999996, -123.300974 39.315961, -123.301093 39.316054, -123.30121199999999 39.31617, -123.30130199999999 39.316274, -123.301451 39.316503, -123.301541 39.316683, -123.30161799999999 39.316986, -123.30163999999999 39.317253, -123.301623 39.317519, -123.301581 39.317727999999995, -123.30090799999999 39.320066, -123.30085199999999 39.320321, -123.300794 39.32065, -123.30077 39.321056, -123.300784 39.321517, -123.300899 39.322317999999996, -123.301023 39.32307, -123.301139 39.32396, -123.301308 39.325125, -123.301355 39.325548999999995, -123.301412 39.325871, -123.301493 39.326207, -123.30165099999999 39.326737, -123.30192199999999 39.327739, -123.302058 39.328157999999995, -123.302197 39.328644, -123.302264 39.328844, -123.302386 39.32909, -123.302448 39.329192, -123.302611 39.3294, -123.302851 39.329609999999995, -123.303614 39.330141, -123.303958 39.330402, -123.304097 39.330551, -123.304208 39.330734, -123.304345 39.331016, -123.304379 39.331108, -123.304441 39.331379999999996, -123.30444 39.331674, -123.304428 39.331759999999996, -123.304271 39.332288999999996, -123.303951 39.333307999999995, -123.303904 39.333528, -123.30389199999999 39.333675, -123.303894 39.333822, -123.30393 39.334047, -123.304 39.334278999999995, -123.304053 39.334405, -123.304107 39.334534999999995, -123.304188 39.334665, -123.304323 39.334834, -123.304445 39.334955, -123.304746 39.335186, -123.304892 39.335271999999996, -123.305111 39.335386, -123.306416 39.335992, -123.308548 39.336979, -123.310563 39.337902, -123.311303 39.338254, -123.31173799999999 39.33848, -123.312044 39.338673, -123.312377 39.338918, -123.31240700000001 39.33894, -123.312598 39.339093999999996, -123.313099 39.33954, -123.313525 39.339954999999996, -123.314347 39.340745, -123.315377 39.341724, -123.317312 39.343554999999995, -123.319219 39.345386, -123.320757 39.346831, -123.321031 39.347099, -123.321242 39.347328999999995, -123.321537 39.347718, -123.321706 39.347978, -123.321804 39.34816, -123.32192599999999 39.348428999999996, -123.32206099999999 39.348794, -123.322194 39.349308, -123.322251 39.349703999999996, -123.322261 39.350096, -123.322235 39.350457, -123.322143 39.350954, -123.322046 39.351296999999995, -123.322021 39.351358999999995, -123.321859 39.351732, -123.321706 39.352005, -123.32067 39.353639, -123.319739 39.355056, -123.319467 39.355454, -123.31912299999999 39.356021, -123.31768600000001 39.358281999999996, -123.317402 39.358702999999991, -123.31705000000001 39.359252999999995, -123.316433 39.360253, -123.316224 39.360572999999995, -123.315875 39.361154999999989, -123.315624 39.36164999999999, -123.31542300000001 39.362169999999992, -123.315296 39.362594999999992, -123.31521 39.362949999999991, -123.315152 39.363345999999986, -123.315121 39.363852999999992, -123.315142 39.364238, -123.315182 39.364589999999986, -123.315269 39.36506099999999, -123.315375 39.36543799999999, -123.31551 39.365836999999992, -123.315653 39.366140999999992, -123.315816 39.366441999999985, -123.315982 39.366706, -123.31630600000001 39.36713499999999, -123.31645 39.36731799999999, -123.31679700000001 39.367699999999985, -123.317078 39.367986999999985, -123.318692 39.369527999999995, -123.31942 39.370191999999996, -123.32026 39.37104, -123.321655 39.372355999999996, -123.322081 39.372754999999991, -123.322573 39.373184999999985, -123.323899 39.374209999999991, -123.32402800000001 39.374296999999991, -123.324281 39.37448599999999, -123.324538 39.374656, -123.325009 39.37492799999999, -123.325465 39.375161, -123.326018 39.37537, -123.326381 39.375474, -123.326883 39.375596, -123.327316 39.375668, -123.327597 39.375696, -123.32782 39.375709, -123.328283 39.375709, -123.32919 39.375648999999996, -123.329678 39.375645, -123.329745 39.375626)', 4326)

declare @tp1 geometry, @tp2 geometry, @tlinestring geometry
declare @wkt varchar(max)

declare @capturepoint1 int, @capturepoint2 int
set @capturepoint1 = -1
set @capturepoint2 = -1

declare @iterator int
set @iterator = 1

while @iterator <= @line.STNumPoints()
BEGIN
    print cast(@iterator as varchar) + ' ' + @line.STPointN(@iterator).STAsText()

    -- generate linestring of current point to next point, test if point1 or point2 intersects
    IF @iterator < @line.STNumPoints() -- don't add one on the last point
    BEGIN
        set @wkt = 'LINESTRING (' 
        + REPLACE(REPLACE(@line.STPointN(@iterator).STAsText(),'POINT (',''),')',',') 
        + REPLACE(REPLACE(@line.STPointN(@iterator + 1).STAsText(),'POINT (',''),')','') 
        + ')'

        set @tlinestring = geometry::STGeomFromText(@wkt, 4326)

        -- capture first intersection of point 1, last intersection of point 2, 

        IF @point1.STBuffer(0.001).STIntersects(@tlinestring) = 1
        BEGIN
            print 'point1 intersects line segment: ' + @tlinestring.STAsText()
            IF @capturepoint1 = -1
            BEGIN
                set @capturepoint1 = @iterator + 1
            END
        END

        IF @point2.STBuffer(0.001).STIntersects(@tlinestring) = 1
        BEGIN
            print 'point2 intersects line segment: ' + @tlinestring.STAsText()
            set @capturepoint2 = @iterator
        END
    END


    set @iterator = @iterator + 1
END

select @capturepoint1, @capturepoint2

-- Assemble the new linestring!

set @iterator = @capturepoint1

-- first point is the original point 1
set @wkt = 'LINESTRING (' 
        + REPLACE(REPLACE(@point1.STAsText(),'POINT (',''),')',',') 

while @iterator <= @capturepoint2
BEGIN
    set @wkt = @wkt 
        + REPLACE(REPLACE(@line.STPointN(@iterator).STAsText(),'POINT (',''),')',',') 

    set @iterator = @iterator + 1
END

set @wkt = @wkt 
        + REPLACE(REPLACE(@point2.STAsText(),'POINT (',''),')','') 
        + ')'


declare @finalgeom geometry
set @finalgeom = geometry::STGeomFromText(@wkt, 4326)

select @finalgeom
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.