if ($this->bIncludeNameDetails) $sSQL .= "hstore_to_json(name)::text as names,";
$sSQL .= "avg(ST_X(centroid)) as lon,avg(ST_Y(centroid)) as lat, ";
$sSQL .= $sImportanceSQL."coalesce(importance,0.75-(rank_search::float/40)) as importance, ";
- $sSQL .= "(select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
+ $sSQL .= "(select max(p.importance*(p.rank_address+2))";
+ $sSQL .= " from place_addressline s, placex p";
+ $sSQL .= " where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END)";
+ $sSQL .= " and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance, ";
$sSQL .= "(extratags->'place') as extra_place ";
$sSQL .= "from placex where place_id in ($sPlaceIDs) ";
$sSQL .= "and (placex.rank_address between $this->iMinAddressRank and $this->iMaxAddressRank ";
if (CONST_Use_US_Tiger_Data) {
// Tiger search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
$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";
+ $sSQL .= " select 'T' as osm_type, place_id as osm_id, 'place' as class,";
+ $sSQL .= " 'house' as type, null as admin_level, 30 as rank_search,";
+ $sSQL .= " 30 as rank_address, min(place_id) as place_id,";
+ $sSQL .= " 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, 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 .= ", (select max(p.importance*(p.rank_address+2))";
+ $sSQL .= " from place_addressline s, placex p";
+ $sSQL .= " where s.place_id = min(blub.parent_place_id)";
+ $sSQL .= " and p.place_id = s.address_place_id and s.isaddress";
+ $sSQL .= " and p.importance is not null) as addressimportance ";
$sSQL .= ", null as extra_place ";
$sSQL .= " from (select place_id";
// interpolate the Tiger housenumbers here
// osmline
// interpolation line search only if a housenumber was searched and if it was found (i.e. aPlaceIDs[placeID] = housenumber != -1) (realized through a join)
$sSQL .= " union ";
- $sSQL .= "select 'W' 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 .= "select 'W' as osm_type, place_id as osm_id, 'place' as class,";
+ $sSQL .= " 'house' as type, null as admin_level, 30 as rank_search,";
+ $sSQL .= " 30 as rank_address, min(place_id) as place_id,";
+ $sSQL .= " 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 (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 .= "select 'L' as osm_type, place_id as osm_id, 'place' as class,";
+ $sSQL .= " 'house' as type, null as admin_level, 0 as rank_search,";
+ $sSQL .= " 0 as rank_address, min(place_id) as place_id,";
+ $sSQL .= " 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->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 .= "(select max(p.importance*(p.rank_address+2))";
+ $sSQL .= " from place_addressline s, placex p";
+ $sSQL .= " where s.place_id = min(location_property_aux.parent_place_id)";
+ $sSQL .= " and p.place_id = s.address_place_id and s.isaddress";
+ $sSQL .= " 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 .= " limit $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
$aPlaceIDs = chksql($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
+ $sSQL = "select distinct place_id from location_property_osmline";
+ $sSQL .= " where parent_place_id in (".$sPlaceIDs.") and (";
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";
+ $sSQL .= "interpolationtype='even'";
} 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";
+ $sSQL .= "interpolationtype='odd'";
}
+ $sSQL .= " or interpolationtype='all') and ";
+ $sSQL .= $searchedHousenumber.">=startnumber and ";
+ $sSQL .= $searchedHousenumber."<=endnumber";
if (sizeof($this->aExcludePlaceIDs)) {
$sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";
//get place IDs
$aPlaceIDs = chksql($this->oDB->getCol($sSQL, 0));
}
-
+
// If nothing found try the aux fallback table
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 nothing was found in placex or location_property_aux, then search in Tiger data for this housenumber(location_property_tiger)
if (CONST_Use_US_Tiger_Data && !sizeof($aPlaceIDs)) {
- //new query for lines, not housenumbers anymore
+ $sSQL = "select distinct place_id from location_property_tiger";
+ $sSQL .= " where parent_place_id in (".$sPlaceIDs.") and (";
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_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='even' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+ $sSQL .= "interpolationtype='even'";
} else {
- //look for housenumber in streets with interpolationtype odd or all
- $sSQL = "select distinct place_id from location_property_tiger where parent_place_id in (".$sPlaceIDs.") and (interpolationtype='odd' or interpolationtype='all') and ".$searchedHousenumber.">=startnumber and ".$searchedHousenumber."<=endnumber";
+ $sSQL .= "interpolationtype='odd'";
}
+ $sSQL .= " or interpolationtype='all') and ";
+ $sSQL .= $searchedHousenumber.">=startnumber and ";
+ $sSQL .= $searchedHousenumber."<=endnumber";
if (sizeof($this->aExcludePlaceIDs)) {
$sSQL .= " and place_id not in (".join(',', $this->aExcludePlaceIDs).")";
$sPlaceGeom = false;
if ($this->iMaxRank < 9 && $bCacheTable) {
// Try and get a polygon to search in instead
- $sSQL = "select geometry from placex where place_id in ($sPlaceIDs) and rank_search < $this->iMaxRank + 5 and st_geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon') order by rank_search asc limit 1";
+ $sSQL = "select geometry from placex";
+ $sSQL .= " where place_id in ($sPlaceIDs)";
+ $sSQL .= " and rank_search < $this->iMaxRank + 5";
+ $sSQL .= " and st_geometrytype(geometry) in ('ST_Polygon','ST_MultiPolygon')";
+ $sSQL .= " order by rank_search asc limit 1";
if (CONST_Debug) var_dump($sSQL);
$sPlaceGeom = chksql($this->oDB->getOne($sSQL));
}
// but this will never happen, because if the searched point is that close to the endnumber, the endnumber house will be directly taken from placex (in ReverseGeocode.php line 220)
// and not interpolated
} else {
- $sSQL = "select placex.place_id, partition, osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode, country_code, parent_place_id, linked_place_id, rank_address, rank_search, ";
+ $sSQL = "select placex.place_id, partition, osm_type, osm_id, class,";
+ $sSQL .= " type, admin_level, housenumber, street, isin, postcode, country_code,";
+ $sSQL .= " parent_place_id, linked_place_id, rank_address, rank_search, ";
$sSQL .= " coalesce(importance,0.75-(rank_search::float/40)) as importance, indexed_status, indexed_date, wikipedia, calculated_country_code, ";
$sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress,";
$sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL) as placename,";
echo "<html><body><h1>Internal Server Error</h1>";
echo '<p>Nominatim has encountered an internal error while processing your request. This is most likely because of a bug in the software.</p>';
echo "<p><b>Details:</b> ".$sError,"</p>";
- echo '<p>Feel free to file an issue on <a href="https://github.com/twain47/Nominatim/issues">Github</a>. Please include the error message above and the URL you used.</p>';
+ echo '<p>Feel free to file an issue on <a href="https://github.com/twain47/Nominatim/issues">Github</a>. ';
+ echo 'Please include the error message above and the URL you used.</p>';
if (CONST_Debug) {
echo "<hr><h2>Debugging Information</h2><br>";
if ($sSQL) {
echo "<html><body><h1>Bad Request</h1>";
echo '<p>Nominatim has encountered an error with your request.</p>';
echo "<p><b>Details:</b> ".$sError."</p>";
- echo '<p>If you feel this error is incorrect feel file an issue on <a href="https://github.com/twain47/Nominatim/issues">Github</a>. Please include the error message above and the URL you used.</p>';
+ echo '<p>If you feel this error is incorrect feel file an issue on <a href="https://github.com/twain47/Nominatim/issues">Github</a>. ';
+ echo 'Please include the error message above and the URL you used.</p>';
echo "\n</body></html>\n";
exit;
}
}
}
echo "<table border=\"1\">";
- echo "<tr><th>rank</th><th>Name Tokens</th><th>Name Not</th><th>Address Tokens</th><th>Address Not</th><th>country</th><th>operator</th><th>class</th><th>type</th><th>house#</th><th>Lat</th><th>Lon</th><th>Radius</th></tr>";
+ echo "<tr><th>rank</th><th>Name Tokens</th><th>Name Not</th>";
+ echo "<th>Address Tokens</th><th>Address Not</th><th>country</th>";
+ echo "<th>operator</th><th>class</th><th>type</th><th>house#</th>";
+ echo "<th>Lat</th><th>Lon</th><th>Radius</th></tr>";
foreach ($aData as $iRank => $aRankedSet) {
foreach ($aRankedSet as $aRow) {
echo "<tr>";
<!-- https://github.com/squizlabs/PHP_CodeSniffer/blob/master/CodeSniffer/Standards/PSR2/ruleset.xml -->
<rule ref="PSR2"/>
-
-
-
- <!-- currently 300 warnings, we set a limit later -->
<rule ref="Generic.Files.LineLength">
<properties>
- <property name="lineLimit" value="9999"/>
- <property name="absoluteLineLimit" value="0"/>
+ <property name="lineLimit" value="199"/>
+ <property name="absoluteLineLimit" value="199"/>
</properties>
</rule>
if (isset($aCMDResult['parse-wikipedia'])) {
$oDB =& getDB();
- $aArticleNames = $oDB->getCol('select page_title from content where page_namespace = 0 and page_id %10 = '.$aCMDResult['parse-wikipedia'].' and (page_content ilike \'%{{Coord%\' or (page_content ilike \'%lat%\' and page_content ilike \'%lon%\'))');
- // $aArticleNames = $oDB->getCol($sSQL = 'select page_title from content where page_namespace = 0 and (page_content ilike \'%{{Coord%\' or (page_content ilike \'%lat%\' and page_content ilike \'%lon%\')) and page_title in (\'Virginia\')');
+ $sSQL = 'select page_title from content where page_namespace = 0 and page_id %10 = ';
+ $sSQL .= $aCMDResult['parse-wikipedia'];
+ $sSQL .= ' and (page_content ilike \'%{{Coord%\' or (page_content ilike \'%lat%\' and page_content ilike \'%lon%\'))'
+ $aArticleNames = $oDB->getCol($sSQL);
+ /* $aArticleNames = $oDB->getCol($sSQL = 'select page_title from content where page_namespace = 0
+ and (page_content ilike \'%{{Coord%\' or (page_content ilike \'%lat%\'
+ and page_content ilike \'%lon%\')) and page_title in (\'Virginia\')');
+ */
foreach ($aArticleNames as $sArticleName) {
$sPageText = $oDB->getOne('select page_content from content where page_namespace = 0 and page_title = \''.pg_escape_string($sArticleName).'\'');
$aP = _templatesToProperties(_parseWikipediaContent($sPageText));
elseif ($iRank <= 26) $fMaxDist = 0.001;
else $fMaxDist = 0.001;
}
- echo "-- FOUND \"".substr($aNominatRecords[$i]['DISPLAY_NAME'], 0, 50)."\", ".$aNominatRecords[$i]['CLASS'].", ".$aNominatRecords[$i]['TYPE'].", ".$aNominatRecords[$i]['PLACE_RANK'].", ".$aNominatRecords[$i]['OSM_TYPE']." (dist:$fDiff, max:$fMaxDist)\n";
+ echo "-- FOUND \"".substr($aNominatRecords[$i]['DISPLAY_NAME'], 0, 50);
+ echo "\", ".$aNominatRecords[$i]['CLASS'].", ".$aNominatRecords[$i]['TYPE'];
+ echo ", ".$aNominatRecords[$i]['PLACE_RANK'].", ".$aNominatRecords[$i]['OSM_TYPE'];
+ echo " (dist:$fDiff, max:$fMaxDist)\n";
if ($fDiff > $fMaxDist) {
echo "-- Diff too big $fDiff (max: $fMaxDist)".$aRecord['lat'].','.$aNominatRecords[$i]['LAT'].' & '.$aRecord['lon'].','.$aNominatRecords[$i]['LON']." \n";
} else {
}
// used by getorcreate_word_id to ignore frequent partial words
- if (!pg_query($oDB->connection, 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS $$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE')) fail(pg_last_error($oDB->connection));
+ $sSQL = 'CREATE OR REPLACE FUNCTION get_maxwordfreq() RETURNS integer AS ';
+ $sSQL .= '$$ SELECT '.CONST_Max_Word_Frequency.' as maxwordfreq; $$ LANGUAGE SQL IMMUTABLE';
+ if (!pg_query($oDB->connection, $sSQL)) {
+ fail(pg_last_error($oDB->connection));
+ }
echo ".\n";
// pre-create the word list
$aPartitions = chksql($oDB->getCol($sSQL));
$aPartitions[] = 0;
- $sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' ' and class is null and type is null and country_code is null group by word_token having count(*) > 1 order by word_token";
+ $sSQL = "select word_token,count(*) from word where substr(word_token, 1, 1) = ' '";
+ $sSQL .= " and class is null and type is null and country_code is null";
+ $sSQL .= " group by word_token having count(*) > 1 order by word_token";
$aDuplicateTokens = chksql($oDB->getAll($sSQL));
foreach ($aDuplicateTokens as $aToken) {
if (trim($aToken['word_token']) == '' || trim($aToken['word_token']) == '-') continue;
echo "Deduping ".$aToken['word_token']."\n";
- $sSQL = "select word_id,(select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num from word where word_token = '".$aToken['word_token']."' and class is null and type is null and country_code is null order by num desc";
+ $sSQL = "select word_id,";
+ $sSQL .= " (select count(*) from search_name where nameaddress_vector @> ARRAY[word_id]) as num";
+ $sSQL .= " from word where word_token = '".$aToken['word_token'];
+ $sSQL .= "' and class is null and type is null and country_code is null order by num desc";
$aTokenSet = chksql($oDB->getAll($sSQL));
$aKeep = array_shift($aTokenSet);
$oDB =& getDB();
-$sSQL = "select placex.place_id, calculated_country_code as country_code, name->'name' as name, i.* from placex, import_polygon_delete i where placex.osm_id = i.osm_id and placex.osm_type = i.osm_type and placex.class = i.class and placex.type = i.type";
+$sSQL = "select placex.place_id, calculated_country_code as country_code,";
+$sSQL .= " name->'name' as name, i.* from placex, import_polygon_delete i";
+$sSQL .= " where placex.osm_id = i.osm_id and placex.osm_type = i.osm_type";
+$sSQL .= " and placex.class = i.class and placex.type = i.type";
$aPolygons = chksql($oDB->getAll($sSQL), "Could not get list of deleted OSM elements.");
if (CONST_Debug) {
// Be nice about our error messages for broken geometry
if (!$sPlaceId) {
- $aPointDetails = chksql($oDB->getRow("select osm_type, osm_id, errormessage, class, type, get_name_by_language(name,$sLanguagePrefArraySQL) as localname, ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom from import_polygon_error where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by updated desc limit 1"));
+ $sSQL = "select osm_type, osm_id, errormessage, class, type,";
+ $sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname,";
+ $sSQL .= " ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom";
+ $sSQL .= " from import_polygon_error where osm_type = '".$sOsmType;
+ $sSQL .= "' and osm_id = ".$iOsmId." order by updated desc limit 1";
+ $aPointDetails = chksql($oDB->getRow($sSQL));
if (!PEAR::isError($aPointDetails) && $aPointDetails) {
if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) {
$aPointDetails['error_x'] = $aMatches[1];
$hLog = logStart($oDB, 'details', $_SERVER['QUERY_STRING'], $aLangPrefOrder);
// Get the details for this point
-$sSQL = "select place_id, osm_type, osm_id, class, type, name, admin_level, housenumber, street, isin, postcode, calculated_country_code as country_code, importance, wikipedia,";
-$sSQL .= " to_char(indexed_date, 'YYYY-MM-DD HH24:MI') as indexed_date, parent_place_id, rank_address, rank_search, get_searchrank_label(rank_search) as rank_search_label, get_name_by_language(name,$sLanguagePrefArraySQL) as localname, ";
+$sSQL = "select place_id, osm_type, osm_id, class, type, name, admin_level,";
+$sSQL .= " housenumber, street, isin, postcode, calculated_country_code as country_code,";
+$sSQL .= " importance, wikipedia,";
+$sSQL .= " to_char(indexed_date, 'YYYY-MM-DD HH24:MI') as indexed_date,";
+$sSQL .= " parent_place_id, rank_address, rank_search,";
+$sSQL .= " get_searchrank_label(rank_search) as rank_search_label,";
+$sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, ";
$sSQL .= " ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, ";
//$sSQL .= " ST_Area(geometry::geography) as area, ";
$sSQL .= " ST_y(centroid) as lat, ST_x(centroid) as lon,";
$aAddressLines = getAddressDetails($oDB, $sLanguagePrefArraySQL, $iPlaceID, $aPointDetails['country_code'], -1, true);
// Linked places
-$sSQL = "select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, ST_DistanceSpheroid(geometry, placegeometry, 'SPHEROID[\"WGS 84\",6378137,298.257223563, AUTHORITY[\"EPSG\",\"7030\"]]') as distance, ";
+$sSQL = "select placex.place_id, osm_type, osm_id, class, type, housenumber,";
+$sSQL .= " admin_level, rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea,";
+$sSQL .= " ST_DistanceSpheroid(geometry, placegeometry, 'SPHEROID[\"WGS 84\",6378137,298.257223563, AUTHORITY[\"EPSG\",\"7030\"]]') as distance, ";
$sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, length(name::text) as namelength ";
$sSQL .= " from placex, (select centroid as placegeometry from placex where place_id = $iPlaceID) as x";
$sSQL .= " where linked_place_id = $iPlaceID";
}
// All places this is an imediate parent of
-$sSQL = "select obj.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, ST_DistanceSpheroid(geometry, placegeometry, 'SPHEROID[\"WGS 84\",6378137,298.257223563, AUTHORITY[\"EPSG\",\"7030\"]]') as distance, ";
+$sSQL = "select obj.place_id, osm_type, osm_id, class, type, housenumber,";
+$sSQL .= " admin_level, rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea,";
+$sSQL .= " ST_DistanceSpheroid(geometry, placegeometry, 'SPHEROID[\"WGS 84\",6378137,298.257223563, AUTHORITY[\"EPSG\",\"7030\"]]') as distance, ";
$sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, length(name::text) as namelength ";
$sSQL .= " from (select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, rank_search, geometry, name from placex ";
$sSQL .= " where parent_place_id = $iPlaceID order by rank_address asc,rank_search asc limit 500) as obj,";
// Be nice about our error messages for broken geometry
if (!$sPlaceId) {
- $aPointDetails = chksql($oDB->getRow("select osm_type, osm_id, errormessage, class, type, get_name_by_language(name,$sLanguagePrefArraySQL) as localname, ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom from import_polygon_error where osm_type = '".$sOsmType."' and osm_id = ".$iOsmId." order by updated desc limit 1"));
+ $sSQL = "select osm_type, osm_id, errormessage, class, type,";
+ $sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname,";
+ $sSQL .= " ST_AsText(prevgeometry) as prevgeom, ST_AsText(newgeometry) as newgeom";
+ $sSQL .= " from import_polygon_error where osm_type = '".$sOsmType;
+ $sSQL .= "' and osm_id = ".$iOsmId." order by updated desc limit 1";
+ $aPointDetails = chksql($oDB->getRow($sSQL));
if ($aPointDetails) {
if (preg_match('/\[(-?\d+\.\d+) (-?\d+\.\d+)\]/', $aPointDetails['errormessage'], $aMatches)) {
$aPointDetails['error_x'] = $aMatches[1];
$aRelatedPlaceIDs = chksql($oDB->getCol($sSQL = "select place_id from placex where linked_place_id = $iPlaceID or place_id = $iPlaceID"));
-$sSQL = "select obj.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, st_area(geometry) as area, ";
+$sSQL = "select obj.place_id, osm_type, osm_id, class, type, housenumber, admin_level,";
+$sSQL .= " rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, st_area(geometry) as area, ";
$sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, length(name::text) as namelength ";
$sSQL .= " from (select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, rank_search, geometry, name from placex ";
$sSQL .= " where parent_place_id in (".join(',', $aRelatedPlaceIDs).") and name is not null order by rank_address asc,rank_search asc limit 500) as obj";
} elseif ($aClaim['m'][2] == 'globecoordinate') {
//
if ($aClaim['m'][3]['globe'] != 'http://www.wikidata.org/entity/Q2') $bFail = true;
- fputcsv($hFileEntityProperty, array($iID, $iClaim, $iPID, null, null, "SRID=4326;POINT(".((float) $aClaim['m'][3]['longitude'])." ".((float)$aClaim['m'][3]['latitude']).")", null));
- // echo "insert into entity_property values (nextval('seq_entity_property'),".$iID.",".$iPID.",null,null,ST_SetSRID(ST_MakePoint(".((float)$aClaim['m'][3]['longitude']).", ".((float)$aClaim['m'][3]['latitude'])."),4326));\n";
+ fputcsv(
+ $hFileEntityProperty,
+ array(
+ $iID,
+ $iClaim,
+ $iPID,
+ null,
+ null,
+ "SRID=4326;POINT(".((float) $aClaim['m'][3]['longitude'])." ".((float)$aClaim['m'][3]['latitude']).")", null
+ )
+ );
+ /* echo "insert into entity_property values (nextval('seq_entity_property'),";
+ * echo $iID.",".$iPID.",null,null,ST_SetSRID(ST_MakePoint(".((float)$aClaim['m'][3]['longitude']);
+ * echo ", ".((float)$aClaim['m'][3]['latitude'])."),4326));\n";
+ */
} elseif ($aClaim['m'][2] == 'time') {
// TODO!
/*
// Notes:
/*
- gzip -dc $FILE.gz | grep -e "^en [^ :]\+ [0-9]\+" | sed "s#\(^[a-z]\{2\}\) \([^ :]\+\) \([0-9]\+\) [0-9]\+#update wikipedia_article set hit_count = coalesce(hit_count,0) + \3 where language = '\1' and title = catch_decode_url_part('\2');#g" | /opt/mapquest/stdbase-dev$
+ gzip -dc $FILE.gz | grep -e "^en [^ :]\+ [0-9]\+" |
+ sed "s#\(^[a-z]\{2\}\) \([^ :]\+\) \([0-9]\+\) [0-9]\+#update wikipedia_article set hit_count = coalesce(hit_count,0) + \3 where language = '\1'
+ and title = catch_decode_url_part('\2');#g" | /opt/mapquest/stdbase-dev$
cat totals.txt | sed "s#\(^[a-z]\{2\}\) \([^ ]\+\) \([0-9]\+\)\$#update entity_link set hits = s,0) + \3 where target = '\1wiki' and value = catch_decode_url_part('\2');#g"
cat totals.txt | sed "s#\(^[a-z]\{2\}\) \([^ ]\+\) \([0-9]\+\)\$#update entity_link set hits = coalesce(hits,0) + \3 where target = '\1wiki' and value = catch_decode_url_part('\2');#g"
*/
projects / nominatim.git / commitdiff