more partitioning work, os open data postcodes, country list fixes

diff --git a/data/country_name.sql b/data/country_name.sql
index ab244b489abf090b12c5e06cc9c498055c28d1fc..a1748d4c770631d56829f01728c9f543008f1ab9 100644 (file)
--- a/data/country_name.sql
+++ b/data/country_name.sql
@@ -249,4 +249,37 @@ pf "name"=>"Polynésie française", "name:af"=>"Franse Polynesië", "name:an"=>"
 
 CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code);
 
 
 CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code);
 

+insert into country_name values('aq','name'=>'Antarctica',null);
+insert into country_name values('as','name'=>'American Samoa',null);
+insert into country_name values('aw','name'=>'Aruba',null);
+insert into country_name values('ax','name'=>'Aland Islands',null);
+insert into country_name values('bl','name'=>'Saint Barthélemy',null);
+insert into country_name values('bv','name'=>'Bouvet Island',null);
+insert into country_name values('eh','name'=>'Western Sahara',null);
+insert into country_name values('gh','name'=>'Ghana',null);
+insert into country_name values('gu','name'=>'Guam',null);
+insert into country_name values('hk','name'=>'Hong Kong',null);
+insert into country_name values('hm','name'=>'Heard Island and MaxDonald Islands',null);

 insert into country_name values('im','name'=>'Isle of Man','en');
 insert into country_name values('im','name'=>'Isle of Man','en');

+insert into country_name values('mf','name'=>'Saint Martin',null);
+insert into country_name values('mo','name'=>'Macao',null);
+insert into country_name values('mp','name'=>'Northern Mariana Islands',null);
+insert into country_name values('pr','name'=>'Puerto Rico');
+insert into country_name values('ps','name'=>'Palestinian Territory',null);
+insert into country_name values('pw','name'=>'Palau',null);
+insert into country_name values('sb','name'=>'Solomon Islands',null);
+insert into country_name values('sh','name'=>'Saint Helena',null);
+insert into country_name values('sj','name'=>'Svalbard and Jan Mayen',null);
+insert into country_name values('um','name'=>'United States Minor Outlying Islands',null);
+insert into country_name values('vi','name'=>'United States Virgin Islands',null);
+insert into country_name values('yt','name'=>'Mayotte',null);
+
+
+
+
+
+
+
+
+
+

diff --git a/lib/db.php b/lib/db.php
index a2bba81f587be2d40260efa45d8c2c154d04d23f..c50b3c404deb4223728b87aae90d6f926e071954 100644 (file)
--- a/lib/db.php
+++ b/lib/db.php
@@ -1,10 +1,10 @@
 <?php
        require_once('DB.php');
 
 <?php
        require_once('DB.php');
 

