Диаграммы классов UML из PostgreSQL. Окончание11.09.2024 11:30
ALTER TABLE IF EXISTS Street DROP CONSTRAINT IF EXISTS fk_Street_StreetType;
ALTER TABLE IF EXISTS StreetSynonym DROP CONSTRAINT IF EXISTS fk_StreetSynonym_Street;
ALTER TABLE IF EXISTS StreetTypeSynonym DROP CONSTRAINT IF EXISTS fk_StreetTypeSynonym_StreetType;
DROP TABLE IF EXISTS StreetSynonym;
DROP TABLE IF EXISTS Street;
DROP TABLE IF EXISTS StreetTypeSynonym;
DROP TABLE IF EXISTS StreetType;
DROP DOMAIN IF EXISTS WCRCCode;
DROP DOMAIN IF EXISTS LocalityID;
DROP DOMAIN IF EXISTS StreetID;
DROP DOMAIN IF EXISTS StreetTypeAcrm;
DROP DOMAIN IF EXISTS public.SynonymID;
CREATE DOMAIN public.WCRCCode AS SMALLINT NOT NULL;
COMMENT ON DOMAIN public.WCRCCode IS 'Код ОКСМ (общероссийский классификатор стран мира).';
CREATE DOMAIN public.LocalityID AS SMALLINT NOT NULL;
COMMENT ON DOMAIN public.LocalityID IS 'ИД населенного пункта';
CREATE DOMAIN public.StreetID AS SMALLINT NOT NULL;
COMMENT ON DOMAIN public.StreetID IS 'ИД улицы населенного пункта';
CREATE DOMAIN public.StreetTypeAcrm AS CHAR(10) NOT NULL;
COMMENT ON DOMAIN public.StreetTypeAcrm IS 'Краткие названия типов улиц населенных пунктов';
CREATE DOMAIN public.SynonymID AS SMALLINT NULL;
COMMENT ON DOMAIN public.SynonymID IS 'ИД синонима названия (териториии, населенного пункта, улицы и т.д.).';
CREATE TABLE IF NOT EXISTS StreetType(
StreetTypeAcrm StreetTypeAcrm NOT NULL,
StreetTypeName varchar(30) NULL,
CONSTRAINT XPKStreetType PRIMARY KEY(StreetTypeAcrm))WITH (OIDS=false);
COMMENT ON TABLE StreetType IS 'Справочник типов улиц';
COMMENT ON COLUMN StreetType.StreetTypeAcrm IS 'Акроним (буквенный код) типа улицы';
COMMENT ON COLUMN StreetType.StreetTypeName IS 'Наименование типа улицы';
DELETE FROM StreetType;
insert into StreetType values ('УЛ ', 'Улица');
insert into StreetType values ('ПР-КТ', 'Проспект');
insert into StreetType values ('ПЕР ', 'Переулок');
insert into StreetType values ('ПЛ ', 'Площадь');
insert into StreetType values ('ПРОЕЗД', 'Проезд');
insert into StreetType values ('ПЕРЕЕЗД', 'Переезд');
insert into StreetType values ('ПЛОЩАДКА', 'Площадка');
insert into StreetType values ('ПРОСЕЛОК', 'Проселок');
insert into StreetType values ('ПРОСЕК', 'Просек');
insert into StreetType values ('ПРОСЕКА', 'Просека');
insert into StreetType values ('ПРОУЛ', 'Проулок');
insert into StreetType values ('УРЧЩ', 'Урочище');
insert into StreetType values ('ЗАС ', 'Застава');
insert into StreetType values ('ЗЗД ', 'Заезд');
insert into StreetType values ('КВ-Л', 'Квартал');
insert into StreetType values ('НАБ ', 'Набережная');
insert into StreetType values ('МГСТР', 'Магистраль');
insert into StreetType values ('МКР ', 'Микрорайон');
insert into StreetType values ('ТРАКТ', 'Тракт');
insert into StreetType values ('САД ', 'Сад');
insert into StreetType values ('СТ ', 'Станция');
insert into StreetType values ('ПСТН', 'Полустанок');
insert into StreetType values ('РЗД ', 'Разъезд');
insert into StreetType values ('РЯД ', 'Ряд(ы)');
insert into StreetType values ('СЛ ', 'Слобода');
insert into StreetType values ('ТЕР ', 'Территория');
insert into StreetType values ('Ш ', 'Шоссе');
insert into StreetType values ('Б-Р', 'Бульвар');
insert into StreetType values ('НИЗВ', 'Неизвестно');
insert into StreetType values ('СКВР', 'Сквер');
insert into StreetType values ('С-К ', 'Спуск');
insert into StreetType values ('СЗД ', 'Съезд');
insert into StreetType values ('ТУП', 'Тупик');
insert into StreetType values ('НП ', 'Населенный пункт');
insert into StreetType values ('ГОРОДОК', 'Городок');
insert into StreetType values ('КМ', 'Километр');
insert into StreetType values ('ААЛ', 'Аал');
insert into StreetType values ('АЛЛЕЯ', 'Аллея');
insert into StreetType values ('АРБАН', 'Арбан');
insert into StreetType values ('ВЫСЕЛ', 'Выселки(ок)');
insert into StreetType values ('ВЪЕЗД', 'Въезд');
insert into StreetType values ('ДОР', 'Дорога');
insert into StreetType values ('КОЛЬЦО', 'Кольцо');
insert into StreetType values ('КОСА', 'Коса');
insert into StreetType values ('ЛИНИЯ', 'Линия');
insert into StreetType values ('ОСТ-В', 'Остров');
CREATE TABLE IF NOT EXISTS StreetTypeSynonym(
StreetTypeAcrm StreetTypeAcrm NOT NULL,
SynonymID SynonymID NOT NULL,
StreetTypeSynonymAcrm VARCHAR(15) NULL,
CONSTRAINT XPKStreetTypeSynonym PRIMARY KEY(StreetTypeAcrm,SynonymID))WITH (OIDS=false);
COMMENT ON TABLE StreetTypeSynonym IS 'Справочник синонимов типов улиц';
COMMENT ON COLUMN StreetTypeSynonym.StreetTypeAcrm IS 'Акроним (буквенный код) типа улицы';
COMMENT ON COLUMN StreetTypeSynonym.SynonymID IS 'ИД (идентификатор) синонима типа улицы';
COMMENT ON COLUMN StreetTypeSynonym.StreetTypeSynonymAcrm IS 'Акроним (буквенный код) синонима типа улицы';
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('АЛЛЕЯ',1,'АЛЛЕЯ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Б-Р',1,'Б-Р.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Б-Р',2,'БУЛ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Б-Р',3,'БУЛ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Б-Р',4,'БУЛЬВАР');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ДОР',1,'ДОР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ДОР',2,'АВТОДОР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ДОР',3,'АВТОДОР');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('КВ-Л',1,'КВ-Л.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('КВ-Л',2,'КВАРТАЛ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('КВ-Л',3,'КВ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('КВ-Л',4,'КВ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',1,'МИКР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',2,'МИКР');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',3,'МИКРОРАЙОН');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',4,'МКР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',5,'МКРН');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',6,'МКРН.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',7,'М-Н');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('МКР',8,'М-ОН');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('НАБ',1,'НАБ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('НАБ',2,'НАБЕРЕЖНАЯ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ОСТ-В',1,'О-В');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ОСТ-В',2,'ОСТРОВ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ОСТ-В',3,'О');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ОСТ-В',4,'О.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПЕР',1,'ПЕР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПЕР',2,'ПЕРЕУЛОК');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПЛ',1,'ПЛ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПЛ',2,'ПЛОЩАДЬ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПР-КТ',1,'ПР-КТ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПР-КТ',2,'ПРОСП.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПР-КТ',3,'ПРОСП');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПР-КТ',4,'ПР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПР-КТ',5,'ПР');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('РЗД',1,'РЗД.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('СКВР',1,'СКВР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('СКВР',2,'СКВЕР');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('СКВР',3,'С-Р');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТУП',1,'ТУП.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТУП',2,'ТУПИК');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',1,'ТЕР.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',2,'ТЕРРИТОРИЯ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',3,'ПРОМЗОНА');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',4,'П/Р');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',5,'ПРОМРАЙОН');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',6,'ЗОНА');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ТЕР',7,'П\Р');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('УЛ',1,'УЛ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('УЛ',2,'УЛИЦА');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Ш',1,'Ш.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('Ш',2,'ШОССЕ');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПРОУЛ',1,'ПРОУЛ.');
INSERT INTO StreetTypeSynonym(StreetTypeAcrm,SynonymID,StreetTypeSynonymAcrm) VALUES('ПРОУЛ',2,'ПРОУЛОК');
CREATE TABLE IF NOT EXISTS Street(
WCRCCode WCRCCode NOT NULL,
LocalityID LocalityID NOT NULL,
StreetID StreetID NOT NULL,
StreetTypeAcrm StreetTypeAcrm NULL,
StreetName varchar(150) NOT NULL,
StreetTSVector TSVector NULL,
StreetTSLiteVector TSVector NULL,
CONSTRAINT XPKStreet PRIMARY KEY(WCRCCode,LocalityID,StreetID)) WITH (OIDS=false);
COMMENT ON TABLE Street IS 'Список улиц в населенных пунктах';
COMMENT ON COLUMN Street.WCRCCode IS 'Код страны';
COMMENT ON COLUMN Street.LocalityID IS 'ИД населенного пункта';
COMMENT ON COLUMN Street.StreetID IS 'ИД улицы населенного пункта';
COMMENT ON COLUMN Street.StreetTypeAcrm IS 'Акроним типа улицы';
COMMENT ON COLUMN Street.StreetName IS 'Наименование улицы населенного пункта';
COMMENT ON COLUMN Street.StreetTSVector IS 'Расширенные характеристики улицы в населенном пункте, преобразованные в вектор полнотекстового поиска';
COMMENT ON COLUMN Street.StreetTSLiteVector IS 'Характеристики улицы в населенном пункте, преобразованные в вектор полнотекстового поиска';
CREATE INDEX XIE1Street ON Street (WCRCCode,LocalityID,StreetTypeAcrm,StreetName);
COMMENT ON INDEX XIE1Street IS 'Индекс по типу и названию улицы населенного пункта';
CREATE INDEX XIE2Street ON Street (WCRCCode,LocalityID,StreetName);
COMMENT ON INDEX XIE2Street IS 'Индекс по названию улицы населенного пункта';
CREATE INDEX XIE3Street ON Street (StreetName);
COMMENT ON INDEX XIE3Street IS 'Индекс по названиям улиц во всех населенных пунктов';
CREATE INDEX XTS1Street ON Street USING gin(StreetTSVector);
COMMENT ON INDEX XTS1Street IS 'Полнотекстовый индекс по названиях улицы, населенного пункта, териитории и их синонимам';
CREATE INDEX XTS2Street ON Street USING gin(StreetTSLiteVector);
COMMENT ON INDEX XTS1Street IS 'Полнотекстовый индекс только по названию улицы в населенном пункте и его синонимам';
INSERT INTO Street(WCRCCode,LocalityID,StreetID,StreetTypeAcrm,StreetName)
VALUES( 643, 11, 401, 'УЛ', 'им. газеты "Пионерская правда"');
INSERT INTO Street(WCRCCode,LocalityID,StreetID,StreetTypeAcrm,StreetName) VALUES( 643,11,1518,'ПР-КТ','им.газеты "Красноярский рабочий"');
UPDATE Street SET StreetTypeAcrm='Б-Р '
WHERE StreetTypeAcrm='БУЛВ';
UPDATE Street SET StreetTypeAcrm='ПРОЕЗД'
WHERE StreetTypeAcrm='ПР-Д';
UPDATE Street SET StreetTypeAcrm='ПР-КТ'
WHERE StreetTypeAcrm='ПР-Т';
UPDATE Street SET StreetTypeAcrm='ТРАКТ'
WHERE StreetTypeAcrm='ТРКТ';
UPDATE Street SET StreetTypeAcrm='ТУП'
WHERE StreetTypeAcrm='ТУПК';
CREATE TABLE IF NOT EXISTS StreetSynonym(
WCRCCode WCRCCode NOT NULL,
LocalityID LocalityID NOT NULL,
StreetID StreetID NOT NULL,
SynonymID SynonymID NOT NULL,
StreetTypeAcrm StreetTypeAcrm NULL,
StreetSynonymName varchar(200) NULL,
CONSTRAINT XPKStreetSynonym PRIMARY KEY(WCRCCode,LocalityID,StreetID,SynonymID))WITH (OIDS=false);
COMMENT ON TABLE StreetSynonym IS 'Список синонимов названий улиц в населенных пунктах';
COMMENT ON COLUMN StreetSynonym.WCRCCode IS 'Код страны';
COMMENT ON COLUMN StreetSynonym.LocalityID IS 'ИД населенного пункта';
COMMENT ON COLUMN StreetSynonym.StreetID IS 'ИД улицы населенного пункта';
COMMENT ON COLUMN StreetSynonym.SynonymID IS 'ИД синонима улицы населенного пункта';
COMMENT ON COLUMN StreetSynonym.StreetTypeAcrm IS 'Акроним типа синонима улицы';
COMMENT ON COLUMN StreetSynonym.StreetSynonymName IS 'Наименование синонима улицы населенного пункта';
CREATE INDEX XIE1StreetSynonym ON StreetSynonym (WCRCCode,LocalityID,StreetID,StreetTypeAcrm,StreetSynonymName);
COMMENT ON INDEX XIE1StreetSynonym IS 'Индекс по типу и названию синонима улицы населенного пункта';
CREATE INDEX XIE2StreetSynonym ON StreetSynonym (WCRCCode,LocalityID,StreetID,StreetSynonymName);
COMMENT ON INDEX XIE2StreetSynonym IS 'Индекс по названию синонима улицы населенного пункта';
CREATE INDEX XIE3StreetSynonym ON StreetSynonym (StreetSynonymName);
COMMENT ON INDEX XIE3StreetSynonym IS 'Индекс по названию синонимов всех улиц во всех населенных пунктах';
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 3, 'УЛ', 'им газеты Пионерская Правда');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 4, 'УЛ', 'им. газеты Пионерская Правда');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 5, 'УЛ', 'им газеты "Пионерская Правда"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 6, 'УЛ', 'Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 7, 'УЛ', 'газеты "Пионерская правда"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 8, 'УЛ', 'газеты Пионерская Правда');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 9, 'УЛ', 'газеты Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 10, 'УЛ', 'газеты "Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 11, 'УЛ', '"Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 12, 'УЛ', 'им.газеты "Пионерская правда"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 13, 'УЛ', 'им.газеты Пионерская Правда');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 14, 'УЛ', 'им.газеты Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 15, 'УЛ', 'им.газеты "Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 16, 'УЛ', 'им. газеты Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 17, 'УЛ', 'им. газеты "Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 18, 'УЛ', 'им газеты Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 19, 'УЛ', 'им газеты "Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 20, 'УЛ', 'имени газеты "Пионерская правда"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 21, 'УЛ', 'имени газеты Пионерская Правда');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 22, 'УЛ', 'имени газеты Пионерской Правды');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 401, 23, 'УЛ', 'имени газеты "Пионерской Правды"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 2, 'ПР-КТ', 'имени газеты Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 3, 'ПР-КТ', 'газеты Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 4, 'ПР-КТ', 'им. газеты "Красноярский рабочий"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 5, 'ПР-КТ', 'имени газеты "Красноярский Рабочий"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 6, 'ПР-КТ', 'им. газ. "Красноярский рабочий"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 7, 'ПР-КТ', 'им.газ."Красноярский рабочий"');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 8, 'ПР-КТ', 'им.газ.Красноярский рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 9, 'ПР-КТ', 'им. газ. Красноярский рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 10, 'УЛ', 'Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 11, 'ПР-КТ', 'Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 12, 'ПР-КТ', 'им. газеты Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 13, 'ПР-КТ', 'им.газеты Красноярский рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 14, 'ПР-КТ', 'Красраб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 15, 'УЛ', 'Красраб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 16, 'ПР-КТ', 'Крас.Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 17, 'УЛ', 'Крас.Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 18, 'ПР-КТ', 'Крас.Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 19, 'УЛ', 'Крас.Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 20, 'ПР-КТ', 'Крас. Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 21, 'УЛ', 'Крас. Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 22, 'ПР-КТ', 'Крас. Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 23, 'УЛ', 'Крас. Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 24, 'ПР-КТ', 'им. газеты «Красноярский рабочий»');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 25, 'ПР-КТ', 'им. газ.«Красноярский рабочий»');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 26, 'ПР-КТ', 'им. газ. «Красноярский рабочий»');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 27, 'ПР-КТ', 'Крас Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 28, 'УЛ', 'Крас Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 29, 'ПР-КТ', 'КРАСН.РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 30, 'ПР-КТ', 'КРАСН. РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 31, 'УЛ', 'КРАСН.РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 32, 'УЛ', 'КРАСН. РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 33, 'ПР-КТ', 'КРАСН РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 34, 'УЛ', 'КРАСН РАБОЧИЙ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 35, 'ПР-КТ', 'КРАСН.РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 36, 'ПР-КТ', 'КРАСН. РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 37, 'УЛ', 'КРАСН.РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 38, 'УЛ', 'КРАСН. РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 39, 'ПР-КТ', 'КРАСН РАБ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 40, 'УЛ', 'КРАСН РАБ');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 41, 'ПР-КТ', 'КРАСН РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 42, 'УЛ', 'КРАСН РАБ.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 43, 'ПР-КТ', 'им газеты Красноярский Рабочий');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 44, 'ПР-КТ', 'Кр.Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 45, 'ПР-КТ', 'Кр.Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 46, 'ПР-КТ', 'Кр. Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 47, 'ПР-КТ', 'КрРаб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 48, 'ПР-КТ', 'Краб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 49, 'УЛ', 'Кр.Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 50, 'УЛ', 'Кр.Раб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 51, 'УЛ', 'Кр. Раб.');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 52, 'УЛ', 'КрРаб');
INSERT INTO StreetSynonym(WCRCCode,LocalityID,StreetID,SynonymID,StreetTypeAcrm,StreetSynonymName) VALUES( 643, 11, 1518, 53, 'УЛ', 'Краб');
UPDATE StreetSynonym SET StreetTypeAcrm='Б-Р '
WHERE StreetTypeAcrm='БУЛВ';
UPDATE StreetSynonym SET StreetTypeAcrm='ПРОЕЗД'
WHERE StreetTypeAcrm='ПР-Д';
UPDATE StreetSynonym SET StreetTypeAcrm='ПР-КТ'
WHERE StreetTypeAcrm='ПР-Т';
UPDATE StreetSynonym SET StreetTypeAcrm='ТРАКТ'
WHERE StreetTypeAcrm='ТРКТ';
UPDATE StreetSynonym SET StreetTypeAcrm='ТУП'
WHERE StreetTypeAcrm='ТУПК';
UPDATE Street s SET StreetTSVector=setweight(to_tsvector(StreetTypeAcrm),'A')||setweight(to_tsvector(StreetName),'A')||setweight(to_tsvector((SELECT StreetTypeName FROM StreetType st WHERE UPPER(TRIM(st.StreetTypeAcrm))=UPPER(TRIM(s.StreetTypeAcrm)))),'A')||
setweight(cnfn_Street_AllSynonymTsVector(WCRCCode,LocalityID,StreetID),'D'),
StreetTSLiteVector=setweight(to_tsvector(StreetTypeAcrm),'A')||setweight(to_tsvector(StreetName),'A')||setweight(to_tsvector((SELECT StreetTypeName FROM StreetType st WHERE UPPER(TRIM(st.StreetTypeAcrm))=UPPER(TRIM(s.StreetTypeAcrm)))),'A');
ALTER TABLE Street ADD CONSTRAINT fk_Street_StreetType FOREIGN KEY(StreetTypeAcrm) REFERENCES StreetType (StreetTypeAcrm) MATCH FULL;
ALTER TABLE Street ADD CONSTRAINT ck_Street_StreetTypeAcrm CHECK(StreetTypeAcrm !~* E'[a-z]');
ALTER TABLE StreetSynonym ADD CONSTRAINT fk_StreetSynonym_Street FOREIGN KEY(WCRCCode, LocalityID, StreetID) REFERENCES Street (WCRCCode, LocalityID, StreetID) MATCH FULL;
ALTER TABLE StreetSynonym ADD CONSTRAINT fk_StreetSynonym_StreetType FOREIGN KEY(StreetTypeAcrm) REFERENCES StreetType (StreetTypeAcrm) MATCH FULL;
ALTER TABLE StreetTypeSynonym ADD CONSTRAINT fk_StreetTypeSynonym_StreetType FOREIGN KEY(StreetTypeAcrm) REFERENCES StreetType(StreetTypeAcrm) MATCH FULL;
© Habrahabr.ru
