Artikel sebelumnya yang menguraikan alamat dan fungsi FIAS untuk bekerja dengannya di lingkungan PostgreSQL membangkitkan minat di antara sebagian kecil pembaca.
Oleh karena itu, masuk akal untuk menggambarkan fungsi serupa di PL / pgSQL untuk bekerja dengan daftar rumah FIAS dimuat ke dalam database yang menjalankan PostgreSQL.
Paruh pertama artikel memberikan komentar tentang implementasi fungsi. Dalam yang kedua, kode sumber fungsi, serta skrip untuk membuat tabel dengan catatan rumah FIAS, serta memuat data ke dalam tabel ini dari file dalam format CSV. Bagi pembaca yang hanya tertarik pada teks sumber, kami sarankan segera melanjutkan ke Lampiran.
Artikel ini terkait erat dengan materi dari serangkaian artikel "Alamat FIAS di Lingkungan PostgreSQL" (
awal ,
lanjutan 1 ,
lanjutan 2 ,
berakhir ).
Pohon keluarga di rumah
Mari kita mulai dengan sebuah contoh.
Memanggil fungsi f stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') akan menghasilkan daftar entri berikut.
Tabel 1. Hasil fungsi.
Pada pemeriksaan lebih dekat, Anda mungkin memperhatikan bahwa pengidentifikasi elemen ( HOUSEGUID ) “d. 1, bldg. 2, hal. 26 ", enam catatan diterima sebagai hasilnya:
- tiga catatan induk dengan elemen pembentuk alamat: tentang wilayah, kota dan jalan;
- tiga catatan dengan karakteristik nomor rumah: nomor rumah, nomor gedung dan nomor bangunan.
Fungsi ini memiliki parameter opsional lain - tanggal kedaluwarsa catatan ( EndDate ), yang dengannya Anda dapat melihat silsilah tidak hanya catatan saat ini tentang rumah, tetapi juga catatan yang sudah usang.
Teks lengkap dari fungsi diberikan dalam Lampiran di bagian Membuat fungsi fstf_Houses_AddressObjectTree .
Dari awal
Jika Anda tahu bagaimana tabel rumah FIAS diatur, maka bagian ini dapat dilewati.
Rumah FIAS ( RUMAH ) adalah daftar anak untuk daftar elemen penghasil alamat FIAS ( ADDROBJ ). Setiap entri daftar rumah merujuk ke elemen penghasil alamat FIAS dengan nilai bidang AOGUID . Untuk menentukan di jalan mana dan di mana lokasi rumah berada, Anda perlu menemukan catatan yang sesuai dengan ADDROBJ pengidentifikasi daftar yang sama dengan nilai AOGUID dari catatan HOUSES .
Terlepas dari kesederhanaan luar dari mekanisme interaksi antara daftar rumah dan daftar elemen pembentuk alamat dalam interaksinya, fitur-fitur menyulitkan implementasi fungsi di HOUSES .
Pertama, setiap catatan daftar rumah oleh pengidentifikasi AOGUID mengacu pada sekelompok elemen pembentuk alamat, yang salah satunya relevan.
Kedua, ada beberapa entri dalam daftar FIAS dengan serangkaian karakteristik nomor rumah yang sama: nomor rumah, nomor gedung, nomor bangunan.
Ketiga, catatan rumah tidak selalu diwarisi dari catatan jalan desa.
Tapi, hal pertama yang pertama.
Untuk pertimbangan lebih lanjut tentang penyimpanan informasi tentang rumah-rumah di FIAS, cukup membatasi diri hingga 4 tabel (file DBF):
- ADDROBJ - daftar elemen pembentuk alamat;
- RUMAH - daftar rumah;
- STRSTAT - direktori fitur struktural;
- ESTSTAT - direktori tanda kepemilikan.
ADDROBJ dibahas secara rinci dalam publikasi sebelumnya, “Alamat FIAS di PostgreSQL,” sehingga fitur-fiturnya akan dibahas di sini sebanyak yang diperlukan untuk menggambarkan karakteristik rumah.
Tabel 2. Sejarah rumah “Wilayah Krasnoyarsk, Distrik Taimyr Dolgan-Nenetsky, Dudinka, ul. Dudinskaya, 1
Seperti dapat dilihat dari tabel, berbeda dengan objek pembentuk alamat, catatan sejarah rumah tidak memiliki tanda-tanda relevansi khusus. Catatan dengan tanggal akhir tertua dari periode, yang lebih besar dari tanggal saat ini, relevan. Sejauh ini, catatan rumah saat ini ditandai dengan tanggal 06.06.2079. Semua catatan lain tentang rumah dianggap historis, dan tanggal mulai dan berakhir mencirikan periode relevansi setiap catatan.
Daftar rumah FIAS tidak berisi petunjuk ke catatan sebelumnya dan selanjutnya tentang rumah. Oleh karena itu, urutan catatan dari jauh ke dalam sejarah rumah ditentukan oleh tanggal akhir yang menurun dan di luarnya tanggal mulai periode, masing-masing EndDate dan StartDate .
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;
Pembaca yang penuh perhatian melihat Gambar. 1, saya mungkin bertanya pada diri sendiri pertanyaan: mengapa buku referensi tanda-tanda struktur dan kepemilikan disebutkan? FIAS menggunakan lebih dari 10 direktori semacam ini, jadi mengapa keduanya disorot?
Jawabannya akan mengejutkan banyak orang - dari sudut pandang "logika FIAS," alamat rumah tidak sepenuhnya diidentifikasi oleh alamat jalan, rumah, bangunan dan nomor bangunan. Istilah "logika FIAS" digunakan dalam jawaban seorang pegawai Layanan Pajak Federal untuk pertanyaan saya mengapa dalam daftar rumah di Wilayah Krasnoyarsk terdapat lebih dari 250 alamat rumah yang dipasangkan. Jawaban yang sama mengatakan bahwa keunikan catatan diberikan oleh nilai-nilai AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.
Dengan kata lain, untuk menemukan objek tidak cukup hanya mengetahui lokalitas, jalan, nomor rumah. Anda juga harus tahu:
- "Kepemilikan" adalah atau "kepemilikan rumah";
- status objek ini didefinisikan atau tidak didefinisikan;
- dll.
Ini adalah contoh sampel dari daftar umum rumah FIAS dengan alamat duplikat.
Fakta bahwa objek yang berbeda memiliki alamat yang sama tidaklah mengejutkan. Bangunan dan tanah di bawahnya; rumah, garasi, pemandian untuk satu pemilik. Mereka semua memiliki alamat yang sama. Tetapi FIAS adalah register alamat, mis. daftar alamat. Oleh karena itu, wajar untuk mengharapkan bahwa alamat akan unik di dalamnya, dan bukan bangunan, struktur, struktur.
Yaitu daftar rumah FIAS dari daftar alamat rumah mulai berkembang menuju daftar bangunan tanah. Dan pengguna FIAS perlu mempertimbangkan ini.
Setiap orang dapat memeriksa keberadaan rumah dengan alamat duplikat dengan menjalankan pernyataan SELECT yang serupa dengan yang berikut ini. Pada saat yang sama, fungsi fsfn_Houses_TreeActualName tidak dapat digunakan, karena ini hanya digunakan untuk mengurangi jumlah kolom dalam hasil. Tidak perlu menggunakan direktori fias_StructureStatus (analog dari STRSTAT) dan fias_EstateStatus (analog dengan ESTSTAT), karena efek yang ditandai juga dapat dilacak pada kode tanda-tanda struktur dan kepemilikan.
kode sumber operator SELECT fsfn_Houses_TreeActualName(h.AOGUID,h.HOUSEGUID),h.HOUSEGUID,str.StructureStatusName,est.EstateStatusName FROM fias_Houses h INNER JOIN (SELECT AOGUID,HOUSENUM,BUILDNUM,STRUCNUM,COUNT(*) FROM fias_Houses h WHERE EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') GROUP BY AOGUID,HOUSENUM,BUILDNUM,STRUCNUM HAVING COUNT(*)>1) hg ON h.AOGUID=hg.AOGUID AND h.HOUSENUM=hg.HOUSENUM AND COALESCE(h.BUILDNUM,'')=COALESCE(hg.BUILDNUM,'') AND COALESCE(h.STRUCNUM,'')=COALESCE(hg.STRUCNUM,'') LEFT OUTER JOIN fias_StructureStatus str ON h.STRSTATUS=str.StructureStatusID LEFT OUTER JOIN fias_EstateStatus est ON h.ESTSTATUS=est.EstateStatusID WHERE h.EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') ORDER BY h.AOGUID,h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,h.STRSTATUS,h.ESTSTATUS;
Dan akhirnya, fitur lain dari daftar beranda FIAS. Setiap catatan rumah dari daftar ini berisi tautan ke elemen pembentuk alamat, daftar yang merupakan hierarki elemen tersebut. Pada setiap tingkat hirarki adalah elemen pembentuk alamat milik tipe yang berbeda. Jadi elemen root adalah wilayah (Wilayah Krasnoyarsk dalam kasus kami), pada tingkat berikutnya adalah okrug otonom, wilayah atau kota subordinasi regional. Dan sebagainya. (Untuk perincian, lihat "Alamat FIAS di PostgreSQL").
Secara formal, catatan rumah memungkinkan Anda untuk merujuk ke elemen hierarki di tingkat mana pun. Untungnya, tidak ada rumah yang merujuk ke sebuah kabupaten atau wilayah di antara data Wilayah Krasnoyarsk. Namun demikian, tidak semua rumah merujuk ke jalan desa:
- 98% rumah FIAS terhubung dengan jalan-jalan di daerah berpenduduk;
- 1,2% rumah - dengan jalan-jalan di asosiasi berkebun;
- 0,3% rumah tinggal dengan pemukiman;
- 0,5% rumah dengan elemen yang bisa dialamatkan lainnya.
Fig. 2.Propagasi alamat rumah oleh pemilik (FIAS vs peta)
Masalah dijelaskan di sini yang mengarah pada interpretasi ambigu dari silsilah keluarga. (Igor Leonidovich Timoshchenkov, spesialis GIS, Aigeo LLC, Krasnoyarsk, menarik perhatian saya pada masalah ini.)
Di atas menunjukkan bagaimana beberapa catatan berisi alamat yang sama di rumah. Apa yang dapat dijelaskan oleh keinginan inspektorat pajak untuk tidak hanya menyimpan catatan rumah pribadi, tetapi juga bangunan di sekitarnya: garasi, gudang, dll. Tetapi ada contoh terbalik ketika beberapa bangunan (fias_House) sesuai dengan satu bangunan (rumah) dengan nomor berbeda untuk rumah ini.
Lihatlah gambar ini. Di sebelah kiri ada screenshot dengan peta desa di mana rumah-rumah untuk dua pemilik berada. Ini adalah rumah biasa satu lantai dengan dua pintu masuk. Satu keluarga tinggal di sebelah kanan, dan satu lagi di sebelah kiri. Mereka masih bisa dibayangkan sebagai rumah dari dua apartemen.
Sekarang lihat tabel di sebelah kanan. Di dalamnya, hampir setiap rumah dengan dua pemilik sesuai dengan 3 entri. Yaitu Tabel rumah FIAS menunjukkan alamat rumah individu ("d. 1") dan alamat bagian-bagian rumah ("d. 1/1", "d. 1/2") milik satu pemilik.
Bagaimana cara kerjanya
Fungsi fstf_Houses_AddressObjectTree memiliki dua versi: dengan empat atau dengan dua parameter. Dalam versi fungsi dengan dua parameter, pengidentifikasi rumah ( HouseGUID ) dan tanggal kedaluwarsa catatan (EndDate ) dikirimkan. Versi dengan empat parameter juga memerlukan pengidentifikasi untuk elemen penghasil alamat ( AOGUID ) dan status saat ini ( CurrStatus ).
kode sumber operator SELECT INTO v_AOGUID,v_CurrStatus h.AOGUID,CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) ELSE 0 END FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;
Fungsi dengan lebih sedikit parameter menghitung nilai parameter yang hilang dan memanggil fungsi dengan sejumlah besar parameter. Untuk melakukan ini, pengidentifikasi elemen pembentuk alamat hanya diambil dari bidang terkait tabel rumah (f ias_House ). Dan nilai status saat ini ( CurrStatus ) dihitung berdasarkan aturan berikut:
- jika tidak ada catatan riwayat elemen pembentuk alamat berisi 0 di bidang CurrStatus, maka variabel v_CurrStatus ditugaskan nilai bidang maksimum untuk elemen pembentuk alamat ini;
- jika tidak, variabel ini diberi nilai 0.
Fungsi dengan sejumlah besar parameter pertama-tama memanggil fungsi fstf_AddressObjects_AddressObjectTree , yang mengembalikan elemen pembentuk alamat induk untuk rumah. Anda dapat membaca lebih lanjut tentang fungsi fstf_AddressObjects_AddressObjectTree di bagian Silsilah elemen pembentuk alamat dari dokumen FIAS Addresses in PostgreSQL
.
Kemudian entri tentang elemen pembentuk alamat dilengkapi dengan entri tentang jumlah rumah, bangunan, struktur (lihat Tabel 1), yang dibuat untuk setiap bidang yang tidak kosong tentang jumlah rumah, bangunan, dan struktur.
Agar semua catatan keluaran memiliki struktur yang sama dan bukan tanpa berbagi foppinginess, nilai tingkat kode bidang ( AOLevel ), status saat ini ( CurrStatus ) dan status relevansi ( ActStatus ) secara artifisial dibuat di badan fungsi.
Kode untuk tingkat rumah (bangunan, struktur) selalu diatur ke 8, lihat buku referensi "Levels of Objectable Objects" dari Informasi FIAS pada dokumen Informasi Komposisi ).
Status relevansi diatur ke 1 jika tanggal kedaluwarsa catatan ( EndDate ) adalah 06.06.2079 , dan 0 sebaliknya.
Nilai bidang CurrStatus lebih rumit. Dengan menggunakan nilainya, dua tugas diselesaikan secara bersamaan: pengidentifikasi setiap versi catatan tentang elemen pembentuk alamat diatur dan tanda relevansi catatan ditugaskan. Oleh karena itu, catatan terakhir saat ini tentang elemen berisi nilai 0 di bidang ini, dan semua catatan sejarah diberi nomor sesuai urutan penampilan - "1" adalah catatan paling awal yang mengikutinya dalam waktu - "2", dll. Urutan pemberian nilai ke bidang CurrStatus dijelaskan secara lebih rinci dalam publikasi Alamat FIAS di PostgreSQL .
Judul spoiler SELECT h.AOGUID, h.HOUSEGUID, h.HOUSENUM, h.BUILDNUM, h.STRUCNUM, h.ENDDATE, CASE WHEN COALESCE(h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 0 ELSE RANK() OVER (PARTITION BY h.AOGUID, h.HOUSEGUID ORDER BY h.ENDDATE ASC) END AS HouseCurrStatus, CASE WHEN COALESCE (h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 1 ELSE 0 END AS HouseActStatus FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.AOGUID=a_AOGUID AND h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;
Alamat rumah lengkap
Gagasan utama dari fungsi fsfn_Houses_TreeActualName adalah mengembalikan nomor rumah yang terhubung dalam satu baris bersama dengan nama semua leluhurnya - elemen pembentuk alamat.
Misalnya, biarkan fungsi silsilah keluarga (fstf_Houses_AddressObjectTree) mengembalikan daftar nilai berikut.
Tabel 4. Hasil dari fungsi fstf_Houses_AddressObjectTree ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')
Maka fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') harus dikembalikan: " Mr. Krasnoyarsk, 34A St. Lazo, bld. 6, hlm. 17 ".
Fungsi fsfn_Houses_TreeActualName dapat disederhanakan sebagai fungsi agregat STRING_AGG atas hasil fungsi yang mengembalikan pohon keluarga di rumah.
Fungsi yang dimaksud memiliki parameter opsional lain - larik topeng ( a_MaskArray ), yang dengannya Anda dapat memasukkan dalam hasil tidak semua nama elemen, tetapi hanya yang dibutuhkan.
Tabel 5. Daftar fungsi mask.
versi teks dari tabel| Nilai | Catatan |
|---|
| {HS} | Topeng - nomor rumah |
| {BY} | Mask - nomor kasus |
| {BG} | Topeng - nomor bangunan |
| {ST} | Masker - jalan |
| {ZC} | Mask - Kode pos |
| {DT} | Topeng - daerah perkotaan |
| {LP} | Topeng - kota bawahan |
| {LM} | Topeng - pemukiman utama |
| {TP} | Topeng - wilayah subjek federasi |
| {TM} | Topeng - subjek federasi (wilayah) |
| {CY} | Topeng - Negara |
Lihat juga bagian "
Nama lengkap elemen pembentuk alamat" dari publikasi "Alamat FIAS di PostgreSQL ".
Teks fungsi diberikan di bagian Aplikasi “
Membuat Fungsi fsfn_Houses_TreeActualName ”.
FIAS Beranda Pencarian
Fungsi fstf_Houses_SearchByName dimaksudkan untuk mencari alamat rumah FIAS dengan nomor mereka dan nama elemen pembentuk alamat. Selain itu, pencarian dapat dilakukan tidak hanya dengan nama dan tipe elemen saat ini, tetapi juga dengan nama dan tipe satu atau dua leluhur terdekatnya.
Mari kita lihat beberapa contoh. Dan untuk memulainya, kita akan menemukan semua rumah dengan angka "220".
Tabel 6. Hasil dari fungsi fstf_Houses_SearchByName ('220')
Berbeda dengan fungsi mencari elemen pembentuk alamat ( fstf_AddressObjects_SearchByName ), hasil fungsi ini tidak mengandung efek "berenang" melalui tingkat elemen pembentuk alamat. Parameter pertama dari fungsi selalu berisi pola pencarian untuk nomor rumah, yang kedua - nomor bangunan, nomor bangunan ketiga.
Sekarang ubah permintaan. Kami menemukan semua rumah elemen pembentuk alamat, yang jumlahnya mengandung angka "1", dan kata "Krasnoyarsk" muncul di namanya.
Tabel 7. Hasil dari fungsi fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnoyarsk')
Tujuan dari parameter yang tersisa persis bertepatan dengan tujuan dari parameter fungsi pencarian dari elemen penghasil alamat (fstf_AddressObjects_SearchByName).
Teks fungsi diberikan di bagian Aplikasi " Membuat Fungsi fstf_Houses_SearchByName "
.
Bagaimana cara kerjanya
Implementasi fstf_Houses_SearchByName dalam banyak hal mirip dengan implementasi fungsi pencarian untuk elemen penghasil alamat (fstf_AddressObjects_SearchByName) . Perbedaan utama adalah bahwa pencarian dilakukan dalam dua tabel terkait, fias_House dan fias_AddressObjects .
Fungsi ini memiliki 9 argumen. Tiga yang pertama adalah nomor rumah ( a_HouseNum ), bangunan (a_BuildNum ) dan bangunan ( a_StrucNum ). 6 sisanya ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) sepenuhnya bertepatan dengan parameter fungsi.
Jika Anda hanya menetapkan nilai parameter "nomor rumah", fungsi akan mengembalikan semua alamat dalam nomor rumah yang dipenuhi oleh urutan yang ditentukan dengan simbol. Jika Anda meneruskan NULL atau string kosong ("") sebagai nomor rumah, maka alamat semua rumah yang elemen alamatnya ditentukan oleh serangkaian parameter lain akan dikembalikan.
Epilog
Bagian ini berisi rekomendasi tentang cara memuat daftar rumah FIAS ke tabel fias_House .
Memuat data ke dalam tabel rumah dilakukan dengan cara yang sama seperti memuat data ke dalam tabel elemen pembentuk alamat . Hanya file sumber akan HOUSE99.DBF , bukan ADDROB99.DBF . Di sini 99 adalah jumlah wilayah (Republik, oblast, wilayah). Misalnya, untuk Wilayah Krasnoyarsk, file sumbernya adalah file HOUSE24.DBF .
Pertama, arsip selanjutnya dengan pembaruan diunduh dari halaman Pembaruan FIAS. File HOUSE99.DBF diekstraksi darinya .
.
Kemudian file HOUSE99.DBF dikonversi ke format CSV dan sudah dikonversi, itu dimuat oleh pernyataan COPY ke tabel sementara fias_Houses_Temp .
Dan akhirnya, data sementara digunakan untuk memperbarui tabel utama, yaitu tidak ada di fias_House ditambahkan, dan yang sudah ada diganti.
Contoh skrip untuk memperbarui tabel rumah diberikan di bagian " Mengunduh Pembaruan Rumah FIAS ke fias_House Table ".
Aplikasi
Membuat Fungsi fstf_Houses_AddressObjectTree
Komentar pada kode sumber fungsi dapat ditemukan di sini .
kode fungsi BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP); CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree( a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36), a_CurrStatus INTEGER default 0, a_ENDDATE TIMESTAMP default '2079-06-06' ) RETURNS TABLE (rtf_GUID VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS $BODY$ DECLARE c_HouseAOLevel CONSTANT INTEGER:=8; c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; c_StatusActual CONSTANT INTEGER:=1; c_StatusNotActual CONSTANT INTEGER:=0; c_MAXENDDATE CONSTANT TIMESTAMP:=to_timestamp('2079-06-06 00:00:00', 'YYYY-MM-DD'); v_HouseActStatus INTEGER; v_HouseCurrStatus INTEGER; v_ENDDATE TIMESTAMP; v_HOUSEGUID VARCHAR(36); v_HOUSENUM VARCHAR(10); v_BUILDNUM VARCHAR(10); v_STRUCNUM VARCHAR(10); v_Return_Error Integer :=0;
Membuat Fungsi fsfn_Houses_TreeActualName
Komentar pada kode sumber fungsi dapat ditemukan di sini .
kode fungsi BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE; CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName( a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36), a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}' ) RETURNS VARCHAR(1000) AS $BODY$ DECLARE c_HouseMaskArray CONSTANT VARCHAR(2)[3]:='{HS,BY,BG}'; c_HouseNoMask CONSTANT VARCHAR(2)[1] :='{HS}'; c_BodyNoMask CONSTANT VARCHAR(2)[1] :='{BY}'; c_BuildingNoMask CONSTANT VARCHAR(2)[1] :='{BG}'; c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; v_ENDDATE TIMESTAMP; v_HOUSENUM VARCHAR(10); v_BUILDNUM VARCHAR(10); v_STRUCNUM VARCHAR(10); v_TreeAddressObjectName VARCHAR(1000); v_Return_Error Integer :=0;
Membuat Fungsi fstf_Houses_SearchByName
Komentar pada kode sumber fungsi dapat ditemukan di sini .kode fungsi BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20), a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20)); CREATE OR REPLACE FUNCTION fstf_Houses_SearchByName( a_HouseNum VARCHAR(20), a_BuildNum VARCHAR(10) default NULL, a_StrucNum VARCHAR(10) default NULL, a_FormalName VARCHAR(150) default NULL, a_ShortName VARCHAR(20) default NULL, a_ParentFormalName VARCHAR(150) default NULL, a_ParentShortName VARCHAR(20) default NULL, a_GrandParentFormalName VARCHAR(150) default NULL, a_GrandParentShortName VARCHAR(20) default NULL ) RETURNS TABLE (rtf_AOGUID VARCHAR(36),rtf_HOUSEGUID VARCHAR(36),rtf_AOLevel INTEGER,rtf_HousesFullName VARCHAR(1000),rtf_HouseNum VARCHAR(20),rtf_BuildNum VARCHAR(10),rtf_StrucNum VARCHAR(10),rtf_EndDate TIMESTAMP,rtf_ShortName VARCHAR(20),rtf_FormalName VARCHAR(150), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_ParentShortName VARCHAR(20),rtf_ParentFormalName VARCHAR(150),rtf_GrandParentShortName VARCHAR(20),rtf_GrandParentFormalName VARCHAR(150)) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(2)='%'; c_BlankChar CONSTANT VARCHAR(2)=' '; v_HouseNumTemplate VARCHAR(150); v_BuildNumTemplate VARCHAR(150); v_StrucNumTemplate VARCHAR(150); v_FormalNameTemplate VARCHAR(150); v_ShortNameTemplate VARCHAR(20); v_ParentFormalNameTemplate VARCHAR(150); v_ParentShortNameTemplate VARCHAR(20); v_GrandParentFormalNameTemplate VARCHAR(150); v_GrandParentShortNameTemplate VARCHAR(20);
Membuat fias_House tabel rumah FIAS
kode skrip BEGIN TRANSACTION; DROP TABLE IF EXISTS fias_Houses; DROP TABLE IF EXISTS fias_EstateStatus; DROP TABLE IF EXISTS fias_StructureStatus; CREATE TABLE IF NOT EXISTS fias_Houses( HOUSEID VARCHAR(36) NOT NULL, AOGUID VARCHAR(36) NULL, HOUSEGUID VARCHAR(36) NULL, HOUSENUM VARCHAR(10) NULL, BUILDNUM VARCHAR(10) NULL, STRUCNUM VARCHAR(10) NULL, POSTALCODE VARCHAR(6) NULL, OKATO VARCHAR(11) NULL, OKTMO VARCHAR(11) NULL, IFNSFL VARCHAR(4) NULL, TERRIFNSFL VARCHAR(4) NULL, IFNSUL VARCHAR(4) NULL, TERRIFNSUL VARCHAR(4) NULL, ESTSTATUS INTEGER NULL, STATSTATUS INTEGER NULL, STRSTATUS INTEGER NULL, STARTDATE TIMESTAMP NULL, ENDDATE TIMESTAMP NULL, UPDATEDATE TIMESTAMP NULL, NORMDOC VARCHAR(36) NULL, COUNTER INTEGER NULL, CADNUM VARCHAR(50) NULL, DIVTYPE INTEGER NULL, CONSTRAINT XPKfias_Houses PRIMARY KEY ( HOUSEID )) WITH (OIDS=False); CREATE INDEX XIE1fias_Houses ON fias_Houses(AOGUID); CREATE INDEX XIE2fias_Houses ON fias_Houses(HOUSEGUID); CREATE INDEX XIE3fias_Houses ON fias_Houses(AOGUID,HOUSEGUID); CREATE INDEX XIE4fias_Houses ON fias_Houses(HOUSENUM,BUILDNUM,STRUCNUM); CREATE INDEX XIE5fias_Houses ON fias_Houses(HOUSENUM); CREATE INDEX XIE6fias_Houses ON fias_Houses(BUILDNUM); CREATE INDEX XIE7fias_Houses ON fias_Houses(STRUCNUM); COMMENT ON TABLE fias_Houses IS 'HOUSE , .'; COMMENT ON COLUMN fias_Houses.HOUSEID IS ' '; COMMENT ON COLUMN fias_Houses.AOGUID IS ' (, , ..)'; COMMENT ON COLUMN fias_Houses.HOUSEGUID IS ' '; COMMENT ON COLUMN fias_Houses.HOUSENUM IS ' '; COMMENT ON COLUMN fias_Houses.BUILDNUM IS ' '; COMMENT ON COLUMN fias_Houses.STRUCNUM IS ' '; COMMENT ON COLUMN fias_Houses.POSTALCODE IS ' '; COMMENT ON COLUMN fias_Houses.IFNSFL IS ' '; COMMENT ON COLUMN fias_Houses.TERRIFNSFL IS ' '; COMMENT ON COLUMN fias_Houses.IFNSUL IS ' '; COMMENT ON COLUMN fias_Houses.TERRIFNSUL IS ' '; COMMENT ON COLUMN fias_Houses.OKATO IS ''; COMMENT ON COLUMN fias_Houses.OKTMO IS ''; COMMENT ON COLUMN fias_Houses.ESTSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STRSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STATSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STARTDATE IS ' '; COMMENT ON COLUMN fias_Houses.ENDDATE IS ' '; COMMENT ON COLUMN fias_Houses.UPDATEDATE IS ' () '; COMMENT ON COLUMN fias_Houses.NORMDOC IS ' '; COMMENT ON COLUMN fias_Houses.COUNTER IS ' 4'; COMMENT ON COLUMN fias_Houses.CADNUM IS ' '; COMMENT ON COLUMN fias_Houses.DIVTYPE IS ' : 0 – 1 – 2 – '; CREATE TABLE IF NOT EXISTS fias_EstateStatus( EstateStatusID INTEGER NOT NULL, EstateStatusName varchar(60) NULL, EstateStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_EstateStatus PRIMARY KEY (EstateStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_EstateStatus IS ' () '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusID IS ' . :0 – ,1 – ,2 – ,3 – '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusName IS ''; COMMENT ON COLUMN fias_EstateStatus.EstateStatusShortName IS ' '; CREATE TABLE IF NOT EXISTS fias_StructureStatus( StructureStatusID INTEGER NOT NULL, StructureStatusName varchar(60) NULL, StructureStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_StructureStatus PRIMARY KEY (StructureStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_StructureStatus IS ' () '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusID IS ' . :0 – ,1 – ,2 – ,3 – '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusName IS ''; COMMENT ON COLUMN fias_StructureStatus.StructureStatusShortName IS ' ';
Unduh FIAS Home Updates ke fias_House Table
kode sumber skrip BEGIN TRANSACTION; do $$ BEGIN DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; CREATE TABLE fias_Houses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_Houses_temp; CREATE TABLE fias_DeletedHouses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_DeletedHouses_temp; CREATE TABLE fias_EstateStatus_temp AS SELECT * FROM fias_EstateStatus LIMIT 1; DELETE FROM fias_EstateStatus_temp; CREATE TABLE fias_StructureStatus_temp AS SELECT * FROM fias_StructureStatus LIMIT 1; DELETE FROM fias_StructureStatus_temp; COPY fias_EstateStatus_temp(EstateStatusID,EstateStatusNAME,EstateStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\ESTSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_EstateStatus s SET EstateStatusNAME=t.EstateStatusNAME, EstateStatusShortName=t.EstateStatusShortName FROM fias_EstateStatus ds INNER JOIN fias_EstateStatus_temp t ON ds.EstateStatusID=t.EstateStatusID WHERE ds.EstateStatusID=s.EstateStatusID; INSERT INTO fias_EstateStatus(EstateStatusID,EstateStatusNAME,EstateStatusShortName) SELECT EstateStatusID,EstateStatusNAME,EstateStatusShortName FROM fias_EstateStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_EstateStatus os WHERE t.EstateStatusID=os.EstateStatusID); COPY fias_StructureStatus_temp(StructureStatusID,StructureStatusNAME,StructureStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\STRSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_StructureStatus s SET StructureStatusNAME=t.StructureStatusNAME, StructureStatusShortName=t.StructureStatusShortName FROM fias_StructureStatus ds INNER JOIN fias_StructureStatus_temp t ON ds.StructureStatusID=t.StructureStatusID WHERE ds.StructureStatusID=s.StructureStatusID; INSERT INTO fias_StructureStatus(StructureStatusID,StructureStatusNAME,StructureStatusShortName) SELECT StructureStatusID,StructureStatusNAME,StructureStatusShortName FROM fias_StructureStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_StructureStatus os WHERE t.StructureStatusID=os.StructureStatusID); COPY fias_Houses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) FROM 'W:\Projects\Enisey GIS\DB\SourceData\HOUSE24_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); UPDATE fias_Houses h SET AOGUID=t.AOGUID, BUILDNUM=t.BUILDNUM, ENDDATE=t.ENDDATE, ESTSTATUS=t.ESTSTATUS, HOUSEGUID=t.HOUSEGUID, HOUSENUM=t.HOUSENUM, STATSTATUS=t.STATSTATUS, IFNSFL=t.IFNSFL, IFNSUL=t.IFNSUL, OKATO=t.OKATO, OKTMO=t.OKTMO, POSTALCODE=t.POSTALCODE, STARTDATE=t.STARTDATE, STRUCNUM=t.STRUCNUM, STRSTATUS=t.STRSTATUS, TERRIFNSFL=t.TERRIFNSFL, TERRIFNSUL=t.TERRIFNSUL, UPDATEDATE=t.UPDATEDATE, NORMDOC=t.NORMDOC, COUNTER=t.COUNTER, CADNUM=t.CADNUM, DIVTYPE=t.DIVTYPE FROM fias_Houses dh INNER JOIN fias_Houses_Temp t ON t.HOUSEID=dh.HOUSEID WHERE h.HOUSEID=dh.HOUSEID; DELETE FROM fias_Houses h WHERE EXISTS(SELECT 1 FROM fias_DeletedHouses_temp delh WHERE delh.HOUSEID=h.HOUSEID); INSERT INTO fias_Houses(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) SELECT AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE FROM fias_Houses_Temp t WHERE NOT EXISTS(SELECT * FROM fias_Houses h WHERE t.HOUSEID=h.HOUSEID); DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; END; $$LANGUAGE plpgsql;