-       function &getDB()
+       function &getDB($bNew = false)

        {
                // Get the database object
        {
                // Get the database object

-               $oDB =& DB::connect(CONST_Database_DSN, false);
+               $oDB =& DB::connect(CONST_Database_DSN.($bNew?'?new_link=true':''), false);

                if (PEAR::IsError($oDB))
                {
                        fail($oDB->getMessage(), 'Unable to connect to the database');
                if (PEAR::IsError($oDB))
                {
                        fail($oDB->getMessage(), 'Unable to connect to the database');

diff --git a/lib/lib.php b/lib/lib.php
index 8a1fb902cbbdba574010f5f6a6d8bb069ec4ca2d..1d2320627b69188d9e471114b18b4186c0706527 100644 (file)
--- a/lib/lib.php
+++ b/lib/lib.php
@@ -152,25 +152,28 @@
        function gbPostcodeCalculate($sPostcode, $sPostcodeSector, $sPostcodeEnd, &$oDB)
        {
                // Try an exact match on the gb_postcode table
        function gbPostcodeCalculate($sPostcode, $sPostcodeSector, $sPostcodeEnd, &$oDB)
        {
                // Try an exact match on the gb_postcode table

-               $sSQL = 'select \'AA\', ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where upper(postcode) = \''.$sPostcode.'\'';
+               $sSQL = 'select \'AA\', ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where postcode = \''.$sPostcode.'\'';

                $aNearPostcodes = $oDB->getAll($sSQL);
                if (PEAR::IsError($aNearPostcodes))
                {
                        var_dump($sSQL, $aNearPostcodes);
                        exit;
                }
                $aNearPostcodes = $oDB->getAll($sSQL);
                if (PEAR::IsError($aNearPostcodes))
                {
                        var_dump($sSQL, $aNearPostcodes);
                        exit;
                }

+               

 
 

-               if (!sizeof($aNearPostcodes))
+               if (sizeof($aNearPostcodes))

                {
                {

-                       $sSQL = 'select substring(upper(postcode) from \'^[A-Z][A-Z]?[0-9][0-9A-Z]? [0-9]([A-Z][A-Z])$\'),ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from placex where country_code::text = \'gb\'::text AND substring(upper(postcode) from \'^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])[A-Z][A-Z]$\') = \''.$sPostcodeSector.'\' and class=\'place\' and type=\'postcode\' ';
-                       $sSQL .= ' union ';
-                       $sSQL .= 'select substring(upper(postcode) from \'^[A-Z][A-Z]?[0-9][0-9A-Z]? [0-9]([A-Z][A-Z])$\'),ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where substring(upper(postcode) from \'^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])[A-Z][A-Z]$\') = \''.$sPostcodeSector.'\'';
-                       $aNearPostcodes = $oDB->getAll($sSQL);
-                       if (PEAR::IsError($aNearPostcodes))
-                       {
-                               var_dump($sSQL, $aNearPostcodes);
-                               exit;
-                       }
+                       return array(array('lat' => $aNearPostcodes[0]['lat'], 'lon' => $aNearPostcodes[0]['lon'], 'radius' => 0.005));
+               }
+
+               $sSQL = 'select substring(upper(postcode) from \'^[A-Z][A-Z]?[0-9][0-9A-Z]? [0-9]([A-Z][A-Z])$\'),ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from placex where country_code::text = \'gb\'::text AND substring(postcode from \'^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])[A-Z][A-Z]$\') = \''.$sPostcodeSector.'\' and class=\'place\' and type=\'postcode\' ';
+               $sSQL .= ' union ';
+               $sSQL .= 'select substring(upper(postcode) from \'^[A-Z][A-Z]?[0-9][0-9A-Z]? [0-9]([A-Z][A-Z])$\'),ST_X(ST_Centroid(geometry)) as lon,ST_Y(ST_Centroid(geometry)) as lat from gb_postcode where substring(postcode from \'^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])[A-Z][A-Z]$\') = \''.$sPostcodeSector.'\'';
+               $aNearPostcodes = $oDB->getAll($sSQL);
+               if (PEAR::IsError($aNearPostcodes))
+               {
+                       var_dump($sSQL, $aNearPostcodes);
+                       exit;

                }
 
                if (!sizeof($aNearPostcodes))
                }
 
                if (!sizeof($aNearPostcodes))


@@ -649,11 +652,8 @@
                                $sSep = '';
                                foreach($aRow['aAddress'] as $iWordID)
                                {
                                $sSep = '';
                                foreach($aRow['aAddress'] as $iWordID)
                                {

-//                                     if (!isset($aRow['aName'][$iWordID]))
-                                       {
-                                               echo $sSep.'#'.$aWordsIDs[$iWordID].'#';
-                                               $sSep = ', ';
-                                       }
+                                       echo $sSep.'#'.$aWordsIDs[$iWordID].'#';
+                                       $sSep = ', ';

                                }
                                echo "</td>";
 
                                }
                                echo "</td>";
 

diff --git a/sql/functions.sql b/sql/functions.sql
index f28e1d0ff9540ded4c92f4caf7efef1c4eb2f64b..c2de656c1950dd921705c86e4b61469c9d77c72c 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -522,6 +522,21 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

+CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS TEXT
+  AS $$
+DECLARE
+  place_centre GEOMETRY;
+  nearcountry RECORD;
+BEGIN
+  FOR nearcountry IN select country_code from country_name where country_code = in_country_code
+  LOOP
+    RETURN nearcountry.country_code;
+  END LOOP;
+  RETURN 'none';
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+

 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id INTEGER) RETURNS BOOLEAN
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION delete_location(OLD_place_id INTEGER) RETURNS BOOLEAN
   AS $$
 DECLARE


@@ -535,8 +550,9 @@ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION add_location(
     place_id INTEGER,
 
 CREATE OR REPLACE FUNCTION add_location(
     place_id INTEGER,

-    place_country_code varchar(2),
-    name hstore,
+    country_code varchar(2),
+    partition varchar(10),
+    keywords INTEGER[],

     rank_search INTEGER,
     rank_address INTEGER,
     geometry GEOMETRY
     rank_search INTEGER,
     rank_address INTEGER,
     geometry GEOMETRY


@@ -544,9 +560,6 @@ CREATE OR REPLACE FUNCTION add_location(
   RETURNS BOOLEAN
   AS $$
 DECLARE
   RETURNS BOOLEAN
   AS $$
 DECLARE

-  keywords INTEGER[];
-  country_code VARCHAR(2);
-  partition VARCHAR(10);

   locationid INTEGER;
   isarea BOOLEAN;
   xmin INTEGER;
   locationid INTEGER;
   isarea BOOLEAN;
   xmin INTEGER;


@@ -561,79 +574,67 @@ DECLARE
   x BOOLEAN;
 BEGIN
 
   x BOOLEAN;
 BEGIN
 

-  -- Allocate all tokens ids - prevents multi-processor race condition later on at cost of slowing down import
-  keywords := make_keywords(name);
-
-  -- 26 = street/highway
-  IF rank_search <= 26 THEN
-    IF place_country_code IS NULL THEN
-      country_code := get_country_code(geometry);
-    END IF;
-    country_code := lower(place_country_code);
-    partition := country_code;
-    IF partition is null THEN
-      partition := 'none';
-    END IF;
+  IF rank_search > 26 THEN
+    RAISE EXCEPTION 'Adding location with rank > 26 (% rank %)', place_id, rank_search;
+  END IF;

 
 

-    isarea := false;
-    IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN
-
-      isArea := true;
-      centroid := ST_Centroid(geometry);
-
-      xmin := floor(st_xmin(geometry));
-      xmax := ceil(st_xmax(geometry));
-      ymin := floor(st_ymin(geometry));
-      ymax := ceil(st_ymax(geometry));
-
-      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
-        FOR lon IN xmin..(xmax-1) LOOP
-          FOR lat IN ymin..(ymax-1) LOOP
-            secgeo := st_intersection(geometry, ST_SetSRID(ST_MakeBox2D(ST_Point(lon,lat),ST_Point(lon+1,lat+1)),4326));
-            IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
-              x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo);
-            END IF;
-          END LOOP;
-        END LOOP;
-      END IF;
+  x := deleteLocationArea(partition, place_id);

 
 

-    ELSEIF rank_search < 26 THEN
+  isarea := false;
+  IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN

 
 

-      diameter := 0.02;
-      IF rank_search = 14 THEN
-        diameter := 1;
-      ELSEIF rank_search = 15 THEN
-        diameter := 0.5;
-      ELSEIF rank_search = 16 THEN
-        diameter := 0.15;
-      ELSEIF rank_search = 17 THEN
-        diameter := 0.05;
-      ELSEIF rank_search = 25 THEN
-        diameter := 0.005;
-      END IF;
+    isArea := true;
+    centroid := ST_Centroid(geometry);

 
 

-      secgeo := ST_Buffer(geometry, diameter);
-      x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, ST_Centroid(geometry), secgeo);
+    xmin := floor(st_xmin(geometry));
+    xmax := ceil(st_xmax(geometry));
+    ymin := floor(st_ymin(geometry));
+    ymax := ceil(st_ymax(geometry));

 
 

+    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
     ELSE

+      FOR lon IN xmin..(xmax-1) LOOP
+        FOR lat IN ymin..(ymax-1) LOOP
+          secgeo := st_intersection(geometry, ST_SetSRID(ST_MakeBox2D(ST_Point(lon,lat),ST_Point(lon+1,lat+1)),4326));
+          IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
+            x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo);
+          END IF;
+        END LOOP;
+      END LOOP;
+    END IF;

 
 

-      -- ~ 20meters
-      secgeo := ST_Buffer(geometry, 0.0002);
-      x := insertLocationAreaRoadNear(partition, place_id, country_code, keywords, rank_search, rank_address, false, ST_Centroid(geometry), secgeo);
-
-      -- ~ 100meters
-      secgeo := ST_Buffer(geometry, 0.001);
-      x := insertLocationAreaRoadFar(partition, place_id, country_code, keywords, rank_search, rank_address, false, ST_Centroid(geometry), secgeo);
+  ELSEIF rank_search < 26 THEN

 
 

+    diameter := 0.02;
+    IF rank_search = 14 THEN
+      diameter := 1;
+    ELSEIF rank_search = 15 THEN
+      diameter := 0.5;
+    ELSEIF rank_search = 16 THEN
+      diameter := 0.15;
+    ELSEIF rank_search = 17 THEN
+      diameter := 0.05;
+    ELSEIF rank_search = 25 THEN
+      diameter := 0.005;

     END IF;
 
     END IF;
 

-    RETURN true;
+    secgeo := ST_Buffer(geometry, diameter);
+    x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
+
+  ELSE
+
+    -- ~ 20meters
+    secgeo := ST_Buffer(geometry, 0.0002);
+    x := insertLocationAreaRoadNear(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);
+
+    -- ~ 100meters
+    secgeo := ST_Buffer(geometry, 0.001);
+    x := insertLocationAreaRoadFar(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo);

 
   END IF;
 
 
   END IF;
 

-  RETURN false;
+  RETURN true;

 END;
 $$
 LANGUAGE plpgsql;
 END;
 $$
 LANGUAGE plpgsql;


@@ -812,9 +813,9 @@ BEGIN
                 -- 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, 
                 -- 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, 

-                  country_code, street_place_id, rank_address, rank_search, indexed_status, geometry)
+                  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, 

-                  prevnode.country_code, prevnode.street_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
+                  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;
               END LOOP;
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
               END LOOP;


@@ -879,12 +880,13 @@ BEGIN
   END IF;
 
   NEW.place_id := nextval('seq_place');
   END IF;
 
   NEW.place_id := nextval('seq_place');

-  NEW.indexed_status := 1;
-  NEW.country_code := lower(NEW.country_code);
+  NEW.indexed_status := 1; --STATUS_NEW
+

   IF NEW.country_code is null THEN
     NEW.country_code := get_country_code(NEW.geometry);
   END IF;
   NEW.geometry_sector := geometry_sector(NEW.geometry);
   IF NEW.country_code is null THEN
     NEW.country_code := get_country_code(NEW.geometry);
   END IF;
   NEW.geometry_sector := geometry_sector(NEW.geometry);

+  NEW.partition := get_partition(NEW.geometry, NEW.country_code);

 
   IF NEW.admin_level > 15 THEN
     NEW.admin_level := 15;
 
   IF NEW.admin_level > 15 THEN
     NEW.admin_level := 15;


@@ -955,10 +957,6 @@ BEGIN
         NEW.rank_address := 22;
       ELSEIF NEW.type in ('postcode') THEN
 
         NEW.rank_address := 22;
       ELSEIF NEW.type in ('postcode') THEN
 

-        -- Postcode processing is very country dependant
-        IF NEW.country_code IS NULL THEN
-        END IF;
-

         NEW.name := 'ref'=>NEW.postcode;
 
         IF NEW.country_code = 'gb' THEN
         NEW.name := 'ref'=>NEW.postcode;
 
         IF NEW.country_code = 'gb' THEN


@@ -1064,10 +1062,6 @@ BEGIN
 --    RETURN NULL;
 --  END IF;
 
 --    RETURN NULL;
 --  END IF;
 

-  IF NEW.name is not null THEN
-    result := add_location(NEW.place_id,NEW.country_code,NEW.name,NEW.rank_search,NEW.rank_address,NEW.geometry);
-  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
 


@@ -1078,9 +1072,9 @@ BEGIN
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 -- work around bug in postgis
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
 -- work around bug in postgis
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-       AND rank_search > NEW.rank_search and indexed = 0 and ST_geometrytype(placex.geometry) = 'ST_Point';
+       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point';

       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 

-       AND rank_search > NEW.rank_search and indexed = 0 and ST_geometrytype(placex.geometry) != 'ST_Point';
+       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point';

     END IF;
   ELSE
     -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
     END IF;
   ELSE
     -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(


@@ -1105,7 +1099,7 @@ BEGIN
     END IF;
     IF diameter > 0 THEN
 --      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
     END IF;
     IF diameter > 0 THEN
 --      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;

-      update placex set indexed = 2 where indexed and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+      update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);

     END IF;
 
   END IF;
     END IF;
 
   END IF;


@@ -1142,7 +1136,7 @@ DECLARE
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;

-  street_place_id_count INTEGER;
+  parent_place_id_count INTEGER;

   isin TEXT[];
   isin_tokens INT[];
 
   isin TEXT[];
   isin_tokens INT[];
 


@@ -1214,12 +1208,12 @@ BEGIN
 
 --RAISE WARNING 'finding street for %', NEW;
 
 
 --RAISE WARNING 'finding street for %', NEW;
 

-      NEW.street_place_id := null;
+      NEW.parent_place_id := null;

 
       -- to do that we have to find our parent road
       -- Copy data from linked items (points on ways, addr:street links, relations)
       -- Note that addr:street links can only be indexed once the street itself is indexed
 
       -- to do that we have to find our parent road
       -- Copy data from linked items (points on ways, addr:street links, relations)
       -- Note that addr:street links can only be indexed once the street itself is indexed

-      IF NEW.street_place_id IS NULL AND NEW.osm_type = 'N' THEN
+      IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN

 
         -- Is this node part of a relation?
         FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]
 
         -- Is this node part of a relation?
         FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['n'||NEW.osm_id]


@@ -1227,10 +1221,10 @@ BEGIN
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP

-              IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+              IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN

 --RAISE WARNING 'node in relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
 --RAISE WARNING 'node in relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 

-                  and rank_search = 26 INTO NEW.street_place_id;
+                  and rank_search = 26 INTO NEW.parent_place_id;

               END IF;
             END LOOP;
           END IF;
               END IF;
             END LOOP;
           END IF;


@@ -1243,9 +1237,9 @@ BEGIN
         LOOP
 --RAISE WARNING '%', location;
           -- Way IS a road then we are on it - that must be our road
         LOOP
 --RAISE WARNING '%', location;
           -- Way IS a road then we are on it - that must be our road

-          IF location.rank_search = 26 AND NEW.street_place_id IS NULL THEN
+          IF location.rank_search = 26 AND NEW.parent_place_id IS NULL THEN

 --RAISE WARNING 'node in way that is a street %',location;
 --RAISE WARNING 'node in way that is a street %',location;

-            NEW.street_place_id := location.place_id;
+            NEW.parent_place_id := location.place_id;

           END IF;
 
           -- Is the WAY part of a relation
           END IF;
 
           -- Is the WAY part of a relation


@@ -1254,10 +1248,10 @@ BEGIN
             -- At the moment we only process one type of relation - associatedStreet
             IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
               FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
             -- At the moment we only process one type of relation - associatedStreet
             IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
               FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP

-                IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+                IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN

 --RAISE WARNING 'node in way that is in a relation %',relation;
                   SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 
 --RAISE WARNING 'node in way that is in a relation %',relation;
                   SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer 

-                    and rank_search = 26 INTO NEW.street_place_id;
+                    and rank_search = 26 INTO NEW.parent_place_id;

                 END IF;
               END LOOP;
             END IF;
                 END IF;
               END LOOP;
             END IF;


@@ -1270,7 +1264,7 @@ BEGIN
           END IF;
 
           -- If this way is a street interpolation line then it is probably as good as we are going to get
           END IF;
 
           -- If this way is a street interpolation line then it is probably as good as we are going to get

-          IF NEW.street_place_id IS NULL AND NEW.street IS NULL AND location.class = 'place' and location.type='houses' THEN
+          IF NEW.parent_place_id IS NULL AND NEW.street IS NULL AND location.class = 'place' and location.type='houses' THEN

             -- Try and find a way that is close roughly parellel to this line
             FOR relation IN SELECT place_id FROM placex
               WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26
             -- Try and find a way that is close roughly parellel to this line
             FOR relation IN SELECT place_id FROM placex
               WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26


@@ -1280,7 +1274,7 @@ BEGIN
                         ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,1))) ASC limit 1
             LOOP
 --RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation;
                         ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,1))) ASC limit 1
             LOOP
 --RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation;

