70@  J 08@ RJLUG$!previous Topology i |'UPDA}  || A' SET=, el8ret1as with nssignGM% if w='(!)'K.0'||OrO~sv )H)# e`nd if -h- m!ovAh6aCDC%2never go through (pCEPTION!N OTHER`S THEW\NOTICE 'G@ot %',!ERRM;W210,211= ERROR: Condi2iscorrect, pleas1ecutfollowlto test yourQpJ-Ӂ x%',sql #ETURN 'FAI%END4ikdkinitializ%',verگ\zT'Pliteral(MA,0,OVR)'D^amingemptied = Bset client_min_`messa1|warAg.IF =b. 2?v /qOZTRUNCATABLE_LRESTART IDENTITY_DROPGEOMETRYCOLUMNaA'the_}]' MAO& 0ELSEdoesn't exist 9CRE;(idoserial PRIMARY KEY,cn}chk ein ^ N@END IF+U( .)OaddGļ(+ W-POINT* 2pԿ?OprVerAO A>, ''{"id"}'w[]OQuO@ 7rpleveYYt -]> cofs!^hawent wro he!h aBieso c| ce , Ps3wait...^Xrow0FOR points IN.b<$aO::BIGINT AS id,' "BStartvPV$):'>End< ><= 6̿ ǻ`LOOP H+ 1+IF% 1000 = 0/processed',K dE _idA"ToId(#". tolerance,AIs?Q #Q:=Ϭ(ϱ RTiY{_-q(y8b ]|z|4.H(+fS#/#/'WARNING: 0% = %, % 'F,4,  Eo5oi/_ndA,[c> TOPOLOGY :DR 4? Row$"4? or1id:q ~ncplude !ice % is{E?,?V'4/&/ )Unexpected error!t9CP P.1o:-["N(,ish +1DbL ] i'! E$@O  BuildArea?I !T 381] $2] |6%3I4])] --RAISEICE 'Curr vex % ' XAsTexSq_J" 5 <newarea#S" -- ILIKE '%/ 4|[cave@,leastKpctex +B/5div), true)5, 'QI_segs=2'/TSIN_S?i'.=initarea float 8Area( q6new 5 K%div integDer#6; temp 22cents[4]benline1resulZt{a|Q]buf1fBEGIN -- We start@ with +ex as our base5[; IF" pctI= `1 THElmthis is the samedasking for5ret8urnl~ELSpGType(. = 'ST_Polygon' -- itasucavgoto getallow_holesyGle<0 RETURN E6remo7fMake8^ExteriorRing " D END IFO VDimen0sion[>) > 1 ANDBETWEEN 0 and 0.98%--N !ۈstr`hatms envelope of&8 Bounda|ry C5 FL`ength1e)/1000.03/`MultiPo0int'=Nubm@ies>34320093we make p1 t%phs sinc"Ey are easier!"in.GNoteLlimit betweenuRbecausTis proceBs2slow,gets quadratically&fsqrt_*0.8/"% *(%\:= ARRAY(SELECT"c FROM)umpUT-- 51#just fudge factor7f$*oUn1J)6 ( -- fnear neighbors togeth er&EDISTINCT ON (i) i, :`stanc#[i], j]),9dufferALinJB^ K buf*5, '!2_segs=3p') Acgenerate_se1,array_upper u, 1)GiD INNER JOINK$jK K! NOTIntersec$*TVKDWithigS[D K10P UNION ALLA'@catchb`onessno,RA$Ki, 0/݄10TLEFT0ceilaP 1)`/2)::/ WHERE j IS NU& ORDER BY` 1, 2f`oo ) DjIsValid)l~){y_'De: fT[UEcj zWs$ؠ :\CZsure  covers original (#3638$ sO/+x/z+ K"/JIFCx/ /kD])/--/1/  kCentroG$) b dOQBGbjeczsymetA-(cutt'a t  will not workdoffset a bit)/Transllat ,':, AXA,Rjuses closest8 onIB. I can't explain why?doisJC{?,,2?/">ebuf/ / -- break eT into 4 trianrgQab1&o-:;y2)Ce32cl0ippe"in eachntAFOR i+1 .. 4 LOO9BA DECLARE rec RECORD; sr varchar;al_schema name ql text; new_srid integer; BEGIN -- Verify geometry type IF (postPgis__W( J, dim) IS NULL ) THEN RAISE EXCEPTION 'InvalidY "%(%)" - ones are: POINT, MULTI LINESTpRING2L0YGON4 CIRCULAR:COMPOUNDCURVE.  ISURFACGEOME TRY,  CO0LLECINTM M  8 M 1 M, TRI`ANGLE |HEDRAL  =INM orM', & dim;!ETURN 'fail'; END IF;*ndimension#j "Zdim >4) OR<2) ) /[i$[Op l"LIKE '%M') ANDu!=3u TypeM needs 3{s| SRID5_in >$ 0vIF 9908999AddG4Colum n() -k must be <= %',J2 G#:= xSEJINTO sr FROM spatial_ref_sys WpHERE(EBIF NOT F!?WabELSE publi c.ST_A('2 EMPTY'l::E)!NOTICE 'value % converted to the officially unknown&14Hp /HS$J RROTS! `!= ''a':= 'nsp& pg3spac@e ' ||'a[(.) = quote_literal(v)=LIMIT 13+CDEBUGD 'sqlXECUTE#qh_if/^S*% is not ad|B', /zKb DetecptingW.9ASca@talog. l ass c$ `'JOIN &n ON n.oid = c.relkin*x'r'"}!I8N (6? ',]pg_toatst%)k table_is_visible(c 3D&/[T| % doe#occur inBUse_path. /-- AddcRBN"useq9mod  %ALTER TABLE#4ident(.z# G. ADD COLUMN9Gu ='#T *U::Q$;j/$b$3 'Y7?T1-AQ$} C HECKs,nNShTRA-' " I'enforceshO] (st2Y vi/ Chdimshn3i*dimh:b 1 Lj= '`')d? ,dgeoR?BRm W ')=4 u e\j!7ullY/.T' t ) &'^:O) TYPE "pDIMS+aEND; 9@A 9@A@y DECLARE@ gcs RECORD;! _old sri integerndimX!type,text; query gc_is_valid boolean; insertekRconstraint_successful6 := false; BEGINF:= 0; -- Iterate through all geometry columns in this table ` FOR 9TSELECT n.nspname,@ c.rel a.att FROM pg_class c, attribute a*tBspace n WHEREfkind IN('r', 'f') AND t.tBy = ''isdrop0ped & typit.oid re= cc= n NOT ILIKE 'emp%GNobl_;L OOP &AISE DEBUG 'Proing %.%.%',  ;W)i:= true;-- F!\the !, coord_dimension, aof current&9 Hin_%P-- which is now a view$U_, INTOol~d7"g q "#@f_"_schema =@ !$GR e$iIF upper(!e)!GEOMETRY' THE N4T1mis an un6ed7we need to do sPhing EW#figure ou@t whats etby inspect!Ydata6EXE@CUTE 'public.ST_(' || quote_idPent( )'P) AsGT8ype:# vNDims=R, '#6ONLY&R$R.#$#e#y ISB/NULL LIMIT x1;'V47$mgcA=!X! there3m so!Bcan not determin.e'`o Y CWARNING 'N/Ja1(A,Xno informat1!_BB!O ݀ RETURNa END IF s:1. ; s ; t 85seamod;sY :0ALTER TABL/] ' RCOLUMN/O/"TYPE3KpostgisrB(7,,6,b',{i:: 4q'~ + 1.EXCE0PTIOZTN inb_param!daue OR feature_noppom{g&Could2convert@ ''%''!''qr'!usRB! with" %,#%: q% +%,p,, SQLERRM6- 6 &END 0r IaOV,:$}U-)/ ??oML ' ADD CONSTRAINTv'eQce_3O&fCHECK (=H$O/ O)"~_violcO O NotDCNintos: cFB3 Ks$%*%)O,?NO2FrO sO! 6+?(? ???f'?||||?I?ay?t;1O*O(O?geo*?$&literal"O -- N7$5be"ied. This$tains a number o2fs.O}Džadd>A?to:.zO>O=O!  --onRunt if we were  inCQrat least on%O/:   LOOP RETUR\; END (@   0@J$I@() 0T ҬisA!ittld_proces require1qw. AAwordthxTs7sSa number,SZits%auK State Route 0225ataIn Tiger 2010+reduced bBeI'm sadQIF9H_-- Check i{contal#ndc '5ggo$:=N_,N)R:O";"E jOe67p1ditmigh porof)37 S1> -- : 24 @:24, N!p" N  +3p_rep0lace/ ,'! s  movG: % +from 2,   4 ەR ҳקí9~+|$') mcZ>$!Dir=< L!T:_ p] LSEOO E'+B][^ ,.\t\r\n\f]*?w(K UJ*?F)!C M|| 0(.*)$UO_'M* 2 0g+- +w*&[_;^` IF COALESXCE(2 )Q "=U --[ ca.be blank, Yo ) ߯r63Main{St{s#xR|| o+''){ ] o8(oW( 'dЏ8* :M s_)v/ i pre(should!a\cnoCn.L;6MV!KJ~internal address fO?8;E?7 {Wg^&))?k S'?:02)?*'OY preDirݨ5F!WO| BN w"8  1 O8t"/z$Gr#~_ ?|[6-- I3=1D(endM ,n noHF$was given. We still need toB ! forL.HSELECT INTO rec]$K,5^oH) as valueM/ 3Q??/D/de sc'rec.Ϣd!m.S +~,;nullֹ&Z#n!HNi$="bmy w!Lbe/AAInt count(*_  upper6f!= 0y N+C{" g RAISE!pIC1exact match 9--hyt equalHbuy!(contained in $it KIt@only&sidered a cf i)ipreceded by a G-utGOP[BX 6O?U w OAy T@awInpuv,he_/&tryto ex tr!! dir: %,/x1&>/ Hg #Of+ JG/n O? G铒 ^/^ /d\_%/ !X4Ѧu~DqS#L befors82(]?K*?]_oPTypY)//O%I?{3w?r OŊ?P(Hߦ4J$I@oundFIF.ξ/IFtZ/,(X K/C9G{ 9 9? 7A } =h$start7+q__ tX endC! -- AjATRint of multiple@ matchAo Q  deshignw!Lwill assu@me thal0ast ZtrueSW:= 0FOR repc in%m/*'W/  b?[)Av8alu/ C/ < LOOPSy< posi(rec.|nl:?5(o+"eZQwzogiven,{iʺafR~t#N/8xqq$'j# Ҹpot`entiaֵsp O-- differenceA98ms vs 16 m!r @exampl-PDutApac#rontAmake 2m easier alwayt&withIZReject a#whe>rQ$2!)qu\al  ' 2"&<]8|y"7|upper p) !=3 ,O'~(u>r0 a:aNo3* AS5is_hwm o&t3x.z STypeU $*+isHighwayJO RAISEICE '4Ek: %,5pnE Ȍ"Bג$RiM#vmost likelyIN%~*"KO%/?/+,< RANK() OVER( ORDER BYwZIN #M)) ) As n_R/X:+ l`engthigend eCOUNTQg$recs,iN?8??(?,ly cons0iderkarrular and< at beginn!ofor,h3s (siqthoserDbe9+endZtq~1/+4?longest e.g Cary Road would\more corrq}th an OOR (#Qx %' ) *((NOT41/A\OR;S #fooP-- N3:  end - ensure.firsxt g3Fooverlappsub ]Then I|endsCt-szgJ64 -4 0DESC LIMIT 1 ɋ]we have founj,Fyp%-- precedese /** TODO: I don'inkjneed a loop any"? #we"just@ retur#n~eSv* I'll leAr nowdugh **/uOTy,gR!d١ Y * \ h > %$V9 :g   $gbh?d5%oGclock_timestapmpJ$I@r DECLARE debug_flag boolean := get_geocode_set@ting('+normalize_address')::;; use_pagc OCOALES8CE(X2Q_p arserX, @false)`result 1y9String VARCHA"RzippreDir ost fullStreet redu(cedsTy$pe at temInt I NTEGEic @RECORDws awInpu -- is this a hi ghway(we treat these differently since road name often comes a0fter ) isHc; BEGIN .d FALSEIF'%THE- := " .e(in_rawi" RETURNgEND IF; ":= tXrim>)"IF%IS NULL [ wsUE'[ ,.\t\n\f\r]'T8NNraise notice '% : %', clock_timestam0p(), w-- Assum% begins with a digit, and extract it fr?:= sub(%B E'^([0-9].*?)[ ,/. ]'PZry to pull2>$4 number @includanon-s li`ke 1R'/Ealphanumerica-zA-Z &There are two f!ìtsA A,i# 5 n in1zip-4. It may also exist.vG$ ws || E'{5})$A>-- Check ifis just a partial or a Hone#-s;or"3has mohan// f, - 0,4}$'),S I2,@&6,14A#y&zip4-X8 7? ? O!C7K)4all we got waazip3of either2%/ /E(\^!$s"qg3  IfQ{onlythen3return itQOT1}~TRUr}n  H_M/E  a  (.*)'||5%+'cull_null(n)3D- ]B?4})?/**r0p off any trail spacesA,**/ b, ' ,dELS 4L#oFIX(ME:_Fshould probably be $aord Dso1canJavoid hav%!q# } 1 O1iingk3s from{"lowerqwidth and height (0-based)"@upp@P2) -Y2w8383max distanc"ecenWixel7$:= w / 28:= h7/6,_+= %," correct1w + 13w, hu@should!odd numbers#=w % 2..!--(WQof nPborhoodu does not permit for aa,CIF huHv3 s coordinatZe c+/ c")$3)!{cx, c((ifV* provided,Otwo &3K4ISib ALNDZa%) r--Rarg is p factor k;[ c1]::o\ksT_k"Tk < 0.#5P (< 0)Vpbetween 0"1. D@efaultBol 0"t0t>1t>QLt1 t{)tsecon!!uwhat9do!) has aS;Rwillcow3"to apply2!(Ln$j&lQgb'3A2#scw*dWTosH/$ & cw",%$!Y   OVkg2T k) 0* -1O&Qcv2[_z][c@y][cx]5hecksee/IOwcwT$ Ecv/RETU RN cv\FO@R y IN_..OLOOPX@x@3@3@߿[%]_z,@ y, x,Sy ][Q3kip NODATAu!)=3 EAOR (x = cxLaPcy)(CONTpINUE+N z || ampythagorean!AQorem_dAAsqrt(b-(cx -2Ay - 2)t>[8d|\dd_&% /pzJzMdf--Mis#zfZ3zB<{>?D/+?A /+5d!U*/h, kSz* t_d +z0 +//00cdahOw (cw * cv!((1 - cwQ*z0% * END; 4*((  J (JJ ]I@R/#&///t/Y />/# pDEALLOCAs;'5@END IF FOR TC IN  USINGrbR_B_,?, G^8 LOOPw/-- If we found a match with ancq , then don't b other Gtrying to do non-<9PesrcS := trueb:F.kkqDDYI'99 y>  )8 ; - u^DWE"%4%):=OfEpB17 117 1typ 1:1k1fat 0 0s 0zi!#*$TRUEpGEOMxOUT >$O2GRATA "/1n. + 1;m>bALAz*h (:=A; /**A first recordAcome is our : ;we will ever get **//-- Only conrEQCdecenZs !'< 90RETURN NEX~T? VV T juturn that= { n >=BmщBC--we have exceededudesired lim idis not horrible a@/?< 3 --i '' ' d(iff(g$7)*$11 + '+END h0 ||'S#f./Q']0Alevenshtein_Sb_case8,6)+m9full =3_9V$4,'''')() )T+(gst_hn^]) % 2)::= ($1 M 1' f$10BETWE9eavA 4 "4f.suftypabrv)qE#pre# + rattributes($5, =dir=,$2,  , $;#6#equ`alabr As2~4tcfea2sBf INNER JOIN addrad ON (f.tlid =lid)^g8$10 = ff>1? : 5ӂ c[(5'4b4Qb~f&"d[Y RMbsap3?h Su)"@.C!?7OR  20K>=}6: #)e= I<=LKJ ( KT_to_numberN''9q}b +4!A]< ((1.0 -s '(A, )::numeric /Z C(1, i lAe ) * 5u+ 5,Q/ASY5/?9Ka~~?b?_CGVb.tfid l ?v`oa/[ a,, aaa a, *n *au loa1Epcaas ; !rj a}edgәb!aK= bWa ߭vfacvS fv( ($ AM= ''L!OR)!)R)#~pp f"$p location HVRөSԽ3D; aD|RKvarњtrict2(!ZST_Isects6E$8:: etry-?)! &D10 , 11 DESC "X20 \9su K( s((_~,2,3,4,5,6,7,9%)hI[^910 ;IFdeb ugzuRAISEICE 'stmt: %'KPre"( .AC!,'$11 ',1%penalty$, '$10', quote_nullable(in_b %2', #4s@treetN1U),'$395)`4 aTypeAbbrevc'$5ApreXDir= @6@otstA7A787>ED9DYÇ!4) );a--+PREPARE query_basBj("`char,E[]2%', !EXECUT%, ); ',e1JJ ]I@` DECLARE results RECORD; zip _infostmt VARCHARin_st atefp exact_street boolean := false!var_ debug get_geocode_setting('&address')::9Nsql te$xtI''n integer0restricnm etrBy#NULL&b@filterVnullBa a > 7Gip@_range9 -2, 2)>$I Sis short, we'll rub@o manys# positives so reduceHrwindow a biC1, 1 & Thigna@ls badCnput, onlpy usSj{itls inpplacS/#zip) != 5"locati|onoD1|A RRAY( DISTIN 8*9\1*48$1=r z"= ANY($3)lower(z.cityQKE$23T'%''3d ' COALESCE('AUIN(2Il>)', '*'J):SlORDER BY 1EXECUTE: USINGuh,,,UIFSAISEICE 'B$Enew&: %',M lR(}B-0_upps7,1}a0a# :4Bydo%!! ignAthem  E IHnew7! 0thatA1ntoo / (4, id '()O[ XV??![H^W[- {;? r2,_pre_ %ϸ<ϵ!STNP/4I5wO[?)(?:$cull_|$" 6a2iovAkafterk-F-s o , lqFanywc F!rimhow >p &:ik P * = ߷2 ?>o   W-- FQs number onlyP putsrstop if reach a non-0e.g. 123-456 wretur:n eAo_;(gC, '[0-9]+')`, '97--get rid of rneous spaces before weziprim(ziTDTr( 0 .z 1  6arseBd/TRUER ETURN ; END =4(   P(RJ  TABLE/ RESTART IDENpTITYgWDROPGEOMETRYCOLUMN ')' :LSE set client_min_messages !7warnU ÈCRE(idserial PRIMARY K@EY,cntEGER,chk xein a ND IF/SaddGˣ](2n1  -POIN2T* 2 [INDEX@VD_s_idxM ON USING GIST (C/ debugleveG*I-n>TNO`uevO /q^O O O~O~#K~, )re group \by>R6A 'ta5HAVING COUNT() =1)redOFMkO #(;|,  ʏV?V<9V orU. foriginalO!O!uO G}iaO4I?G?惏5,)ta1 GROUP BY!5" 5re.,8J*)=1'; END; etR J@+ DECLARE edge_table TEXT := $1; toleranc2p_num (int0ret&''gis_ver_old BOOLEAN!_pgrsionless(post,lib(), '2.1@.0.0')Nvst_line_substringo locat0e_pointabout @n_pkeygHeomamhr ecordsnam  ou srid INTEGERinfo Oplits BIGINRoucheduntypkdebuglevelrows_whe re_ou BEGIN RAISE NOTICE 'PROCESSING: id: %', i the1"(!\_ending,0.,outall( $!qnodeNetwork(''%'', % ,   %)', 9!,7<,  Perfor"dchecks, please wait .EXECUTE 'SHOW client_min_mes@sages'!jO( ; xDEBUG 'Clu% exispts',o qELECT * FROM3getT*Name('||quote_literal(J)||',0)3L^2G=. O2A IF4zIS NULL OR4}THE a"-> % found RETURN 'FAILq EL&SQ= SOK*ND IFqDn=||'.'||D#=||'_< E=E:!<= CASE WHEN le ngth(") > 2 AND#. '(' ||R')'x '' uqbWpHEREd/id column "%" s IN % ',id,/%C6/?"d&b=&dLis,, 'ERROR: '% /$z<6FBHcHe7HrHS O U= On1[Cghave sABb HifSRID of etryq',rST_:3ident(5)3 AS'J c';") @?# /C!k ,QOT#LIMIT 1$A[g.lh1.Gm9m;CanvdeteqC[̟I/q5:= $o#%',.EXCEPTIONSOTHERSpOG2A indexed?*IF ("oisCI,#s1))N->w3QAdqqdex "%_%_i0dx". ESET TO wa rnv8CREATE INDEX H5d idx ON ?p||' U btre?X5jR '||n+4-P5,B5Buniqueg {@/IQ _R3d<' \3!_#&gist_/*/+/;initializ϶Fϲłʵ rɷ )ATRUNCT0ABLE? RESTART IDENTITYuDROPGEOMETRYCOLUMN_H83/((&id bigserial PRIMARY KEY4ld_qNTEGERsubource BIGINTtargetx|uxtyp_[<_ limit;U-> CreatxTKqyps H addG=s9/:B?D  )+ 2v,.zLt'U(fCGIST  o R\-q>Processing, please wait .'roif pgis_ver_oldGvst_line_suPbstr R J  TABLE/ RESTART IDENpTITYgWDROPGEOMETRYCOLUMN ')' :LSE set client_min_messages !7warnU ÈCRE(idserial PRIMARY K@EY,cntEGER,chk xein a ND IF/SaddGˣ](2n1  -POIN2T* 2 [INDEX@VD_s_idxM ON USING GIST (C/ debugleveG*I-n>__;^II e>x:_J EPo(pul%Vwa it..._sql= 'with lines(($disaeas id,S8startpoit_Fmer~gRCrWA k 1 'R[>/yORt@fPmLax'Hid(i3 xGROUPZ in(serbO_`(itd,VV wrjoind us0ing(i=? G*GET DIAGNOSTICS tot = ROW_COUNTp,vabo b*+-=pb.id!+xI7id t<  %!"Yd T0ecntxsetv,id_seqscoal esce(Y,1yc, 2et>@ VERS҃D WITH %',&6T!FOR Y ElDGWd+qcEdq0$1[,#>{#-:>RlprocessedRO Verticdesk f % isA?>&8mh$ Çޓ yUnexpected errorJJ ^I@(u*"%f.*, ad.8 rad.fromhn tohn, RANK() OVER(&"DPCASE!N> '' ' d(iff(g$7)*$11 + '+END h0 ||'S#f./Q']0Alevenshtein_Sb_case8,6)+m9full =3_9V$4,'''')() )T+(gst_hn^]) % 2)::= ($1 M 1' f$10BETWE9eavA 4 "4f.suftypabrv)qE#pre# + rattributes($5, =dir=,$2,  , $;#6#equ`alabr As2~4tcfea2sBf INNER JOIN addrad ON (f.tlid =lid)^g8$10 = ff>1? : 5ӂ c[(5'4b4Qb~f&"d[Y RMbsap3?h Su)"@.C!?7OR  20K>=}6: #)e= I<=LKJ ( KT_to_numberN''9q}b +4!A]< ((1.0 -s '(A, )::numeric /Z C(1, i lAe ) * 5u+ 5,Q/ASY5/?9Ka~~?b?_CGVb.tfid l ?v`oa/[ a,, aaa a, *n *au loa1Epcaas ; !rj a}edgәb!aK= bWa ߭vfacvS fv( ($ AM= ''L!OR)!)R)#~pp f"$p location HVRөSԽ3D; aD|RKvarњtrict2(!ZST_Isects6E$8:: etry-?)! &D10 , 11 DESC "X20 \9su K( s((_~,2,3,4,5,6,7,9%)hI[^910 ;IFdeb ugzuRAISEICE 'stmt: %'KPre"( .AC!,'$11 ',1%penalty$, '$10', quote_nullable(in_b %2', #4s@treetN1U),'$395)`4 aTypeAbbrevc'$5ApreXDir= @6@otstA7A787>ED9DYÇ!4) );a--+PREPARE query_basBj("`char,E[]2%', !EXECUT%, ); ',e1J ^I@` DECLARE results RECORD; zip _infostmt VARCHARin_st atefp exact_street boolean := false!var_ debug get_geocode_setting('&address')::9Nsql te$xtI''n integer0restricnm etrBy#NULL&b@filterVnullBa a > 7Gip@_range9 -2, 2)>$I Sis short, we'll rub@o manys# positives so reduceHrwindow a biC1, 1 & Thigna@ls badCnput, onlpy usSj{itls inpplacS/#zip) != 5"locati|onoD1|A RRAY( DISTIN 8*9\1*48$1=r z"= ANY($3)lower(z.cityQKE$23T'%''3d ' COALESCE('AUIN(2Il>)', '*'J):SlORDER BY 1EXECUTE: USINGuh,,,UIFSAISEICE 'B$Enew&: %',M lR(}B-0_upps7,1}a0a# :4Bydo%!! ignAthem  E IHnew7! 0thatA1ntoo / (4, 4 P 5PJzip}7 6W*TypeAbbrev,'')  o'$7trim  , 8'$8/Rn9', num_re sults, )&;aSIF4mFOR & IN E@XECUTE2sql USING2 ', -),o%T/&,pv a"1j[5%z LOOP ADDYDir6"H:=|.Seb1 1Q1 1Q 1post s 1location6 1Bat0* +aB  [ziparsed$TRUE dsubstrping(Lb'[0-9]+')::ger4GEOMOUT amRAT1  e; RETURN NEXT;L 1w!; ; END; JJ aI@ DECLARE var_na_road norm_addy; inter1sql text := '"'zipchar(5)[]in_state"f2) 6debug booleanSget_geocode_setting('&section')::>Uresults record;@ BEGINIF COALESCE(way1 ,'')  OR2THEE-- not enough to give a{ just r eturn3RETURNELS:=qalizevress('0 ' || 3 ',in_city ~zip)2:END I"F..:=  FROM _lookup As s WHERE s.abbrev = upper,o_x>limit se!2 plus or minus the input zip9!:=_rangec -2,2 8IS NULL AND<farray_axgg(>z_base:=Rhlower(k!|-- if we don`'t ha"o r|,bother doing/check,3keep as null ~LIKE "%' 5(C RAISE NOTICE ': %,,: %', @quote_able(%)Y~QyWITH a1 AS (SELECT f.*, 1.fromhn to `side  % X KG*Ztiger.fea@tnames, e$1](Nc0!h$2 2 ;CASEN le0ngthT .s@treetN?>4 53'!tZfuLll^$6'|%'AbQ`!?<).y /f LEFT JOINAl>)!!ON (hlid = f  6f )_$5::q][]6OR = ANY(%) 2TORDE`R BY l>.@==`0918DLIMIT 50000 ),6a2/y4/xs/z /y7/y /z/'3p //'> /typ /*?s /%f1/0 at? . .# .zi"TRUE`GEOMOUT :2CRAT1 ub_ratin6g13n, + 1o32our <s go above 99 exit becau se itreally bad3'>9O.P` NEXT QReXRo4Rreturn thax= 0 AF:n > maxA~R# k--Qave exceeded '' T' least((d$7 r(0) *$9)::ger,H__ignore_case($7@(*$9C)yELSE '3' END::text + gm3/(),YH8),5) !T/Ybe%03  6(LDa.tlidbEA7a1U7' '','''' aq4 ;FQ%! "!,r:, r1 attributes($5$$$2, , $4$aH%6% )>+C10?2G$1ق52_20K>=3_hn(F: C":5 FJ I<= gq>.e LKJ ( K% 2P(to_numberN''9''))? ,4!A< ((1.0 - 'CA, h)::/Z (1, h k)A b) * 5 + 5)ND?Z'? 9K($1$rZ?ZASwIEHbtiger`.featAjoin_ bc =AM V a* E'quoiteral(&bo'mtfcc LIKE ''S%'' vo5'aato_strinqcu:o+$ lower($R  (m'trim(=, 15} 7f8ull[sub$2,1,1<5)!K EN&D-11.LI@MIT 20o) ASAJOIN4hedges e2i?= e6<' 3e7e?&]'@var_reQcft1OT#ST_Isects(|$8)#,_+ sх^ faces4 f^f`fidl !]Qcm!EFT olookup_baseA8,= wv = oyfHRp }county cocKcosub4 c/s c&tcs.cosbidOHb||s,ub#(ideq'L'' and$v'B +R +(KhG,2,3,4,5,6,7,9 (C1I; IFB:debugB&!RAISEABICE '%',` stmt6 PREPARE query2AU(3,echar,AAetry,dJ^I@R/#&///t/Y />/# pDEALLOCAs;'5@END IF FOR TC IN  USINGrbR_B_,?, G^8 LOOPw/-- If we found a match with ancq , then don't b other Gtrying to do non-<9PesrcS := trueb:F.kkqDDYI'99 y>  )8 ; - u^DWE"%4%):=OfEpB17 117 1typ 1:1k1fat 0 0s 0zi!#*$TRUEpGEOMxOUT >$O2GRATA "/1n. + 1;m>bALAz*h (:=A; /**A first recordAcome is our : ;we will ever get **//-- Only conrEQCdecenZs !'< 90RETURN NEX~T? VV T juturn that= { n >=BmщBC--we have exceededudesired lim idis not horrible a@/?< 3 --iInAKwe will be subtrac"whi4ch!w6%antte+LineLocatet)*-))::numeric(10H The odd even'T ’ofrudleBIF % 2) ! ?w' + 1BETWE@A wB E-q^qe ZEND"] /EZqzip !Gm(:Gzip kLSE@:-- l!io1T / o/kkPG]&ca cross - on|ly1 Aua?-,<>Z|4v:= array_append(, include_#{_rangT{9*::varchar, '')4' -;R;1 :' '3d'' & \]L(conArxa potential1<*1.1 )020) ?We#Ussible!rit!real1loseѤa`intp/,p_gramps hhave ses bu2nect a3&of a "viso¬f4lofNqBi1dqZusefulJ pCurr las%,4hAisd . -o a l),JUse cvalueEBEeprevious!] has7z&Fupper1) > 1[*- 1]>(7.eӴ! probabQn offshootome sort7Qor entry withof new1&had_al3`Ҳ;(JfI@,, DECLARE var_redge RECORD;state text := NULLuspscou ntyfpaddy NORM_ADDY _altnstrnum numeri c(10)primary_line geometry p%disBtE,2) #tC>lace char stmtdebug boolean = get_geocode_setting('&_reverse')::9 Nra0_highway integer = COALESCE(_ Y_numbered_roadshJ,0);/**0 no preference, 1 zB, 2local name **/zip!0full6(''; BEGIN IF pt IS< THEN RETURN; ELSE  %ST_SRID(pt) = P4269- := pt; 5 1> 0.ST_Transform(pt,V)BE --If srid is unknown, assume its/RSetw PND IF ,napToGrid(, 0.00005); /** Get @of flo" point junk that wouldvent"1rsect0ions}-- Determ1A4ables to check$thisneededtake advantageco1aexclusion7AISE NOTICE 'matchsrt: %', clock_timestamp(=SELECT6fp, DINTOG,IFROMWHEREtI!s(th21m4!(LIM IT 1;&end: % -  l< -- We don't have any data for8:5 YBi--Bte;|y "R"1e ,?C=Q?$1 AND$2'XECUTEW&xXUSING-/pt ; zipzcta5cQ]}i{5> '' utr.:=).Cs&As zz.Bh(S_ion,gQcousub7**!&s/UVz.!F #ip_lookup_bas/ z.4s 'WvF'O&V%',A5__ U3_3OAbbrev1cC Find thqreet sd,mlosestqwith tolerancŕt5 but onlyrideeR if Nontained inright or left faceThen orLaddresses by proxim!pto oLSWITH ref AS$ (U( 4' || quote_literal)::)':2:ƆAsL2 ) ,1Z(Ts.*3U CROSS JOIN@/3Y,7\!2Z7&8";w OMG)0-)e .tlid , UPCASEAMNf4idf " 'P'R'' %l%L''AE3END::sAs e! ,  ST_Cu3 P!( Gc~e!#INNERf ON (fo OR)}/; b/;7/;*DWithlin?.,1) mtfcc LIKE ''S%'') --KACs andB/s) #a%%(p", a.froDmh.toe.zip"0@ LEFT{A!aaxy3*= e." * DISTINC`T ON(7 foo.,prJbI( 8 " !sql, '$1', quote_nullable(in_'$2ASa/road-) N3Nin_city)> 4 P 5PJzip}7 6W*TypeAbbrev,'')  o'$7trim  , 8'$8/Rn9', num_re sults, )&;aSIF4mFOR & IN E@XECUTE2sql USING2 ', -),o%T/&,pv a"1j[5%z LOOP ADDYDir6"H:=|.Seb1 1Q1 1Q 1post s 1location6 1Bat0* +aB  [ziparsed$TRUE dsubstrping(Lb'[0-9]+')::ger4GEOMOUT amRAT1  e; RETURN NEXT;L 1w!; ; END; JbI@ DECLARE var_na_road norm_addy; inter1sql text := '"'zipchar(5)[]in_state"f2) 6debug booleanSget_geocode_setting('&section')::>Uresults record;@ BEGINIF COALESCE(way1 ,'')  OR2THEE-- not enough to give a{ just r eturn3RETURNELS:=qalizevress('0 ' || 3 ',in_city ~zip)2:END I"F..:=  FROM _lookup As s WHERE s.abbrev = upper,o_x>limit se!2 plus or minus the input zip9!:=_rangec -2,2 8IS NULL AND<farray_axgg(>z_base:=Rhlower(k!|-- if we don`'t ha"o r|,bother doing/check,3keep as null ~LIKE "%' 5(C RAISE NOTICE ': %,,: %', @quote_able(%)Y~QyWITH a1 AS (SELECT f.*, 1.fromhn to `side  % X KG*Ztiger.fea@tnames, e$1](Nc0!h$2 2 ;CASEN le0ngthT .s@treetN?>4 53'!tZfuLll^$6'|%'AbQ`!?<).y /f LEFT JOINAl>)!!ON (hlid = f  6f )_$5::q][]6OR = ANY(%) 2TORDE`R BY l>.@==`0918DLIMIT 50000 ),6a2/y4/xs/z /y7/y /zU2lu?m;,S)N?[I?["?[7\>;=\e dg !_dat~a?qK6v6v NOT NULL9"6sstart_3n0$end" a_left_a(abs_,(righU-yI]#G$K !"HOq.OqLINESTRINGOvO|v Ow:\R $!~G}=~_r1~'-F;N?t/RIk(DbIDIRBINITIALLYED7R/8' n_ s+ZxIB}R8{}(select` ruleOͿVIEWz]ASaw+id,H,FQz,,, :, 6,}8,,\%E%'zC`OLUMN|id Unique =ifier |of&:-a>t ?F!endFQext#T3on O (when loo@king idirec! from START _NODEAEND ), mov6counterclockwise aroundqbary??2P.sid& ':!ۄb ThevMRAinserRULEt)_)qON INSERxTOoDOGpTEAD Hufi8ntoXdRVALUES (NEWe,  jnLjB}absNO8 9:/ }! OFFION({ layer sequenc& pSEQUQ$O_id_seq qrela2Xw$6T9R(#"geointeger$ element&( UNIQUE(y,,i N)%RIGGERR_yrity_checkaBEc UPD8dOR5ON#`FOR EACH ROW%SPRO`CEDURQ"@.R:TriPINl xBuild a gi!dex on 0INDEX "u_7"UPSING[(B;' xbtreyiwbu (mScacX#loo"!*Of&|1O(% f1%1'RjORQIsEmpty(K 8-- !Q !O 1D"yno ringO :C!N%:O& I overlap Aontainmeqv or MBRJ z3uf2 2u@r1{/m, U 2~<h1%&&& * \ T*T**oYQsBfoY̔1aincA2F*WX$FFF58 sp}(K; END PI@ DECLARE retrec topology.ValidateT_ReturnType;/c RECORD2i i@nteger nvK_edges[] fac sql text; BEGIN -- Check for coincident nodes@ FORIN EXECUTE 'SELECT a.%_id as id1, b 2 FROM ' || quotent(name)'8 a,,b+ 'WHERE<& AND ST_DWithin(a.geom0)'NOTE: see #1625 and #1789 0LOOPP.error = ' 9'n'id1 =F 22RETURN NEXT&END ~;! crossed-- TODO: do thi! the single4loop nπnid, e.;eidnee.start_K!=* e.end&ST_$ n$e)'nsneid;0n%/Scan all`? 2e.leftB* righORDEpR BYEC-`- AnyE#becomes a cancer"higher level complex IF1IsRG(B THEB u? NULL[:= array_append( ,o)[ISx OR1 = ANY ( 3)L $ #f)!xIF (()!@1\ O5iCONTI8NUE )Si!-*AK4not s5/Do,ing:=G)1O"2 f2UrRe1g- )ST_Re`late(2,)WmOp1oI2oJ< -&&83!OT#& ]R&}$wliteralKCb 6ff,e~#$]ohsqlZIF1$Match2'im, 'FF1F**1*2':26vs rior sectioneKeRClolin@es hav1d boundary, so end`point2 N would be consideredx1See http://trac.osgeo.org/postgis/ticket/770 4also fuqxplanar AddEdLge3LS; 0F01F;-- first (g1) is open, second (g2c1 82 has puntual*ithL"Oqute <, cB}it equa\lsH%3E#(QaDSe2Sg1),-ST_SPU2)o5Wo 01Foe8L/GQ1?0F?bothD areyi (Zg--|ve}betwee1z;{'}v--`F _and_%%12aC. b.,?z7 BvɟB@`0yetry mis-m!pmover4mEtable scan߰/.w P/-Z?csq^endEnd&endqQw/ouQ /)sM> 0 EXCEPT ( if/d UNIONPyQ& [JNow create a temporq 3~totruc`t allk%iiorsing 1ir"sten cyh6CREATE TEMP 0TABLsON COMMIT DROP AS*7,ST_GetFaceG$Զ;ޫ,LOambr/.3JiI4ce$hC!|!pa"jmatchAh_ "#=ret ,C 0, Vss8B%N%s &) - 1, !Us,+s +T_vb=OQke$>4ND("Squpals()1) 8\6) ' #.2_d*_g%"1#g!ccorrectTODO: Lt#/rN.tpkۼqCOALESCE(t.|0rq&'ڻ.  S t9_ ?L#e== 'DROP TABLGQt ; @RETURNv; END JiI@ DECLARE var_sql text;rgeom etrystatefp *cnt@ biginesult& := ''@rid in4269precision double := 0; BEGIN CASE region_type WHEN 'place' THEN SELECT th$e_, FROM . INT4O , 'WHERE plci dfp =tid;scountyt s.tcnty!uELSEgRAISE EXCEPTION 'R&  % IS NOT SUPPORTED',0; END G; ,rtopologyv. o0  name f=L ; &k! CREATE TEMPORARY TABLE tmp_edgeV AS WITH te AS(pl ST_G$N(ST_SnapT oGridTransform LineMerge(),$3),$4),1) As", tnidft, tfidlr ,'[As orig Diger.s= $ 1 ANDCovers($2^)8  DISTINCT ON (t.) u_id,t.w,34start_nodJetKndCOALESCEp0.left_fac;r;righ< #lAS nexLt_i, .r .s.\/t.]"W t LEFT JOIN"i0s tl^#= NDp JrJt3rx]Jr J'1X ECUTE3{ql USINGI93IQ?; GE!6AGNOSTICS5Q= ROW_COUNT4T$:=%::R|| '"\s holding in temporary. 9ALTER< ADD CONSTRAI@NT pk_PRIMA KEY($ );; -- DA! indexes Q  4INDEX idx}P4qbtree ($);L56J" 1#16# 7K$A0K+ ( LQU ) "K loa @INSERT4'!1|quot1enTt(uU)2.?(cQ mbFCd0fBY,ahEnvelHope{f.$3)RGmbrEh;Q1ffo( r7 INu'eYL9hOR 4(N 5 OR? RRhQU2Rd)B_$_+ ||_addedR --q,_add remainQmis&sSof sentV--6/U,=MB,z}~v)N2? (e/*N4Pr/,L2Li;Lu? ) #b~}& 3Or>O FOXX)?(F߈(R)B!*k(?{N!:APoint(e2F&?:e"(B"!e.= n_idkUNION ALL End? :TW IS NULL%L6$?%4'?,"8#3!3;Mark which@re containeLjN1p9XUPD@E5IT]ing=8B vur+ZZ U/ *StsProperly^ n2),4269)\ /@U}a.g"3-wSet orphanrsZ to itself an d setTwi thto world$~F^!X= -1*D$ "D)S E# 0ׅ= 0(.| ?}~/#y#foxr.0  `\pPI /$XECUTE!<y 15u RETURN tg; END PI@ DECLARE layer_info RECORD; topologyQ rec 2elem TEXT% s[]sq"ltyp olerance FLOAT8ah IN"a4BEGIN -- Get rmation SELECT id, name FROM,. jO WHERE id =d(tg)IF NOT FOUND THEN4RAISE EXCEPTION 'NBowithP" %" in',D }END IF;.:=%:: nfo.>D, if 0 was gi veI:= COALESCE( NULLIF("a;0),_st_min(, ageom) )  *, CAS!N feature_type = 1 'punPtal'* 2*l(ine* 3*ar) 4)mixed)ELSE 'unexpecte`d_'|| 8'ND as1&e%+"l0"STRICT8RE :l3_")'&AN8D l&'id"w%5 N NO_DATA_<? ?8#,'$%+Can't conver! a hierarch0icalW!^etr1IF.level > 0 &Lof is m, cannoa simple it.5--heck" compatibility and set TopoGg#H -- 1:#, 2:#, 3:", 4:collec4_lQ=I(3D 'GEOMETRYCOL!1ION'E--0 A \ only gQ S:J!5KM %hold a o)(4,GI)o5,RgQ=4 LSMPOIN T' ORMULTIBC-- `point[go1B! or`$!I! SI<}Z1}LINEQ8NG'< 3E Fc<Z2?LYGAE>Bn Fs;Z39-- Should never hap6p U d imenss%', ST_DhEj Now that we haveemptq,w,loop odistincqjmponen tsNadd themajthe defini" Dofq We%a0s sod possible s>at eacheTnfurther editD;fby splitting FOR3INǎv,as lyr:gd. ;]YX!gsh || NA"TODO:s`ider !ngle INSERT statCIfkhe whol!C+sqlj'5'squote_ide)S|| '.rel3ma9gCDbid) VALUpES (TXid^,lyr {!) iFwe're avoidANduplicaajrows her9ed%ly Sk ? PI ӱ%oi sESymDiffereEz@=Pʅ9defin? at%nearTST_AsTNOnSur1DH? 25."% (invali/?)8|f8[BOT3nGXHe_newETURNJj+|5Get LBid#sequ(nextval(' ||nwliteral(zR7_id_seqb) @jy֛6nserINSERT INTO s|| (, mbr) VALUES(CA34>,$minimumrectanLgl-$1  US>svelopeF/UpdateXEhavXfUq8.2)&Y.UPDA)rdata SET{g= C%)(B WHERE_xANYe<@) kv,!n-.JG/D0Set/of any contabS>h `Q/p&Is($1, ?92} ingnod0E o @mly END PI@] DECLARE bounds geometry; symdif faceid intrec RECO RD elat e tex,ight_edges?[]lefall yold_ }(sql oside boLolUeg Wp*1 2 3 loc float8Anegnum|n umseg; BEGIN -- Atopology and apoly are required+IF a+IS NUL0L OR2THE] RAISE EXCEPTION 'Invalid null argument'END IF; line must be a dgonsubstring(type(), 1, 4) != 'P@OLY'  F8aceLu for#OIN SELECT (d).* FROM (dST_DumpRings(ST_ForceR$HR) ^) foo LOtOP{B Fill5"+,cing#M-hand-rule6to know what's"c6... l`=B ary(.)#:= 'e, e._id 2;1 #"|| quot4ent*(&]) 'U e, (o$1 a3}) r WHERE r &&a DEBUG 'SQL:0 %',"FORB,IN EXECUTEUSI|NG(Z YE% has5box1tersection',j)Pfirst non-emp ty11$ of xthe25NumPoints((D= 1WHILE<I_"BxYVNV,O7^B* +1,IF2istance(p1, p2`) > 07EXIT85z ++ZA(/ mWARNis colla0psed:CONTINUE;we don't want1{spend time on itDFUDMakeL in)Skip"covered:NOBlEquals(p2,lnd') 9(@ _ST_I%s(#, p1) AND2) 5KX_?p" %B% not8$ng with Q4}, st_asqJ(p1) 4q/JELS[dW2B-onlyk, leQ6use Cs (more expensive V5+1g,isby/+|p3#wStart#(E4Within(, p3, 0 -- Eae@ng endO, See bug #874;!Locateq--5: this'as robaangthQ45^allowsew> 0.9 shift last# d ,G-- Collectfinal fBage check9/"E(^u}yԉXxF1x no!sda,ry؊C tha und, taken togheter,1y matchjwardnoaR,g qBjItest fail @eitherAneedWadd6cprom qo 8splQaoRxistone~saPI8oL./KhU=ow' Q#-Q(Q Qe2 j E1 hav3-or-3Z /be1pŦf;? le1߮+1߯ c%rendz! /withBE2?E   */&[either com^m  r2;-in-(r 4nˋ]%FOBVAthDow" ]o1c/29 2!iM;VAA(L($*| 2u?* vkAiBfrom aJc 1KO -O L?r >/ k"7y ':HSQ1QOEND PI@z1 DECLARE tgbuf topology.TopoGeometry; rec RE0CORD'name varcharquery text; BEGIN I@F tg1.V_id != tg"2 THE- -- TODO: revert @to ::g instead ?,RAISE EXCEPTION 'Cannot compute intersection between ies from differenoies'END IF; Order Aso that has less-or-same7dimensionality of,(point,line,polygon,Pcoll):ype >2 +:M21b1buf>NOTICE 'j0id:%h ',9~idA-- %  are yurFly supported= 4\C[Q b!xs()y !SELECT FROM'0into&9WHERE id l)%+Hier1ical-2. 3D= 'level layer'|| '9[8= ' ,AND;( ] 96OR)2)) l> 0 '/!z#FORByIN EXECUTE LOOP?O40%/9= 1"[mult|i]2#;!*#/)%<# Two features if they sharD any Nod $6(%a"rgeo!&"# $quote_i dent(5l) ||H.relaUC,<(b ;'3/a&/*b0, 0 b\,QvXelement,b  LIMIT 1"3)?k!?n 6r2JETURN TRUE;!$'4 an .7 ) < Obon2 % boundD2 eOOD1 1ftIIN]Ei+4c8ollrAEXCEPTION 'I5 not imp#ed yet'ELSEUvalid TopoGeometry !2oa{ZM1 3i32S*O?o ?f=hJz =x)oZmA7 ann +Rcrossingv3[tE1s,x E21R1UQs[ >#y.FACE idgoA D!<oL7,  0ЏRJ@& DECLARE edge_table TEXT := $1;tolerance F(LOA2points recordsridinfo ource_id BIGINTYargetotcount rowk INTEGERqlnamZ ab v er 5g id k noti@nclude i namingQbflag bo oleanquery typ    deb@uglevendummyRecfnN errmsgKind intmptied BOOLEAN; BEGIN -= 1;H --=ce g= 'pgr_createTopo logy'ZRAISE4 'PROCESSING: G(''%'', %,  rows_whelre1&c%()',7h,6X,the_geom,id,,x,QHEXECUTE 'show client_min_messaPges'!BO;  erfor"^checks, please @wait .';: xSELECT * FROM _1getT('|| quote_literal()O|| ',2,' X) ||' )31+F =. 2 C!=3||'.'|| #B2=||'_A1ices$[}IH=K<%(== ' AND Ha||')UD@EBUG '--> OK #" 'Column$es inRlePDCK,N!2,TX'T4XXjX4iXS6Z7IQ =in *(,r,u) ORjB,!= p3onError(~, 2 ,T9,='Two$'s share the s|_AParameter$Ifor id and must be different', sGMrOK'2)/ "/a/xTyp) 1Q/ \(\tX$< T,MNOT in('integer','smalli'big /m-lWrong! of $id:a; ' Expected)Yb i, or  but'2a#wa!und(^;e}(TH: ( `} 8 X  Or&SRIDQetr.yDHo=DST _5(iden t(T) AS'Z2>u Cr5WHERE'6t/IS" NULL LIMIT 1  %', 8ÔSh((|$}CvrOR.Cb/x'Can bdamindYof !bs!)i!7#"C[, 7ofqe{'||T k:= ՟ "-zqkµ xindo/Index ,Uj, 'btree'::?I HM08M0, 'gistGH =?%j{ #$ (, ,, oC issue # 193 & 21(0 &3 *this sqlQUtry!]out"ìclaus7e select *;to avoid anU conflicts >limit` 1, j02Nn first record 3susebllmed it wbe caught2he exceptionql{'||_XASJtrueڭB18yU kummyRec#-- end'abov e worksone shpould <ueQby also count(*)cU>lEx'||S3.:#)=falsRlVISQL otincluded8if clea7=ReJ declare sql TEXT @:= $1;eids INTEGER[]2pcts FLO,AT3directed B@OOLEAN4has_rcost5; i integer-rr REC(ORDl first booleanHtrueaseq1L02everseoint_is_vertex falsledges_M float; beginSELECT 0D::W AS, NULL id123aaINTO4=_pgr_parameter_check('dijkstra',!,) :=!IF ( Y!=) THEN'1+NOT -- user does not w@ant to dcolumW#= 'lid, source, target,@FROM (' ||2|| `') a'ELSE _raise EXCEPT ION ''seC!~ but_found ]ND IF  FOREACH f IN ARRAY3kLOOPJf in (0,1E-:}\turn_res trictOIS"_ OR length$) = 0) AND  -- no >ions then its a "withPDsVia9RETURN query5Ma.seq6, path_id?Mnode?M!4y?Ma.x",(U&q,R-,,F) auif array_[J1)1K ]1)-X#excepK 'The oE[sSand#Mm uQ6eI same!$end if-- loop through each p(airWvPcopmputK{CorR 1 ..|-1^RAY+& dMrrNsePlect,*asa.id12 from trsp- [i] 4+16 8%Q,wF-?a1$ c@ombiner=rmediate viaROs w1Bis split across Htwo parts" segm$en!caQit@ stops a=!qnext leg a`lso oAB 0we mighSeMdo tho can knowr#Bs ar&ulgQSneeds m~oI18 +ufm@ultiplandi2we haveeal A{etwee1of onAg2O/1 @1.B !S_id.R_id=-1; drop record Jmeans:'endsV *NOTICE:: (19,1,44570022,-1,0)1 0,201768045,2.896911967 17448D2.#sumLmp`onent. /_$} R># 169628,1775909,9.328858851485323>0.7713863509843895)S@cno@tice 'D%', ifEjUp:=5{Fls` ifO.id3 = -1qu els G K RP +7 `{W2 ß)^id uX(ƽs1{ tseq)t "-don$ &reaB/&o a<o_?O?/; end; PIsγ for negative#-\i>=Tj- ( + 1id䒵sIF8 IF: [#v}" OF/,EXIT=end ofq^'h :_ee3`sO  9 O5=S 14)C}ELSIF tg.typQ4- collection^RAISE EXCEPTION 'C  etries are not supported by As&JSON C; PI@ DECLARE toponame text; json sql bounds GEOMETRYrec RECORD2side inqsheP) @lookWfor_ holesBEGIN IF tg IS NULL THEN RE0TURNJEN D IF;1-- Getlogy (for subsequent queries) SELECT'FRO M:.CintoWHERE id = tg3_idTODO: implem{scale ?Puntal TopoGeometry, s.y delegate to AsGeoJSO.type = 1":= S4T_/(_(tg))4!turn8ELS [2[lineal FOR"IN;(ST_Dumpl).geom /!};&}u:= || ( '[array_to_stqWs, ',')]' ~cpsU'{ "QE": "MultiA?Sf", b"N": ,}'sZ_3V arVPoly8gon %SHOW 5'#K&5ET 4 5_C VWF'agg(12as fcAV(fhtuTEsysb[1]=id gE3T; y:= ARRAY[]::*!?:= false '[E1data R 7d( e.left= ANY (a78 e.righ+s( ^?;1b2Xin!IWITH UR SIVE AS< (0 as lf , as r!c^me4yANDbeU]g+Y@) ), _most_non_danglU?,e.lf !=ST_XMin(*Y LIMIT 1O2 pCAf;WAalBklmU`LSE - Q-a@s sign_id"back,#a1ofe.next_'z?GuT,  lwabs(# UNIO p.`&5p.Tp.8 <  < 07p ,1|ntru o d,YY&pN! E ; <N"!@END ) e8bs( 1M5}=-- For each defining node, print a directedNo"dFOR"IN EXECUTE 'r.element_id, n.geom from '|| quote_ident().rela r LEFT 0JOIN; n ON ( !P_id)J'wr.layer*2 tg /AND brgeo /)LOOP2<'VDoIHkkHeepifTable was@ given:IF /2tru'$|| U::A&Lv&4erecLIMIT 1 INTO"  xlink:hrefH="#idN " /5*CONTINUEX$Mark as}INSxERT|h (^,W) VALUES (1,a)WE F_AsGML!( t,A ,iRt ab v e r 5g sourDceZrge :queryecnt BIGINKrid INTEGER ]typ] sql #otcPount Ni notinclude vdebugleve idum myRecfnN err bool; BEGIN %= 'pgr_createVertic(esT' Edgele!: OK columnes,HfFROMnC9,1%2,4j)UHU4U S U5u W#A9= IN (,k) ORYq=p3gonError(err, m,&'Twos share `the syS', 'Parameter for%I6 and must be different'4.1U/,as inr)#m/: b.Typ)9 1/=X XRnot in('integer','smallint','big Wrong1< ofwy: Q ' Expected2 #BL i,  or  but'7Qwa!=undV\yH;=!:? SRID1petry4%4z= 'SELECTP ST_1(hi`dent(DQ) as':"?:n ?dw:1WH8ERE2l +IS" NULL LIMIT 1 %',ua3?$]$^'jfOR=.Z/Y '(Can"dQ#minTBXof !E{c+! i!q/"%F, 7ofU'||Cf:= ^m+Qr/P QĢM Ind<rIndexj ,I', 'btree'::?J8mJ-r, 'gist D>|{qe ig =/ $)x;Od)=Conditio5-- issue #193 & 210 &3&thiJs@itryPo ut"|clause3ewis to avoid anE|r conflicts:limit 1, j"n fixrst31~sHuseRgllmed it wbe caugha05excep = '||O1Ctrue2?hE~gend#2abov ـe worksone should8YeA by also(*)c"ETQ7orB 'Bs null')=TW_sqlaSQL :#CEPTIONN OTHERS THE1/Got %',UERRM;7/210,2119ERROR:7no`rrectexecutcfollow1Ucto test your1#:tETURN 'FAIL7ENDA$I&SinitializA',W7K!;,0_IF X TRUNCATER J<! DECLARE edge_table TEXT : = $1;s_in_rules)[]2out3t;4=;5ecnt INTEGERinstr"naming @recordnam Et ab v ow sourceHrge ]1*typ* ver =debuglevel BEGIN RAISE NOTICE 'PROCESSING:'; pgr_analyzeOneway(''%'',%)', ',) Ů, o}, ,,two_way_if_null EXECUTE 'show client_min_messages'!O"; DEBUG ' Check!% exists',kqSELECT * FROM _ OK@EXCEPTION WHEN raise_exception THEg-kERROR: somethrwent wrong cHthele$er RETURN 'FAILENDVXS:TabC)  ''{"id","einout"}p''::C UPDA||{k identi||@' SET \=0 ,ein=0 P%nkcolumn"s inb dlX:CJ2",3h,0,2)PXPDPd7LTL0perform<onError( IN (,pP) ori=,X 2, '3reateToplogy', 'Twos shhare"s{2, 'ParameterforUTand must be different'*W|OK'/E?h)? ? !? R/oTyp%o9  ~1? S(S/ NOT IN('integer','smalli!'big ) /o&Wfof $P'||8C, ' Expected* # || '( isx, OR BIGINTp but6Qwa1und/ Gz' ]8^^b( ^Xj^i__){_]OeBOe ur nASgraphU way street e!?s.'}:= CASE߼xeZIS NUL L' *ELSE ''V:= '''array_to_strping(n'G0m(a sin=coalesce(ein,0)+b.cntXB(T'>', count(*) AS AF#o||WHERv||KZ '||'):GRO UP BY ) b*_a.id<=b )/-sis 25%% complete ..&E :r , 5>0&<out?j?m 7?l)?o10?pu A0qG0TO ecntF % potential p@roblemLdirecality' , HO rR Jw#to test your w%',< $/22ionu='with ? !edas( ($"u AS id/0*/||' UNIOl! 79)'1=3@_AeIdead endswait...ͥ0~3|8ing_a.v,6AS cn\ts$F*/ ) a GROUP BYxing tZQ ,tot|al bntsAANDt#0/ ]L?1 O /[else=+Tend as totcnversA$1a lefTjoin?kt using(id) >)M *$%. UP0DATE/~set= +Fb$a.id=b.i~dG5j^zqLi[|~|rU?Q tnt=1"N8GiRQP @num?Q[QZU2qanalio7 ҟo gapo %, d?S@bufferLs4t_(*Q>ol`erancR="u ff/W _xverypclosz,1,crosses(R1,b.flagWoU<1kBbANb on &&?u}!B/2mMOBhk=1;distin8/v=(H O O GET DIAGNOSTICS A != ROW_COUNTjO*O-??࿼ ;? isolated edge? o kq@]2,/ ibXEcW 2j22e 9b.wtH=cEcE_?Num~I$mI?k?i[;?sDr aetri?t G'$!5ytypee 5g limit 1geoCEkIF (='MULTILINESTRING')f8?sHO!st_isRjst_linemergk?oAMiJ ELS#G HND IF? OK';tEND|Vle^}~"(:Tab~) } ''{"id" ,"cnthk@"}''::1:[] UPDA ||zjidenth||' SET cnt=0 ,chk=0+ mCEPTION WHEN raise_excepA THEEP5>ERROR: @somethQwent wro5OqtheiRETURN 'FAIL3lcolumnAes inb eSELECxT *ld*:RC7J2S",3 AO2)LheLDPhPDPcVDRpS$>onError( IN ( ,b,}) OR/zx,!=, 2,( n3', 'Two$s share"ssnQ1Parameter$*for id and must be different'P\OK'B&?t9-????r/Typ%96 1?: S(S/ NOT in('integer','smalli!'bigi ) / WcoDf $N'||8e, ' Expec* # || 'L i,  or  but'7a@wa1und/ D{' ]\\_(\H\\){\]\O`|B O` ÖOaSRIDQpetryh b:=D 'DST_5(26dsR) AS'nBD>y )5WHERE6t/IS"NULL LIMIT 1 ӥQUERYT8ahZeaOZR.,?O'Can not bdq3mintR8o>f !g!in7S3), 7of%.'||s$csB yk IF-.  "%"%',^ 9?ND IFyn:=  ?NO THERSGotSQLERRM;--issue 210,21`1,213? O9 whenG>%F.ÆO^ ind›:acreateIndexE ,S, 'btree=xEA$h*A$3, 'gist=O] &+ ++ '#oq$7o /d'select count(*) fromexQcjtrue ,rows_wher8e?gc?0ecnt!C-- >RowsYcondi':KFOi9Oh wYOj Ca%Qcorrect. Please e xecutSfo0lloh=(  XH TN O`@mf declare geome1@ text; 2 srid  BEGIN /*percent_detection limite por el cual la intersecc#no sera valida, Enoc hangeDno habra cambio*/ EXECUTE 'create table __cespaci1 as select * from "'|| esch ||'".name:ia';Y2YoriZ SEL@ECT f_htry_column!o FROM !s WHERE f_VB_` = ''j;`2`&2drops=!C!CST_SRIDn(5-)Y group byL;'"r2r#s 2; /materialized viewi#eas re."IA/I", ORI 3 $::(G , 4326) AS#( ra.$t (ltJbpien,j ? filBt ''N1y4|''buIHaeHaHta |r4e.Bm,ia, (st_area(%) / ()) * 1|00`: 8  YST_INTERSECTIONUtransform set!@(pol3"7,'||"||'),,? 6?"w? ?)+1f8id,S), I IiaAO 2-Z , ?06 CSm)re)ta whera~4` > ''u|d|'1Nnd8(/)teb5> 1v_6, UNION ALL OuevO /q^O O O~O~K~, )re group by>R6A 'ta5HAVING @COUNT() =1)redOF?MkO #(;, _ ʏV?VO\a. O(O(O(>O( andI)/ A-b5> 1nochange _6, @OuevO /q^O O O~O~K~, )re group by>R6A2 'ta5HAVING COUNT() =1)redOFMkO #(;,  ʏV?VOda O0O0O0>O0 andI1/ )te b5> 1nochange _6, HOTE E`x;' BEGIN --concav zonificando los edios EXECUTE 'drop table if exists "'|| esch ||'".tbl_salida _v ";';KcreateMC as select st_col ionextract(st _make-(geom),3)5A fromle0ntramin(dex >_ONUSING GpIST v0edi_unionz,G~cd ?/im1qEbu /3/ t <plongitud_callesn/)&///D/+)a.7 ,/Z _( Ȅ$OwN_sue\ltO5MzL1C),''Points without ''errores, p#5r1%,AJ1, r2 2׳}UAsy[,u/j") r1  " r2 $on<> ndQ!qexp (,1000) &&?(FN*!) = 0)aF>2P1On7/elete P" b  = b.idM ?w0c3A2CO4q@:P9T1S8N<R@dYGd_3diferenciafV5P3˓S;U&!M-z END; R J DECLARE edges_SQL TEXT;quoted eryhas_geom BOOLEAN := TRUEsource @points idfnNamerec RECORD; BEGIN (= 'pgr_extractVertic es'; %-- get theD = '.*' || $1'\s*aH("= forma0t($$SELECT regexp_replace( statement, %1$L,'','i'),';$','') FROM pg_prepared_2s WHERE n%2$L$$, , $1)EXECUTEINTO'$'CEPTIONgN OTHERS ^T;sqld$1gEN IF(mIS NULL9J A IFis executable'{*>('||||' ) AS __a__ limit 1 RAISE-'%', SQLERRM )USING HINTPlease check{: '"zRETURNb--1<G identifierid: ^pg_typeopf(id$i)#r ec DY:= FALSDR*NOT dryrun AND1 IIFBu.5IN ('smallint1nteger','big)/ `ExpecAoY of colum@n "id"!ANY-INTEGER'/A-5V )DQetry?/>b ?_`?fck start-, end  t s`-targety/, 06?p)` s_t,) t_t -OdO5O s_tOQOFP#OWI&FG5_--id.,B*끗qWITH;mainAS ($q$O3F),Dthe_out C ::BIG AS out_U, PST_SAPA;(2F2p~agg~ array _agg(z ORDER BYx sxxzYydGpROUPi /:in9in8End62z5v4u 2+in1dl5coalesce($,.`, E2 OUTER` JOINJ3(x, y)Now_number() over([ST_X4@jd,4om:$q$D[ELSM^Q?Ob p%can notins (andsO\sub_H/'O} ?h ^?l _ DISTINXQ:L y,/  y&? 1Y8T4? _=ʇ?0?NULLU[]??$? 4/+?""#kH_%O G-\?O >9source+, targe?A S vXfs{/^X]o~U{? _# o3vid/ vido #FLOAT !etry/ /o/}/8*/$UNIO=$_L_VOy4E&RAISE EXCEPTION 'Missing column')#H`= 'Please cheDck: '1AeND IF IF dryrun)zNOTICE '%',$ || ';'; RETURN QUERY EXECUl1.(PsRAS l1idG` l232 3fw13ine3"start(vRq FendDS@Dst_closestPF ," h;eRX({ rows_wherK Ql1OJOINO32OON (sHt_d!in1,toleranBcr)'||p'3O/<>/ QANDt_equalsy)T ),/)=false}8/{ I_{ cj?:R"RmdStsecond^mlocus ()of_} on theB-- to avoid upd ata/!prev0ious_*we keep only_ occuBrbrontoi,@ not a}ets enda @-- droif existterao-'o-id1o~(CNUC SD`ISTIN \(ST_DumpRv"sd)).bASQfjfooaCH<>0 and31/z/y $[n _iVx(2)HThen compu5h(P(sFset, which is much smaller than full set]aRre are very f,ew0-Eab@ needs1be for`yRdd,,id,the2,usXso it canSinme@diatlyusUbTopologyP_RETINSE߯sL ( WITH cut##ions AS ?3!1wAEgenerRend4Bfor eacht haveqcut buy< a.aUNION ALL?li.06 i LEFTl]' bFb= b QR1mORDER BY)FS am_nupmberD i0nputf\be Rto sel`f-joi3St oa#between two consecutive&A`19Bb ^,() OVER (PARTIT4Rxidx:%finA,origis3 uG-ar referencfunBj 1. 4lcRDloc2 }: 4M# 2A(lid)/l" =id{idx+w--sbt/etrie3AρyType(gC)Q'LINESTRING'') ~ GEAqAGNOSTIC S splt= ROW_COUNT9'bdiff2QMc[Ԑo CTFcount(*)?d'ctouched-- q,q.misse`s all#t `7Fo(sтSYM{O{ b  =6'4'y!NO[O*W)r ows_w!P_ou=un$;A6kNOTIC S1@ Edges: ў( ,UU/j1Tota(i+ BD1vd;3New segme2_g 7T14#p*-'IF EXISTSNZ"WRETURN 'OK'; END; ??,x  @P0xTi m`[ declare geome1 text; srid camposcBEGIN /*Descripcion: Saca errores topológicos */ EXECUTE 'create table __cespaci1 as select * from "'|| esch ||'".aname:'; SELECT f_try_column into FROM !s WHERE f_RB_\ = '';drop !ST_SRIDn() group byJ;'; ExecuteImaterialized viewedlrow_number() OVER () AS id ||', id,, st_transform(@St_set(Geom,'||||'),4326)::~" <,  ( J g!zassconcat(''Cies with union points'','': id '',By 2k"", s0eudoy (c1.idH max (c2.idid2,$BO, co@unt (*)n uY Y(st_dump-')Rk?) c1, `l,a y2 where-<> and && c2sndistance (%,"l))=0 &U,%h)as2}arX= 1  #all %F'E''JunctiDon#=out#@''"D,( Pr% "r2,/;ast_in`tersebuffer(a\Q0),b)e  a& b| c  s`t_relA*, ''T*''&)[K>b2vr1 left join?MIr2 on'',r#) = 02)id2 i1ll/P/ ['%"/#-I1 c/`r2 b<>#FexpB&q 1000)!wr2/x  Xinct on H(ra r2 ''HolPlow est_centroi=og rexre.contiene,qtouches (ST_B36st_fo rce2d makevalid>ll))1PZ)NB#id <a, (ains ((%xounpdaryxMulti S$), ST_ExorRin>gna,`  I wR w) re) ra $ra&FF1,ra.$T= ''true''/gidaFIsoQd?#HST_BOnSurface%XpolygonST_ColaionExt ract(orno,2tKtta,![macltN1._ tUgHidb2t1Otsb=<>sCvs ,tNn`O Repd vertexAMqpGy*,l2 M po8anot@asewkbkremoveru =6r GROUP BY HAVING COUNT(*)>1 HRs duplic7joriginalfs ^jPC"Q.quals1SnapToGѸ 0.005c=(()/LlappingMie߹6չSOq[8P uUlre'; EXECUTE0 'cr1xque index!view(id YND; T] `` makep_a/c_idXPK.LX3/Z/N$/D%fbuffer_id3N(Mz ,0.05/W!+Fo ?0ntersec?Q4Q 8\areaz 4 (S,S))T (X) a, $b dG s @ /D&d@?,UoAImax-V+Q#Imax(!!) _%group by[O?orioga./a, D*a.7 =b.Znd a. = b o*/C/. A b.*,y/^ y joinAson |Oj( 2CBh o_boundaryY?ext eriorYG_poligc@qDY-HR'vdO;/ߟY:]'S4j V'_YGdSND; T] ``@N4BEGIN /*Descripcion: une los atributos iniciales a las capas generalizadas */ EXECUTE 'create table "'|| esch ||'".name||'_tipo" as select2pk_field6, st_union (geom) om fromc" gr@oup by\; disso0lve" (s@t_dump).; @CREATE_simplify" AS SELECT st0preservetop ology, +6#y4) @ FROM;; TABLE!2" as fid,st_area(< (row_number() OVER ()H ASY 1 ST_GeomFromText(ST_As  Dump*f)&@ / )t(G&_anillos" AS ).A%rings force2d([s23AS0Y(a9Ua/SKJ~ HBE!!fid , eZ7 ?^UNION ST_BufferC&%toleranc ^)1; UPD!`%SET)=ST_makevaliDWHERE NOTisV{ o,dD?eOvC& ;822 5>c ;9o4  [1 ;!r3ST_ColionExtract(,3(OcUM), R3)/((,Peri meter9)^B2ssrela, 4" 7N YsM)e*o; INDEX z_g _'_gidxBONUSING` gistsT& tbl_sQ\a2'a.*, bJ2%" a @join b` on a%;=D bandQintersectsBsetsri d(repsgrai; alter dr$op; ;reCtoF%add column ida serial; DELETE#H sXBIN#/$idxI ROW_@NUMBERTq( PARTITI`ON BYW-ORDERvRT#?Z ) #t.J > 1 ?v E_/cedg?w?l "!Ob ;937"@-A+|l C0J7O;/J" !aALTER<.COLUMN2 TYPE etry( VJ)4MST_SetSRIDǸaU; _((1a dou:precision;Q)1UupdqiPsezt=DDEC=v Ƴ//0.079577471546 7 {I*C where>''/miC3''r1> (#pedri"+^2/ t;'; EXECUTE '[ ()ovbas id_todoz,2 c ;_tmp o oFB.]R  2Rqjfo*X106)/RFboundary+`$bas (DeI5R.[_8ext`erior_ringc/!_:_//h&_poligZ#ST_MakePolygoncm&8&MST'D+M/-*} /67Ο/>/}OQ7_ Vif existswcontainsO7Q!k $a/}9$,/.b ST_CProperly (b,/13/-/e//%-hueco/3J jTO Q` O~O~K~, )re group by>R6A2 'ta5HAVING COUNT() =1)redOFMkO #(;,  ʏV?V ''#h||'1Nndc8(/)teb5> 1v_6, UNION ALL OuevO /q^O O O~GO~K~, )re group by>R6A ' ta5HAVING COUNT() =1)redOFMkO #(;,  ʏV?VrO\a O(O(O(>O( andI)/ A-b5> 1nochange _6, @OxuevO /q^O O O~O~K~, )re group by>R6A 'ta5HAVING @COUNT() =1)redOF?MkO #(;, _ ʏV?VOda O0O0O0>O0 andI1/ X)teb5> 1nochange _6, HOuevO w/q^O OC(  Șh T `@>> declare geoma text; srid1 BEGIN ---función por terminar queda darle una vuelta al tema de ifs Execute 'create table __cespaci1 as select * from "'|| sch ||'".tbl'; SEL@ECT f_etry_column into aFROM  s WHERE  f__name = 'adrop'; EXECUTE 'ST_SRID() group b:yB'if existsZschXtbl||'_apoyo" ;}8 /"`index 0_ ]_idx ON USING GIST ( 6)ohex01 if h3 _zoom1' then Fcountp,h3_ "h3_to_parent,''1''ide,a.3L ($I(*) ASU 0 a.\st_set!(h3_h3hgeoboundary(5), 4326)::%(G >,o]2#( a_1.id_polz_  >2>h3(st_centroid(>, 1qy3y Cpdumptransformo _I,"1||'P))> row_number() OVpER (t7|+| }Ron es.__!r_@teselamakevalidB:{2G, ''9''::dou1precision/f? ) tV)21GROUP BY*,() a '7  a1 END IF IF"B >B AOcOO OO,l b where1hco@ntains/"b/)4킵end if-Bf#C2Z22_2A O_WAF (2r.*,b.ideETs "a.* IA_1),''2VXX_O2_Oq_Or2.5_Q_Sw)r left jpoin KQTon b5=r 6) tTt2is null_ 2_ j2_  {ڏ,y sy!o@ntains/*bs!/1,a#џTT end if; tdroprif existjsV7D "= '7' 7u[Úu,58HT ` DECLARE begin --num_city es el numero de ciudades maximo 50 execute 'drop view if exists "'||esch||'$". vw '; 8create:0 as select ''