Juhae: /* Check the Data Validity */

2021-01-06T08:28:38Z

‎Check the Data Validity

Admin: 새 문서: ==Create Photo Archive Database Shell== ===Bulkload Data Repository===

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

===Metadata XML Reposit...

2021-01-06T04:07:41Z

새 문서: ==Create Photo Archive Database Shell== ===Bulkload Data Repository=== <pre> create table photoBulkload ( section nvarchar(40) NULL, xmltxt XML ); </pre> ===Metadata XML Reposit...

새 문서

==Create Photo Archive Database Shell==

===Bulkload Data Repository===
<pre>
create table photoBulkload (
section nvarchar(40) NULL,
xmltxt XML );
</pre>

===Metadata XML Repository===
<pre>
create table photoXml (
id nvarchar(40) NULL,
section nvarchar(40) NULL,
xmltxt XML )
</pre>

===Metadata View===
<pre>
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
</pre>

===Metadata Table===
<pre>
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)
)
</pre>

==XML File Bulkload==
<pre>
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
</pre>

==Generate Individual Metadata==
<pre>
/* 기입력 데이터 확인 */
select * from photoXml
</pre>

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

/* 전체 메타데이터 새로 생성 */
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
</pre>

===Partial Update===
<pre>
/* 특정 지역 데이터만 삭제하고 새로 반입하려 할 때 */
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'
</pre>

==Create Relational Table==

===Check the Data Validity===
<pre>
/* 중복 id 검사 */
select id, count(*) as occurence from photoMeta
group by id
order by count(*) desc

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

===Create Online Service Data ===
<pre>
/* 온라인 서비스 데이터 일괄 생성 */
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
</pre>

==Find the Center of Region==
<pre>
/* 지역 중심 위치 좌표 찾기: 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
</pre>

[[분류:튜토리얼]]

Photo Archive Management - 편집 역사

Juhae: /* Check the Data Validity */

Admin: 새 문서: ==Create Photo Archive Database Shell== ===Bulkload Data Repository===
create table photoBulkload ( section nvarchar(40) NULL, xmltxt XML );
===Metadata XML Reposit...

@@ 122번째 줄: / 122번째 줄: @@
 group by id
 order by count(*) desc
 /* 사진 설명 유무 검사 */