-              NEW.street_place_id := relation.place_id;
+              NEW.parent_place_id := relation.place_id;

             END LOOP;
           END IF;
 
             END LOOP;
           END IF;
 


@@ -1290,17 +1284,17 @@ BEGIN
 
 --RAISE WARNING 'x2';
 
 
 --RAISE WARNING 'x2';
 

-      IF NEW.street_place_id IS NULL AND NEW.osm_type = 'W' THEN
+      IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN

         -- Is this way part of a relation?
         FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
         -- Is this way part of a relation?
         FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id::integer] and members @> ARRAY['w'||NEW.osm_id]
         LOOP
           -- At the moment we only process one type of relation - associatedStreet
           IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
             FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP

-              IF NEW.street_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+              IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN

 --RAISE WARNING 'way that is in a relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer
 --RAISE WARNING 'way that is in a relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer

-                  and rank_search = 26 INTO NEW.street_place_id;
+                  and rank_search = 26 INTO NEW.parent_place_id;

               END IF;
             END LOOP;
           END IF;
               END IF;
             END LOOP;
           END IF;


@@ -1309,7 +1303,7 @@ BEGIN
       
 --RAISE WARNING 'x3';
 
       
 --RAISE WARNING 'x3';
 

-      IF NEW.street_place_id IS NULL AND NEW.street IS NOT NULL THEN
+      IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN

        address_street_word_id := get_name_id(make_standard_name(NEW.street));
 --RAISE WARNING 'street: % %', NEW.street, address_street_word_id;
         IF address_street_word_id IS NOT NULL THEN
        address_street_word_id := get_name_id(make_standard_name(NEW.street));
 --RAISE WARNING 'street: % %', NEW.street, address_street_word_id;
         IF address_street_word_id IS NOT NULL THEN


