if (30 >= $this->iMinAddressRank && 30 <= $this->iMaxAddressRank)
{
- //query also location_property_osmline and location_property_tiger and location_property_aux
- //Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
- //only Tiger housenumbers need to be interpolated, because they are saved as lines with start- and endnumber, the common osm housenumbers are usually saved as points
- $sHousenumbers = "";
- $i = 0;
- $length = count($aPlaceIDs);
- foreach($aPlaceIDs as $placeID => $housenumber)
+ if (CONST_Use_US_Tiger_Data)
{
- $i++;
- $sHousenumbers .= "(".$placeID.", ".$housenumber.")";
- if($i<$length)
- $sHousenumbers .= ", ";
+ //query also location_property_tiger and location_property_aux
+ //Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
+ //only Tiger housenumbers need to be interpolated, because they are saved as lines with start- and endnumber, the common osm housenumbers are usually saved as points
+ $sHousenumbers = "";
+ $i = 0;
+ $length = count($aPlaceIDs);
+ foreach($aPlaceIDs as $placeID => $housenumber)
+ {
+ $i++;
+ $sHousenumbers .= "(".$placeID.", ".$housenumber.")";
+ if($i<$length)
+ $sHousenumbers .= ", ";
+ }
+
+ $sSQL .= "union ";
+ $sSQL .= "select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code";
+ $sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress ";
+ $sSQL .= ", null as placename";
+ $sSQL .= ", null as ref";
+ if ($this->bIncludeExtraTags) $sSQL .= ", null as extra";
+ if ($this->bIncludeNameDetails) $sSQL .= ", null as names";
+ $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
+ $sSQL .= $sImportanceSQL."-1.15 as importance ";
+ $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance ";
+ $sSQL .= ", null as extra_place ";
+ $sSQL .= " from (select place_id";
+ //interpolate the Tiger housenumbers here
+ $sSQL .= ", ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) as centroid, parent_place_id, housenumber_for_place ";
+ $sSQL .= "from (location_property_tiger ";
+ $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) ";
+ $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here
+ $sSQL .= " group by place_id, housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique
+ if (!$this->bDeDupe) $sSQL .= ", place_id ";
}
+ // osmline, osm_type is 'I' for Interpolation Line
+ $sSQL .= " union ";
+ $sSQL .= "select 'I' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, calculated_country_code as country_code, ";
+ $sSQL .= "get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress, ";
+ $sSQL .= "null as placename, ";
+ $sSQL .= "null as ref, ";
+ if ($this->bIncludeExtraTags) $sSQL .= "null as extra, ";
+ if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
+ $sSQL .= " avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
+ $sSQL .= $sImportanceSQL."-0.1 as importance, "; // slightly smaller than the importance for normal houses with rank 30, which is 0
+ $sSQL .= " (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
+ $sSQL .= " null as extra_place ";
+ $sSQL .= " from (select place_id, calculated_country_code ";
+ //interpolate the housenumbers here
+ $sSQL .= ", CASE WHEN startnumber != endnumber THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ";
+ $sSQL .= " ELSE ST_LineInterpolatePoint(linegeo, 0.5) END as centroid";
+ $sSQL .= ", parent_place_id, housenumber_for_place ";
+ $sSQL .= " from (location_property_osmline ";
+ $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) ";
+ $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here
+ $sSQL .= " group by place_id, housenumber_for_place, calculated_country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique
+ if (!$this->bDeDupe) $sSQL .= ", place_id ";
- // tiger
- $sSQL .= " union ";
- $sSQL .= "select 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 30 as rank_search, 30 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code";
- $sSQL .= ", get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) as langaddress ";
- $sSQL .= ", null as placename";
- $sSQL .= ", null as ref";
- if ($this->bIncludeExtraTags) $sSQL .= ", null as extra";
- if ($this->bIncludeNameDetails) $sSQL .= ", null as names";
- $sSQL .= ", avg(st_x(centroid)) as lon, avg(st_y(centroid)) as lat,";
- $sSQL .= $sImportanceSQL."-1.15 as importance ";
- $sSQL .= ", (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(blub.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance ";
- $sSQL .= ", null as extra_place ";
- $sSQL .= " from (select place_id";
- //interpolate the Tiger housenumbers here
- $sSQL .= ", CASE WHEN startnumber != endnumber THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ";
- $sSQL .= " ELSE ST_LineInterpolatePoint(linegeo, 0.5) END as centroid";
- $sSQL .= ", parent_place_id, housenumber_for_place ";
- $sSQL .= " from (location_property_tiger ";
- $sSQL .= " join (values ".$sHousenumbers.") as housenumbers(place_id, housenumber_for_place) using(place_id)) ";
- $sSQL .= " where housenumber_for_place>=0 and 30 between $this->iMinAddressRank and $this->iMaxAddressRank) as blub"; //postgres wants an alias here
- $sSQL .= " group by place_id, housenumber_for_place"; //is this group by really needed?, place_id + housenumber (in combination) are unique
- if (!$this->bDeDupe) $sSQL .= ", place_id ";
-
- // aux
- $sSQL .= " union ";
- $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, ";
- $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, ";
- $sSQL .= "null as placename, ";
- $sSQL .= "null as ref, ";
- if ($this->bIncludeExtraTags) $sSQL .= "null as extra, ";
- if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
- $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, ";
- $sSQL .= $sImportanceSQL."-1.10 as importance, ";
- $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
- $sSQL .= "null as extra_place ";
- $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) ";
- $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank ";
- $sSQL .= "group by place_id";
- if (!$this->bDeDupe) $sSQL .= ", place_id";
- $sSQL .= ", get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) ";
+ if (CONST_Use_Aux_Location_data)
+ {
+ $sSQL .= " union ";
+ $sSQL .= "select 'L' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, 0 as rank_search, 0 as rank_address, min(place_id) as place_id, min(parent_place_id) as parent_place_id, 'us' as country_code, ";
+ $sSQL .= "get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress, ";
+ $sSQL .= "null as placename, ";
+ $sSQL .= "null as ref, ";
+ if ($this->bIncludeExtraTags) $sSQL .= "null as extra, ";
+ if ($this->bIncludeNameDetails) $sSQL .= "null as names, ";
+ $sSQL .= "avg(ST_X(centroid)) as lon, avg(ST_Y(centroid)) as lat, ";
+ $sSQL .= $sImportanceSQL."-1.10 as importance, ";
+ $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
+ $sSQL .= "null as extra_place ";
+ $sSQL .= "from location_property_aux where place_id in ($sPlaceIDs) ";
+ $sSQL .= "and 30 between $this->iMinAddressRank and $this->iMaxAddressRank ";
+ $sSQL .= "group by place_id";
+ if (!$this->bDeDupe) $sSQL .= ", place_id";
+ $sSQL .= ", get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) ";
+ }
}
$sSQL .= " order by importance desc";
$sSQL .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
$aPlaceIDs = $this->oDB->getCol($sSQL);
+
+ // if nothing found, search in the interpolation line table
+ if(!sizeof($aPlaceIDs))
+ {
+ // do we need to use transliteration and the regex for housenumbers???
+ //new query for lines, not housenumbers anymore
+ if($searchedHousenumber%2 == 0){
+ //if housenumber is even, look for housenumber in streets with interpolationtype even or all
+ $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+ }else{
+ //look for housenumber in streets with interpolationtype odd or all
+ $sSQL = "select distinct place_id from location_property_osmline where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+ }
+ if (sizeof($this->aExcludePlaceIDs))
+ {
+ $sSQL .= " and parent_place_id not in (".join(',', $this->aExcludePlaceIDs).")";
+ }
+ //$sSQL .= " limit $this->iLimit";
+ if (CONST_Debug) var_dump($sSQL);
+ //get place IDs
+ $aPlaceIDs = $this->oDB->getCol($sSQL, 0);
+ }
+
// If nothing found try the aux fallback table
- if (!sizeof($aPlaceIDs))
+ if (CONST_Use_Aux_Location_data && !sizeof($aPlaceIDs))
{
$sSQL = "select place_id from location_property_aux where parent_place_id in (".$sPlaceIDs.") and housenumber = '".pg_escape_string($aSearch['sHouseNumber'])."'";
if (sizeof($this->aExcludePlaceIDs))
if (CONST_Debug) var_dump($sSQL);
$aPlaceIDs = $this->oDB->getCol($sSQL);
}
- //if nothing found search in Tiger data for this housenumber(location_property_tiger)
- if (!sizeof($aPlaceIDs))
++
+ //if nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger)
+ $searchedHousenumber = intval($aSearch['sHouseNumber']);
+ if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs))
{
//new query for lines, not housenumbers anymore
if($searchedHousenumber%2 == 0){
$iParentPlaceID = $aPlace['parent_place_id'];
$bIsInUnitedStates = ($aPlace['calculated_country_code'] == 'us');
}
+ // if a street or house was found, look in interpolation lines table
+ if ($iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 || $aPlace['rank_search'] == 30))
+ {
+ $fSearchDiam = 0.001;
+ if ($aPlace['rank_search'] == 30)
+ {
+ // if a house was found, the closest road needs to be searched, to use its place id as parent_place_id of the interpolation line
+ // because a road can be closer to the point than the house from above
+ $iRoadID = null;
+ while(!$iRoadID && $fSearchDiam < $fMaxAreaDistance)
+ {
+ $fSearchDiam = $fSearchDiam * 2;
+ $sSQL = 'select place_id ';
+ $sSQL .= ' FROM placex';
+ $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
+ $sSQL .= ' and (rank_search = 26 or rank_search = 27)';
+ $sSQL .= ' and class not in (\'waterway\',\'railway\',\'tunnel\',\'bridge\',\'man_made\')';
+ $sSQL .= ' and indexed_status = 0 ';
+ $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', geometry) ASC limit 1';
+ $aPlaceRoad = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aPlace))
+ {
+ failInternalError("Could not determine closest place.", $sSQL, $aPlace);
+ }
+ $iRoadID = $aPlaceRoad['place_id'];
+ $iTempPlaceID = $iRoadID;
+ }
+ }
+ else
+ {
+ // if a street was found, we can take its place_id as parent_place_id
+ $iTempPlaceID = $iPlaceID;
+ }
+ $sSQL = 'SELECT place_id, parent_place_id, 30 as rank_search, ST_line_locate_point(linegeo,'.$sPointSQL.') as fraction';
+ //if (CONST_Debug) { $sSQL .= ', housenumber, ST_distance('.$sPointSQL.', centroid) as distance, st_y(centroid) as lat, st_x(centroid) as lon'; }
+ $sSQL .= ' FROM location_property_osmline WHERE parent_place_id = '.$iTempPlaceID;
+ $sSQL .= ' AND ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.') AND indexed_status = 0';
+ $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', linegeo) ASC limit 1';
+ if (CONST_Debug)
+ {
+ $sSQL = preg_replace('/limit 1/', 'limit 100', $sSQL);
+ var_dump($sSQL);
+ $aAllHouses = $this->oDB->getAll($sSQL);
+ foreach($aAllHouses as $i)
+ {
+ echo $i['housenumber'] . ' | ' . $i['distance'] * 1000 . ' | ' . $i['lat'] . ' | ' . $i['lon']. ' | '. "<br>\n";
+ }
+ }
+ $aPlaceLine = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aPlaceLine))
+ {
+ failInternalError("Could not determine closest housenumber on an osm interpolation line.", $sSQL, $aPlaceLine);
+ }
+ $iInterpolationLinePlaceID = $aPlaceLine['place_id'];
+ if ($aPlaceLine)
+ {
+ if (CONST_Debug) var_dump('found housenumber in interpolation lines table', $aPlaceLine);
+ if ($aPlace['rank_search'] == 30)
+ {
+ // if a house was already found in placex, we have to find out,
+ // if the placex house or the interpolated house are closer to the searched point
+ // distance between point and placex house
+ $sSQL = 'SELECT ST_distance('.$sPointSQL.', house.geometry) as distance FROM placex as house WHERE house.place_id='.$iPlaceID;
+ $aDistancePlacex = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aDistancePlacex))
+ {
+ failInternalError("Could not determine distance between searched point and placex house.", $sSQL, $aDistancePlacex);
+ }
+ $fDistancePlacex = $aDistancePlacex['distance'];
+ // distance between point and interpolated house (fraction on interpolation line)
+ $sSQL = 'SELECT ST_distance('.$sPointSQL.', ST_LineInterpolatePoint(linegeo, '.$aPlaceLine['fraction'].')) as distance';
+ $sSQL .= ' FROM location_property_osmline WHERE place_id = '.$iInterpolationLinePlaceID;
+ $aDistanceInterpolation = $this->oDB->getRow($sSQL);
+ if (PEAR::IsError($aDistanceInterpolation))
+ {
+ failInternalError("Could not determine distance between searched point and interpolated house.", $sSQL, $aDistanceInterpolation);
+ }
+ $fDistanceInterpolation = $aDistanceInterpolation['distance'];
+ if ($fDistanceInterpolation < $fDistancePlacex)
+ {
+ // interpolation is closer to point than placex house
+ $bPlaceIsLine = true;
+ $aPlace = $aPlaceLine;
+ $iPlaceID = $iInterpolationLinePlaceID;
+ $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street
+ $fFraction = $aPlaceLine['fraction'];
+ }
+ // else: nothing to do, take placex house from above
+ }
+ else
+ {
+ $bPlaceIsLine = true;
+ $aPlace = $aPlaceLine;
+ $iPlaceID = $aPlaceLine['place_id'];
+ $iParentPlaceID = $aPlaceLine['parent_place_id']; // the street
+ $fFraction = $aPlaceLine['fraction'];
+ }
+ }
+ }
+
// Only street found? If it's in the US we can check TIGER data for nearest housenumber
- if ($bIsInUnitedStates && $iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 ))
+ if (CONST_Use_US_Tiger_Data && $bIsInUnitedStates && $iMaxRank_orig >= 28 && $iPlaceID && ($aPlace['rank_search'] == 26 || $aPlace['rank_search'] == 27 ))
{
$fSearchDiam = 0.001;
$sSQL = 'SELECT place_id,parent_place_id,30 as rank_search, ST_line_locate_point(linegeo,'.$sPointSQL.') as fraction';
END IF;
END IF;
END IF;
-
- -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
- IF NEW.importance is null THEN
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
- END IF;
- -- Still null? how about looking it up by the node id
- IF NEW.importance IS NULL THEN
- select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
- END IF;
-
END IF;
- -- make sure all names are in the word table
- IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
- perform create_country(NEW.name, lower(NEW.country_code));
+ -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
+ IF NEW.importance is null THEN
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ END IF;
+ -- Still null? how about looking it up by the node id
+ IF NEW.importance IS NULL THEN
+ select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
END IF;
- NEW.parent_place_id = 0;
- parent_place_id_rank = 0;
-
- -- convert isin to array of tokenids
- isin_tokens := '{}'::int[];
- IF NEW.isin IS NOT NULL THEN
- isin := regexp_split_to_array(NEW.isin, E'[;,]');
- IF array_upper(isin, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin, 1) LOOP
- address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
+ END IF;
- -- merge word into address vector
- address_street_word_id := get_word_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- END IF;
- END LOOP;
- END IF;
- END IF;
- IF NEW.postcode IS NOT NULL THEN
- isin := regexp_split_to_array(NEW.postcode, E'[;,]');
- IF array_upper(isin, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(isin, 1) LOOP
- address_street_word_id := get_name_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
- END IF;
+ -- make sure all names are in the word table
+ IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN
+ perform create_country(NEW.name, lower(NEW.country_code));
+ END IF;
- -- merge into address vector
- address_street_word_id := get_word_id(make_standard_name(isin[i]));
- IF address_street_word_id IS NOT NULL THEN
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- END IF;
- END LOOP;
- END IF;
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
+
++
+ -- convert isin to array of tokenids
+ isin_tokens := '{}'::int[];
+ IF NEW.isin IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.isin, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+
+ -- merge word into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
+ END LOOP;
END IF;
+ END IF;
+ IF NEW.postcode IS NOT NULL THEN
+ isin := regexp_split_to_array(NEW.postcode, E'[;,]');
+ IF array_upper(isin, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(isin, 1) LOOP
+ address_street_word_id := get_name_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
- -- %NOTIGERDATA% IF 0 THEN
- -- for the USA we have an additional address table. Merge in zip codes from there too
- IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
- FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
- address_street_word_id := get_name_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
- isin_tokens := isin_tokens || address_street_word_id;
-
- -- also merge in the single word version
- address_street_word_id := get_word_id(make_standard_name(location.postcode));
- nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ -- merge into address vector
+ address_street_word_id := get_word_id(make_standard_name(isin[i]));
+ IF address_street_word_id IS NOT NULL THEN
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END IF;
END LOOP;
END IF;
- -- %NOTIGERDATA% END IF;
+ END IF;
+
++ -- %NOTIGERDATA% IF 0 THEN
+ -- for the USA we have an additional address table. Merge in zip codes from there too
+ IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+ FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
+ address_street_word_id := get_name_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ isin_tokens := isin_tokens || address_street_word_id;
+
+ -- also merge in the single word version
+ address_street_word_id := get_word_id(make_standard_name(location.postcode));
+ nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+ END LOOP;
+ END IF;
++ -- %NOTIGERDATA% END IF;
-- RAISE WARNING 'ISIN: %', isin_tokens;
countryname HSTORE;
hadcountry BOOLEAN;
BEGIN
-
- --first query tiger data
- -- %NOTIGERDATA% IF 0 THEN
- select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
+ -- first query osmline (interpolation lines)
+ select parent_place_id, calculated_country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
IF for_place_id IS NOT NULL THEN
searchhousenumber = in_housenumber::text;
END IF;
-
++
+ --then query tiger data
++ -- %NOTIGERDATA% IF 0 THEN
+ IF for_place_id IS NULL THEN
+ select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
+ WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
+ INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ IF for_place_id IS NOT NULL THEN
+ searchhousenumber = in_housenumber::text;
+ END IF;
+ END IF;
-
+ -- %NOTIGERDATA% END IF;
+
+ -- %NOAUXDATA% IF 0 THEN
IF for_place_id IS NULL THEN
select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
WHERE place_id = in_place_id
);
SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
-
+CREATE TABLE location_property_aux () INHERITS (location_property);
+CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id);
+CREATE INDEX idx_location_property_aux_parent_place_id ON location_property_aux USING BTREE (parent_place_id);
+CREATE INDEX idx_location_property_aux_housenumber_parent_place_id ON location_property_aux USING BTREE (parent_place_id, housenumber);
+GRANT SELECT ON location_property_aux TO "{www-user}";
+
+CREATE TABLE location_property_tiger (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT);
+GRANT SELECT ON location_property_tiger TO "{www-user}";
+
+CREATE TABLE location_property_osmline (
+ linegeo GEOMETRY,
+ place_id BIGINT NOT NULL,
+ partition INTEGER,
+ osm_id BIGINT,
+ parent_place_id BIGINT,
+ startnumber INTEGER,
+ endnumber INTEGER,
+ interpolationtype TEXT,
+ admin_level INTEGER,
+ street TEXT,
+ postcode TEXT,
+ calculated_country_code VARCHAR(2),
+ geometry_sector INTEGER,
+ indexed_status INTEGER,
+ indexed_date TIMESTAMP);
+CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline (place_id) {ts:search-index};
+CREATE INDEX idx_osmline_parent_place_id ON location_property_osmline (parent_place_id) {ts:search-index};
+GRANT SELECT ON location_property_osmline TO "{www-user}";
+
drop table IF EXISTS search_name;
CREATE TABLE search_name (
place_id BIGINT,
projects / nominatim.git / commitdiff