number of errors=49
number of warnings=0
country=IS
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='IS' 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/IS785Öræfum.kml IS null Öræfum 785 null 1.048683791037799 f http://qa.geonames.org/qa/kml/IS512Hólmavík.kml IS null Hólmavík 512 3416144 0.4281660269982103 f http://qa.geonames.org/qa/kml/IS645Fosshólli.kml IS null Fosshólli 645 null 0.3621553681452931 f http://qa.geonames.org/qa/kml/IS345Flatey á Breiðafirði.kml IS null Flatey á Breiðafirði 345 null 0.2979472327572884 f http://qa.geonames.org/qa/kml/IS524Norðurfirði.kml IS null Norðurfirði 524 null 0.20475202680982477 f http://qa.geonames.org/qa/kml/IS401Ísafirði.kml IS null Ísafirði 401 3415667 0.16378081327049088 t http://qa.geonames.org/qa/kml/IS113Reykjavík - 13.kml IS null Reykjavík - 13 113 3413829 0.11079694885351324 f http://qa.geonames.org/qa/kml/IS116Kjalarnesi.kml IS null Kjalarnesi 116 null 0.0913130088732326 f http://qa.geonames.org/qa/kml/IS276Mosfellsbæ.kml IS null Mosfellsbæ 276 3415021 0.08931859179151311 f http://qa.geonames.org/qa/kml/IS111Reykjavík - 11.kml IS null Reykjavík - 11 111 3413829 0.0710161114965756 f http://qa.geonames.org/qa/kml/IS233Reykjanesbæ.kml IS null Reykjanesbæ 233 8644037 0.06989240969665789 f http://qa.geonames.org/qa/kml/IS112Reykjavík - 12.kml IS null Reykjavík - 12 112 3413829 0.0691640695399202 f http://qa.geonames.org/qa/kml/IS203Kópavogi.kml IS null Kópavogi 203 3415212 0.057097257733643186 f http://qa.geonames.org/qa/kml/IS107Reykjavík - 7.kml IS null Reykjavík - 7 107 3413829 0.05351735331699716 f http://qa.geonames.org/qa/kml/IS110Reykjavík - 10.kml IS null Reykjavík - 10 110 3413829 0.052148344009235265 f http://qa.geonames.org/qa/kml/IS109Reykjavík - 9.kml IS null Reykjavík - 9 109 3413829 0.045338636564812296 f http://qa.geonames.org/qa/kml/IS625Ólafsfirði.kml IS null Ólafsfirði 625 null 0.044502477377505234 t http://qa.geonames.org/qa/kml/IS235Keflavíkurflugvelli.kml IS null Keflavíkurflugvelli 235 null 0.03986459911591329 f http://qa.geonames.org/qa/kml/IS715Mjóafirði.kml IS null Mjóafirði 715 null 0.03959222184238088 t http://qa.geonames.org/qa/kml/IS451Patreksfirði.kml IS null Patreksfirði 451 null 0.03772550344792922 t http://qa.geonames.org/qa/kml/IS271Mosfellsbæ.kml IS null Mosfellsbæ 271 3415021 0.02977099133588845 t http://qa.geonames.org/qa/kml/IS601Akureyri.kml IS null Akureyri 601 2633274 0.028454986362413313 t http://qa.geonames.org/qa/kml/IS101Reykjavík - 1.kml IS null Reykjavík - 1 101 3413829 0.021556507120508113 f http://qa.geonames.org/qa/kml/IS471Þingeyri.kml IS null Þingeyri 471 3412655 0.019576311071230194 t http://qa.geonames.org/qa/kml/IS641Húsavík.kml IS null Húsavík 641 2629833 0.018893122817362606 t http://qa.geonames.org/qa/kml/IS301Akranesi.kml IS null Akranesi 301 3418653 0.018415220672162134 f http://qa.geonames.org/qa/kml/IS816Ölfus.kml IS null Ölfus 816 3412589 0.018109739624430805 t http://qa.geonames.org/qa/kml/IS701Egilsstöðum.kml IS null Egilsstöðum 701 2632132 0.017878480510657366 t http://qa.geonames.org/qa/kml/IS531Hvammstanga.kml IS null Hvammstanga 531 3415811 0.01667527780558482 t http://qa.geonames.org/qa/kml/IS551Sauðárkróki.kml IS null Sauðárkróki 551 2627309 0.015761956723100382 t http://qa.geonames.org/qa/kml/IS781Höfn í Hornafirði.kml IS null Höfn í Hornafirði 781 2630299 0.014430283042535564 t http://qa.geonames.org/qa/kml/IS603Akureyri.kml IS null Akureyri 603 2633274 0.013908276154919554 f http://qa.geonames.org/qa/kml/IS104Reykjavík - 4.kml IS null Reykjavík - 4 104 3413829 0.012691471709942502 f http://qa.geonames.org/qa/kml/IS311Borgarnesi.kml IS null Borgarnesi 311 3418076 0.01219589512699715 t http://qa.geonames.org/qa/kml/IS801Selfossi.kml IS null Selfossi 801 3413604 0.011513651806416697 t http://qa.geonames.org/qa/kml/IS201Kópavogi.kml IS null Kópavogi 201 3415212 0.010909417666494116 f http://qa.geonames.org/qa/kml/IS621Dalvík.kml IS null Dalvík 621 2632287 0.008333971414828677 t http://qa.geonames.org/qa/kml/IS871Vík.kml IS null Vík 871 2625252 0.007911093405034788 t http://qa.geonames.org/qa/kml/IS371Búðardal.kml IS null Búðardal 371 3417878 0.006627396097544461 t http://qa.geonames.org/qa/kml/IS541Blönduósi.kml IS null Blönduósi 541 3418130 0.00582452036768553 t http://qa.geonames.org/qa/kml/IS681Þórshöfn.kml IS null Þórshöfn 681 2626002 0.005707065622225551 t http://qa.geonames.org/qa/kml/IS861Hvolsvelli.kml IS null Hvolsvelli 861 3415720 0.004969501082918723 t http://qa.geonames.org/qa/kml/IS566Hofsós.kml IS null Hofsós 566 2630277 0.004121730264451416 t http://qa.geonames.org/qa/kml/IS260Reykjanesbæ.kml IS null Reykjanesbæ 260 8644037 0.0033933965594288573 f http://qa.geonames.org/qa/kml/IS851Hellu.kml IS null Hellu 851 3416453 0.003258859905310003 t http://qa.geonames.org/qa/kml/IS103Reykjavík - 3.kml IS null Reykjavík - 3 103 3413829 0.0027580186136061855 f http://qa.geonames.org/qa/kml/IS671Kópaskeri.kml IS null Kópaskeri 671 2629105 0.0008083064736243544 t http://qa.geonames.org/qa/kml/IS105Reykjavík - 5.kml IS null Reykjavík - 5 105 3413829 0.0005855897884127788 t http://qa.geonames.org/qa/kml/IS221Hafnarfirði.kml IS null Hafnarfirði 221 3416706 3.829493652244249e-05 t