@@ -1319,11 +1313,11 @@ BEGIN
             ORDER BY ST_distance(NEW.geometry, search_name.centroid) ASC limit 1
           LOOP
 --RAISE WARNING 'streetname found nearby %',location;
             ORDER BY ST_distance(NEW.geometry, search_name.centroid) ASC limit 1
           LOOP
 --RAISE WARNING 'streetname found nearby %',location;

-            NEW.street_place_id := location.place_id;
+            NEW.parent_place_id := location.place_id;

           END LOOP;
         END IF;
         -- Failed, fall back to nearest - don't just stop
           END LOOP;
         END IF;
         -- Failed, fall back to nearest - don't just stop

-        IF NEW.street_place_id IS NULL THEN
+        IF NEW.parent_place_id IS NULL THEN

 --RAISE WARNING 'unable to find streetname nearby % %',NEW.street,address_street_word_id;
 --          RETURN null;
         END IF;
 --RAISE WARNING 'unable to find streetname nearby % %',NEW.street,address_street_word_id;
 --          RETURN null;
         END IF;


@@ -1331,35 +1325,35 @@ BEGIN
 
 --RAISE WARNING 'x4';
 
 
 --RAISE WARNING 'x4';
 

-      IF NEW.street_place_id IS NULL THEN
+      IF NEW.parent_place_id IS NULL THEN

         FOR location IN SELECT place_id FROM getNearRoads(NEW.partition, place_centroid) LOOP
         FOR location IN SELECT place_id FROM getNearRoads(NEW.partition, place_centroid) LOOP

