restrict interpolation housenumbers to 0-999999

Ensures that the numbers fit into integers.

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index 8bc9ad429926db9fa76346dff75cc16be0c8a2d1..b0797df2bddcd574ea05df20f1e83c57d68a7e87 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -222,10 +222,11 @@ BEGIN
           -- formatted postcode and therefore 'postcode' contains a derived
           -- variant.
           CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode,
           -- formatted postcode and therefore 'postcode' contains a derived
           -- variant.
           CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode,

-          substring(address->'housenumber','[0-9]+')::integer as hnr
+          (address->'housenumber')::integer as hnr

         FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos
         WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
               and address is not NULL and address ? 'housenumber'
         FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos
         WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
               and address is not NULL and address ? 'housenumber'

+              and address->'housenumber' ~ '^[0-9]{1,6}$'

               and ST_Distance(NEW.linegeo, geometry) < 0.0005
         ORDER BY nodeidpos
     LOOP
               and ST_Distance(NEW.linegeo, geometry) < 0.0005
         ORDER BY nodeidpos
     LOOP