From c5e69fadd0beed697a4a3d1708fcdaa4f29ac703 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 28 Jan 2012 23:27:25 +0000 Subject: [PATCH] improve import performance for special phrases - create a temporary index on placex - create indices on place_classtype_* tables after insert - grant www-data permission to tables --- utils/specialphrases.php | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/utils/specialphrases.php b/utils/specialphrases.php index 39c4dc4f..5b3abe40 100755 --- a/utils/specialphrases.php +++ b/utils/specialphrases.php @@ -81,13 +81,15 @@ } } + echo "create index idx_placex_classtype on placex (class, type);"; + foreach($aPairs as $aPair) { if ($aPair[1] == 'highway') continue; echo "create table place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." as "; echo "select place_id as place_id,st_centroid(geometry) as centroid from placex where "; - echo "class = '".pg_escape_string($aPair[0])."' and type = '".pg_escape_string($aPair[1])."' limit 0;\n"; + echo "class = '".pg_escape_string($aPair[0])."' and type = '".pg_escape_string($aPair[1])."';\n"; echo "CREATE INDEX idx_place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])."_centroid "; echo "ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." USING GIST (centroid);\n"; @@ -95,10 +97,9 @@ echo "CREATE INDEX idx_place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])."_place_id "; echo "ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." USING btree(place_id);\n"; - echo "truncate place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1]).";\n"; - echo "insert into place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." "; - echo "select place_id as place_id,st_centroid(geometry) as centroid from placex where "; - echo "class = '".pg_escape_string($aPair[0])."' and type = '".pg_escape_string($aPair[1])."';\n"; + echo "GRANT SELECT ON place_classtype_".pg_escape_string($aPair[0])."_".pg_escape_string($aPair[1])." TO \"www-data\";"; } + + echo "drop index idx_placex_classtype;"; } -- 2.45.1