[Talk-ro] Import în masă a localităților (thread #2)
Cristian Draghici
cristian.draghici at gmail.com
Thu Jun 18 14:32:20 BST 2009
Salut
Cu urmatoarele SQL-uri (posgresql, postgis) am facut o detectie a
duplicatelor existente in extrasul de azi 18 Jun luat de pe stalpu.org:
create or replace function strip_diac(varchar) returns varchar as $$DECLARE
src ALIAS FOR $1; result VARCHAR;
BEGIN result := TRANSLATE(src,
E'\xc3\x82\xc3\x83\xc3\xa2\xc3\xa3\xc4\x82\xc3\x8e\xc3\xae\xc5\x9e\xc5\x9f\xc5\xa2\xc3\xa3\xc7\x8d\xc7\x8e\xc8\x98\xc8\x99\xc8\x9a\xc8\x9B\xc3\x83',
'AaaaAIiSsTtAaSsTtA');
return result;END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
select p1.name, p2.name, p1.osm_id, p2.osm_id, p1.place, p2.place,
distance(p1.way, p2.way) from planet_osm_point p1, planet_osm_point p2
where p1.place !='' and p2.place!='' and p1.osm_id != p2.osm_id and
distance(p1.way, p2.way) < 5000 and lower(strip_diac(p1.name)) =
lower(strip_diac(p2.name)) order by strip_diac(p1.name), p1.osm_id desc;
Secventele de escape diacrice sunt cele folosite in xybot.
Fisierul de duplicate (160 de intrari) este atasat. Cele cu cel putin un
OSM_ID > 400 de milioane sunt cele din importul recent al lui Nini, restul
sunt istorice.
Planul nostru este sa re-rulam detectia de duplicate si reconcilierea
efectiva dupa ce terminam cu importul.
Daca aveti alte propuneri pentru continuarea importului, va rugam sa ni le
comunicati.
Multumesc,
Cristi
2009/6/18 Eddy Petrișor <eddy.petrisor at gmail.com>
> În data de 17 iunie 2009, 23:08, Francisc TOTH <yo6own at yahoo.com> a scris:
> >
> > Eu am folosit JOSM: am comparat datele sa fiu sigur ca nu distrug vreo
> informatie, am copiat datele noi peste
> > cele vechi cu CTRL-C, CTRL-SHIFT-V si am sters punctul vechi. Nu am mutat
> punctul vechi, de obicei era plasat
>
> Datele VECHI trebuie să aibă precedență față de datele noi, exceptând
> câteva situații particulare!
>
> De obicei datele existente sunt obținute din sondaje locale sau surse
> locale, deci au șanse să fie mai corecte decât cele din import.
>
> Asta este una din modalitățile prin care importul o ia razna și face
> mai mult rău decât bine.
>
>
> > mai la drum decat punctul nou, pe care intr-un caz l-am gasit exact pe
> calea ferata. In sate centrul e traditional
> > mai pe langa biserica, in oras pe langa primarie. Daca tot eram cu
> editarea am preschimbat si codul postal
> > vechi cu cel nou si gata.
>
> Poate era util să se păstreze codul pustal vechi într-un câmp dedicat
> (old_postal_code sau siruta:old_postal_code), așa cum am sugerat.
>
>
> --
> Regards,
> EddyP
> =============================================
> "Imagination is more important than knowledge" A.Einstein
>
> _______________________________________________
> Talk-ro mailing list
> Talk-ro at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/talk-ro
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/talk-ro/attachments/20090618/6a9f763b/attachment.html>
-------------- next part --------------
name | name | osm_id | osm_id | place | place | distance
------------------+------------------+-----------+-----------+---------+---------+-------------------
Agrișu Mare | Agrisu Mare | 422878260 | 416270970 | village | village | 381.752535165326
Albești | Albești | 422879794 | 150584260 | village | village | 319.055276527564
Aldești | Aldești | 422877657 | 171343156 | village | village | 477.743447670973
Aluniș | Aluniș | 422877522 | 171295528 | village | village | 244.373317753315
Arad | Arad | 422877520 | 25721593 | city | city | 1929.19290245303
Arăneag | Arăneag | 422878265 | 416270972 | village | village | 249.356651684195
Avram Iancu | Avram Iancu | 422878411 | 316124305 | village | village | 1073.65557121819
Bârsa | Bârsa | 422877650 | 171345081 | village | village | 1137.50084412498
Bârzava | Bârzava | 422877654 | 34088329 | village | village | 2036.54170478054
Bascov | Bascov | 422879620 | 295841294 | village | village | 1523.15647497867
Bata | Bata | 422877641 | 34088389 | village | village | 613.983096553286
Berechiu | Berechiu | 422877624 | 262324890 | village | village | 541.905832743884
Birchiș | Birchiș | 422877646 | 34088354 | village | village | 456.519873762174
Bobărești | Bobărești | 422871209 | 422870404 | village | village | 4128.57116878007
Bocsig | Bocsig | 422877669 | 137708596 | village | village | 350.849181758216
Bodrogu Nou | Bodrogu Nou | 422877727 | 243727271 | village | village | 1641.75387438858
Bohotin | Bohotin | 383436192 | 383434024 | hamlet | hamlet | 1969.88215285569
Bonțești | Bonțești | 422877753 | 171357710 | village | village | 329.155427327052
Boteni | Boteni | 422880066 | 295837869 | village | village | 1403.11449773649
Buciumeni | Buciumeni | 280268226 | 280256458 | village | village | 165.742080800018
Buftea | Buftea | 255591401 | 166396372 | town | suburb | 530.650142467365
Bulci | Bulci | 422877639 | 302380433 | village | village | 310.833325751957
Buteni | Buteni | 422877679 | 171346899 | village | village | 778.282710224136
Bătuța | Bătuța | 422877661 | 307503725 | village | village | 219.97993779341
Camna | Camna | 422878170 | 394035456 | village | village | 366.73831828241
Cermei | Cermei | 422877686 | 262324569 | village | village | 411.202176811699
Chier | Chier | 422878249 | 207386458 | village | village | 423.711489628983
Chisindia | Chisindia | 422877682 | 171348605 | village | village | 516.168190902057
Cil | Cil | 422877615 | 316125061 | village | village | 347.723946141695
Cilieni | Cilieni | 400914641 | 346195907 | village | village | 3941.34839131743
Cladova | Cladova | 422877990 | 389554888 | village | village | 590.816201953194
Clucereasa | Clucereasa | 422879647 | 295837868 | village | village | 510.631854262156
Cobleș | Cobles | 422868656 | 260581619 | village | hamlet | 1963.06835762296
Colibași | Colibasi | 422879649 | 301131843 | village | village | 1059.74478285579
Conop | Conop | 422877684 | 34088348 | village | village | 523.391931708821
Costești | Costești | 422879704 | 140142216 | town | village | 991.238212994324
Covăsinț | Covăsinț | 422877698 | 171374807 | village | village | 775.328640382267
Crevedia | Crevedia | 255598653 | 248416016 | village | village | 996.235779659851
Cruceni | Cruceni | 422878239 | 267474212 | village | village | 279.019113176357
Cuceu | Cuceu | 280528257 | 280528255 | village | village | 0.646099712640986
Cuied | Cuied | 422877671 | 278454426 | village | village | 948.255508664295
Curtea de Argeș | Curtea de Argeș | 422879715 | 129642766 | city | town | 1348.04366385864
Curtici | Curtici | 422877577 | 114362107 | town | town | 609.570835607906
Cuvin | Cuvin | 422877739 | 171317999 | village | village | 514.022163048958
Călugăreni | Călugăreni | 422877742 | 243727439 | village | village | 288.497229815794
Căprioara | Căprioara | 422878046 | 317309986 | village | village | 230.336756030958
Căpruța | Căpruța | 422877660 | 307504891 | village | village | 467.760734258895
Dâmbovicioara | Dâmbovicioara | 422880781 | 259423619 | village | village | 750.172748136919
Davidești | Davidesti | 422880747 | 295836507 | village | village | 1108.45238243926
Dieci | Dieci | 422877721 | 171355654 | village | village | 238.865834194967
Dolina | Dolina | 412399798 | 412399702 | place | place | 114.354464666494
Domnești | Domnesti | 422880624 | 317657425 | village | village | 221.040125814221
Dorgoș | Dorgos | 422877733 | 34088397 | village | village | 446.812771203315
Dorobanți | Dorobanți | 422877559 | 129708850 | village | village | 430.016654256336
Dragoslavele | Dragoslavele | 422880658 | 301133952 | village | village | 426.232499536397
Drauț | Drauț | 422878254 | 416270971 | village | village | 539.558507725161
Dumitrana | Dumitrana | 310191138 | 310191129 | village | village | 408.126704117295
Dumitrana | Dumitrana | 310191129 | 310191138 | village | village | 408.126704117295
Dumitrana | Dumitrana | 275397576 | 310191138 | village | village | 884.067778109265
Fântânele | Fântânele | 422877521 | 34088274 | village | village | 435.880696407593
Felnac | Felnac | 422877728 | 34088279 | village | village | 178.271038727167
Firiteaz | Firiteaz | 422878234 | 267474232 | village | village | 197.585734787846
Frumușeni | Frumușeni | 422877524 | 171292877 | village | village | 334.405428970898
Galda de Jos | Galda de Jos | 422869624 | 375528772 | village | village | 515.795225535533
Galșa | Galșa | 422878311 | 137639213 | village | village | 227.53792560219
Ghioroc | Ghioroc | 422877740 | 34088310 | village | village | 562.787699348614
Godeni | Godeni | 422880670 | 301126653 | village | village | 940.584290633508
Grozești | Grozești | 366109133 | 266203108 | village | village | 2971.52018582829
Gurahonț | Gurahont | 422877735 | 132543761 | village | village | 716.648324038515
Gurba | Gurba | 422878199 | 262323300 | village | village | 535.170341912834
Hârtiești | Hartiesti | 422880804 | 295836505 | village | village | 176.450628988135
Homocea | Homocea | 366273306 | 280289078 | village | village | 3552.10656084518
Horia | Horia | 422877552 | 130500517 | village | village | 571.917589672809
Ighiu | Ighiu | 422869725 | 390754487 | village | village | 254.311826213855
Ineu | Ineu | 422877583 | 130503003 | town | town | 195.823219995893
Joia Mare | Joia Mare | 422877627 | 171351349 | village | village | 316.68644347598
Joita | Joița | 255589804 | 246497243 | village | village | 757.870958815433
Leasa | Leasa | 422877749 | 316124301 | village | village | 186.467494855303
Leordeni | Leordeni | 422880812 | 295840777 | village | village | 1946.8230423537
Lerești | Leresti | 422880795 | 295843379 | village | village | 912.328565622039
Lipova | Lipova | 422877589 | 34088255 | town | town | 934.502937836932
Livada | Livada | 422877528 | 137629673 | village | village | 300.559573214143
Luguzău | Luguzău | 422878162 | 394034462 | village | village | 633.962703331956
Lăzești | Lăzești | 422871134 | 422870322 | village | village | 3283.58522870843
Macea | Macea | 422877938 | 119866632 | village | village | 706.952986886022
Mailat | Mailat | 422878416 | 243726709 | village | village | 354.79559719965
Mândruloc | Mândruloc | 422877553 | 124238393 | village | village | 672.540980914007
Mânerău | Mânerău | 422877662 | 171339649 | village | village | 376.537886572631
Merișani | Merisani | 422880851 | 296077323 | village | village | 1314.77959137283
Miniș | Miniș | 422877738 | 171315656 | village | village | 239.435920096804
Mioveni | Mioveni | 422879646 | 129639790 | town | town | 631.710664588705
Mocrea | Mocrea | 422877579 | 207388436 | village | village | 363.257465725604
Munar | Munar | 422878149 | 243726714 | village | village | 123.965598544288
Măcărești | Măcărești | 385677881 | 263024847 | hamlet | village | 2657.99371680567
Măderat | Măderat | 422877599 | 137645196 | village | village | 1009.43985393455
Mănăștur | Mănăștur | 422878423 | 243726708 | village | village | 126.905401047736
Nadab | Nadab | 422877555 | 264485931 | village | village | 191.010156659435
Neudorf | Neudorf | 422878349 | 171301830 | village | village | 185.1759025754
Nucșoara | Nucsoara | 422881046 | 386843910 | village | village | 2095.90752643866
Nădlac | Nădlac | 422877591 | 34088260 | town | town | 576.609200541434
Oarja | Oarja | 422880997 | 392896090 | village | town | 693.869764192383
Olari | Olari | 422877902 | 207396655 | village | village | 17.6316944448408
Pecica | Pecica | 422878019 | 34088253 | town | town | 649.358427407807
Petriș | Petris | 422877954 | 34088363 | village | village | 1880.57568400078
Pilu | Pilu | 422878070 | 317371150 | village | village | 64.9307691478716
Pitești | Pitești | 422879619 | 126383941 | city | city | 513.543766152387
Pleșcuța | Pleșcuța | 422878079 | 316124926 | village | village | 75.8476937963181
Prosia | Prosia | 422880865 | 317657905 | village | village | 843.218129583657
Păuleasca | Păuleasca | 422880852 | 422880839 | village | village | 1625.39436853765
Păuliș | Păuliș | 422878000 | 34088304 | village | village | 388.720010840068
Radna | Radna | 422877586 | 137582774 | village | suburb | 408.034859301999
Rosiori | Rosiori | 406136052 | 406135356 | village | village | 3977.34520532803
Rucăr | Rucăr | 422881251 | 130607410 | village | village | 132.071014459235
Răpsig | Răpsig | 422877663 | 171341388 | village | village | 198.540442984582
Șagu | Sagu | 422878244 | 34088315 | village | village | 273.775849841149
Sâmbăteni | Sâmbăteni | 422877995 | 114236882 | village | village | 425.262905908066
Sânpaul | Sânpaul | 422877544 | 243531619 | village | village | 459.502228939792
Sânpetru German | Sânpetru German | 422878123 | 243727472 | village | village | 766.120897513479
Șard | Sard | 422869795 | 260397731 | village | village | 1288.42150133866
Satu Mare | Satu Mare | 422878119 | 243726713 | village | village | 166.904636105847
Sebiș | Sebiș | 422877609 | 130406730 | town | village | 296.632236524487
Secusigiu | Secusigiu | 422878145 | 34088273 | village | village | 739.749039977442
Șeitin | Seitin | 422878218 | 34088331 | village | village | 901.358550885324
Seleuș | Seleus | 422878128 | 264484967 | village | village | 181.9591647582
Semlac | Semlac | 422878107 | 34088314 | village | village | 667.941895960267
Șicula | Sicula | 422878209 | 264485054 | village | village | 117.454166339332
Șimand | Șimand | 422878154 | 119844649 | village | village | 279.762767425302
Sintea Mare | Sintea Mare | 422878111 | 137660806 | village | village | 321.365445498077
Sintea Mică | Sintea Mică | 422877897 | 207396266 | village | village | 64.8814133470651
Șiria | Șiria | 422878306 | 137634171 | village | village | 940.998434185806
Socodor | Socodor | 422878098 | 128105834 | village | village | 670.599012924153
Șofronea | Sofronea | 422877539 | 114361647 | village | village | 404.17739544772
Șomoșcheș | Somosches | 422877689 | 262324640 | village | village | 808.862481712971
Stâlpeni | Stalpeni | 422881163 | 301137858 | village | village | 1361.24412790174
Stolnici | Stolnici | 422881355 | 383847026 | village | village | 2290.75852477765
Săvârșin | Săvârșin | 422878043 | 34088325 | village | village | 483.434424266452
Târnova | Târnova | 422878274 | 416270965 | village | village | 1610.66862921423
Tauț | Tauț | 422878294 | 137705471 | village | village | 166.401922139416
Teiuș | Teiuș | 422870744 | 114310477 | town | town | 364.818775921578
Topoloveni | Topoloveni | 422879741 | 135038276 | town | town | 628.802569194191
Turnu | Turnu | 422878014 | 119869598 | village | village | 454.973085402135
Tăriceni | Tăriceni | 284177050 | 284176641 | village | village | 422.991003836517
Uiasca | Uiasca | 422879634 | 356267877 | village | village | 293.527704918936
Ungheni | Ungheni | 385677300 | 261076253 | village | town | 1657.52758157327
Ususău | Ususău | 422877730 | 137703996 | village | village | 461.239990381313
Valea Faurului | Valea Faurului | 422880866 | 317657616 | village | village | 833.072223201076
Valea Iașului | Valea Iasului | 422881443 | 317657624 | village | village | 269.431375804078
Valea Mare | Valea Mare | 422878141 | 280953963 | village | village | 131.102235439895
Vârfurile | Vârfurile | 422878407 | 195602958 | village | village | 313.886216822523
Vinerea | Vinerea | 422868394 | 290135976 | village | village | 46.0449869055464
Vinga | Vinga | 422878394 | 34088266 | village | village | 885.513102054709
Vladimirescu | Vladimirescu | 422877547 | 34088257 | village | village | 520.957598977028
Vulturești | Vulturesti | 422880810 | 295836506 | village | village | 2549.15130011227
Vărșand | Vărșand | 422878066 | 128104842 | village | village | 413.976326176831
Vărădia de Mureș | Vărădia de Mureș | 422878385 | 34088356 | village | village | 1827.5428082597
Zerind | Zerind | 422878335 | 119839814 | village | village | 322.053750411243
Zimandu Nou | Zimandu Nou | 422878362 | 119846475 | village | village | 597.985496523219
Zăbrani | Zăbrani | 422878320 | 34088296 | village | village | 870.680107041041
Zădăreni | Zădăreni | 422877741 | 243727159 | village | village | 651.424269785733
Zărand | Zărand | 422878344 | 137702027 | village | village | 160.788750623179
(320 rows)
More information about the Talk-ro
mailing list