Technique ต่างๆเกี่ยวกับฐานข้อมูลและแผนที่

สำหรับฐานข้อมูล PostgreSQL เมื่อติดตั้งแล้วระบบจะกำหนดค่า Configuration ตามมาตรฐานซึ่งจะกำหนดไม่เพียงพอต่อการใช้งาน เราจำเป็นต้องกำหนดให้ถูกต้องตามสภาพแวดล้อมของเครื่อง โดยหลักๆ แล้วจะกำหนดที่ shared_buffers ซึ่งจะกำหนดประมาณ 25% ของ ram ที่เหลืออยู่ โดยค่าต่างๆ ที่กำหนด เช่น # DB Version: 9.6 # OS Type: linux # DB Type: web # Total Memory (RAM): 32 GB # CPUs num: 2 # Data Storage: hdd max_connections = 200 shared_buffers = 8GB effective_cache_size = 24GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 4 effective_io_concurrency = 2 work_mem = 41943kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 2 max_parallel_workers_per_gather = 1 โดยรายละเอียดนี้จะแก้ไขในไฟล์ postgresql.conf ซึ่งในส่วนของวินโดว์ส่วนของ shared_buffer จะมีค่าสูงสุดแค่ 512 MB เท่านั้น ในตัวอย่างข้างต้นเป็น CentOS จะสามารถกำหนดได้ตามจำนวนเปอร์เซ็นต์ของ ram ซึ่งถ้า...

ให้สร้างไฟล์โดยใช้คำสั่ง vi หรือสร้างไฟล์จากเครื่องวินโดว์โดยเนื้อหาคำสั่งในการกำหนดการทำงานให้กำหนดดังนี้ rootbk=ตำแหน่งสำรองข้อมูล dbname=ชื่อฐานข้อมูล  folderbk=`date +%F` filename=$dbname-`date +%F-%H%M%S`.backup [ ! -d "$rootbk$folderbk" ] && mkdir -p "$rootbk$folderbk" export PGPASSWORD=รหัสผ่านของฐานข้อมูล pg_dump -U postgres -h ไอพีเครื่องฐานข้อมูล -Fc -Z 9 -b -v $dbname -f $rootbk$folderbk/$filename เมื่อได้ไฟล์ตามรายละเอียดดังนี้แล้ว การกำหนดใน centos จะต้องใช้ crontab ในการสั่งให้ระบบทำงานอัตโนมัติ #crontab -e ระบบจะเปิดให้ระบบรายละเอียดในการสั่งการทำงานให้ระบุดังนี้ 59 23 * * * ตำแหน่งไฟล์และชื่อไฟล์ที่สร้างไว้ โดยค่าต่างๆ มีความหมายดังนี้ 59 นาที 23 ชั่วโมง * วันที่ในเดือน (1-31) * เดือน (1-12) * วันในสัปดาห์ (0-7) ถ้าระบุเป็น * แสดงว่าดำเนินการทุกช่วงเวลา เมื่อถึงเวลาในการกำหนดไว้ระบบจะไปเรียกตามคำสั่งตามไฟล์ที่กำหนดไว้ ซึ่งสามารถนำไปประยุกต์ในเรื่องอื่นๆ ได้

การนำเข้าข้อมูลจาก shape file ไปยังฐานข้อมูลนั้น ถ้ามีการกำหนด encoding ของฐานข้อมูลเป็น utf8 ส่วนใน shape fiile จะเป็น ascii ซึ่งถ้ามีการนำเข้าไปยังฐานข้อมูลภาษาไทยจะไม่ถูกต้อง จึงจำเป็นต้องมีการกำหนดรายละเอียดก่อนที่จะใช้คำสั่งในการนำเข้าข้อมูล โดยการกำหนด set PGCLIENTENCDING=WIN874 set CLIENTENCODING=WIN874 สำหรับการกำหนด Encoding จะกำหนดใน Option ของคำสั่งเลยก็ได้ เช่น shp2pgsql -s 32647 -I -W "WINDOWS-874" "location shape file and filename" schema.table | psql -U username -d database_name แต่การนำข้อมูลออกไปนั้นจะต้องกำหนดโดยการระบุด้านบนอย่างเดียวจะไม่มี option มาให้เมื่อกำหนด environment เรียบร้อยแล้วจึงทำการสั่งให้ส่งออกข้อมูลเป็น shape file ตามที่ต้องการ เช่น pgsql2shp -u postgres -P password -p port_number -h host_ip -f location_and_filename database_name schema.table

