Photo Archive Management

Create Photo Archive Database Shell

Bulkload Data Repository

create table photoBulkload ( 
	section nvarchar(40) NULL, 
	xmltxt XML );

Metadata XML Repository

create table photoXml (
	id nvarchar(40) NULL, 
	section nvarchar(40) NULL, 
	xmltxt XML )

Metadata View

create view photoMeta as
select id, section,
	xmltxt.value('(/Photo/FileName)[1]', 'nvarchar(40)') as FileName,
	xmltxt.value('(/Photo/PixelWidth)[1]', 'nvarchar(40)') as PixelWidth,
	xmltxt.value('(/Photo/PixelHeight)[1]', 'nvarchar(40)') as PixelHeight,
	xmltxt.value('(/Photo/DateTimeOriginal)[1]', 'nvarchar(40)') as DateTimeOriginal,
	xmltxt.value('(/Photo/Description)[1]', 'nvarchar(40)') as Description,
	xmltxt.value('(/Photo/Latitude)[1]', 'nvarchar(40)') as Latitude,
	xmltxt.value('(/Photo/Longitude)[1]', 'nvarchar(40)') as Longitude,
	xmltxt.value('(/Photo/ObjectName)[1]', 'nvarchar(40)') as ObjectName,
	xmltxt.value('(/Photo/Keywords)[1]', 'nvarchar(40)') as Keywords
from photoXml

Metadata Table

create table photoData (
	id nvarchar(40) not NULL,
	class nvarchar(40) NULL,
	groupName nvarchar(40) NULL,
	partName nvarchar(40) NULL,
	label nvarchar(160 ) NULL,
	objectName nvarchar(80) NULL,
	description nvarchar(80) NULL,
	infoUrl nvarchar(256) NULL,
	iconUrl nvarchar(256) NULL,
	note nvarchar(256) NULL,
	latitude nvarchar(40) NULL,
	longitude nvarchar(40) NULL,
	remark nvarchar(256) NULL,
	primary key (id)
)

XML File Bulkload

use mokpo

/* 기입력 데이터 확인 */
select * from photoBulkload

/* 기존 데이터를 모두 삭제하고 새로 반입하려 할 때 */
delete photoBulkload

/* 특정 지역 데이터만 삭제하고 새로 반입하려 할 때 */
delete photoBulkload where section='bogwang'

/* 특정 지역 데이터 반입(Import) */
insert into photoBulkload ( section, xmltxt )
select 'bogwang', x.* from OPENROWSET(  
   BULK 'e:\inetpub\wwwroot\data\Mokpo\bogwang\Export_CSV.xml',
   SINGLE_BLOB) as x;

select * from photoBulkload

Generate Individual Metadata

/* 기입력 데이터 확인 */
select * from photoXml

Rebuild

/* 기존 데이터를 모두 삭제하고 새로 생성하려 할 때 */

/* 전체 메타데이터 새로 생성 */
delete photoXml

insert into photoXml(section, xmltxt)
select section, node.query('.') as xmltxt
from  photoBulkload CROSS APPLY xmltxt.nodes('/Album/Photo') as R(node) 

update photoXml
set id=replace(xmltxt.value('(/Photo/FileName)[1]', 'nvarchar(40)'), '.jpg', '')

select * from photoXml

Partial Update

/* 특정 지역 데이터만 삭제하고 새로 반입하려 할 때 */
delete photoXml where section='bogwang'

insert into photoXml(section, xmltxt)
select section, node.query('.') as xmltxt
from  photoBulkload CROSS APPLY xmltxt.nodes('/Album/Photo') as R(node) 
where section='bogwang'

update photoXml
set id=replace(xmltxt.value('(/Photo/FileName)[1]', 'nvarchar(40)'), '.jpg', '')
where section='bogwang'

select * from photoXml where section='bogwang'

Create Relational Table

Check the Data Validity

/* 중복 id 검사 */
select id, count(*) as occurence from photoMeta
group by id 
order by count(*) desc

/* 중복 사진 삭제 * /
delete photometa
where id in('DJI_0451', 'DJI_0374-Pano', '_HUH7623')

/* 사진 설명 유무 검사 */
select ObjectName, Description from photoMeta
order by len(ObjectName)

Create Online Service Data

/* 온라인 서비스 데이터 일괄 생성 */
use mokpo
delete photoData

insert into photoData
select 
id, 'HUH_Photo' as class, section grupName, NULL as partName, NULL as label,
ObjectName,
Description,
'http://dh.aks.ac.kr/~mokpo/photo/gallery/'+section+'/'+FileName as infoUrl,
'http://dh.aks.ac.kr/~mokpo/photo/gallery/'+section+'/thumb/'+FileName as iconUrl,
Keywords as note,
Latitude,
Longitude,
DateTimeOriginal as remark
from photoMeta

update photoData
set label=objectName+' - '+description
where description is not NULL

update photoData
set label=objectName
where description is NULL

select * from photoData

Find the Center of Region

/* 지역 중심 위치 좌표 찾기: wiki 용 */

use mokpo
create procedure getCenter( @region nvarchar(40) ) 
as   
begin
select  @region as 지역,
	avg(convert(float, latitude)) as 중심_위도, 
	avg(convert(float, longitude))as 중심_경도
from photoData where groupName=@region
end

exec getCenter bukgyo