number of errors=42
number of warnings=0
country=PR
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='PR' 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/PR00765Vieques.kml PR 147 Vieques 00765 null 0.7350036165080351 f http://qa.geonames.org/qa/kml/PR00740Puerto Real.kml PR 053 Puerto Real 00740 null 0.13439855813290727 f http://qa.geonames.org/qa/kml/PR00924San Juan.kml PR 127 San Juan 00924 4568127 0.0942241928685557 f http://qa.geonames.org/qa/kml/PR00926San Juan.kml PR 127 San Juan 00926 4568127 0.08586609728717791 f http://qa.geonames.org/qa/kml/PR00925San Juan.kml PR 127 San Juan 00925 4568127 0.07648386038721354 f http://qa.geonames.org/qa/kml/PR00923San Juan.kml PR 127 San Juan 00923 4568127 0.07542692299524736 f http://qa.geonames.org/qa/kml/PR00936San Juan.kml PR 127 San Juan 00936 4568127 0.07387374231167068 f http://qa.geonames.org/qa/kml/PR00927San Juan.kml PR 127 San Juan 00927 4568127 0.0697199337315444 f http://qa.geonames.org/qa/kml/PR00965Guaynabo.kml PR 061 Guaynabo 00965 4565119 0.06684901596194359 f http://qa.geonames.org/qa/kml/PR00921San Juan.kml PR 127 San Juan 00921 4568127 0.0654964040183194 f http://qa.geonames.org/qa/kml/PR00913San Juan.kml PR 127 San Juan 00913 4568127 0.05876102638765101 f http://qa.geonames.org/qa/kml/PR00917San Juan.kml PR 127 San Juan 00917 4568127 0.05716984364269821 f http://qa.geonames.org/qa/kml/PR00979Carolina.kml PR 127 Carolina 00979 4563243 0.05682858531804139 f http://qa.geonames.org/qa/kml/PR00915San Juan.kml PR 127 San Juan 00915 4568127 0.051762864918112834 f http://qa.geonames.org/qa/kml/PR00968Guaynabo.kml PR 127 Guaynabo 00968 4565119 0.044802710130972055 f http://qa.geonames.org/qa/kml/PR00918San Juan.kml PR 127 San Juan 00918 4568127 0.04419763002164693 f http://qa.geonames.org/qa/kml/PR00911San Juan.kml PR 127 San Juan 00911 4568127 0.04304660044335785 f http://qa.geonames.org/qa/kml/PR00912San Juan.kml PR 127 San Juan 00912 4568127 0.04264644719939294 f http://qa.geonames.org/qa/kml/PR00909San Juan.kml PR 127 San Juan 00909 4568127 0.03569997452282685 f http://qa.geonames.org/qa/kml/PR00920San Juan.kml PR 127 San Juan 00920 4568127 0.03562607118572442 f http://qa.geonames.org/qa/kml/PR00694Vega Baja.kml PR 145 Vega Baja 00694 4568533 0.028325694279399235 f http://qa.geonames.org/qa/kml/PR00731Ponce.kml PR 113 Ponce 00731 4566880 0.024149146191943676 f http://qa.geonames.org/qa/kml/PR00966Guaynabo.kml PR 061 Guaynabo 00966 4565119 0.023772090528181744 f http://qa.geonames.org/qa/kml/PR00982Carolina.kml PR 127 Carolina 00982 4563243 0.023443231092367522 f http://qa.geonames.org/qa/kml/PR00704Aguirre.kml PR 057 Aguirre 00704 8335063 0.02331973434172458 f http://qa.geonames.org/qa/kml/PR00957Bayamon.kml PR 021 Bayamon 00957 4562831 0.018703337825373124 f http://qa.geonames.org/qa/kml/PR00907San Juan.kml PR 127 San Juan 00907 4568127 0.018239001763530695 f http://qa.geonames.org/qa/kml/PR00983Carolina.kml PR 031 Carolina 00983 4563243 0.017458269606404717 f http://qa.geonames.org/qa/kml/PR00956Bayamon.kml PR 021 Bayamon 00956 4562831 0.014256436357312656 f http://qa.geonames.org/qa/kml/PR00950Toa Baja.kml PR 137 Toa Baja 00950 4568407 0.010969937048591902 f http://qa.geonames.org/qa/kml/PR00906San Juan.kml PR 127 San Juan 00906 4568127 0.01031318722339524 f http://qa.geonames.org/qa/kml/PR00728Ponce.kml PR 113 Ponce 00728 4566880 0.009959195569928641 f http://qa.geonames.org/qa/kml/PR00951Toa Baja.kml PR 137 Toa Baja 00951 4568407 0.009935354188453363 f http://qa.geonames.org/qa/kml/PR00960Bayamon.kml PR 021 Bayamon 00960 4562831 0.00978886880134942 f http://qa.geonames.org/qa/kml/PR00682Mayaguez.kml PR 097 Mayaguez 00682 4566385 0.007474593049480563 t http://qa.geonames.org/qa/kml/PR00716Ponce.kml PR 113 Ponce 00716 4566880 0.00649386421170864 t http://qa.geonames.org/qa/kml/PR00725Caguas.kml PR 025 Caguas 00725 4563008 0.004929470200316711 t http://qa.geonames.org/qa/kml/PR00971Guaynabo.kml PR 061 Guaynabo 00971 4565119 0.003922230676539593 t http://qa.geonames.org/qa/kml/PR00987Carolina.kml PR 031 Carolina 00987 4563243 0.00348025805836564 t http://qa.geonames.org/qa/kml/PR00959Bayamon.kml PR 021 Bayamon 00959 4562831 0.0024925022062364114 t http://qa.geonames.org/qa/kml/PR00949Toa Baja.kml PR 137 Toa Baja 00949 4568407 0.0007958164151312375 t http://qa.geonames.org/qa/kml/PR00717Ponce.kml PR 113 Ponce 00717 4566880 8.560592368653338e-05 t