-          NEW.street_place_id := location.place_id;
+          NEW.parent_place_id := location.place_id;

         END LOOP;
       END IF;
 
         END LOOP;
       END IF;
 

---RAISE WARNING 'x6 %',NEW.street_place_id;
+--RAISE WARNING 'x6 %',NEW.parent_place_id;

 
       -- If we didn't find any road fallback to standard method
 
       -- If we didn't find any road fallback to standard method

-      IF NEW.street_place_id IS NOT NULL THEN
+      IF NEW.parent_place_id IS NOT NULL THEN

 
         -- Some unnamed roads won't have been indexed, index now if needed
 -- ALL are now indexed!
 
         -- Some unnamed roads won't have been indexed, index now if needed
 -- ALL are now indexed!

---        select count(*) from place_addressline where place_id = NEW.street_place_id INTO street_place_id_count;
---        IF street_place_id_count = 0 THEN
---          UPDATE placex set indexed = true where indexed = false and place_id = NEW.street_place_id;
+--        select count(*) from place_addressline where place_id = NEW.parent_place_id INTO parent_place_id_count;
+--        IF parent_place_id_count = 0 THEN
+--          UPDATE placex set indexed = true where indexed = false and place_id = NEW.parent_place_id;

 --        END IF;
 
         -- Add the street to the address as zero distance to force to front of list
 --        END IF;
 
         -- Add the street to the address as zero distance to force to front of list

-        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.street_place_id, true, true, 0, 26);
+        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);

         address_havelevel[26] := true;
 
         -- Import address details from parent, reclculating distance in process
         INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
           from place_addressline as x join placex on (address_place_id = placex.place_id)
         address_havelevel[26] := true;
 
         -- Import address details from parent, reclculating distance in process
         INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
           from place_addressline as x join placex on (address_place_id = placex.place_id)

-          where x.place_id = NEW.street_place_id and x.address_place_id != NEW.street_place_id;
+          where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;

 
         -- Get the details of the parent road
 
         -- Get the details of the parent road

-        select * from search_name where place_id = NEW.street_place_id INTO location;
+        select * from search_name where place_id = NEW.parent_place_id INTO location;

         NEW.country_code := location.country_code;
 
 --RAISE WARNING '%', NEW.name;
         NEW.country_code := location.country_code;
 
 --RAISE WARNING '%', NEW.name;


@@ -1373,7 +1367,7 @@ BEGIN
 
         -- 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
 
         -- 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

-        INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_address, NEW.country_code,
+        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;
           name_vector, nameaddress_vector, place_centroid);
 
         return NEW;


@@ -1403,7 +1397,7 @@ BEGIN
     location_distance := 0;
     FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
 
     location_distance := 0;
     FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
 

---RAISE WARNING '  AREA: % % %',location.keywords,NEW.country_code,location.country_code;
+--RAISE WARNING '  AREA: %',location;

 
       IF location.rank_search < location_rank_search THEN
         location_rank_search := location.rank_search;
 
       IF location.rank_search < location_rank_search THEN
         location_rank_search := location.rank_search;


@@ -1442,7 +1436,12 @@ BEGIN
 
     -- if we have a name add this to the name search table
     IF NEW.name IS NOT NULL THEN
 
     -- if we have a name add this to the name search table
     IF NEW.name IS NOT NULL THEN

-      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, NEW.country_code, 
+
+      IF NEW.rank_search <= 26 THEN
+        result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
+      END IF;
+
+      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, 

         name_vector, nameaddress_vector, place_centroid);
     END IF;
 
         name_vector, nameaddress_vector, place_centroid);
     END IF;
 


@@ -1586,7 +1585,7 @@ BEGIN
         ,NEW.isin
         ,NEW.postcode
         ,NEW.country_code
         ,NEW.isin
         ,NEW.postcode
         ,NEW.country_code

-        ,NEW.street_place_id
+        ,NEW.parent_place_id

         ,NEW.rank_address
         ,NEW.rank_search
         ,NEW.indexed
         ,NEW.rank_address
         ,NEW.rank_search
         ,NEW.indexed


@@ -1716,7 +1715,7 @@ BEGIN
       isin = NEW.isin,
       postcode = NEW.postcode,
       country_code = NEW.country_code,
       isin = NEW.isin,
       postcode = NEW.postcode,
       country_code = NEW.country_code,

-      street_place_id = null,
+      parent_place_id = null,

       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 
       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 


@@ -1727,8 +1726,8 @@ BEGIN
       isin = NEW.isin,
       postcode = NEW.postcode,
       country_code = NEW.country_code,
       isin = NEW.isin,
       postcode = NEW.postcode,
       country_code = NEW.country_code,

-      street_place_id = null,
-      indexed = false,
+      parent_place_id = null,
+      indexed_status = 2,

       geometry = NEW.geometry
       where place_id = existingplacex.place_id;
 
       geometry = NEW.geometry
       where place_id = existingplacex.place_id;
 


@@ -1802,8 +1801,6 @@ BEGIN
   search := ARRAY['ref'];
   result := '{}';
 
   search := ARRAY['ref'];
   result := '{}';
 

-  UPDATE placex set indexed = true where indexed = false and place_id = for_place_id;
-

   select postcode from placex where place_id = for_place_id limit 1 into for_postcode;
 
   FOR location IN 
   select postcode from placex where place_id = for_place_id limit 1 into for_postcode;
 
   FOR location IN 


@@ -1956,10 +1953,10 @@ BEGIN
 -- remove  isaddress = true because if there is a matching polygon it always wins
     select count(*) from place_addressline where address_place_id = search_place_id into numfeatures;
     insert into place_boundingbox select place_id,
 -- remove  isaddress = true because if there is a matching polygon it always wins
     select count(*) from place_addressline where address_place_id = search_place_id into numfeatures;
     insert into place_boundingbox select place_id,

