number of errors=53
number of warnings=0
country=FO
Group=chkGrpPostalCode
select 'http://qa.geonames.org/qa/kml/' || zip_code.countrycode || zip_code.zip || zip_code.placename || '.kml' as kml,zip_code.countrycode,admincode1,placename,zip, zip_code.geonameid,st_distance(wkb_geometry,st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326)) as distance,st_within(st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326),st_envelope(wkb_geometry)) as inbbox from zip_code,postalcode_shape where zip_code.countrycode=postalcode_shape.countrycode and zip=postalcode and not st_within(st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326),wkb_geometry) and zip_code.countrycode='FO' and postalcode_shape.place=zip_code.placename order by distance desc
Make sure the zip code is unique!
fix with st_centroid or ST_PointOnSurface
create table tmp as select zip_code.countrycode,admincode1,placename,zip,st_distance(wkb_geometry,st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326)) as distance,st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326) as geom,wkb_geometry as polygon from zip_code,postalcode_shape where zip_code.countrycode=postalcode_shape.countrycode and zip=postalcode and not within(st_GeomFromText('Point(' || estlonglat[0] || ' ' || estlonglat[1] || ')',4326),wkb_geometry) and zip_code.countrycode='CA' order by distance desc;
update tmp set geom=st_centroid(wkb_geometry);
update tmp set geom=ST_PointOnSurface(polygon) where not st_within(geom,polygon);
update zip_code set estlonglat = point(st_x(geom), st_y(geom)) from tmp where tmp.zip=zip_code.zip and tmp.countrycode=zip_code.countrycode
kml countrycode admincode1 placename zip geonameid distance inbbox http://qa.geonames.org/qa/kml/FO286Stóra Dimun.kml FO null Stóra Dimun 286 null 0.29729634945446065 f http://qa.geonames.org/qa/kml/FO185Kaldbaksbotnur.kml FO null Kaldbaksbotnur 185 null 0.10260326554999183 f http://qa.geonames.org/qa/kml/FO795Sydradalur, Kalsoy.kml FO null Sydradalur, Kalsoy 795 null 0.08277903962196975 f http://qa.geonames.org/qa/kml/FO925Nes, Vágur.kml FO null Nes, Vágur 925 null 0.034529422403582304 f http://qa.geonames.org/qa/kml/FO336Skællingur.kml FO null Skællingur 336 null 0.021386710439526602 f http://qa.geonames.org/qa/kml/FO380Sørvágur.kml FO null Sørvágur 380 2612890 0.015776615006607195 t http://qa.geonames.org/qa/kml/FO511Gøtugjógv.kml FO null Gøtugjógv 511 2621294 0.014476309698463774 f http://qa.geonames.org/qa/kml/FO737Múli.kml FO null Múli 737 2616709 0.012744617533767643 t http://qa.geonames.org/qa/kml/FO180Kaldbak.kml FO null Kaldbak 180 2619192 0.012175263879863639 f http://qa.geonames.org/qa/kml/FO626Lambareidi.kml FO null Lambareidi 626 2618011 0.01196579273622406 f http://qa.geonames.org/qa/kml/FO438Langasandur.kml FO null Langasandur 438 2617970 0.010551371743889804 f http://qa.geonames.org/qa/kml/FO330Stykkid.kml FO null Stykkid 330 2612147 0.009272657672222642 t http://qa.geonames.org/qa/kml/FO656Saltnes.kml FO null Saltnes 656 2614295 0.008811539848371042 f http://qa.geonames.org/qa/kml/FO186Sund.kml FO null Sund 186 2612123 0.008467046387882385 f http://qa.geonames.org/qa/kml/FO520Leirvik.kml FO null Leirvik 520 2617844 0.008367730895449082 t http://qa.geonames.org/qa/kml/FO513Sydrugøta.kml FO null Sydrugøta 513 2611962 0.00816944211964895 t http://qa.geonames.org/qa/kml/FO475Funningur.kml FO null Funningur 475 2621765 0.007827994859567956 t http://qa.geonames.org/qa/kml/FO797Mikladalur.kml FO null Mikladalur 797 2616900 0.007782944835414722 t http://qa.geonames.org/qa/kml/FO796Húsar.kml FO null Húsar 796 2619615 0.007448796616823763 t http://qa.geonames.org/qa/kml/FO730Norddepil.kml FO null Norddepil 730 2616148 0.007403652678196872 f http://qa.geonames.org/qa/kml/FO440Haldarsvik.kml FO null Haldarsvik 440 2620913 0.007203853766498282 t http://qa.geonames.org/qa/kml/FO767Hattarvik.kml FO null Hattarvik 767 2620680 0.006950508177860659 f http://qa.geonames.org/qa/kml/FO285Koltur.kml FO null Koltur 285 2618487 0.006901512616458692 f http://qa.geonames.org/qa/kml/FO494Innan Glyvur.kml FO null Innan Glyvur 494 2619389 0.006181716788610837 f http://qa.geonames.org/qa/kml/FO620Runavik.kml FO null Runavik 620 2614402 0.006073991907905584 f http://qa.geonames.org/qa/kml/FO280Hestur.kml FO null Hestur 280 2620338 0.005664477489225682 t http://qa.geonames.org/qa/kml/FO666Gøtueidi.kml FO null Gøtueidi 666 2621296 0.00529471688905575 t http://qa.geonames.org/qa/kml/FO798Trøllanes.kml FO null Trøllanes 798 2611199 0.004928144372079002 t http://qa.geonames.org/qa/kml/FO726Ánir.kml FO null Ánir 726 2624689 0.0045284910537699945 t http://qa.geonames.org/qa/kml/FO735Depil.kml FO null Depil 735 2623031 0.003694227589285719 t http://qa.geonames.org/qa/kml/FO495Kolbanargjógv.kml FO null Kolbanargjógv 495 2618537 0.0031222135961808816 t http://qa.geonames.org/qa/kml/FO187Hvitanes.kml FO null Hvitanes 187 2619504 0.003100373884335456 f http://qa.geonames.org/qa/kml/FO665Skipanes.kml FO null Skipanes 665 2613735 0.0028623144746252735 t http://qa.geonames.org/qa/kml/FO480Skáli.kml FO null Skáli 480 2613915 0.002606400126521107 t http://qa.geonames.org/qa/kml/FO490Strendur.kml FO null Strendur 490 2612233 0.0025749298113036984 f http://qa.geonames.org/qa/kml/FO645Æduvik.kml FO null Æduvik 645 2625012 0.0024551318725737755 f http://qa.geonames.org/qa/kml/FO660Søldarfjørdur.kml FO null Søldarfjørdur 660 2613140 0.0022812485239831737 t http://qa.geonames.org/qa/kml/FO740Hvannasund.kml FO null Hvannasund 740 2619568 0.0021412423452097365 t http://qa.geonames.org/qa/kml/FO960Hov.kml FO null Hov 960 2619734 0.0020823337203726222 t http://qa.geonames.org/qa/kml/FO235Dalur.kml FO null Dalur 235 2623092 0.002001898310479636 t http://qa.geonames.org/qa/kml/FO270Nólsoy.kml FO null Nólsoy 270 2616437 0.0018621619235972004 t http://qa.geonames.org/qa/kml/FO860Sandvik.kml FO null Sandvik 860 2614203 0.0018004307073359108 t http://qa.geonames.org/qa/kml/FO850Hvalba.kml FO null Hvalba 850 2619596 0.0017190290513149477 t http://qa.geonames.org/qa/kml/FO766Kirkja.kml FO null Kirkja 766 2618855 0.0015970894621149163 t http://qa.geonames.org/qa/kml/FO358Válur.kml FO null Válur 358 2610762 0.0015548743251715708 f http://qa.geonames.org/qa/kml/FO640Rituvik.kml FO null Rituvik 640 2614699 0.001396847146299401 t http://qa.geonames.org/qa/kml/FO240Skopun.kml FO null Skopun 240 2613660 0.001226155272664126 t http://qa.geonames.org/qa/kml/FO178Nordradalur.kml FO null Nordradalur 178 2616143 0.0004987740718234786 t http://qa.geonames.org/qa/kml/FO437Nesvik.kml FO null Nesvik 437 2616491 0.0004915875670251227 t http://qa.geonames.org/qa/kml/FO410Kollafjørdur.kml FO null Kollafjørdur 410 2618511 0.0003484856519027712 t http://qa.geonames.org/qa/kml/FO827Øravik.kml FO null Øravik 827 2615720 0.0001651056816275042 t http://qa.geonames.org/qa/kml/FO220Skálavik.kml FO null Skálavik 220 2613921 0.00014582023886534247 t http://qa.geonames.org/qa/kml/FO450Oyri.kml FO null Oyri 450 2615253 2.2035605199436435e-05 t