ФИАС в PostgreSQL и скорость ответа

Суть в том, что коллега импортировал в PostgreSQL базу  ФИАС


Создавалось всё скорее всего по этой статье:
http://www.wd5.ru/zametki-razrabotchika/bazy-dannyh-adresnoy-informacii-rossii/

Самая подробная и достоверная база адресов это ФИАС

    Федеральная информационная адресная система (ФИАС) содержит достоверную единообразную и структурированную адресную информацию по территории Российской Федерации, доступную для использования органами государственной власти, органами местного самоуправления, физическими и юридическими лицами.

Страница загрузки базы данных: http://fias.nalog.ru/Public/DownloadPage.aspx

Размер в архиве: 1 ГБ

Формат: xml, dbf

Вторая база данных часто используемя в программах это КЛАДР

    КЛАДР - Классификатор Адресов Российской Федерации. Ведомственный классификатор ФНС России, созданный для распределения территорий между налоговыми инспекциями и автоматизированной рассылки корреспонденции. КЛАДР введен в действие с 01.12.2005 приказом ФНС России от 17.11.2005 № САЭ-3-13/594.

    КЛАДР доступен для свободного скачивания на сайте www.ksoft.ru и может использоваться для автоматизации деятельности и ускорения ввода данных. Для установки вначале необходимо распаковать архив в любую папку и указать путь к ней в нужной программе.

    В КЛАДРе есть почтовые индексы и коды ОКАТО всех населённых пунктов.

Страница загрузки базы данных: http://www.ksoft.ru/kladr.htm или http://www.gnivc.ru/inf_provision/classifiers_reference/kladr/

Формат: dbf

Размер в архиве: 8 мб

Без сжатия: 106 мб

Как конвертировать

Конвертировать в файлы пригодные для импорта в postgresql можно с помощью утилиты PgDBF и iconv

pgdbf ADDROBJ.DBF | iconv -c -f CP866 -t UTF-8

Для MySQL с помощью MyDBF2MySQL

С начала была эпопея со старым импортом который был в PostgreSQL 8х и не работает в PostgreSQL 9х
На форуме sql.ru подсказали решение этой проблемы

В 9тке просто поведение по умолчанию изменилось (на ANSI SQL стандартное).
можно сделать и как в старой версии... в конфиге все это есть в секции
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

в районе
#backslash_quote = safe_encoding # on, off, or safe_encoding
и
#standard_conforming_strings = on

Можно и не глобально поменять а для конкретного пользователя или для конкретной сессии.

Потом выяснилось, что ФИАС долго ищет данные
Вот такой запрос выполнялся около 6 секунд:
SELECT
ao.id, ao.fullname ,ao.formalname as obj ,cast(aolevel as char)||ao.fullname as xx
FROM
addrobj ao
WHERE
livestatus='1' and  actstatus='1' AND formalname ilike 'ростов%'
ORDER BY ao.aolevel,formalname limit 10;

Было принято быстрое решение завернуть всё через sphinxsearch
Но база всего 1.7Гб, это не 1.7Тб это не 170Гб и даже не 17Гб!
Всё на том же форуме уважаемый grufos подсказал как решить эту проблему:

1. Добавляем расширение для работы с триграммами
   CREATE EXTENSION pg_trgm;
2. Создаем индекс на нужном поле
   CREATE INDEX addrobj_formalname_idx ON addrobj USING gist (formalname gist_trgm_ops);
3. Теперь ищем
   select * from addrobj where formalname ~ 'ростов';

Всё ура!, теперь поиск происходит за пол секунды

Позже был найден wiki по этому самому ФИАСу wiki.gis-lab.info/w/ФИАС
И скрипты для импорта в базу gist.github.com/wiz/4244207

Импорт DBF ФИАС в PostgreSQL
fix-fias.json
{
    "actstat": {
        "convert": {
            "actstatid": "int"
        }
    },
    "addrobj": {
        "null": [
            "actstat",
            "nextid",
            "previd",
            "terrifnsfl",
            "terrifnsul",
            "ifnsfl",
            "ifnsul",
            "normdoc",
            "parentguid",
            "okato",
            "oktmo",
            "enddate",
            "startdate",
            "postalcode"
        ],
        "convert": {
            "aoguid": "uuid",
            "aoid": "uuid",
            "parentguid": "uuid",
            "nextid": "uuid",
            "previd": "uuid",
            "normdoc": "uuid",

            "actstatus": "int",
            "aolevel": "int",
            "centstatus": "int",
            "currstatus": "int",
            "operstatus": "int",
            "livestatus": "int"
        }
    },
    "centerst": {
        "convert": {
            "centerstid": "int"
        }
    },
    "curentst": {
        "convert": {
            "curentstid": "int"
        }
    },
    "eststat": {
        "convert": {
            "eststatid": "int"
        }
    },
    "hststat": {
        "convert": {
            "hststatid": "int"
        }
    },
    "intvstat": {
        "convert": {
            "intvstatid": "int"
        }
    },
    "ndoctype": {
        "convert": {
            "ndtypeid": "int"
        }
    },
    "operstat": {
        "convert": {
            "operstatid": "int"
        }
    },
    "socrbase": {
        "convert": {
            "level": "int"
        }
    },
    "strstat": {
        "convert": {
            "strstatid": "int"
        }
    }
}

fix.py
#!/usr/bin/env python

convert_sql = """
BEGIN;
    alter table {table} rename column {column} to {column}_x;
    alter table {table} add column {column} {cast};
    update {table} set {column} = {column}_x;
    alter table {table} drop column {column}_x;
COMMIT;
"""

def convert(table, column, cast):
    return convert_sql.format(table=table, column=column, cast=cast)

def null(table, column):
    return "UPDATE {table} SET {column} = NULL WHERE {column} = '';".format(table=table, column=column)

if __name__=='__main__':
    import sys, json

    spec = json.load(open(sys.argv[1]))
    for table, task in spec.iteritems():
        for column in task.get('null', []):
            print null(table, column)
        for column, cast in sorted(task.get('convert', {}).iteritems()):
            print convert(table, column, cast)

import.sh
mkdir house normdoc
mv house*.dbf house/
mv normdoc*.dbf normdoc/

for file in *.dbf; do
    echo "Importing $file..."
    pgdbf $file | iconv -f cp866 -t utf-8 | psql fias
done


PS: Что бы нормально всё работало рекомендуют создавать базу именно так:
CREATE DATABASE fias
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'ru_RU.UTF-8'
       LC_CTYPE = 'ru_RU.UTF-8'
       CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE fias TO public;
GRANT ALL ON DATABASE fias TO postgres;