From: Sarah Hoffmann Date: Sat, 18 Aug 2012 08:33:01 +0000 (+0200) Subject: Merge branch 'master' of http://github.com/twain47/Nominatim X-Git-Tag: deploy~684 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/f2ad6c961c68a0fd49e180906bc75cb577f23041?hp=03873df21182ee9a87f168f6108d93187db0c83e Merge branch 'master' of http://github.com/twain47/Nominatim --- diff --git a/.gitignore b/.gitignore new file mode 100644 index 00000000..1c7d233e --- /dev/null +++ b/.gitignore @@ -0,0 +1,21 @@ +*.log + +nominatim/*.d +nominatim/*.o +nominatim/nominatim +module/nominatim.so +settings/configuration.txt +settings/download.lock +settings/state.txt +settings/local.php + +.deps/ +autom4te.cache/ +config.* +configure +Makefile +Makefile.in +stamp-h1 +missing +INSTALL +aclocal.m4 diff --git a/sql/functions.sql b/sql/functions.sql index 2c4429bb..548362ad 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -2582,93 +2582,6 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, - in_endnumber INTEGER, interpolationtype TEXT, - in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER - AS $$ -DECLARE - - startnumber INTEGER; - endnumber INTEGER; - stepsize INTEGER; - housenum INTEGER; - newpoints INTEGER; - numberrange INTEGER; - rangestartnumber INTEGER; - place_centroid GEOMETRY; - out_partition INTEGER; - out_parent_place_id BIGINT; - location RECORD; - address_street_word_id INTEGER; - -BEGIN - - IF in_endnumber > in_startnumber THEN - startnumber = in_startnumber; - endnumber = in_endnumber; - ELSE - startnumber = in_endnumber; - endnumber = in_startnumber; - END IF; - - numberrange := endnumber - startnumber; - rangestartnumber := startnumber; - - IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN - startnumber := startnumber + 1; - stepsize := 2; - ELSE - IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN - stepsize := 2; - ELSE -- everything else assumed to be 'all' - stepsize := 1; - END IF; - END IF; - - -- Filter out really broken tiger data - IF numberrange > 0 AND (numberrange::float/stepsize::float > 500) - AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN - RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin, - ST_length(linegeo)/(numberrange::float/stepsize::float); - RETURN 0; - END IF; - - place_centroid := ST_Centroid(linegeo); - out_partition := get_partition(place_centroid, 'us'); - out_parent_place_id := null; - - address_street_word_id := get_name_id(make_standard_name(in_street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP - out_parent_place_id := location.place_id; - END LOOP; - END IF; - - IF out_parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP - out_parent_place_id := location.place_id; - END LOOP; - END IF; - - IF out_parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP - out_parent_place_id := location.place_id; - END LOOP; - END IF; - - newpoints := 0; - FOR housenum IN startnumber..endnumber BY stepsize LOOP - insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid) - values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode, - ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float)); - newpoints := newpoints + 1; - END LOOP; - - RETURN newpoints; -END; -$$ -LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT, in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER AS $$ diff --git a/sql/tiger_import_finish.sql b/sql/tiger_import_finish.sql new file mode 100644 index 00000000..a0f4efc7 --- /dev/null +++ b/sql/tiger_import_finish.sql @@ -0,0 +1,12 @@ +CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id_imp ON location_property_tiger_import (parent_place_id, housenumber); +CREATE UNIQUE INDEX idx_location_property_tiger_place_id_imp ON location_property_tiger_import (place_id); + +GRANT SELECT ON location_property_tiger_import TO "www-data"; + +DROP TABLE location_property_tiger; +ALTER TABLE location_property_tiger_import RENAME TO location_property_tiger; + +ALTER INDEX idx_location_property_tiger_housenumber_parent_place_id_imp RENAME TO idx_location_property_tiger_housenumber_parent_place_id; +ALTER INDEX idx_location_property_tiger_place_id_imp RENAME TO idx_location_property_tiger_place_id; + +DROP FUNCTION tigger_create_interpolation (linegeo geometry, in_startnumber integer, in_endnumber integer, interpolationtype text, in_street text, in_isin text, in_postcode text); diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql new file mode 100644 index 00000000..f40f544b --- /dev/null +++ b/sql/tiger_import_start.sql @@ -0,0 +1,89 @@ +DROP TABLE IF EXISTS location_property_tiger_import; +CREATE TABLE location_property_tiger_import () INHERITS (location_property); + +CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, + in_endnumber INTEGER, interpolationtype TEXT, + in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER + AS $$ +DECLARE + + startnumber INTEGER; + endnumber INTEGER; + stepsize INTEGER; + housenum INTEGER; + newpoints INTEGER; + numberrange INTEGER; + rangestartnumber INTEGER; + place_centroid GEOMETRY; + out_partition INTEGER; + out_parent_place_id BIGINT; + location RECORD; + address_street_word_id INTEGER; + +BEGIN + + IF in_endnumber > in_startnumber THEN + startnumber = in_startnumber; + endnumber = in_endnumber; + ELSE + startnumber = in_endnumber; + endnumber = in_startnumber; + END IF; + + numberrange := endnumber - startnumber; + rangestartnumber := startnumber; + + IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN + startnumber := startnumber + 1; + stepsize := 2; + ELSE + IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN + stepsize := 2; + ELSE -- everything else assumed to be 'all' + stepsize := 1; + END IF; + END IF; + + -- Filter out really broken tiger data + IF numberrange > 0 AND (numberrange::float/stepsize::float > 500) + AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN + RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin, + ST_length(linegeo)/(numberrange::float/stepsize::float); + RETURN 0; + END IF; + + place_centroid := ST_Centroid(linegeo); + out_partition := get_partition(place_centroid, 'us'); + out_parent_place_id := null; + + address_street_word_id := get_name_id(make_standard_name(in_street)); + IF address_street_word_id IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP + out_parent_place_id := location.place_id; + END LOOP; + END IF; + + IF out_parent_place_id IS NULL THEN + FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP + out_parent_place_id := location.place_id; + END LOOP; + END IF; + + IF out_parent_place_id IS NULL THEN + FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP + out_parent_place_id := location.place_id; + END LOOP; + END IF; + + newpoints := 0; + FOR housenum IN startnumber..endnumber BY stepsize LOOP + insert into location_property_tiger_import (place_id, partition, parent_place_id, housenumber, postcode, centroid) + values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode, + ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float)); + newpoints := newpoints + 1; + END LOOP; + + RETURN newpoints; +END; +$$ +LANGUAGE plpgsql; diff --git a/utils/setup.php b/utils/setup.php index 989e9887..fb83dd0a 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -359,6 +359,8 @@ { $bDidSomething = true; + pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_start.sql'); + $aDBInstances = array(); for($i = 0; $i < $iInstances; $i++) { @@ -407,6 +409,9 @@ } echo "\n"; } + + echo "Creating indexes\n"; + pgsqlRunScriptFile(CONST_BasePath.'/sql/tiger_import_finish.sql'); } if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])