$oDB =& DB::connect(CONST_Database_DSN.($bNew?'?new_link=true':''), false);
if (PEAR::IsError($oDB))
{
- fail($oDB->getMessage(), 'Unable to connect to the database');
+ var_dump(CONST_Database_DSN);
+ var_Dump($oDB);
+ fail($oDB->getMessage());
}
$oDB->setFetchMode(DB_FETCHMODE_ASSOC);
$oDB->query("SET DateStyle TO 'sql,european'");
$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)";
-// and isaddress";
- if ($sCountryCode)
- {
- $sSQL .= " and (placex.country_code IS NULL OR placex.country_code = '".$sCountryCode."' OR rank_address < 4)";
- }
- $sSQL .= " order by cached_rank_address desc,fromarea desc,distance asc,rank_search desc,namelength desc";
+ $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))
float: right;
}
</style>
- <script src="OpenLayers.js"></script>
- <script src="http://www.openstreetmap.org/openlayers/OpenStreetMap.js"></script>
+ <script src="js/OpenLayers.js"></script>
+ <script src="js/OpenStreetMap.js"></script>
<script src="prototype-1.6.0.3.js"></script>
<script type="text/javascript">
echo ' <div>Coverage: <span class="area">'.($aPointDetails['isarea']=='t'?'Polygon':'Point').'</span></div>';
$sOSMType = ($aPointDetails['osm_type'] == 'N'?'node':($aPointDetails['osm_type'] == 'W'?'way':($aPointDetails['osm_type'] == 'R'?'relation':'')));
if ($sOSMType) echo ' <div>OSM: <span class="osm"><span class="label"></span>'.$sOSMType.' <a href="http://www.openstreetmap.org/browse/'.$sOSMType.'/'.$aPointDetails['osm_id'].'">'.$aPointDetails['osm_id'].'</a></span></div>';
+ echo ' <div>Extra Tags: ';
+ foreach($aPointDetails['aExtraTags'] as $sKey => $sValue)
+ {
+ echo ' <div class="line"><span class="name">'.$sValue.'</span> ('.$sKey.')</div>';
+ }
+ echo ' </div>';
echo '</div>';
echo '<h2>Address</h2>';
pg_prepare_params[0] = PG_OID_INT4;
res = PQprepare(conn, "index_sectors",
- "select geometry_sector,count(*) from placex where rank_search = $1 and indexed = true group by geometry_sector order by geometry_sector",
+ "select geometry_sector,count(*) from placex where rank_search = $1 and indexed_status = 0 group by geometry_sector order by geometry_sector",
1, pg_prepare_params);
if (PQresultStatus(res) != PGRES_COMMAND_OK) exit(EXIT_FAILURE);
PQclear(res);
PQclear(resPlaces);
exit(EXIT_FAILURE);
}
- if (PQftype(resPlaces, 0) != PG_OID_INT8)
+ if (PQftype(resPlaces, 0) != PG_OID_INT4)
{
fprintf(stderr, "Place_id value has unexpected type\n");
PQclear(resPlaces);
tuples = PQntuples(resPlaces);
for(i = 0; i < tuples; i++)
{
- nominatim_exportPlace(PGint64(*((uint64_t *)PQgetvalue(resPlaces, i, 0))), conn, writer, NULL);
+ nominatim_exportPlace(PGint32(*((uint32_t *)PQgetvalue(resPlaces, i, 0))), conn, writer, NULL);
rankTotalDone++;
if (rankTotalDone%1000 == 0) printf("Done %i (k)\n", rankTotalDone/1000);
}
pg_prepare_params[0] = PG_OID_INT8;
res = PQprepare(conn, "placex_address",
- "select osm_type,osm_id,class,type,distance,cached_rank_address from place_addressline join placex on (address_place_id = placex.place_id) where isaddress and place_addressline.place_id = $1 and address_place_id != place_addressline.place_id order by cached_rank_address asc",
+ "select osm_type,osm_id,class,type,distance,cached_rank_address,isaddress from place_addressline join placex on (address_place_id = placex.place_id) where place_addressline.place_id = $1 and address_place_id != place_addressline.place_id order by cached_rank_address asc",
1, pg_prepare_params);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
pg_prepare_params[0] = PG_OID_INT8;
res = PQprepare(conn, "placex_names",
- "select (each(name)).key,(each(name)).value from (select name as name from placex where place_id = $1) as x",
+ "select (each(name)).key,(each(name)).value from (select name from placex where place_id = $1) as x",
1, pg_prepare_params);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
exit(EXIT_FAILURE);
}
PQclear(res);
+
+ pg_prepare_params[0] = PG_OID_INT8;
+ res = PQprepare(conn, "placex_extratags",
+ "select (each(extratags)).key,(each(extratags)).value from (select extratags from placex where place_id = $1) as x",
+ 1, pg_prepare_params);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Error preparing placex_extratags: %s", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
+ PQclear(res);
}
xmlTextWriterPtr nominatim_exportXMLStart(const char *structuredoutputfile)
PGresult * res;
PGresult * resNames;
PGresult * resAddress;
+ PGresult * resExtraTags;
int i;
exit(EXIT_FAILURE);
}
+ resExtraTags = PQexecPrepared(conn, "placex_extratags", 1, paramValues, paramLengths, paramFormats, 0);
+ if (PQresultStatus(resExtraTags) != PGRES_TUPLES_OK)
+ {
+ fprintf(stderr, "placex_extratags: SELECT failed: %s", PQerrorMessage(conn));
+ PQclear(resExtraTags);
+ exit(EXIT_FAILURE);
+ }
+
if (writer_mutex) pthread_mutex_lock( writer_mutex );
xmlTextWriterStartElement(writer, BAD_CAST "feature");
xmlTextWriterWriteAttribute(writer, BAD_CAST "rank", BAD_CAST PQgetvalue(res, 0, 9));
xmlTextWriterWriteAttribute(writer, BAD_CAST "importance", BAD_CAST PQgetvalue(res, 0, 10));
- if (PQgetvalue(res, 0, 4) && strlen(PQgetvalue(res, 0, 4)))
+ if (PQntuples(resNames))
{
xmlTextWriterStartElement(writer, BAD_CAST "names");
xmlTextWriterWriteAttribute(writer, BAD_CAST "key", BAD_CAST PQgetvalue(resAddress, i, 2));
xmlTextWriterWriteAttribute(writer, BAD_CAST "value", BAD_CAST PQgetvalue(resAddress, i, 3));
xmlTextWriterWriteAttribute(writer, BAD_CAST "distance", BAD_CAST PQgetvalue(resAddress, i, 4));
+ xmlTextWriterWriteAttribute(writer, BAD_CAST "isaddress", BAD_CAST PQgetvalue(resAddress, i, 6));
xmlTextWriterEndElement(writer);
}
xmlTextWriterEndElement(writer);
}
+ if (PQntuples(resExtraTags))
+ {
+ xmlTextWriterStartElement(writer, BAD_CAST "tags");
+
+ for(i = 0; i < PQntuples(resExtraTags); i++)
+ {
+ xmlTextWriterStartElement(writer, BAD_CAST "tag");
+ xmlTextWriterWriteAttribute(writer, BAD_CAST "type", BAD_CAST PQgetvalue(resExtraTags, i, 0));
+ xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(resExtraTags, i, 1));
+ xmlTextWriterEndElement(writer);
+ }
+
+ xmlTextWriterEndElement(writer);
+ }
+
+
xmlTextWriterStartElement(writer, BAD_CAST "osmGeometry");
xmlTextWriterWriteString(writer, BAD_CAST PQgetvalue(res, 0, 7));
xmlTextWriterEndElement(writer);
#define MAX_FEATUREADDRESS 500
#define MAX_FEATURENAMES 1000
+#define MAX_FEATUREEXTRATAGS 100
+#define MAX_FEATURENAMESTRING 100000
+#define MAX_FEATUREEXTRATAGSTRING 50000
struct feature_address {
int place_id;
int rankAddress;
+ char isAddress[2];
xmlChar * type;
xmlChar * id;
xmlChar * key;
xmlChar * distance;
};
-struct feature_name {
+struct feature_tag {
xmlChar * type;
xmlChar * value;
};
int fileMode = FILEMODE_ADD;
PGconn * conn;
struct feature_address featureAddress[MAX_FEATUREADDRESS];
-struct feature_name featureName[MAX_FEATURENAMES];
+struct feature_tag featureName[MAX_FEATURENAMES];
+struct feature_tag featureExtraTag[MAX_FEATUREEXTRATAGS];
struct feature feature;
int featureAddressLines = 0;
int featureNameLines = 0;
+int featureExtraTagLines = 0;
int featureCount = 0;
xmlHashTablePtr partionTableTagsHash;
-
-
+char featureNameString[MAX_FEATURENAMESTRING];
+char featureExtraTagString[MAX_FEATUREEXTRATAGSTRING];
void StartElement(xmlTextReaderPtr reader, const xmlChar *name)
{
}
return;
}
+ if (xmlStrEqual(name, BAD_CAST "tags")) return;
+ if (xmlStrEqual(name, BAD_CAST "tag"))
+ {
+ featureExtraTag[featureExtraTagLines].type = xmlTextReaderGetAttribute(reader, BAD_CAST "type");
+ featureExtraTag[featureExtraTagLines].value = xmlTextReaderReadString(reader);
+ featureExtraTagLines++;
+ if (featureExtraTagLines >= MAX_FEATUREEXTRATAGS)
+ {
+ fprintf( stderr, "Too many extra tag elements\n");
+ exit_nicely();
+ }
+ return;
+ }
if (xmlStrEqual(name, BAD_CAST "osmGeometry"))
{
feature.geometry = xmlTextReaderReadString(reader);
featureAddress[featureAddressLines].rankAddress = atoi(value);
xmlFree(value);
+ value = (char*)xmlTextReaderGetAttribute(reader, BAD_CAST "isaddress");
+ if (!value)
+ {
+ fprintf( stderr, "Address element missing rank\n");
+ exit_nicely();
+ }
+ if (*value == 't') strcpy(featureAddress[featureAddressLines].isAddress, "t");
+ else strcpy(featureAddress[featureAddressLines].isAddress, "f");
+ xmlFree(value);
+
featureAddress[featureAddressLines].type = xmlTextReaderGetAttribute(reader, BAD_CAST "type");
featureAddress[featureAddressLines].id = xmlTextReaderGetAttribute(reader, BAD_CAST "id");
featureAddress[featureAddressLines].key = xmlTextReaderGetAttribute(reader, BAD_CAST "key");
const char * paramValues[11];
char * place_id;
char * partionQueryName;
- int i;
+ int i, namePos, lineTypeLen, lineValueLen;
if (xmlStrEqual(name, BAD_CAST "feature"))
{
paramValues[2] = (const char *)feature.id;
paramValues[3] = (const char *)feature.key;
paramValues[4] = (const char *)feature.value;
-// paramValues[5] = (const char *)feature.name;
- paramValues[6] = (const char *)feature.adminLevel;
- paramValues[7] = (const char *)feature.houseNumber;
- paramValues[8] = (const char *)feature.rankAddress;
- paramValues[9] = (const char *)feature.rankSearch;
- paramValues[10] = (const char *)feature.geometry;
- res = PQexecPrepared(conn, "placex_insert", 11, paramValues, NULL, NULL, 0);
+
+ featureNameString[0] = 0;
+ if (featureNameLines)
+ {
+ namePos = 0;
+ lineTypeLen = 0;
+ lineValueLen = 0;
+ for(i = 0; i < featureNameLines; i++)
+ {
+ lineTypeLen = strlen(BAD_CAST featureName[i].type);
+ lineValueLen = strlen(BAD_CAST featureName[i].value);
+ if (namePos+lineTypeLen+lineValueLen+7 > MAX_FEATURENAMESTRING)
+ {
+ fprintf(stderr, "feature name too long: %s", (const char *)featureName[i].value);
+ break;
+ }
+ if (namePos) strcpy(featureNameString+(namePos++), ",");
+ strcpy(featureNameString+(namePos++), "\"");
+ strcpy(featureNameString+namePos, BAD_CAST featureName[i].type);
+ namePos += lineTypeLen;
+ strcpy(featureNameString+namePos, "\"=>\"");
+ namePos += 4;
+ strcpy(featureNameString+namePos, BAD_CAST featureName[i].value);
+ namePos += lineValueLen;
+ strcpy(featureNameString+(namePos++), "\"");
+ }
+ }
+ paramValues[5] = (const char *)featureNameString;
+
+ featureExtraTagString[0] = 0;
+ if (featureExtraTagLines)
+ {
+ namePos = 0;
+ lineTypeLen = 0;
+ lineValueLen = 0;
+ for(i = 0; i < featureExtraTagLines; i++)
+ {
+ lineTypeLen = strlen(BAD_CAST featureExtraTag[i].type);
+ lineValueLen = strlen(BAD_CAST featureExtraTag[i].value);
+ if (namePos+lineTypeLen+lineValueLen+7 > MAX_FEATUREEXTRATAGSTRING)
+ {
+ fprintf(stderr, "feature extra tag too long: %s", (const char *)featureExtraTag[i].value);
+ break;
+ }
+ if (namePos) strcpy(featureExtraTagString+(namePos++),",");
+ strcpy(featureExtraTagString+(namePos++), "\"");
+ strcpy(featureExtraTagString+namePos, BAD_CAST featureExtraTag[i].type);
+ namePos += lineTypeLen;
+ strcpy(featureExtraTagString+namePos, "\"=>\"");
+ namePos += 4;
+ strcpy(featureExtraTagString+namePos, BAD_CAST featureExtraTag[i].value);
+ namePos += lineValueLen;
+ strcpy(featureExtraTagString+(namePos++), "\"");
+ }
+ }
+ paramValues[6] = (const char *)featureExtraTagString;
+
+ paramValues[7] = (const char *)feature.adminLevel;
+ paramValues[8] = (const char *)feature.houseNumber;
+ paramValues[9] = (const char *)feature.rankAddress;
+ paramValues[10] = (const char *)feature.rankSearch;
+ paramValues[11] = (const char *)feature.geometry;
+ res = PQexecPrepared(conn, "placex_insert", 12, paramValues, NULL, NULL, 0);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "index_placex: INSERT failed: %s", PQerrorMessage(conn));
paramValues[3] = (const char *)featureAddress[i].id;
paramValues[4] = (const char *)featureAddress[i].key;
paramValues[5] = (const char *)featureAddress[i].value;
- res = PQexecPrepared(conn, "place_addressline_insert", 6, paramValues, NULL, NULL, 0);
+ paramValues[6] = (const char *)featureAddress[i].isAddress;
+ res = PQexecPrepared(conn, "place_addressline_insert", 7, paramValues, NULL, NULL, 0);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "place_addressline_insert: INSERT failed: %s", PQerrorMessage(conn));
}
res = PQprepare(conn, "placex_insert",
- "insert into placex (place_id,osm_type,osm_id,class,type,name,admin_level,housenumber,rank_address,rank_search,geometry) "
- "values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, st_setsrid($11, 4326))",
- 11, NULL);
+ "insert into placex (place_id,osm_type,osm_id,class,type,name,extratags,admin_level,housenumber,rank_address,rank_search,geometry) "
+ "values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, st_setsrid($12, 4326))",
+ 12, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to prepare placex_insert: %s\n", PQerrorMessage(conn));
1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
- fprintf(stderr, "Failed to prepare placex_insert: %s\n", PQerrorMessage(conn));
+ fprintf(stderr, "Failed to prepare search_name_insert: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
res = PQprepare(conn, "place_addressline_insert",
"insert into place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) "
- "select $1, place_id, false, true, $2, rank_address from placex where osm_type = $3 and osm_id = $4 and class = $5 and type = $6",
- 6, NULL);
+ "select $1, place_id, false, $7, $2, rank_address from placex where osm_type = $3 and osm_id = $4 and class = $5 and type = $6",
+ 7, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Failed to prepare place_addressline_insert: %s\n", PQerrorMessage(conn));
fprintf(stderr, "Failed preparing index_placex: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
+ PQclear(res);
+
+ res = PQexec(thread_data[i].conn, "set enable_seqscan = false");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ fprintf(stderr, "Failed disabling sequential scan: %s\n", PQerrorMessage(conn));
+ exit(EXIT_FAILURE);
+ }
PQclear(res);
nominatim_exportCreatePreparedQueries(thread_data[i].conn);
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;
+
x := deleteLocationArea(partition, place_id);
isarea := false;
ELSEIF rank_search < 26 THEN
diameter := 0.02;
- IF rank_search = 14 THEN
+ IF rank_address = 0 THEN
+ diameter := 0.02;
+ ELSEIF rank_search <= 14 THEN
+ diameter := 1.2;
+ ELSEIF rank_search <= 15 THEN
diameter := 1;
- ELSEIF rank_search = 15 THEN
+ ELSEIF rank_search <= 16 THEN
diameter := 0.5;
- ELSEIF rank_search = 16 THEN
- diameter := 0.15;
- ELSEIF rank_search = 17 THEN
+ ELSEIF rank_search <= 17 THEN
+ diameter := 0.2;
+ ELSEIF rank_search <= 21 THEN
diameter := 0.05;
- ELSEIF rank_search = 21 THEN
- diameter := 0.01;
ELSEIF rank_search = 25 THEN
diameter := 0.005;
END IF;
+ 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);
housenum INTEGER;
linegeo GEOMETRY;
search_place_id INTEGER;
+ defpostalcode TEXT;
havefirstpoint BOOLEAN;
linestr TEXT;
newpoints := 0;
IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN
+ select postcode from placex where osm_type = 'W' and osm_id = wayid INTO defpostalcode;
select nodes from planet_osm_ways where id = wayid INTO waynodes;
--RAISE WARNING 'interpolation % % %',wayid,interpolationtype,waynodes;
IF array_upper(waynodes, 1) IS NOT NULL THEN
FOR housenum IN startnumber..endnumber BY stepsize LOOP
-- this should really copy postcodes but it puts a huge burdon on the system for no big benefit
-- ideally postcodes should move up to the way
- insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin,
+ insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode,
country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
- values ('N',prevnode.osm_id, prevnode.class, prevnode.type, prevnode.admin_level, housenum, prevnode.street, prevnode.isin,
+ values ('N',prevnode.osm_id, prevnode.class, prevnode.type, prevnode.admin_level, housenum, prevnode.street, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),
prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
newpoints := newpoints + 1;
--RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
- NEW.country_code := get_country_code(NEW.geometry, NEW.country_code);
+ NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
NEW.partition := get_partition(NEW.geometry, NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- copy 'name' to or from the default language (if there is a default language)
- IF NEW.name is not null THEN
+ IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.rank_search := 17;
NEW.rank_address := 0;
ELSEIF NEW.type in ('town') THEN
- NEW.rank_search := 17;
- NEW.rank_address := NEW.rank_search;
- ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
NEW.rank_search := 18;
- NEW.rank_address := 17;
+ NEW.rank_address := 16;
+ ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
+ NEW.rank_search := 19;
+ NEW.rank_address := 16;
ELSEIF NEW.type in ('airport') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
NEW.rank_search := 18;
NEW.rank_address := 17;
END IF;
ELSEIF NEW.class = 'boundary' THEN
+ IF ST_GeometryType(NEW.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') THEN
+ return NULL;
+ END IF;
NEW.rank_search := NEW.admin_level * 2;
NEW.rank_address := NEW.rank_search;
ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
NEW.rank_address := 30;
END IF;
+ IF (NEW.extratags -> 'capital') = 'yes' THEN
+ NEW.rank_search := NEW.rank_search -1;
+ END IF;
+
-- Block import below rank 22
-- IF NEW.rank_search > 22 THEN
-- RETURN NULL;
search_maxrank INTEGER;
address_maxrank INTEGER;
address_street_word_id INTEGER;
- parent_place_id_count INTEGER;
+ parent_place_id_rank INTEGER;
+
isin TEXT[];
isin_tokens INT[];
--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])
+ and osm_id in (select id from planet_osm_ways where nodes && ARRAY[NEW.osm_id::integer] limit 10)
LOOP
--RAISE WARNING '%', location;
-- Way IS a road then we are on it - that must be our road
search_diameter := search_diameter * 2;
END LOOP;
+--return NEW;
--RAISE WARNING 'x6 %',NEW.parent_place_id;
-- If we didn't find any road fallback to standard method
-- Merge address from parent
nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector);
-
+--return NEW;
-- Performance, it would be more acurate to do all the rest of the import process but it takes too long
-- Just be happy with inheriting from parent road only
+
+ IF NEW.rank_search <= 25 THEN
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, 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_address, 0, NEW.country_code,
--- name_vector, nameaddress_vector, place_centroid);
return NEW;
END IF;
END IF;
---RAISE WARNING ' INDEXING: %',NEW;
+RAISE WARNING ' INDEXING: %',NEW;
+
+ NEW.parent_place_id = 0;
+ parent_place_id_rank = 0;
-- convert isin to array of tokenids
isin_tokens := '{}'::int[];
END IF;
isin_tokens := uniq(sort(isin_tokens));
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 THEN
+ isin_tokens := isin_tokens + address_street_word_id;
+ END IF;
+ END LOOP;
+ 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;
-- Process area matches
location_rank_search := 100;
location_distance := 0;
---RAISE WARNING '%', NEW.partition;
FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
--RAISE WARNING ' AREA: %',location;
INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, 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 IF;
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
+ -- for long ways we should add search terms for the entire length
+ IF st_length(NEW.geometry) > 0.05 THEN
- FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+ location_rank_search := 100;
+ location_distance := 0;
+
+ FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP
+
+ IF location.rank_search < location_rank_search THEN
+ location_rank_search := location.rank_search;
+ location_distance := location.distance * 1.5;
+ END IF;
+
+ IF location.distance < location_distance THEN
+
+ -- Add it to the list of search terms
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;
- END LOOP;
+ INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address);
+
+ END IF;
END LOOP;
+
END IF;
-- if we have a name add this to the name search table
searchcountrycode varchar(2);
searchhousenumber TEXT;
searchrankaddress INTEGER;
+ searchpostcode TEXT;
BEGIN
found := 1000;
search := languagepref;
result := '{}';
- select country_code,housenumber,rank_address from placex where place_id = for_place_id into searchcountrycode,searchhousenumber,searchrankaddress;
+ 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,
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 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;
isin = place.isin,
postcode = place.postcode,
country_code = place.country_code,
- parent_place_id = null,
- indexed_status = 2
+ parent_place_id = null
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 = 2 where place_id = search_place_id;
update placex set indexed_status = 0 where place_id = search_place_id;
return true;
END;
newpoints INTEGER;
numberrange INTEGER;
rangestartnumber INTEGER;
+ place_centroid GEOMETRY;
+ partition INTEGER;
+ parent_place_id INTEGER;
+ location RECORD;
+ address_street_word_id INTEGER;
BEGIN
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
+ parent_place_id := location.place_id;
+ END LOOP;
+ END IF;
+
newpoints := 0;
FOR housenum IN startnumber..endnumber BY stepsize LOOP
- insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, isin, postcode,
- country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
- values ('T', nextval('seq_tigger_house'), 'place', 'house', null, housenum, in_street, in_isin, in_postcode,
- 'us', null, 30, 30, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
+ 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,
+ ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
newpoints := newpoints + 1;
END LOOP;
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 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-_parent_place_id ON location_property_-partition- USING BTREE (parent_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);
+
-- end
create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$
UNION ALL
SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank
) as location_area
- ORDER BY rank_search desc, isin_tokens && keywords desc, isguess asc, rank_address asc, ST_Distance(point, centroid) ASC
+ ORDER BY rank_address desc, isin_tokens && keywords desc, isguess asc, ST_Distance(point, centroid) * CASE WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 ELSE 1 END ASC
LOOP
RETURN NEXT r;
END LOOP;
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;
DECLARE
BEGIN
+ DELETE FROM search_name WHERE place_id = in_place_id;
INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
in_name_vector, in_nameaddress_vector, in_centroid);
IF in_rank_search <= 4 THEN
+ DELETE FROM search_name_country WHERE place_id = in_place_id;
INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
in_name_vector, in_nameaddress_vector, in_centroid);
RETURN TRUE;
-- start
IF in_partition = -partition- THEN
+ DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
INSERT INTO search_name_-partition- values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code,
in_name_vector, in_nameaddress_vector, in_centroid);
RETURN TRUE;
CREATE TABLE location_area_roadnear () INHERITS (location_area);
CREATE TABLE location_area_roadfar () INHERITS (location_area);
+drop table IF EXISTS location_property CASCADE;
+CREATE TABLE location_property (
+ place_id INTEGER,
+ partition integer,
+ parent_place_id INTEGER,
+ housenumber TEXT,
+ postcode TEXT
+ );
+SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2);
+CREATE TABLE location_property_tiger () INHERITS (location_property);
+CREATE INDEX idx_location_property_tiger_place_id ON location_property_tiger USING BTREE (place_id);
+CREATE INDEX idx_location_property_tiger_parent_place_id ON location_property_tiger USING BTREE (parent_place_id);
+CREATE INDEX idx_location_property_tiger_housenumber_parent_place_id ON location_property_tiger USING BTREE (parent_place_id, housenumber);
+
drop table IF EXISTS search_name_blank CASCADE;
CREATE TABLE search_name_blank (
place_id INTEGER,
where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed = false and class='place' and type='houses';
CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
+
+DROP SEQUENCE seq_postcodes;
+CREATE SEQUENCE seq_postcodes start 1;
array('create-tables', '', 0, 1, 0, 0, 'bool', 'Create main tables'),
array('create-partitions', '', 0, 1, 0, 0, 'bool', 'Create required partition tables and triggers'),
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'),
+ 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('index', '', 0, 1, 0, 0, 'bool', 'Index the data'),
);
getCmdOpt($_SERVER['argv'], $aCMDOptions, $aCMDResult, true, true);
pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/country_name.sql');
- pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturaleathdata.sql');
+ pgsqlRunScriptFile(CONST_BasePath.'/data/country_naturalearthdata.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/country_osm_grid.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/gb_postcode.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/us_statecounty.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/us_state.sql');
+ pgsqlRunScriptFile(CONST_BasePath.'/data/us_postcode.sql');
pgsqlRunScriptFile(CONST_BasePath.'/data/worldboundaries.sql');
}
{
$bDidSomething = true;
passthru(CONST_BasePath.'/osm2pgsql/osm2pgsql -lsc -O gazetteer -C 10000 --hstore -d nominatim '.$aCMDResult['osm-file']);
+
+ $oDB =& getDB();
+ $x = $oDB->getRow('select * from place limit 1');
+ if (!$x || PEAR::isError($x)) fail('No Data');
}
if ($aCMDResult['create-functions'] || $aCMDResult['all'])
}
$sTemplate = str_replace($aMatch[0], $sResult, $sTemplate);
}
+
pgsqlRunScript($sTemplate);
}
echo "\n";
}
- if ($aCMDResult['import-tiger-data'] || $aCMDResult['all'])
+ 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;
}
}
+ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all'])
+ {
+ $oDB =& getDB();
+ if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
+ $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
+ $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";
+ 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) ";
+ $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
+ $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
+ if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
+ }
+
+ if ($aCMDResult['index'] || $aCMDResult['all'])
+ {
+ $bDidSomething = true;
+ passthru(CONST_BasePath.'/nominatim/nominatim -i -d nominatim -t '.$iInstances);
+ }
+
if (!$bDidSomething)
{
showUsage($aCMDOptions, true);
require_once(dirname(dirname(__FILE__)).'/lib/init-website.php');
require_once(CONST_BasePath.'/lib/log.php');
+ $sOutputFormat = 'html';
+/*
$fLoadAvg = getLoadAverage();
if ($fLoadAvg > 3)
{
echo "Page temporarily blocked due to high server load\n";
exit;
}
-
+*/
ini_set('memory_limit', '200M');
$oDB =& getDB();
$aPointDetails['icon'] = $aClassType[$aPointDetails['class'].':'.$aPointDetails['type']]['icon'];
// Get all alternative names (languages, etc)
- $aPointDetails['aNames'] = array();
-/*
- for($i = 1; $i <= $aPointDetails['numnames']; $i++)
- {
- $sSQL = "select name[$i].key, name[$i].value from placex where place_id = $iPlaceID limit 1";
- $aNameItem = $oDB->getRow($sSQL);
- if (substr($aNameItem['key'],0,5) == 'name:') $aNameItem['key'] = substr($aNameItem['key'],5);
- $aPointDetails['aNames'][$aNameItem['key']] = $aNameItem['value'];
- }
-*/
+ $sSQL = "select (each(name)).key,(each(name)).value from placex where place_id = $iPlaceID order by (each(name)).key";
+ $aPointDetails['aNames'] = $oDB->getAssoc($sSQL);
+
+ // Extra tags
+ $sSQL = "select (each(extratags)).key,(each(extratags)).value from placex where place_id = $iPlaceID order by (each(extratags)).key";
+ $aPointDetails['aExtraTags'] = $oDB->getAssoc($sSQL);
+
// Get the bounding box and outline polygon
$sSQL = "select *,ST_AsText(outline) as outlinestring from get_place_boundingbox($iPlaceID)";
$aPointPolygon = $oDB->getRow($sSQL);
$aPointPolygon['maxlon'] = $aPointPolygon['maxlon'] + $fRadius;
}
- // If it is a road then force all nearby buildings to be indexed (so we can show then in the list)
-/*
- if ($aPointDetails['rank_address'] == 26)
- {
- $sSQL = "UPDATE placex set indexed = true from placex as srcplace where placex.indexed = false";
- $sSQL .= " and ST_DWithin(placex.geometry, srcplace.geometry, 0.0005) and srcplace.place_id = $iPlaceID";
- $oDB->query($sSQL);
- }
-*/
// Address
$aAddressLines = getAddressDetails($oDB, $sLanguagePrefArraySQL, $iPlaceID, $aPointDetails['country_code'], true);
-/*
- $sSQL = "select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, rank_address, rank_search, ";
- $sSQL .= "get_searchrank_label(rank_search) as rank_search_label, fromarea, distance, ";
- $sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, 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 AND rank_address < ".$aPointDetails['rank_address'].") OR address_place_id = $iPlaceID) and placex.place_id != $iPlaceID";
- if ($aPointDetails['country_code'])
- {
- $sSQL .= " and (placex.country_code IS NULL OR placex.country_code = '".$aPointDetails['country_code']."' OR rank_address < 4)";
- }
- $sSQL .= " order by cached_rank_address desc,rank_search desc,fromarea desc,distance asc,namelength desc";
- $aAddressLines = $oDB->getAll($sSQL);
- IF (PEAR::IsError($aAddressLines))
- {
- var_dump($aAddressLines);
- exit;
- }
-*/
- // All places this is a parent of
- $iMaxRankAddress = $aPointDetails['rank_address']+13;
- $sSQL = "select placex.place_id, osm_type, osm_id, class, type, housenumber, admin_level, cached_rank_address, ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea, distance, ";
+
+ // All places this is an imediate parent of
+ $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_distance(geometry, placegeometry) as distance, ";
$sSQL .= " get_name_by_language(name,$sLanguagePrefArraySQL) as localname, length(name::text) as namelength ";
- $sSQL .= " from (select * from place_addressline where address_place_id = $iPlaceID and cached_rank_address < $iMaxRankAddress) as place_addressline join placex on (place_addressline.place_id = placex.place_id)";
- $sSQL .= " where place_addressline.address_place_id = $iPlaceID and placex.rank_address < $iMaxRankAddress and cached_rank_address > 0 and placex.place_id != $iPlaceID";
- $sSQL .= " and type != 'postcode'";
- $sSQL .= " order by cached_rank_address asc,rank_search asc,get_name_by_language(name,$sLanguagePrefArraySQL),housenumber limit 1000";
+ $sSQL .= " from placex, (select geometry as placegeometry from placex where place_id = $iPlaceID) as x";
+ $sSQL .= " where parent_place_id = $iPlaceID";
+// $sSQL .= " and type != 'postcode'";
+ $sSQL .= " order by rank_address asc,rank_search asc,get_name_by_language(name,$sLanguagePrefArraySQL),housenumber";
$aParentOfLines = $oDB->getAll($sSQL);
logEnd($oDB, $hLog, 1);
- include('.htlib/output/details-html.php');
+ include(CONST_BasePath.'/lib/template/details-'.$sOutputFormat.'.php');
projects / nominatim.git / commitdiff