-             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),2)),
-             ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),3)),
-             numfeatures, ST_Area(area),
-             area from location_area where place_id = search_place_id;
+             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
+             ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
+             numfeatures, ST_Area(geometry),
+             geometry as area from location_area where place_id = search_place_id;

     select * from place_boundingbox into result where place_id = search_place_id;
   END IF;
   IF result.place_id IS NULL THEN
     select * from place_boundingbox into result where place_id = search_place_id;
   END IF;
   IF result.place_id IS NULL THEN


@@ -1993,10 +1990,10 @@ BEGIN
   IF result IS NULL AND rank > 14 THEN
     select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
     insert into place_boundingbox select place_id,
   IF result IS NULL AND rank > 14 THEN
     select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures;
     insert into place_boundingbox select place_id,

-             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(area)),2)),
-             ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(area)),3)),
-             numfeatures, ST_Area(area),
-             area from location_area where place_id = search_place_id;
+             ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)),
+             ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)),
+             numfeatures, ST_Area(geometry),
+             geometry as area from location_area where place_id = search_place_id;

     select * from place_boundingbox into result where place_id = search_place_id;
   END IF;
   IF result IS NULL THEN
     select * from place_boundingbox into result where place_id = search_place_id;
   END IF;
   IF result IS NULL THEN


@@ -2034,13 +2031,13 @@ BEGIN
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,

-      street_place_id = null,
-      indexed = false      
+      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;
       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 = true where place_id = search_place_id and indexed = false;
+  update placex set indexed_status = 0 where place_id = search_place_id;

   return true;
 END;
 $$
   return true;
 END;
 $$


@@ -2059,13 +2056,13 @@ BEGIN
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,
       isin = place.isin,
       postcode = place.postcode,
       country_code = place.country_code,

-      street_place_id = null,
-      indexed = false      
+      parent_place_id = null,
+      indexed_status = 2      

       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;
       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 = true where place_id = search_place_id and indexed = false;
+  update placex set indexed_status = 0 where place_id = search_place_id;

   return true;
 END;
 $$
   return true;
 END;
 $$

diff --git a/sql/partitions.src.sql b/sql/partitions.src.sql
index a47563f60b16208a54e9d303510527e4eff26a2c..b6acd0f28263e8d099336251ebc4775583c264c9 100644 (file)
--- a/sql/partitions.src.sql
+++ b/sql/partitions.src.sql
@@ -1,9 +1,9 @@
 create type nearplace as (
 create type nearplace as (

-  place_id bigint
+  place_id integer

 );
 
 create type nearfeature as (
 );
 
 create type nearfeature as (

-  place_id bigint,
+  place_id integer,

   keywords int[],
   rank_address integer,
   rank_search integer,
   keywords int[],
   rank_address integer,
   rank_search integer,


@@ -11,16 +11,20 @@ create type nearfeature as (
 );
 
 CREATE TABLE location_area_country () INHERITS (location_area_large);
 );
 
 CREATE TABLE location_area_country () INHERITS (location_area_large);

+CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id);

 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry);
 
 -- start
 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large);
 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry);
 
 -- start
 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large);

+CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id);

 CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry);
 
 CREATE TABLE location_area_roadnear_-partition- () INHERITS (location_area_roadnear);
 CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large_-partition- USING GIST (geometry);
 
 CREATE TABLE location_area_roadnear_-partition- () INHERITS (location_area_roadnear);

+CREATE INDEX idx_location_area_roadnear_-partition-_place_id ON location_area_roadfar_-partition- USING BTREE (place_id);

 CREATE INDEX idx_location_area_roadnear_-partition-_geometry ON location_area_roadnear_-partition- USING GIST (geometry);
 
 CREATE TABLE location_area_roadfar_-partition- () INHERITS (location_area_roadfar);
 CREATE INDEX idx_location_area_roadnear_-partition-_geometry ON location_area_roadnear_-partition- USING GIST (geometry);
 
 CREATE TABLE location_area_roadfar_-partition- () INHERITS (location_area_roadfar);

+CREATE INDEX idx_location_area_roadfar_-partition-_place_id ON location_area_roadfar_-partition- USING BTREE (place_id);

 CREATE INDEX idx_location_area_roadfar_-partition-_geometry ON location_area_roadfar_-partition- USING GIST (geometry);
 -- end
 
 CREATE INDEX idx_location_area_roadfar_-partition-_geometry ON location_area_roadfar_-partition- USING GIST (geometry);
 -- end
 


@@ -77,15 +81,15 @@ END
 $$
 LANGUAGE plpgsql;
 
 $$
 LANGUAGE plpgsql;
 

-create or replace function deleteLocationArea(in_partition TEXT, in_place_id bigint) RETURNS BOOLEAN AS $$
+create or replace function deleteLocationArea(in_partition TEXT, in_place_id integer) RETURNS BOOLEAN AS $$

 DECLARE
 BEGIN
 
 -- start
   IF in_partition = '-partition-' THEN
 DECLARE
 BEGIN
 
 -- start
   IF in_partition = '-partition-' THEN

-    DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
-    DELETE from location_area_roadnear_-partition- WHERE place_id = in_place_id;
-    DELETE from location_area_roadfar_-partition- WHERE place_id = in_place_id;
+--    DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
+--    DELETE from location_area_roadnear_-partition- WHERE place_id = in_place_id;
+--    DELETE from location_area_roadfar_-partition- WHERE place_id = in_place_id;

     RETURN TRUE;
   END IF;
 -- end
     RETURN TRUE;
   END IF;
 -- end


