大字・町丁目レベル


街区レベル位置参照と町丁目を合わせて階層構造データを作成するため町丁目データを取り込む
街区レベル位置参照のSQL Server取り込み
市役所やら県庁位置も入れてくれれば親切なのだが。

USE gisdb;

GO

--DROP TABLE CITY_PT;
--GO

DECLARE @path AS VARCHAR(MAX);
DECLARE @postPath AS VARCHAR(MAX);
DECLARE @colDef AS VARCHAR(MAX);

SET @path = 'C:\data\大字町丁目\';
SET @postPath = '_2012.csv';
SET @colDef = 'C:\data\kc.xml';

DECLARE @csvPath AS VARCHAR(MAX);
DECLARE @counter int;
DECLARE @ex_query AS VARCHAR(MAX);

SET @counter = 1;
SET @csvPath = @path + 
RIGHT('00' + convert(varchar,@counter), 2)
+ @postPath

--SELECT * INTO CITY_PT FROM other_table
SET @ex_query =
'SELECT * INTO CITY_PT FROM (
SELECT
*,
GEOGRAPHY::Point(lat,lon,4612) AS SHAPE -- 4612=JGD2000
FROM OPENROWSET(
BULK ''' + @csvPath + ''',
FIRSTROW = 2,
FORMATFILE='''+ @colDef +'''
) AA) CSV;';

exec(@ex_query);
SET @counter = @counter + 1;

 

WHILE (@counter < 48)
BEGIN
SET @csvPath = @path + 
RIGHT('00' + convert(varchar,@counter), 2)
+ @postPath
SET @ex_query =
'INSERT INTO CITY_PT SELECT
*,
GEOGRAPHY::Point(lat,lon,4612) AS SHAPE -- 4612=JGD2000
FROM OPENROWSET(
BULK ''' + @csvPath + ''',
FIRSTROW = 2,
FORMATFILE='''+ @colDef +'''
) AS CSV;';

exec(@ex_query);

SET @counter = @counter + 1;
END

XML

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="0" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="&quot;\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="KEN" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="SHIKUTYOSON" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="OOAZATYOU" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="GAIKU" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="KEI" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="X" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Y" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="lat" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="lon" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="10" NAME="JyukyoCode" xsi:type="SQLINT"/>
<COLUMN SOURCE="11" NAME="DaihyoCode" xsi:type="SQLINT"/>
<COLUMN SOURCE="12" NAME="PreCode" xsi:type="SQLINT"/>
<COLUMN SOURCE="13" NAME="PostCode" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>
カテゴリー: 開発 タグ: パーマリンク