function getClassTypes()
{
return array(
- 'boundary:adminitrative:2' => array('label'=>'Country','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:4' => array('label'=>'State','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:5' => array('label'=>'State District','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:6' => array('label'=>'County','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:8' => array('label'=>'City','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:9' => array('label'=>'City District','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:10' => array('label'=>'Suburb','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
- 'boundary:adminitrative:11' => array('label'=>'Neighbourhood','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:2' => array('label'=>'Country','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:4' => array('label'=>'State','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:5' => array('label'=>'State District','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:6' => array('label'=>'County','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:8' => array('label'=>'City','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:9' => array('label'=>'City District','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:10' => array('label'=>'Suburb','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative:11' => array('label'=>'Neighbourhood','frequency'=>0,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
'place:city' => array('label'=>'City','frequency'=>66,'icon'=>'poi_place_city','defzoom'=>12, 'defdiameter' => 0.32,),
'place:country' => array('label'=>'Country','frequency'=>0,'icon'=>'poi_boundary_administrative','defzoom'=>6, 'defdiameter' => 15,),
'place:state' => array('label'=>'State','frequency'=>0,'icon'=>'poi_boundary_administrative','defzoom'=>8, 'defdiameter' => 5.12,),
'place:region' => array('label'=>'State','frequency'=>0,'icon'=>'poi_boundary_administrative','defzoom'=>8, 'defdiameter' => 5.12,),
'place:island' => array('label'=>'Island','frequency'=>288,'icon'=>'','defzoom'=>11, 'defdiameter' => 0.64,),
'place:county' => array('label'=>'County','frequency'=>108,'icon'=>'poi_boundary_administrative','defzoom'=>10, 'defdiameter' => 1.28,),
- 'boundary:adminitrative' => array('label'=>'Administrative','frequency'=>413,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
+ 'boundary:administrative' => array('label'=>'Administrative','frequency'=>413,'icon'=>'poi_boundary_administrative', 'defdiameter' => 0.32,),
'place:town' => array('label'=>'Town','frequency'=>1497,'icon'=>'poi_place_town','defzoom'=>14, 'defdiameter' => 0.08,),
'place:village' => array('label'=>'Village','frequency'=>11230,'icon'=>'poi_place_village','defzoom'=>15, 'defdiameter' => 0.04,),
'place:hamlet' => array('label'=>'Hamlet','frequency'=>7075,'icon'=>'poi_place_village','defzoom'=>15, 'defdiameter' => 0.04,),
'amenity:shop' => array('label'=>'Shop','frequency'=>61,'icon'=>'',),
'place:house' => array('label'=>'House','frequency'=>2086,'icon'=>'','defzoom'=>18,),
+ 'place:house_name' => array('label'=>'House','frequency'=>2086,'icon'=>'','defzoom'=>18,),
+ 'place:house_number' => array('label'=>'House Number','frequency'=>2086,'icon'=>'','defzoom'=>18,),
+ 'place:country_code' => array('label'=>'Country Code','frequency'=>2086,'icon'=>'','defzoom'=>18,),
//
function _debugDumpGroupedSearches($aData, $aTokens)
{
$aWordsIDs = array();
- foreach($aTokens as $sToken => $aWords)
+ if ($aTokens)
{
- foreach($aWords as $aToken)
+ foreach($aTokens as $sToken => $aWords)
{
- $aWordsIDs[$aToken['word_id']] = $sToken.'('.$aToken['word_id'].')';
+ if ($aWords)
+ {
+ foreach($aWords as $aToken)
+ {
+ $aWordsIDs[$aToken['word_id']] = $sToken.'('.$aToken['word_id'].')';
+ }
+ }
}
}
echo "<table border=\"1\">";
function getAddressDetails(&$oDB, $sLanguagePrefArraySQL, $iPlaceID, $sCountryCode = false, $bRaw = false)
{
- $aHouseNumber = $oDB->getRow('select housenumber, get_name_by_language(name,ARRAY[\'addr:housename\']) as housename,rank_search from placex where place_id = '.$iPlaceID);
+ $sSQL = "select *,get_name_by_language(name,$sLanguagePrefArraySQL) as localname from get_addressdata($iPlaceID)";
+ IF (!$bRaw) $sSQL .= " WHERE isaddress OR type = 'country_code'";
+ $sSQL .= " order by rank_address desc,isaddress desc";
+ $aAddressLines = $oDB->getAll($sSQL);
+ if (PEAR::IsError($aAddressLines))
+ {
+ var_dump($aAddressLines);
+ exit;
+ }
+ if ($bRaw) return $aAddressLines;
+//echo "<pre>";
+//var_dump($aAddressLines);
+ $aAddress = array();
+ $aClassType = getClassTypes();
+ foreach($aAddressLines as $aLine)
+ {
+ $aTypeLabel = false;
+ if (isset($aClassType[$aLine['class'].':'.$aLine['type'].':'.$aLine['admin_level']])) $aTypeLabel = $aClassType[$aLine['class'].':'.$aLine['type'].':'.$aLine['admin_level']];
+ elseif (isset($aClassType[$aLine['class'].':'.$aLine['type']])) $aTypeLabel = $aClassType[$aLine['class'].':'.$aLine['type']];
+ else $aTypeLabel = array('simplelabel'=>$aLine['class']);
+ if ($aTypeLabel && ($aLine['localname'] || $aLine['housenumber']))
+ {
+ $sTypeLabel = strtolower(isset($aTypeLabel['simplelabel'])?$aTypeLabel['simplelabel']:$aTypeLabel['label']);
+ if (!isset($aAddress[$sTypeLabel]) && $aLine['localname']) $aAddress[$sTypeLabel] = $aLine['localname']?$aLine['localname']:$aLine['housenumber'];
+ }
+ }
+//var_dump($aAddress);
+//exit;
+ return $aAddress;
+
+ $aHouseNumber = $oDB->getRow('select housenumber, get_name_by_language(name,ARRAY[\'addr:housename\']) as housename,rank_search,postcode from placex where place_id = '.$iPlaceID);
$sHouseNumber = $aHouseNumber['housenumber'];
$sHouseName = $aHouseNumber['housename'];
+ $sPostcode = $aHouseNumber['postcode'];
$iRank = $aHouseNumber['rank_search'];
// Address
$sSQL .= " length(name::text) as namelength ";
$sSQL .= " from place_addressline join placex on (address_place_id = placex.place_id)";
$sSQL .= " where place_addressline.place_id = $iPlaceID and (rank_address > 0 OR address_place_id = $iPlaceID)";
- $sSQL .= " order by cached_rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc,namelength
-desc";
+ if (!$bRaw) $sSQL .= " and isaddress";
+ $sSQL .= " order by cached_rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc,namelength desc";
//var_dump($sSQL);
$aAddressLines = $oDB->getAll($sSQL);
if (PEAR::IsError($aAddressLines))
$iMinRank = $aLine['rank_address'];
}
}
+
+ if ($sPostcode)
+ {
+ $aAddress['postcode'] = $sPostcode;
+ }
+
if ($iMinRank > 4 && $sCountryCode)
{
$sSQL = "select get_name_by_language(country_name.name,$sLanguagePrefArraySQL) as name";
font-weight: bold;
}
.notused{
- color:#aaa;
+ color:#ddd;
}
.noname{
color:#800;
{
$sOSMType = ($aAddressLine['osm_type'] == 'N'?'node':($aAddressLine['osm_type'] == 'W'?'way':($aAddressLine['osm_type'] == 'R'?'relation':'')));
- echo '<div class="line'.($iPrevRank<=$aAddressLine['rank_address'] || $sPrevLocalName == $aAddressLine['localname']?' notused':'').'">';
+ echo '<div class="line'.($aAddressLine['isaddress']=='f'?' notused':'').'">';
if (!($iPrevRank<=$aAddressLine['rank_address'] || $sPrevLocalName == $aAddressLine['localname']))
{
$iPrevRank = $aAddressLine['rank_address'];
fprintf(stderr, " -i|--index\t\tIndex the database.\n");
fprintf(stderr, " -e|--export\t\tGenerate a structured file.\n");
fprintf(stderr, " -I|--import\t\tImport a structured file.\n");
+ fprintf(stderr, " -r|--minrank\t\tMinimum / starting rank. (default: 0))\n");
+ fprintf(stderr, " -R|--maxrank\t\tMaximum / finishing rank. (default: 30)\n");
fprintf(stderr, " -t|--threads\t\tNumber of threads to create for indexing.\n");
fprintf(stderr, " -F|--file\t\tfile to use (either to import or export).\n");
fprintf(stderr, " -T|--tagfile\t\tfile containing 'special' tag pairs\n");
int index = 0;
int export = 0;
int import = 0;
+ int minrank = 0;
+ int maxrank = 30;
int threads = 1;
const char *file = NULL;
const char *tagsfile = "partitionedtags.def";
{"file", 1, 0, 'F'},
{"tagsfile", 1, 0, 'T'},
+ {"minrank", 1, 0, 'r'},
+ {"maxrank", 1, 0, 'R'},
+
+
{0, 0, 0, 0}
};
- c = getopt_long(argc, argv, "vhd:U:WH:P:ieIt:F:T:", long_options, &option_index);
+ c = getopt_long(argc, argv, "vhd:U:WH:P:ieIt:F:T:r:R:", long_options, &option_index);
if (c == -1)
break;
case 't':
threads=atoi(optarg);
break;
+ case 'r':
+ minrank=atoi(optarg);
+ break;
+ case 'R':
+ maxrank=atoi(optarg);
+ break;
case 'F':
file=optarg;
break;
fprintf(stderr, "Please select index, export or import.\n");
exit(EXIT_FAILURE);
}
- if (index) nominatim_index(0, 30, threads, conninfo, file);
- if (export) nominatim_export(0, 30, conninfo, file);
+ if (index) nominatim_index(minrank, maxrank, threads, conninfo, file);
+ if (export) nominatim_export(minrank, maxrank, conninfo, file);
if (import) nominatim_import(conninfo, tagsfile, file);
return 0;
SELECT min(word_id) FROM word WHERE word_token = lookup_token and class=lookup_class and type = lookup_type and operator = op into return_word_id;
IF return_word_id IS NULL THEN
return_word_id := nextval('seq_word');
- INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, null, op);
+ INSERT INTO word VALUES (return_word_id, lookup_token, null, null, lookup_class, lookup_type, null, 0, op, null);
END IF;
RETURN return_word_id;
END;
RAISE EXCEPTION 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
END IF;
- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
+-- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search;
x := deleteLocationArea(partition, place_id);
IF xmin = xmax OR ymin = ymax OR (xmax-xmin < 2 AND ymax-ymin < 2) THEN
x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, geometry);
ELSE
+-- RAISE WARNING 'Spliting geometry: % to %, % to %', xmin, xmax, ymin, ymax;
FOR lon IN xmin..(xmax-1) LOOP
FOR lat IN ymin..(ymax-1) LOOP
secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(lon,lat),ST_Point(lon+1,lat+1)),4326);
diameter := 0.005;
END IF;
- RAISE WARNING 'adding % diameter %', place_id, diameter;
+-- RAISE WARNING 'adding % diameter %', place_id, diameter;
secgeo := ST_Buffer(geometry, diameter);
x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
b BOOLEAN;
BEGIN
b := deleteLocationArea(partition, place_id);
+-- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry);
END;
$$
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION update_location_nameonly(OLD_place_id INTEGER, name hstore) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id INTEGER, name hstore) RETURNS BOOLEAN
AS $$
DECLARE
newkeywords INTEGER[];
-- just block these
IF NEW.class = 'highway' and NEW.type in ('turning_circle','traffic_signals','mini_roundabout','noexit','crossing') THEN
+-- RAISE WARNING 'bad highway %',NEW.osm_id;
RETURN null;
END IF;
IF NEW.class in ('landuse','natural') and NEW.name is null THEN
+-- RAISE WARNING 'empty landuse %',NEW.osm_id;
RETURN null;
END IF;
IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
-- block all invalid geometary - just not worth the risk. seg faults are causing serious problems.
+-- RAISE WARNING 'invalid geometry %',NEW.osm_id;
RETURN NULL;
-- Dead code
END IF;
NEW.geometry := ST_buffer(NEW.geometry,0);
IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
- RAISE WARNING 'Invalid geometary, rejecting: % %', NEW.osm_type, NEW.osm_id;
+-- RAISE WARNING 'Invalid geometary, rejecting: % %', NEW.osm_type, NEW.osm_id;
RETURN NULL;
END IF;
END IF;
ELSEIF NEW.class = 'boundary' THEN
IF ST_GeometryType(NEW.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') THEN
+-- RAISE WARNING 'invalid boundary %',NEW.osm_id;
return NULL;
END IF;
NEW.rank_search := NEW.admin_level * 2;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND
NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
+-- RAISE WARNING 'unnamed minor feature %',NEW.osm_id;
RETURN NULL;
ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN
RETURN NULL;
-- RETURN NULL;
-- END IF;
- RETURN NEW;
- -- The following is not needed until doing diff updates, and slows the main index process down
+-- RETURN NEW; -- The following is not needed until doing diff updates, and slows the main index process down
IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
-- Performance: We just can't handle re-indexing for country level changes
diameter := 0;
-- 16 = city, anything higher than city is effectively ignored (polygon required!)
IF NEW.type='postcode' THEN
- diameter := 0.001;
+ diameter := 0.05;
ELSEIF NEW.rank_search < 16 THEN
diameter := 0;
ELSEIF NEW.rank_search < 18 THEN
i INTEGER;
iMax FLOAT;
location RECORD;
+ way RECORD;
relation RECORD;
search_diameter FLOAT;
search_prevdiameter FLOAT;
RETURN NEW;
END IF;
+ -- defered delete
+ IF OLD.indexed_status = 100 THEN
+ delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
+ RETURN NULL;
+ END IF;
+
IF NEW.indexed_status = 0 and OLD.indexed_status != 0 THEN
NEW.indexed_date = now();
--RAISE WARNING 'x1';
-- Is this node part of a way?
- FOR location IN select * from placex where osm_type = 'W'
- and osm_id in (select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] limit 10)
+ FOR way IN select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] LOOP
+--RAISE WARNING '%', way;
+ FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id
LOOP
--RAISE WARNING '%', location;
-- Way IS a road then we are on it - that must be our road
END IF;
END LOOP;
+ END LOOP;
END IF;
END IF;
--RAISE WARNING 'x4';
-
-- Still nothing, just use the nearest road
- search_diameter := 0.00005;
- WHILE NEW.parent_place_id IS NULL AND search_diameter < 0.1 LOOP
- FOR location IN SELECT place_id FROM placex
- WHERE ST_DWithin(place_centroid, placex.geometry, search_diameter) and rank_search between 22 and 27
- ORDER BY ST_distance(NEW.geometry, placex.geometry) ASC limit 1
- LOOP
- NEW.parent_place_id := location.place_id;
- END LOOP;
- search_diameter := search_diameter * 2;
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) LOOP
+ NEW.parent_place_id := location.place_id;
END LOOP;
--return NEW;
END IF;
-RAISE WARNING ' INDEXING: %',NEW;
+-- RAISE WARNING ' INDEXING: %',NEW;
NEW.parent_place_id = 0;
parent_place_id_rank = 0;
END IF;
isin_tokens := uniq(sort(isin_tokens));
END IF;
-
- -- try using the isin value to find parent places
- IF array_upper(isin_tokens, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin_tokens, 1) LOOP
---RAISE WARNING ' ISIN: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
-
- FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
- nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
- INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
- address_havelevel[location.rank_address] := true;
-
- IF location.rank_address > parent_place_id_rank THEN
- NEW.parent_place_id = location.place_id;
- parent_place_id_rank = location.rank_address;
- END IF;
-
- END LOOP;
-
- END LOOP;
- END IF;
+--RAISE WARNING 'ISIN: %', isin_tokens;
-- Process area matches
location_rank_search := 100;
location_distance := 0;
+--RAISE WARNING ' getNearFeatures(%,%,%,%)',NEW.partition, place_centroid, search_maxrank, isin_tokens;
FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
--RAISE WARNING ' AREA: %',location;
END LOOP;
+ -- try using the isin value to find parent places
+ IF array_upper(isin_tokens, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin_tokens, 1) LOOP
+--RAISE WARNING ' ISIN: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+
+ FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+ nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
+ address_havelevel[location.rank_address] := true;
+
+ IF location.rank_address > parent_place_id_rank THEN
+ NEW.parent_place_id = location.place_id;
+ parent_place_id_rank = location.rank_address;
+ END IF;
+
+ END LOOP;
+
+ END LOOP;
+ END IF;
+
-- for long ways we should add search terms for the entire length
IF st_length(NEW.geometry) > 0.05 THEN
result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
+ IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
+ result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
+ END IF;
+
result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid);
-- INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, name_vector, nameaddress_vector, place_centroid);
BEGIN
-- RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
- delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
+
+ -- mark for delete
+ UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
+
+-- delete from placex where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
RETURN OLD;
END;
existinggeometry GEOMETRY;
existingplace_id INTEGER;
result BOOLEAN;
+ partition INTEGER;
BEGIN
IF FALSE AND NEW.osm_type = 'R' THEN
END IF;
-- Patch in additional country names
- -- adminitrative (with typo) is unfortunately hard codes - this probably won't get fixed until v2
- IF NEW.admin_level = 2 AND NEW.type = 'adminitrative' AND NEW.country_code is not null THEN
+ IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN
select country_name.name || NEW.name from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name;
END IF;
IF existing.osm_type IS NULL
OR existingplacex.osm_type IS NULL
OR coalesce(existing.admin_level, 100) != coalesce(NEW.admin_level, 100)
--- OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
- OR (existing.geometry != NEW.geometry AND ST_Distance(ST_Centroid(existing.geometry),ST_Centroid(NEW.geometry)) > 0.01 AND NOT
+ OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
+ OR (existing.geometry::text != NEW.geometry::text AND ST_Distance(ST_Centroid(existing.geometry),ST_Centroid(NEW.geometry)) > 0.01 AND NOT
(ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')))
THEN
IF existing.osm_type IS NOT NULL THEN
-- RAISE WARNING 'insert delete % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,ST_Distance(ST_Centroid(existing.geometry),ST_Centroid(NEW.geometry)),existing;
- IF existing.rank_search < 26 THEN
--- RAISE WARNING 'replace placex % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
- END IF;
DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
END IF;
-- RAISE WARNING 'delete and replace2';
-- No - process it as a new insertion (hopefully of low rank or it will be slow)
- insert into placex values (NEW.place_id
- ,NEW.osm_type
+ insert into placex (osm_type, osm_id, class, type, name, admin_level, housenumber,
+ street, isin, postcode, country_code, extratags, geometry)
+ values (NEW.osm_type
,NEW.osm_id
,NEW.class
,NEW.type
,NEW.isin
,NEW.postcode
,NEW.country_code
- ,NEW.parent_place_id
- ,NEW.rank_address
- ,NEW.rank_search
- ,NEW.indexed
+ ,NEW.extratags
,NEW.geometry
);
-- Various ways to do the update
-- Debug, what's changed?
- IF FALSE AND existing.rank_search < 26 THEN
+ IF FALSE THEN
IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN
RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text;
END IF;
END IF;
-- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
- IF existing.geometry != NEW.geometry
+ IF existing.geometry::text != NEW.geometry::text
AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon')
THEN
--- IF existing.rank_search < 26 THEN
--- RAISE WARNING 'existing polygon change % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
--- END IF;
-
-- Get the version of the geometry actually used (in placex table)
select geometry from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type into existinggeometry;
update placex set indexed_status = 2 where indexed_status = 0 and
(ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
- AND rank_search > NEW.rank_search;
+ AND rank_search > existingplacex.rank_search;
update placex set indexed_status = 2 where indexed_status = 0 and
(ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > NEW.rank_search;
+ AND rank_search > existingplacex.rank_search;
END IF;
END IF;
-- Special case - if we are just adding extra words we hack them into the search_name table rather than reindexing
- IF existingplacex.rank_search < 26
+ IF FALSE AND existingplacex.rank_search < 26
AND coalesce(existing.housenumber, '') = coalesce(NEW.housenumber, '')
AND coalesce(existing.street, '') = coalesce(NEW.street, '')
AND coalesce(existing.isin, '') = coalesce(NEW.isin, '')
AND coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
THEN
--- IF existing.rank_search < 26 THEN
--- RAISE WARNING 'name change only % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
--- END IF;
-
IF NOT update_location_nameonly(existingplacex.place_id, NEW.name) THEN
IF st_area(NEW.geometry) < 0.5 THEN
OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
--- IF existing.rank_search < 26 THEN
--- RAISE WARNING 'other change % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
--- END IF;
-
-- performance, can't take the load of re-indexing a whole country / huge area
IF st_area(NEW.geometry) < 0.5 THEN
UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id
END IF;
IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
+ OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
OR coalesce(existing.street, '') != coalesce(NEW.street, '')
OR coalesce(existing.isin, '') != coalesce(NEW.isin, '')
OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '')
OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '')
- OR existing.geometry != NEW.geometry
+ OR existing.geometry::text != NEW.geometry::text
THEN
update place set
isin = NEW.isin,
postcode = NEW.postcode,
country_code = NEW.country_code,
- parent_place_id = null,
+ extratags = NEW.extratags,
geometry = NEW.geometry
where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
postcode = NEW.postcode,
country_code = NEW.country_code,
parent_place_id = null,
- indexed_status = 2,
+ extratags = NEW.extratags,
+ indexed_status = 2,
geometry = NEW.geometry
where place_id = existingplacex.place_id;
- result := update_location(existingplacex.place_id, existingplacex.country_code, NEW.name, existingplacex.rank_search, existingplacex.rank_address, NEW.geometry);
+-- now done as part of indexing
+-- partition := get_partition(NEW.geometry, existingplacex.country_code);
+-- result := update_location(partition, existingplacex.place_id, existingplacex.country_code, NEW.name, existingplacex.rank_search, existingplacex.rank_address, NEW.geometry);
END IF;
AS $$
DECLARE
result TEXT[];
- search TEXT[];
- found INTEGER;
+ currresult TEXT;
+ prevresult TEXT;
location RECORD;
- searchcountrycode varchar(2);
- searchhousenumber TEXT;
- searchrankaddress INTEGER;
- searchpostcode TEXT;
BEGIN
- found := 1000;
- search := languagepref;
result := '{}';
+ prevresult := '';
- select country_code,housenumber,rank_address,postcode from placex where place_id = for_place_id into searchcountrycode,searchhousenumber,searchrankaddress,searchpostcode;
-
- FOR location IN
- select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,
- CASE WHEN type = 'postcode' THEN 'name' => postcode ELSE name END as name,
- distance,length(name::text) as namelength
- from place_addressline join placex on (address_place_id = placex.place_id)
- where place_addressline.place_id = for_place_id and ((rank_address > 0 AND rank_address < searchrankaddress) OR address_place_id = for_place_id)
- and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
- order by rank_address desc,fromarea desc,distance asc,rank_search desc,namelength desc
- LOOP
- IF array_upper(search, 1) IS NOT NULL AND location.name IS NOT NULL THEN
- FOR j IN 1..array_upper(search, 1) LOOP
- IF (found > location.rank_address AND location.name ? search[j] AND location.name -> search[j] != ''
- AND NOT result && ARRAY[location.name -> search[j]]) THEN
- result[(100 - location.rank_address)] := trim(location.name -> search[j]);
- found := location.rank_address;
- END IF;
- END LOOP;
+ FOR location IN select * from get_addressdata(for_place_id) where isaddress order by rank_address desc LOOP
+ currresult := trim(get_name_by_language(location.name, languagepref));
+ IF currresult != prevresult AND currresult IS NOT NULL THEN
+ result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
+ prevresult := currresult;
END IF;
END LOOP;
- IF searchhousenumber IS NOT NULL AND COALESCE(result[(100 - 28)],'') != searchhousenumber THEN
- IF result[(100 - 28)] IS NOT NULL THEN
- result[(100 - 29)] := result[(100 - 28)];
- END IF;
- result[(100 - 28)] := searchhousenumber;
- END IF;
-
- IF searchpostcode IS NOT NULL THEN
- result[(100 - 5)] := searchpostcode;
- END IF;
-
- -- No country polygon - add it from the country_code
- IF found > 4 THEN
- select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code)
- where place_id = for_place_id limit 1 INTO location;
- IF location IS NOT NULL THEN
- result[(100 - 4)] := trim(location.name);
- END IF;
- END IF;
-
RETURN array_to_string(result,', ');
END;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_addressdata_by_language(for_place_id INTEGER, languagepref TEXT[]) RETURNS TEXT[]
+DROP TYPE addressline CASCADE;
+create type addressline as (
+ place_id INTEGER,
+ osm_type CHAR(1),
+ osm_id INTEGER,
+ name HSTORE,
+ class TEXT,
+ type TEXT,
+ fromarea BOOLEAN,
+ isaddress BOOLEAN,
+ rank_address INTEGER,
+ distance FLOAT
+);
+
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id INTEGER) RETURNS setof addressline
AS $$
DECLARE
+ for_place_id INTEGER;
result TEXT[];
search TEXT[];
found INTEGER;
location RECORD;
searchcountrycode varchar(2);
searchhousenumber TEXT;
+ searchhousename HSTORE;
+ searchrankaddress INTEGER;
+ searchpostcode TEXT;
+ countryname HSTORE;
BEGIN
- found := 1000;
- search := languagepref;
- result := '{}';
+ select parent_place_id,'us', housenumber, 30, postcode, null from location_property_tiger
+ WHERE place_id = in_place_id
+ INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
+
+ IF for_place_id IS NULL THEN
- UPDATE placex set indexed_status = 0 where indexed_status > 0 and place_id = for_place_id;
+ select parent_place_id, country_code, housenumber, rank_address, postcode, name from placex
+ WHERE place_id = in_place_id and rank_address = 30
+ INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
- select country_code,housenumber from placex where place_id = for_place_id into searchcountrycode,searchhousenumber;
+ IF for_place_id IS NULL THEN
+ for_place_id := in_place_id;
+ select country_code, housenumber, rank_address, postcode, null from placex where place_id = for_place_id
+ INTO searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
+ END IF;
+
+ END IF;
+--RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode;
+ found := 1000;
FOR location IN
- select CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 ELSE rank_address END as rank_address,
- name,distance,length(name::text) as namelength
+ select placex.place_id, osm_type, osm_id,
+ CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+ class, type, fromarea, isaddress,
+ CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
+ distance
from place_addressline join placex on (address_place_id = placex.place_id)
- where place_addressline.place_id = for_place_id and (rank_address > 0 OR address_place_id = for_place_id)
+ where place_addressline.place_id = for_place_id
+ and ((cached_rank_address > 0 AND cached_rank_address < searchrankaddress) OR address_place_id = for_place_id)
and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
- order by rank_address desc,fromarea desc,distance asc,rank_search desc,namelength desc
+ order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
LOOP
- IF array_upper(search, 1) IS NOT NULL AND array_upper(location.name, 1) IS NOT NULL THEN
- FOR j IN 1..array_upper(search, 1) LOOP
- FOR k IN 1..array_upper(location.name, 1) LOOP
- IF (found > location.rank_address AND location.name[k].key = search[j] AND location.name[k].value != '') AND NOT result && ARRAY[trim(location.name[k].value)] THEN
- result[(100 - location.rank_address)] := trim(location.name[k].value);
- found := location.rank_address;
- END IF;
- END LOOP;
- END LOOP;
+--RAISE WARNING '%',location;
+ IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
+ location.isaddress := FALSE;
END IF;
+ RETURN NEXT location;
+ found := location.rank_address;
END LOOP;
- IF searchhousenumber IS NOT NULL AND result[(100 - 28)] IS NULL THEN
- result[(100 - 28)] := searchhousenumber;
- END IF;
-
- -- No country polygon - add it from the country_code
IF found > 4 THEN
- select get_name_by_language(country_name.name,languagepref) as name from placex join country_name using (country_code)
- where place_id = for_place_id limit 1 INTO location;
- IF location IS NOT NULL THEN
- result[(100 - 4)] := trim(location.name);
+ select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
+--RAISE WARNING '% % %',found,searchcountrycode,countryname;
+ IF countryname IS NOT NULL THEN
+ location := ROW(null, null, null, countryname, 'place', 'country', true, true, 4, 0)::addressline;
+ RETURN NEXT location;
END IF;
END IF;
- RETURN result;
+ IF searchcountrycode IS NOT NULL THEN
+ location := ROW(null, null, null, 'ref'=>searchcountrycode, 'place', 'country_code', true, false, 4, 0)::addressline;
+ RETURN NEXT location;
+ END IF;
+
+ IF searchhousename IS NOT NULL THEN
+ location := ROW(in_place_id, null, null, searchhousename, 'place', 'house_name', true, true, 29, 0)::addressline;
+ RETURN NEXT location;
+ END IF;
+
+ IF searchhousenumber IS NOT NULL THEN
+ location := ROW(in_place_id, null, null, 'ref'=>searchhousenumber, 'place', 'house_number', true, true, 28, 0)::addressline;
+ RETURN NEXT location;
+ END IF;
+
+ IF searchpostcode IS NOT NULL THEN
+ location := ROW(null, null, null, 'ref'=>searchpostcode, 'place', 'postcode', true, true, 5, 0)::addressline;
+ RETURN NEXT location;
+ END IF;
+
+ RETURN;
END;
$$
LANGUAGE plpgsql;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
- AS $$
-DECLARE
- result place_boundingbox;
- numfeatures integer;
-BEGIN
- update placex set
- name = place.name,
- housenumber = place.housenumber,
- street = place.street,
- isin = place.isin,
- postcode = place.postcode,
- country_code = place.country_code,
- parent_place_id = null,
- indexed_status = 1
- from place
- where placex.place_id = search_place_id
- and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
- and place.class = placex.class and place.type = placex.type;
- update placex set indexed_status = 1 where place_id = search_place_id;
- update placex set indexed_status = 0 where place_id = search_place_id;
- return true;
-END;
-$$
-LANGUAGE plpgsql;
-
CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
AS $$
DECLARE
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);
+ 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);
partition := get_partition(place_centroid, 'us');
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(partition, place_centroid, address_street_word_id) LOOP
END LOOP;
END IF;
+ IF parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
+ parent_place_id := location.place_id;
+ END LOOP;
+ END IF;
+
+ IF parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+ 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'), 2, parent_place_id, housenum, in_postcode,
+ values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
newpoints := newpoints + 1;
END LOOP;
CREATE TABLE search_name_country () INHERITS (search_name_blank);
CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id);
CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid);
-CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector gin__int_ops);
-CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops);
+CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
-- start
CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large);
CREATE TABLE search_name_-partition- () INHERITS (search_name_blank);
CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id);
CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid);
-CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops);
-CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops);
+CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
CREATE TABLE location_property_-partition- () INHERITS (location_property);
CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id);
CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber);
--CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid);
+CREATE TABLE location_road_-partition- (
+ partition integer,
+ place_id INTEGER,
+ country_code VARCHAR(2)
+ );
+SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2);
+CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry);
+CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id);
+
-- end
create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$
BEGIN
IF in_rank_search <= 4 THEN
- DELETE FROM location_area_country where place_id = in_place_id;
INSERT INTO location_area_country values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
RETURN TRUE;
END IF;
-- start
IF in_partition = -partition- THEN
- DELETE FROM location_area_large_-partition- where place_id = in_place_id;
INSERT INTO location_area_large_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
RETURN TRUE;
END IF;
$$
LANGUAGE plpgsql;
-create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) RETURNS setof nearfeature AS $$
+create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER)
+ RETURNS setof nearfeature AS $$
DECLARE
r nearfeature%rowtype;
BEGIN
END
$$
LANGUAGE plpgsql;
+
+create or replace function insertLocationRoad(
+ in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
+DECLARE
+BEGIN
+
+-- start
+ IF in_partition = -partition- THEN
+ DELETE FROM location_road_-partition- where place_id = in_place_id;
+ INSERT INTO location_road_-partition- values (in_partition, in_place_id, in_country_code, in_geometry);
+ RETURN TRUE;
+ END IF;
+-- end
+
+ RAISE EXCEPTION 'Unknown partition %', in_partition;
+ RETURN FALSE;
+END
+$$
+LANGUAGE plpgsql;
+
+create or replace function deleteRoad(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$
+DECLARE
+BEGIN
+
+-- start
+ IF in_partition = -partition- THEN
+ DELETE FROM location_road_-partition- where place_id = in_place_id;
+ RETURN TRUE;
+ END IF;
+-- end
+
+ RAISE EXCEPTION 'Unknown partition %', in_partition;
+
+ RETURN FALSE;
+END
+$$
+LANGUAGE plpgsql;
+
+create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$
+DECLARE
+ r nearfeature%rowtype;
+ search_diameter FLOAT;
+BEGIN
+
+-- start
+ IF in_partition = -partition- THEN
+ search_diameter := 0.00005;
+ WHILE search_diameter < 0.1 LOOP
+ FOR r IN
+ SELECT place_id, null, null, null,
+ ST_Distance(geometry, point) as distance
+ FROM location_road_-partition-
+ WHERE ST_DWithin(geometry, point, search_diameter)
+ ORDER BY distance ASC limit 1
+ LOOP
+ RETURN NEXT r;
+ RETURN;
+ END LOOP;
+ search_diameter := search_diameter * 2;
+ END LOOP;
+ RETURN;
+ END IF;
+-- end
+
+ RAISE EXCEPTION 'Unknown partition %', in_partition;
+END
+$$
+LANGUAGE plpgsql;
+
+create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$
+DECLARE
+ r nearfeature%rowtype;
+ search_diameter FLOAT;
+ p1 GEOMETRY;
+ p2 GEOMETRY;
+ p3 GEOMETRY;
+BEGIN
+
+ IF st_geometrytype(line) not in ('ST_LineString') THEN
+ RETURN;
+ END IF;
+
+ p1 := ST_Line_Interpolate_Point(line,0);
+ p2 := ST_Line_Interpolate_Point(line,0.5);
+ p3 := ST_Line_Interpolate_Point(line,1);
+
+-- start
+ IF in_partition = -partition- THEN
+ search_diameter := 0.0005;
+ WHILE search_diameter < 0.01 LOOP
+ FOR r IN
+ SELECT place_id, null, null, null,
+ ST_Distance(geometry, line) as distance
+ FROM location_road_-partition-
+ WHERE ST_DWithin(line, geometry, search_diameter)
+ ORDER BY (ST_distance(geometry, p1)+
+ ST_distance(geometry, p2)+
+ ST_distance(geometry, p3)) ASC limit 1
+ LOOP
+ RETURN NEXT r;
+ RETURN;
+ END LOOP;
+ search_diameter := search_diameter * 2;
+ END LOOP;
+ RETURN;
+ END IF;
+-- end
+
+ RAISE EXCEPTION 'Unknown partition %', in_partition;
+END
+$$
+LANGUAGE plpgsql;
SELECT AddGeometryColumn('word', 'location', 4326, 'GEOMETRY', 2);
CREATE INDEX idx_word_word_id on word USING BTREE (word_id);
CREATE INDEX idx_word_word_token on word USING BTREE (word_token);
-CREATE INDEX idx_word_trigram ON word USING gin(word_trigram gin_trgm_ops);
+--CREATE INDEX idx_word_trigram ON word USING gin(word_trigram gin_trgm_ops) WITH (fastupdate = off);
GRANT SELECT ON word TO "www-data" ;
DROP SEQUENCE seq_word;
CREATE SEQUENCE seq_word start 1;
drop table IF EXISTS search_name;
CREATE TABLE search_name () INHERITS (search_name_blank);
-CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector gin__int_ops);
-CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector gin__int_ops);
+CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector gin__int_ops) WITH (fastupdate = off);
+CREATE INDEX searchnameplacesearch_search_nameaddress_vector_idx ON search_name USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off);
CREATE INDEX idx_search_name_centroid ON search_name USING GIST (centroid);
CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
linked_place_id INTEGER,
rank_address INTEGER,
rank_search INTEGER,
+ importance FLOAT,
indexed_status INTEGER,
indexed_date TIMESTAMP,
geometry_sector INTEGER
array('load-data', '', 0, 1, 0, 0, 'bool', 'Copy data to live tables from import table'),
array('import-tiger-data', '', 0, 1, 0, 0, 'bool', 'Import tiger data (not included in \'all\')'),
array('calculate-postcodes', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
+ array('create-roads', '', 0, 1, 0, 0, 'bool', 'Calculate postcode centroids'),
+ array('osmosis-init', '', 0, 1, 0, 0, 'bool', 'Generate default osmosis configuration'),
+ array('osmosis-init-date', '', 0, 1, 1, 1, 'string', 'Generate default osmosis configuration'),
array('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
+ array('index-output', '', 0, 1, 1, 1, 'string', 'File to dump index information to'),
);
getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
$iInstances = getProcessorCount();
echo "WARNING: resetting threads to $iInstances\n";
}
+ if (isset($aCMDResult['osm-file']) && !isset($aCMDResult['osmosis-init-date']))
+ {
+ $sBaseFile = basename($aCMDResult['osm-file']);
+ if (preg_match('#^planet-([0-9]{2})([0-9]{2})([0-9]{2})[.]#', $sBaseFile, $aMatch))
+ {
+ $iTime = mktime(0, 0, 0, $aMatch[2], $aMatch[3], '20'.$aMatch[1]);
+ $iTime -= (60*60*24);
+ $aCMDResult['osmosis-init-date'] = date('Y-m-d', $iTime).'T22:00:00Z';
+ }
+ }
if ($aCMDResult['create-db'] || $aCMDResult['all'])
{
+ echo "Create DB\n";
$bDidSomething = true;
$oDB =& DB::connect(CONST_Database_DSN, false);
if (!PEAR::isError($oDB))
if ($aCMDResult['create-db'] || $aCMDResult['all'])
{
+ echo "Create DB (2)\n";
$bDidSomething = true;
// TODO: path detection, detection memory, etc.
if ($aCMDResult['import-data'] || $aCMDResult['all'])
{
+ echo "Import\n";
$bDidSomething = true;
+
+ if (!file_exists(CONST_BasePath.'/osm2pgsql/osm2pgsql')) fail("please download and build osm2pgsql");
passthru(CONST_BasePath.'/osm2pgsql/osm2pgsql -lsc -O gazetteer -C 10000 --hstore -d nominatim '.$aCMDResult['osm-file']);
$oDB =& getDB();
if ($aCMDResult['create-functions'] || $aCMDResult['all'])
{
+ echo "Functions\n";
$bDidSomething = true;
if (!file_exists(CONST_BasePath.'/module/nominatim.so')) fail("nominatim module not built");
$sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
if ($aCMDResult['create-tables'] || $aCMDResult['all'])
{
+ echo "Tables\n";
$bDidSomething = true;
pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
if ($aCMDResult['create-partitions'] || $aCMDResult['all'])
{
+ echo "Partitions\n";
$bDidSomething = true;
+echo "here";
$oDB =& getDB();
+echo "there";
$sSQL = 'select partition from country_name order by country_code';
+var_dump($sSQL);
$aPartitions = $oDB->getCol($sSQL);
+var_dump($aPartitions);
if (PEAR::isError($aPartitions))
{
fail($aPartitions->getMessage());
preg_match_all('#^-- start(.*?)^-- end#ms', $sTemplate, $aMatches, PREG_SET_ORDER);
foreach($aMatches as $aMatch)
{
+var_dump($aMatch);
$sResult = '';
foreach($aPartitions as $sPartitionName)
{
+var_dump($sPartitionName);
$sResult .= str_replace('-partition-', $sPartitionName, $aMatch[1]);
}
$sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
if ($aCMDResult['load-data'] || $aCMDResult['all'])
{
+ echo "Load Data\n";
$bDidSomething = true;
$oDB =& getDB();
echo "\n";
}
+ if ($aCMDResult['create-roads'])
+ {
+ $bDidSomething = true;
+
+ $oDB =& getDB();
+ $aDBInstances = array();
+ for($i = 0; $i < $iInstances; $i++)
+ {
+ $aDBInstances[$i] =& getDB(true);
+ if (!pg_query($aDBInstances[$i]->connection, 'set enable_bitmapscan = off')) fail(pg_last_error($oDB->connection));
+ $sSQL = 'select count(*) from (select insertLocationRoad(partition, place_id, country_code, geometry) from ';
+ $sSQL .= 'placex where osm_id % '.$iInstances.' = '.$i.' and rank_search between 26 and 27 and class = \'highway\') as x ';
+ if ($aCMDResult['verbose']) echo "$sSQL\n";
+ if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+ }
+ $bAnyBusy = true;
+ while($bAnyBusy)
+ {
+ $bAnyBusy = false;
+ for($i = 0; $i < $iInstances; $i++)
+ {
+ if (pg_connection_busy($aDBInstances[$i]->connection)) $bAnyBusy = true;
+ }
+ sleep(1);
+ echo '.';
+ }
+ echo "\n";
+ }
+
if ($aCMDResult['import-tiger-data'])
{
$bDidSomething = true;
foreach(glob(CONST_BasePath.'/data/tiger2009/*.sql') as $sFile)
{
echo $sFile.': ';
- if ((int)basename($sFile) <= 53033) continue;
$hFile = fopen($sFile, "r");
$sSQL = fgets($hFile, 100000);
$iLines = 0;
$sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,country_code,";
$sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select country_code,postcode,";
$sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y ";
- $sSQL .= "from place where postcode is not null group by country_code,postcode) as x";
+ $sSQL .= "from placex where postcode is not null group by country_code,postcode) as x";
if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
$sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
}
+ if (($aCMDResult['osmosis-init'] || $aCMDResult['all']) && isset($aCMDResult['osmosis-init-date']))
+ {
+ $bDidSomething = true;
+
+ if (!file_exists(CONST_BasePath.'/osmosis-0.38/bin/osmosis')) fail("please download osmosis");
+ if (file_exists(CONST_BasePath.'/settings/configuration.txt')) echo "settings/configuration.txt already exists\n";
+ else passthru(CONST_BasePath.'/osmosis-0.38/bin/osmosis --read-replication-interval-init '.CONST_BasePath.'/settings');
+
+ $sDate = $aCMDResult['osmosis-init-date'];
+ $sStateFile = file_get_contents('http://toolserver.org/~mazder/replicate-sequences/?'.$sDate);
+ if (!$sStateFile || strlen($sStateFile) > 1000) fail("unable to obtain state file");
+ file_put_contents(CONST_BasePath.'/settings/state.txt', $sStateFile);
+ }
+
if ($aCMDResult['index'] || $aCMDResult['all'])
{
$bDidSomething = true;
- passthru(CONST_BasePath.'/nominatim/nominatim -i -d nominatim -t '.$iInstances);
+ $sOutputFile = '';
+ if (isset($aCMDResult['index-output'])) $sOutputFile = ' -F '.$aCMDResult['index-output'];
+ passthru(CONST_BasePath.'/nominatim/nominatim -i -d nominatim -t '.$iInstances.$sOutputFile);
}
if (!$bDidSomething)
$hProcess = proc_open($sCMD, $aDescriptors, $ahPipes);
if (!is_resource($hProcess)) fail('unable to start pgsql');
+echo "write";
fwrite($ahPipes[0], $sScript);
+echo "close";
fclose($ahPipes[0]);
+echo "done";
// TODO: error checking
while(!feof($ahPipes[1]))
{
+echo "read";
echo fread($ahPipes[1], 4096);
}
+echo "done";
fclose($ahPipes[1]);
+echo "done1";
proc_close($hProcess);
+echo "done2";
}
if ($aCMDResult['wiki-import'])
{
+ $aPairs = array();
+
foreach($aLanguageIn as $sLanguage)
{
$sURL = 'http://wiki.openstreetmap.org/wiki/Special:Export/Nominatim/Special_Phrases/'.strtoupper($sLanguage);
$sWikiPageXML = file_get_contents($sURL);
- if (preg_match_all('#\\| ([^|]+) \\|\\| ([^|]+) \\|\\| ([^|]+) \\|\\| ([\\-YN]) \\|\\| ([\\-YN])#', $sWikiPageXML, $aMatches, PREG_SET_ORDER))
+ if (preg_match_all('#\\| ([^|]+) \\|\\| ([^|]+) \\|\\| ([^|]+) \\|\\| ([^|]+) \\|\\| ([\\-YN])#', $sWikiPageXML, $aMatches, PREG_SET_ORDER))
{
foreach($aMatches as $aMatch)
{
$sLabel = $aMatch[1];
$sClass = $aMatch[2];
$sType = $aMatch[3];
+ $aPairs[$sClass.'|'.$sType] = array($sClass, $sType);
switch(trim($aMatch[4]))
{
echo "select getorcreate_amenityoperator(make_standard_name('".pg_escape_string($sLabel)."'), '$sClass', '$sType', 'near');\n";
break;
case 'in':
- echo "select getorcreate_amenityoperator(make_standard_name('".pg_escape_string($sLabel)."'), '$sClass', '$sType','in');\n";
+ echo "select getorcreate_amenityoperator(make_standard_name('".pg_escape_string($sLabel)."'), '$sClass', '$sType', 'in');\n";
break;
default:
echo "select getorcreate_amenity(make_standard_name('".pg_escape_string($sLabel)."'), '$sClass', '$sType');\n";
}
}
}
+
+ foreach($aPairs as $aPair)
+ {
+
+ echo "create table place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." as ";
+ echo "select place_id as place_id,st_centroid(geometry) as centroid from placex where ";
+ echo "class = '".pg_escape_string($aPair[0])."' and type = '".pg_escape_string($aPair[1])."';\n";
+
+ echo "CREATE INDEX idx_place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])."_centroid ";
+ echo "ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." USING GIST (centroid);\n";
+
+ echo "CREATE INDEX idx_place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])."_place_id ";
+ echo "ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." USING btree(place_id);\n";
+
+ }
}
$iPlaceID = (int)$_GET['place_id'];
+ $iParentPlaceID = $oDB->getOne('select parent_place_id from location_property_tiger where place_id = '.$iPlaceID);
+ if ($iParentPlaceID) $iPlaceID = $iParentPlaceID;
+
$aLangPrefOrder = getPrefferedLangauges();
$sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted",$aLangPrefOrder))."]";
{
$aSearch['sHouseNumber'] = $sToken;
if ($aSearch['iSearchRank'] < $iMaxRank) $aNewWordsetSearches[] = $aSearch;
-
+/*
// Fall back to not searching for this item (better than nothing)
$aSearch = $aCurrentSearch;
- $aSearch['iSearchRank'] += 2;
+ $aSearch['iSearchRank'] += 1;
if ($aSearch['iSearchRank'] < $iMaxRank) $aNewWordsetSearches[] = $aSearch;
+*/
}
}
elseif ($aSearchTerm['class'] !== '' && $aSearchTerm['class'] !== null)
{
$aSearch = $aCurrentSearch;
$aSearch['iSearchRank']+=5;
- $aSearch['aAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
+// $aSearch['aAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
if (!sizeof($aSearch['aName']) || $aSearch['iNamePhrase'] == $iPhrase)
{
$aSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
foreach($aSearches as $aSearch)
{
$iQueryLoop++;
+
// Must have a location term
- if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress'] && !$aSearch['fLon']))
+ if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']) && !$aSearch['fLon'])
{
if (!$bBoundingBoxSearch && !$aSearch['fLon']) continue;
if (!$aSearch['sClass']) continue;
}
else
{
- if ($aSearch['aName'] == array(282=>'282')) continue;
-
if (CONST_Debug) var_dump('<hr>',$aSearch);
if (CONST_Debug) _debugDumpGroupedSearches(array($iGroupedRank => array($aSearch)), $aValidTokens);
$aPlaceIDs = array();
if ($aSearch['sHouseNumber'] && sizeof($aPlaceIDs))
{
+ $aRoadPlaceIDs = $aPlaceIDs;
$sPlaceIDs = join(',',$aPlaceIDs);
- $sHouseNumberRegex = '\\\\m'.str_replace(' ','[-, ]',$aSearch['sHouseNumber']).'\\\\M';
-
- // Make sure everything nearby is indexed (if we pre-indexed houses this wouldn't be needed!)
- $sSQL = "update placex set indexed = true from placex as f where placex.indexed = false";
- $sSQL .= " and f.place_id in (".$sPlaceIDs.") and ST_DWithin(placex.geometry, f.geometry, 0.004)";
- $sSQL .= " and placex.housenumber ~* E'".$sHouseNumberRegex."'";
- $sSQL .= " and placex.class='place' and placex.type='house'";
- if (CONST_Debug) var_dump($sSQL);
- $oDB->query($sSQL);
-
// Now they are indexed look for a house attached to a street we found
+ $sHouseNumberRegex = '\\\\m'.str_replace(' ','[-, ]',$aSearch['sHouseNumber']).'\\\\M';
$sSQL = "select place_id from placex where parent_place_id in (".$sPlaceIDs.") and housenumber ~* E'".$sHouseNumberRegex."'";
if (sizeof($aExcludePlaceIDs))
{
$sSQL .= " limit $iLimit";
if (CONST_Debug) var_dump($sSQL);
$aPlaceIDs = $oDB->getCol($sSQL);
+
+ // If not try the tiger fallback table
+ if (!sizeof($aPlaceIDs))
+ {
+ $sSQL = "select place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'";
+ if (sizeof($aExcludePlaceIDs))
+ {
+ $sSQL .= " and place_id not in (".join(',',$aExcludePlaceIDs).")";
+ }
+// $sSQL .= " limit $iLimit";
+ if (CONST_Debug) var_dump($sSQL);
+ $aPlaceIDs = $oDB->getCol($sSQL);
+ }
+
+ // Fallback to the road
+ if (!sizeof($aPlaceIDs))
+ {
+ $aPlaceIDs = $aRoadPlaceIDs;
+ }
+
}
if ($aSearch['sClass'] && sizeof($aPlaceIDs))
{
$sOrderSQL .= 'when min(place_id) = '.$iPlaceID.' then '.$iOrder.' ';
}
- $sOrderSQL .= ' ELSE 10000000 END ASC';
+ $sOrderSQL .= ' ELSE 10000000 END';
$sSQL = "select osm_type,osm_id,class,type,rank_search,rank_address,min(place_id) as place_id,country_code,";
$sSQL .= "get_address_by_language(place_id, $sLanguagePrefArraySQL) as langaddress,";
$sSQL .= "get_name_by_language(name, $sLanguagePrefArraySQL) as placename,";
$sSQL .= "get_name_by_language(name, ARRAY['ref']) as ref,";
- $sSQL .= "avg(ST_X(ST_Centroid(geometry))) as lon,avg(ST_Y(ST_Centroid(geometry))) as lat ";
+ $sSQL .= "avg(ST_X(ST_Centroid(geometry))) as lon,avg(ST_Y(ST_Centroid(geometry))) as lat, ";
+ $sSQL .= $sOrderSQL." as porder ";
$sSQL .= "from placex where place_id in ($sPlaceIDs) ";
$sSQL .= "group by osm_type,osm_id,class,type,rank_search,rank_address,country_code";
if (!$bDeDupe) $sSQL .= ",place_id";
$sSQL .= ",get_address_by_language(place_id, $sLanguagePrefArraySQL) ";
$sSQL .= ",get_name_by_language(name, $sLanguagePrefArraySQL) ";
$sSQL .= ",get_name_by_language(name, ARRAY['ref']) ";
- $sSQL .= "order by rank_search,rank_address,".$sOrderSQL;
+ $sSQL .= " union ";
+ $sSQL .= "select 'T' as osm_type,place_id as osm_id,'place' as class,'house' as type,30 as rank_search,30 as rank_address,min(place_id) as place_id,'us' as country_code,";
+ $sSQL .= "get_tiger_address_by_language(place_id, $sLanguagePrefArraySQL) as langaddress,";
+ $sSQL .= "null as placename,";
+ $sSQL .= "null as ref,";
+ $sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, ";
+ $sSQL .= $sOrderSQL." as porder ";
+ $sSQL .= "from location_property_tiger where place_id in ($sPlaceIDs) ";
+ $sSQL .= "group by place_id";
+ if (!$bDeDupe) $sSQL .= ",place_id";
+ $sSQL .= ",get_tiger_address_by_language(place_id, $sLanguagePrefArraySQL) ";
+ $sSQL .= "order by rank_search,rank_address,porder asc";
if (CONST_Debug) var_dump('<hr>',$sSQL);
$aSearchResults = $oDB->getAll($sSQL);
//var_dump($sSQL,$aSearchResults);exit;
if ($bShowAddressDetails)
{
$aResult['address'] = getAddressDetails($oDB, $sLanguagePrefArraySQL, $aResult['place_id'], $aResult['country_code']);
+//var_dump($aResult['address']);
+//exit;
}
if (isset($aClassType[$aResult['class'].':'.$aResult['type']]['importance'])
$aResult['foundorder'] = $iResNum;
$aSearchResults[$iResNum] = $aResult;
}
+
+//var_dump($aSearchResults);exit;
uasort($aSearchResults, 'byImportance');
projects / nominatim.git / commitdiff