@@ -98,7 +102,7 @@ $$
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaLarge(
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaLarge(

-  in_partition TEXT, in_place_id bigint, in_country_code VARCHAR(2), in_keywords INTEGER[], 
+  in_partition TEXT, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[], 

   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE
   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE


@@ -123,7 +127,7 @@ $$
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaRoadNear(
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaRoadNear(

-  in_partition TEXT, in_place_id bigint, in_country_code VARCHAR(2), in_keywords INTEGER[], 
+  in_partition TEXT, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[], 

   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE
   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE


@@ -143,7 +147,7 @@ $$
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaRoadFar(
 LANGUAGE plpgsql;
 
 create or replace function insertLocationAreaRoadFar(

-  in_partition TEXT, in_place_id bigint, in_country_code VARCHAR(2), in_keywords INTEGER[], 
+  in_partition TEXT, in_place_id integer, in_country_code VARCHAR(2), in_keywords INTEGER[], 

   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE
   in_rank_search INTEGER, in_rank_address INTEGER, in_estimate BOOLEAN, 
   in_centroid GEOMETRY, in_geometry GEOMETRY) RETURNS BOOLEAN AS $$
 DECLARE

diff --git a/sql/tables.sql b/sql/tables.sql
index 62a4eb36a1398a249809ba1234c3207e8a2b2955..2901b1c67ef881fe570bb63ab1ddeed6856aa06e 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -96,6 +96,7 @@ CREATE TABLE search_name (
   place_id INTEGER,
   search_rank integer,
   address_rank integer,
   place_id INTEGER,
   search_rank integer,
   address_rank integer,

+  importance FLOAT,

   country_code varchar(2),
   name_vector integer[],
   nameaddress_vector integer[]
   country_code varchar(2),
   name_vector integer[],
   nameaddress_vector integer[]


@@ -172,7 +173,8 @@ CREATE TABLE placex (
   postcode TEXT,
   country_code varchar(2),
   extratags HSTORE,
   postcode TEXT,
   country_code varchar(2),
   extratags HSTORE,

-  street_place_id INTEGER,
+  parent_place_id INTEGER,
+  linked_place_id INTEGER,

   rank_address INTEGER,
   rank_search INTEGER,
   indexed_status INTEGER,
   rank_address INTEGER,
   rank_search INTEGER,
   indexed_status INTEGER,


@@ -193,7 +195,7 @@ CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometr
 
 --CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search)  where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
 
 
 --CREATE INDEX idx_placex_pendingbylatlon ON placex USING BTREE (geometry_index(geometry_sector,indexed,name),rank_search)  where geometry_index(geometry_sector,indexed,name) IS NOT NULL;
 

-CREATE INDEX idx_placex_street_place_id ON placex USING BTREE (street_place_id) where street_place_id IS NOT NULL;
+CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL;

 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
 
 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
 
 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);

diff --git a/utils/setup.php b/utils/setup.php
index 72dd9c9a12e40e09ba2afb08c2d02a50ff941729..5e40822309646ae138a30dfd0d50db2b8f3cac3b 100755 (executable)
--- a/utils/setup.php
+++ b/utils/setup.php
@@ -73,6 +73,11 @@
        {
                $bDidSomething = true;
                pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');
        {
                $bDidSomething = true;
                pgsqlRunScriptFile(CONST_BasePath.'/sql/tables.sql');

+
+               // re-run the functions
+               $sTemplate = file_get_contents(CONST_BasePath.'/sql/functions.sql');
+               $sTemplate = str_replace('{modulepath}',CONST_BasePath.'/module', $sTemplate);
+               pgsqlRunScript($sTemplate);

        }
 
        if ($aCMDResult['create-partitions'] || isset($aCMDResult['all']))
        }
 
        if ($aCMDResult['create-partitions'] || isset($aCMDResult['all']))


@@ -104,7 +109,41 @@
        if ($aCMDResult['load-data'] || isset($aCMDResult['all']))
        {
                $bDidSomething = true;
        if ($aCMDResult['load-data'] || isset($aCMDResult['all']))
        {
                $bDidSomething = true;

-               pgsqlRunScriptFile(CONST_BasePath.'/sql/loaddata.sql');
+
+               $oDB =& getDB();
+               if (!pg_query($oDB->connection, 'TRUNCATE placex')) fail(pg_last_error($oDB->connection));
+               echo '.';
+               if (!pg_query($oDB->connection, 'TRUNCATE place_addressline')) fail(pg_last_error($oDB->connection));
+               echo '.';
+               if (!pg_query($oDB->connection, 'TRUNCATE location_area')) fail(pg_last_error($oDB->connection));
+               echo '.';
+               if (!pg_query($oDB->connection, 'DROP SEQUENCE seq_place')) fail(pg_last_error($oDB->connection));
+               echo '.';
+               if (!pg_query($oDB->connection, 'CREATE SEQUENCE seq_place start 100000')) fail(pg_last_error($oDB->connection));
+               echo '.';
+
+               $iInstances = 16;
+               $aDBInstances = array();
+               for($i = 0; $i < $iInstances; $i++)
+               {
+                       $aDBInstances[$i] =& getDB(true);
+                       $sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
+                       $sSQL .= 'housenumber, street, isin, postcode, country_code, extratags, ';
+                       $sSQL .= 'geometry) select * from place where osm_id % '.$iInstances.' = '.$i;
+                       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 (!$bDidSomething)
        }
 
        if (!$bDidSomething)

diff --git a/website/details.php b/website/details.php
new file mode 100755 (executable)
index 0000000..6eaa4ad
--- /dev/null
+++ b/website/details.php
@@ -0,0 +1,138 @@
+<?php
+        require_once(dirname(dirname(__FILE__)).'/lib/init-website.php');
+        require_once(CONST_BasePath.'/lib/log.php');
+
+        $fLoadAvg = getLoadAverage();
+        if ($fLoadAvg > 3)
+        {
+               echo "Page temporarily blocked due to high server load\n";
+                exit;
+        }
+
+       ini_set('memory_limit', '200M');
+
+       $oDB =& getDB();
+
+       if (isset($_GET['osmtype']) && isset($_GET['osmid']) && (int)$_GET['osmid'] && ($_GET['osmtype'] == 'N' || $_GET['osmtype'] == 'W' || $_GET['osmtype'] == 'R'))
+       {
+               $_GET['place_id'] = $oDB->getOne("select place_id from placex where osm_type = '".$_GET['osmtype']."' and osm_id = ".(int)$_GET['osmid']." order by type = 'postcode' asc");
+       }
+
+       if (!isset($_GET['place_id']))
+       {
+               echo "Please select a place id";
+               exit;
+       }
+
+       $iPlaceID = (int)$_GET['place_id'];
+
+       $aLangPrefOrder = getPrefferedLangauges();
+       $sLanguagePrefArraySQL = "ARRAY[".join(',',array_map("getDBQuoted",$aLangPrefOrder))."]";
+
+       $hLog = logStart($oDB, 'details', $_SERVER['QUERY_STRING'], $aLangPrefOrder);
+
+       // Make sure the point we are reporting on is fully indexed
+       //$sSQL = "UPDATE placex set indexed = true where indexed = false and place_id = $iPlaceID";
+       //$oDB->query($sSQL);
+
+       // Get the details for this point
+       $sSQL = "select place_id, osm_type, osm_id, class, type, name, admin_level, housenumber, street, isin, postcode, country_code, ";
+       $sSQL .= " 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 .= " ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') as isarea,ST_GeometryType(geometry) as geotype, ST_Y(ST_Centroid(geometry)) as lat,ST_X(ST_Centroid(geometry)) as lon ";
+       $sSQL .= " from placex where place_id = $iPlaceID";
+       $aPointDetails = $oDB->getRow($sSQL);
+       IF (PEAR::IsError($aPointDetails))
+       {
+               var_dump($aPointDetails);
+               exit;
+       }
+        $aPointDetails['localname'] = $aPointDetails['localname']?$aPointDetails['localname']:$aPointDetails['housenumber'];
+       $fLon = $aPointDetails['lon'];
+       $fLat = $aPointDetails['lat'];
+       $iZoom = 14;
+
+       $aClassType = getClassTypesWithImportance();
+       $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'];
+       }
+*/
+       // Get the bounding box and outline polygon
+       $sSQL = "select *,ST_AsText(outline) as outlinestring from get_place_boundingbox($iPlaceID)";
+       $aPointPolygon = $oDB->getRow($sSQL);
+       IF (PEAR::IsError($aPointPolygon))
+       {
+               var_dump($aPointPolygon);
+               exit;
+       }
+       if (preg_match('#POLYGON\\(\\(([- 0-9.,]+)#',$aPointPolygon['outlinestring'],$aMatch))
+       {
+               preg_match_all('/(-?[0-9.]+) (-?[0-9.]+)/',$aMatch[1],$aPolyPoints,PREG_SET_ORDER);
+       }
+       elseif (preg_match('#POINT\\((-?[0-9.]+) (-?[0-9.]+)\\)#',$aPointPolygon['outlinestring'],$aMatch))
+       {
+               $fRadius = 0.01;
+               $iSteps = ($fRadius * 40000)^2;
+               $fStepSize = (2*pi())/$iSteps;
+               $aPolyPoints = array();
+               for($f = 0; $f < 2*pi(); $f += $fStepSize)
+               {
+
+                       $aPolyPoints[] = array('',$aMatch[1]+($fRadius*sin($f)),$aMatch[2]+($fRadius*cos($f)));
+               }
+               $aPointPolygon['minlat'] = $aPointPolygon['minlat'] - $fRadius;
+               $aPointPolygon['maxlat'] = $aPointPolygon['maxlat'] + $fRadius;
+               $aPointPolygon['minlon'] = $aPointPolygon['minlon'] - $fRadius;
+               $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, ";
+       $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";
+       $aParentOfLines = $oDB->getAll($sSQL);
+
+       logEnd($oDB, $hLog, 1);
+
+       include('.htlib/output/details-html.php');

diff --git a/website/search.php b/website/search.php
index a123e833a0e7ee66e9dbfec0f551a8d78ae7d847..a1052394410b5dc5e4dc40d1a3f900175c3ad029 100755 (executable)
--- a/website/search.php
+++ b/website/search.php
@@ -419,8 +419,11 @@
                                                                                }
                                                                                else
                                                                                {
                                                                                }
                                                                                else
                                                                                {

-                                                                                       $aSearch['aAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
-                                                                                       if (!sizeof($aSearch['aName']))
+                                                                                       if (sizeof($aSearch['aName']))
+                                                                                       {
+                                                                                               $aSearch['aAddress'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
+                                                                                       }
+                                                                                       else

                                                                                        {
                                                                                                $aSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
                                                                                                $aSearch['iNamePhrase'] = $iPhrase;
                                                                                        {
                                                                                                $aSearch['aName'][$aSearchTerm['word_id']] = $aSearchTerm['word_id'];
                                                                                                $aSearch['iNamePhrase'] = $iPhrase;


@@ -590,7 +593,7 @@
                                        {
                                                $iQueryLoop++;
                                                // Must have a location term
                                        {
                                                $iQueryLoop++;
                                                // Must have a location term

-                                               if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress']))
+                                               if (!sizeof($aSearch['aName']) && !sizeof($aSearch['aAddress'] && !$aSearch['fLon']))

                                                {
                                                        if (!$bBoundingBoxSearch && !$aSearch['fLon']) continue;
                                                        if (!$aSearch['sClass']) continue;
                                                {
                                                        if (!$bBoundingBoxSearch && !$aSearch['fLon']) continue;
                                                        if (!$aSearch['sClass']) continue;


@@ -710,7 +713,7 @@
                                                                $oDB->query($sSQL);
                                                        
                                                                // Now they are indexed look for a house attached to a street we found
                                                                $oDB->query($sSQL);
                                                        
                                                                // Now they are indexed look for a house attached to a street we found

-                                                               $sSQL = "select place_id from placex where street_place_id in (".$sPlaceIDs.") and housenumber ~* E'".$sHouseNumberRegex."'";
+                                                               $sSQL = "select place_id from placex where parent_place_id in (".$sPlaceIDs.") and housenumber ~* E'".$sHouseNumberRegex."'";

                                                                if (sizeof($aExcludePlaceIDs))
                                                                {
                                                                        $sSQL .= " and place_id not in (".join(',',$aExcludePlaceIDs).")";
                                                                if (sizeof($aExcludePlaceIDs))
                                                                {
                                                                        $sSQL .= " and place_id not in (".join(',',$aExcludePlaceIDs).")";