สำหรับวินโดว์ เท่านั้น มีบางกรณีที่เราไม่สามารถที่จะ start service ของ PostgreSQL ได้เมื่อเราได้ทำการติดตั้งไปแล้ว โดยวิธีการใช้ setup หรือการติดตั้งแบบ manual อาจจะเกิดจากที่ตำแหน่งที่ติดตั้งไม่ได้อนุญาตให้ user postgres ทำการอ่านและเขียนไฟล์ในตำแหน่งที่ติดตั้ง หรืออาจจะเกิดจากกรณีที่ไม่ได้กำหนดการ logon สำหรับ user postgres ในกรณีไม่ได้กำหนดสิทธิตำแหน่งที่ติดตั้งให้กำหนดใน tab security ของตำแหน่ง root ที่ติดตั้ง ถ้าในกรณีที่สอง จะต้องกำหนดใน policy ของ window ที่ติดตั้ง Control Panel\System and Security\Administrative Tools\Local Security Policy แล้วทำการ double click รายการนี้ จะเปิดหน้าต่างขึ้นมา ให้ mouse ขวาที่รายการ Log on as a service จะได้หน้าต่างขึ้นมาให้ทำการคลิกปุ่ม Add User or Group ... จะเปิดหน้าต่างมาให้ทำการเพิ่ม user postgres เข้าไปแล้วทำการคลิกปุ่ม ok แล้วก็ ok

เป็นตัวอย่างการเขียนฟังก์ชั่นในการตัดตัวอักษรที่ไม่ใช่ตัวเลขออกจากคำที่ต้องการ create or replace function test_valid_number (arg1 varchar) returns float AS $$ DECLARE len_char int; DECLARE loop_char int; DECLARE temp_char1 varchar; DECLARE temp_char2 varchar; DECLARE temp_i int; DECLARE temp_ret float; BEGIN /* ASCII 46 . 48-57 0-9 */ if (upper(arg1)='NIL' or upper(arg1)='ND') then RETURN 0.01; else len_char=char_length(arg1); loop_char=0; temp_ret=0; temp_char2=''; FOR loop_char IN 1..len_char LOOP temp_char1=SUBSTRING(arg1 from loop_char for 1); temp_i=ASCII(temp_char1); if (temp_i >= 48 and temp_i 0 ) then temp_ret=cast(temp_char2 as float); else temp_ret=0; end if; RETURN temp_ret; end if; END; $$ LANGUAGE plpgsql; ทุกท่านอาจจะนำไปประยุกต์กับการเขียนฟังก์ชั่นในการใช้งานสำหรับงานที่ท่านต้องการได้

เราอาจจะมีข้อมูลที่เป็นเส้นหรือพื้นที่ปิด ที่ต้องการดูตำแหน่ง vertex บนข้อมูลนั้น จะมี SQL ในการเรียกข้อมูลมาแสดงผล ตัวอย่าง SELECT x(vertex) ,y(vertex) from (select ( st_pointn(the_geom,generate_series(1,st_npoints(the_geom)) )) as vertex from line where linecode='xxx') as a ลองไปประยุกต์กับข้อมูลของทุกท่านดูนะครับ

การจัดการ Partitioning เพื่อใช้สำหรับข้อมูลที่มีปริมาณมาก และต้องการให้มีการค้นหาข้อมูลที่รวดเร็วไปยังข้อมูลที่ต้องการ โดยมีการจัดทำดังนี้ เริ่มต้นด้วยมีตารางข้อมูลที่เราต้องการ create table main_table ( id serial not null, name varchar(50) , provcode char(2) , primary key (id)); create index idx_main_table_provcode on main_table (provcode); ซึ่งจะเป็นตารางที่กำหนด provcode เป็นรหัสจังหวัดซึ่งจะจัดทำ partition โดยแยกตามจังหวัด (ถ้าใครต้องการเปลี่ยนเป็นอย่างอื่นๆก็ได้เช่นวันที่ เป็นต้น เมื่อได้ตารางข้อมูลแล้วก็จัดทำตาราง partion ตามที่ต้องการ โดยในตัวอย่างจะใช้ provcode เป็นตัวแยกข้อมูล create table main_table_part_10 ( check (provcode='10') ) INHERITS (main_table); create index main_table_part_10_provcode on main_table_part_10 (provcode); create ...