number of errors=106
number of warnings=0
country=NL
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='NL' 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/NL3062Rotterdam.kml NL 11 Rotterdam 3062 null 0.5599410424113228 f http://qa.geonames.org/qa/kml/NL6431Hoensbroek.kml NL 05 Hoensbroek 6431 null 0.2126771207044379 f http://qa.geonames.org/qa/kml/NL4944Raamsdonk.kml NL 06 Raamsdonk 4944 null 0.10999404490299401 f http://qa.geonames.org/qa/kml/NL5449Rijkevoort-De Walsert.kml NL 06 Rijkevoort-De Walsert 5449 null 0.09269457148355166 f http://qa.geonames.org/qa/kml/NL4901Oosterhout.kml NL 06 Oosterhout 4901 null 0.0454218186057548 f http://qa.geonames.org/qa/kml/NL5114Castelre.kml NL 06 Castelre 5114 null 0.036838604164069914 f http://qa.geonames.org/qa/kml/NL1331Almere.kml NL 16 Almere 1331 null 0.034724747420550796 f http://qa.geonames.org/qa/kml/NL8872Midlum.kml NL 02 Midlum 8872 null 0.029856556415043933 f http://qa.geonames.org/qa/kml/NL6418Heerlen.kml NL 05 Heerlen 6418 null 0.02964254403172933 f http://qa.geonames.org/qa/kml/NL4931Geertruidenberg.kml NL 06 Geertruidenberg 4931 null 0.019271405797224663 f http://qa.geonames.org/qa/kml/NL3297Maasdam.kml NL 11 Maasdam 3297 2751299 0.018005861448279658 f http://qa.geonames.org/qa/kml/NL4941Raamsdonksveer.kml NL 06 Raamsdonksveer 4941 null 0.016282058869894794 f http://qa.geonames.org/qa/kml/NL6415Heerlen.kml NL 05 Heerlen 6415 2754652 0.015452936216981922 f http://qa.geonames.org/qa/kml/NL5445Landhorst.kml NL 06 Landhorst 5445 null 0.015143226021284236 f http://qa.geonames.org/qa/kml/NL4881Wernhout.kml NL 06 Wernhout 4881 2744824 0.014912951763305697 f http://qa.geonames.org/qa/kml/NL1309Almere.kml NL 16 Almere 1309 null 0.014212235742348898 t http://qa.geonames.org/qa/kml/NL9824Noordwijk.kml NL 04 Noordwijk 9824 null 0.011738091076852732 f http://qa.geonames.org/qa/kml/NL8278Kampen.kml NL 15 Kampen 8278 null 0.011059479040418657 f http://qa.geonames.org/qa/kml/NL9613Meerstad.kml NL 04 Meerstad 9613 11395901 0.009924743610244452 f http://qa.geonames.org/qa/kml/NL7814Emmen.kml NL 01 Emmen 7814 2756136 0.009905328809866212 t http://qa.geonames.org/qa/kml/NL5692Son en Breugel.kml NL 06 Son en Breugel 5692 11776989 0.009398577105813656 t http://qa.geonames.org/qa/kml/NL9614Harkstede GN.kml NL 04 Harkstede GN 9614 11746710 0.009132637195671854 t http://qa.geonames.org/qa/kml/NL6413Heerlen.kml NL 05 Heerlen 6413 null 0.007933770096935055 t http://qa.geonames.org/qa/kml/NL2212Noordwijkerhout.kml NL 11 Noordwijkerhout 2212 2749811 0.007807943097023663 f http://qa.geonames.org/qa/kml/NL7005Terborg.kml NL 03 Terborg 7005 null 0.007513391422622013 f http://qa.geonames.org/qa/kml/NL5674Nuenen.kml NL 06 Nuenen 5674 2749780 0.006744328903443365 t http://qa.geonames.org/qa/kml/NL6432Hoensbroek.kml NL 05 Hoensbroek 6432 2753996 0.0065582793544528735 f http://qa.geonames.org/qa/kml/NL7948Meppel.kml NL 01 Meppel 7948 null 0.0058924746885839225 t http://qa.geonames.org/qa/kml/NL5406Uden.kml NL 06 Uden 5406 2746005 0.004837579167693057 t http://qa.geonames.org/qa/kml/NL1787Julianadorp.kml NL 07 Julianadorp 1787 2753184 0.00481658763423468 t http://qa.geonames.org/qa/kml/NL5051Riel.kml NL 06 Riel 5051 2748164 0.004004019531635409 t http://qa.geonames.org/qa/kml/NL9734Groningen.kml NL 04 Groningen 9734 null 0.0037175253323468217 t http://qa.geonames.org/qa/kml/NL5053Goirle.kml NL 06 Goirle 5053 2755464 0.0036317893498704207 t http://qa.geonames.org/qa/kml/NL2692's-Gravenzande.kml NL 11 's-Gravenzande 2692 2747364 0.00339361966219992 t http://qa.geonames.org/qa/kml/NL9976Lauwersoog.kml NL 04 Lauwersoog 9976 2751849 0.0031645749135303545 t http://qa.geonames.org/qa/kml/NL7351Hoenderloo.kml NL 03 Hoenderloo 7351 2754004 0.0028132394181066022 t http://qa.geonames.org/qa/kml/NL8221Lelystad.kml NL 16 Lelystad 8221 null 0.0028060902964678856 t http://qa.geonames.org/qa/kml/NL7933Pesse.kml NL 01 Pesse 7933 2748643 0.002698392929673979 t http://qa.geonames.org/qa/kml/NL8631Loënga.kml NL 02 Loënga 8631 2751515 0.00261738767712145 t http://qa.geonames.org/qa/kml/NL6411Heerlen.kml NL 05 Heerlen 6411 2754652 0.0025046264045208385 f http://qa.geonames.org/qa/kml/NL8254Dronten.kml NL 16 Dronten 8254 2756559 0.0024413340585803796 t http://qa.geonames.org/qa/kml/NL4727Heerle.kml NL 06 Heerle 4727 2754653 0.002376901216837144 t http://qa.geonames.org/qa/kml/NL2019Haarlem.kml NL 07 Haarlem 2019 2755003 0.0023743693508842608 t http://qa.geonames.org/qa/kml/NL6704Wageningen.kml NL 03 Wageningen 6704 2745088 0.002289597487387404 t http://qa.geonames.org/qa/kml/NL1778Westerland.kml NL 07 Westerland 1778 2744720 0.0020716170834276146 f http://qa.geonames.org/qa/kml/NL2216Voorhout.kml NL 11 Voorhout 2216 2745311 0.0018834924521599823 t http://qa.geonames.org/qa/kml/NL5844Stevensbeek.kml NL 06 Stevensbeek 5844 2746716 0.0018748750228408518 f http://qa.geonames.org/qa/kml/NL8082Elburg.kml NL 03 Elburg 8082 2756232 0.001845519920151329 f http://qa.geonames.org/qa/kml/NL1822Alkmaar.kml NL 07 Alkmaar 1822 null 0.0018453107841082208 t http://qa.geonames.org/qa/kml/NL1458Spijkerboor.kml NL 07 Spijkerboor 1458 2746930 0.0017242631689409842 t http://qa.geonames.org/qa/kml/NL3776Voorthuizen.kml NL 03 Voorthuizen 3776 null 0.001635921666923282 f http://qa.geonames.org/qa/kml/NL3231Vierpolders.kml NL 11 Vierpolders 3231 null 0.0015852599746173173 t http://qa.geonames.org/qa/kml/NL1022Amsterdam.kml NL 07 Amsterdam 1022 null 0.001483521400074268 t http://qa.geonames.org/qa/kml/NL3084Rotterdam.kml NL 11 Rotterdam 3084 null 0.0014785860396941042 t http://qa.geonames.org/qa/kml/NL3995Houten.kml NL 09 Houten 3995 2753557 0.001462994894644423 t http://qa.geonames.org/qa/kml/NL4858Ulvenhout AC.kml NL 06 Ulvenhout AC 4858 null 0.0014094462087364916 t http://qa.geonames.org/qa/kml/NL5036Tilburg.kml NL 06 Tilburg 5036 null 0.0013835299828823565 t http://qa.geonames.org/qa/kml/NL4762Zevenbergen.kml NL 06 Zevenbergen 4762 2743947 0.0013615252059730057 t http://qa.geonames.org/qa/kml/NL2989Ridderkerk.kml NL 11 Ridderkerk 2989 null 0.0013334499068566653 t http://qa.geonames.org/qa/kml/NL7317Apeldoorn.kml NL 03 Apeldoorn 7317 null 0.001225124634997859 t http://qa.geonames.org/qa/kml/NL2632Nootdorp.kml NL 11 Nootdorp 2632 2749800 0.0011434603870361013 t http://qa.geonames.org/qa/kml/NL7909Hoogeveen.kml NL 01 Hoogeveen 7909 2753719 0.0009941186416055725 t http://qa.geonames.org/qa/kml/NL7722Dalfsen.kml NL 15 Dalfsen 7722 2757850 0.0009121014918741713 t http://qa.geonames.org/qa/kml/NL7858Eeserveen.kml NL 01 Eeserveen 7858 2756325 0.000879047695482271 t http://qa.geonames.org/qa/kml/NL5422Gemert.kml NL 06 Gemert 5422 2755605 0.0008597269465893649 t http://qa.geonames.org/qa/kml/NL2672Naaldwijk.kml NL 11 Naaldwijk 2672 2750523 0.0008445148566742775 t http://qa.geonames.org/qa/kml/NL8472Wolvega.kml NL 02 Wolvega 8472 2744194 0.0008417512210681302 t http://qa.geonames.org/qa/kml/NL7554Hengelo.kml NL 15 Hengelo 7554 null 0.0008348902431345067 t http://qa.geonames.org/qa/kml/NL5272Sint-Michielsgestel.kml NL 06 Sint-Michielsgestel 5272 2747234 0.0007960008374403572 t http://qa.geonames.org/qa/kml/NL9979Eemshaven.kml NL 04 Eemshaven 9979 null 0.0007556982880568247 t http://qa.geonames.org/qa/kml/NL5111Baarle-Nassau.kml NL 06 Baarle-Nassau 5111 2759554 0.0007352406132057714 t http://qa.geonames.org/qa/kml/NL4584Kuitaart.kml NL 10 Kuitaart 4584 2752157 0.00073257779973787 t http://qa.geonames.org/qa/kml/NL8266Kampen.kml NL 15 Kampen 8266 2753106 0.0006972156688282433 t http://qa.geonames.org/qa/kml/NL1704Heerhugowaard.kml NL 07 Heerhugowaard 1704 2754659 0.0006657081759140376 t http://qa.geonames.org/qa/kml/NL1732Lutjewinkel.kml NL 07 Lutjewinkel 1732 2751369 0.0006256123409012395 f http://qa.geonames.org/qa/kml/NL8941Leeuwarden.kml NL 02 Leeuwarden 8941 null 0.0005874677990060956 t http://qa.geonames.org/qa/kml/NL4208Gorinchem.kml NL 11 Gorinchem 4208 null 0.0005041254398289817 t http://qa.geonames.org/qa/kml/NL3235Oostvoorne.kml NL 11 Oostvoorne 3235 2749330 0.0004993352965381864 f http://qa.geonames.org/qa/kml/NL2662Bergschenhoek.kml NL 11 Bergschenhoek 2662 2759113 0.0004887325211691803 t http://qa.geonames.org/qa/kml/NL1262Blaricum.kml NL 07 Blaricum 1262 null 0.00044197752197145225 f http://qa.geonames.org/qa/kml/NL1965Heemskerk.kml NL 07 Heemskerk 1965 2754697 0.000411001981836771 t http://qa.geonames.org/qa/kml/NL8766Breezanddijk.kml NL 02 Breezanddijk 8766 null 0.00036272205514931887 t http://qa.geonames.org/qa/kml/NL6574Nijmegen.kml NL 03 Nijmegen 6574 null 0.00035093578266594765 f http://qa.geonames.org/qa/kml/NL1384Weesp.kml NL 07 Weesp 1384 2744904 0.0003474025615634494 t http://qa.geonames.org/qa/kml/NL3329Dordrecht.kml NL 11 Dordrecht 3329 null 0.000331665429467319 t http://qa.geonames.org/qa/kml/NL2652Berkel en Rodenrijs.kml NL 11 Berkel en Rodenrijs 2652 6929992 0.00031949690121216783 t http://qa.geonames.org/qa/kml/NL4211Spijk.kml NL 03 Spijk 4211 2746938 0.00030832536294082895 t http://qa.geonames.org/qa/kml/NL5256Haarsteeg.kml NL 06 Haarsteeg 5256 2754986 0.0003070468834234361 t http://qa.geonames.org/qa/kml/NL2626Delft.kml NL 11 Delft 2626 null 0.0003004030846100418 t http://qa.geonames.org/qa/kml/NL8802Franeker.kml NL 02 Franeker 8802 2755845 0.00029389713834545705 t http://qa.geonames.org/qa/kml/NL1095Amsterdam.kml NL 07 Amsterdam 1095 null 0.00028208491555850926 t http://qa.geonames.org/qa/kml/NL1724Oudkarspel.kml NL 07 Oudkarspel 1724 2748943 0.0002544079201492005 t http://qa.geonames.org/qa/kml/NL4772Langeweg.kml NL 06 Langeweg 4772 2751902 0.00022620295917787816 t http://qa.geonames.org/qa/kml/NL7482Haaksbergen.kml NL 15 Haaksbergen 7482 2755030 0.00021902770423453015 t http://qa.geonames.org/qa/kml/NL7702Dedemsvaart.kml NL 15 Dedemsvaart 7702 2757698 0.00020528940710144323 t http://qa.geonames.org/qa/kml/NL3903Veenendaal.kml NL 09 Veenendaal 3903 2745774 0.0002028603619733756 t http://qa.geonames.org/qa/kml/NL8291Genemuiden.kml NL 15 Genemuiden 8291 null 0.0001569356890602476 t http://qa.geonames.org/qa/kml/NL3604Maarssen.kml NL 09 Maarssen 3604 2751316 0.00015035144547323115 t http://qa.geonames.org/qa/kml/NL3113Schiedam.kml NL 11 Schiedam 3113 2747596 0.00011399001680508904 t http://qa.geonames.org/qa/kml/NL7384Twello.kml NL 03 Twello 7384 null 0.00010866805113252138 t http://qa.geonames.org/qa/kml/NL2541's-Gravenhage.kml NL 11 's-Gravenhage 2541 null 0.00010416723995205292 t http://qa.geonames.org/qa/kml/NL5405Uden.kml NL 06 Uden 5405 2746005 9.721983204209583e-05 t http://qa.geonames.org/qa/kml/NL2561's-Gravenhage.kml NL 11 's-Gravenhage 2561 null 9.531014654146753e-05 t http://qa.geonames.org/qa/kml/NL4386Vlissingen.kml NL 10 Vlissingen 4386 null 6.106838543501183e-05 t http://qa.geonames.org/qa/kml/NL1722Noord-Scharwoude.kml NL 07 Noord-Scharwoude 1722 2749835 5.101528413898225e-05 t http://qa.geonames.org/qa/kml/NL6341Walem.kml NL 05 Walem 6341 2745074 1.112910422801302e-05 t