March 2008
    Apr »

Loading LAR-IAC 1 Aerial Imagery Tiles to SDE

LAR-IAC data can a beast! Countywide, the imagery is around 1.5 Tb, split into over 13,000 tiles. This can be a major distribution headache. As we move forward to LAR-IAC 2, we plan on providing data in more easily consumable formats.

These will be:

  • JPEG2000
  • MrSID compressed
  • ECW format

These formats will provide valuable mechanisms to deploy data to your staff and ease some of the burden. LA City supported the creation of an ECW format for LAR-IAC1 – please contact me if you would like to access those.

In the County, we have deployed the LAR-IAC data using SDE, which has been a success, since it can be placed in ArcIMS map services for rapid deployment. This can be tricky, so I wanted to pass along my knowledge of how this is done.

In 2003, ESRI worked with the County Department of Public Works, and created a set of scripts to load data into seamless SDE rasters. This has provided three major benefits:

  1. Seamless data – no more searching for the right tile
  2. Faster access – SDE provides fast search and access to data
  3. Compression – by compressing the raw .tif format to JPEG, initial compression was about 20:1, reducing space requirements dramatically.

A note about compression to JPEG

I was concerned the JPEG compression, which is lossy, would cause a loss of valuable information, so I ran a test to comprase raw .tif vs. .jpg vs. LZ77 (.png) compression. As you can see from the results below, JPEG compression results in little to no loss of contextual information. I decided that a compression of 60 would give acceptable results.

Based on experience, I broke the loading process into two steps. The first step loads and mosiacs the data, and the second step creates the pyramids and statistics.

Note – these batch files run from the command line.

Script 1: Load data (load_1ft_nostat.bat).

  • Open a text editor like notepad.
  • Copy the text below into the editor
  • Edit the source location (this batch file reads below it recursively)
  • Edit the name of the output raster file – here it is EMERGE1F – this is the name you will see in ArcCatalog
  • Edit the connection parameters to match your server and connection information. We use SQL Server, which requires the -D (database name) parameter
  • Edit the compression parameter – in this script, we use JPEG (-c JPEG) compression with a quality of 60 (-q 60)
  • Rename the .txt file to .bat – this turns this into a batch file.
  • Note the -L 0 – this tells SDE not to build pyramids – otherwise SDE will build a pyramid after every addition, making this load untenable.

set source=d:\emerge\1foot
set table_name=EMERGE1F
set connection=-s gismapdb -i esri_sde -D EMERGE -u EMERGE -p EMERGE
set first_one_loaded=false

for /R %source% %%f in (*.tif) do call :block %%f
if %first_one_loaded%==false (
sderaster -o import -l %table_name%,image -G 102645 -f %1 -c JPEG -q 60 -g %connection% -L 0 -I bilinear
set first_one_loaded=true
) else (
sderaster -o mosaic -l %table_name%,image -v 1 -f %1 %connection% -L 0
goto :eof

echo %TIME%

For more information about all of the parameters, look at SDE help files installed on the SDE machine.

This script recursively reads all .tif files under the source directory. The first time through, when first_one_loaded is false, the raster is created. Thereafter, all other rasters are appended (mosaicked) to create the seamless image.

To run this script, open a command prompt, navigate to the script location, and enter the name of the script. Often, I will pipe the results to an output file so that I can see where something failed ..

C:\aerial_load>load_1f_nostat.bat > load_results.txt

Step 2: Create Pyramids, Build Statistics (finish_load.bat)

Once the data has loaded, you need to build pyramids and statistics to ensure speed and proper data display.

Again – copy the text below into a text editor,make changes that match those above, and run.

Note – this process takes about as long as the original load – which could be a few days for a large dataset.

set table_name=EMERGE1F
set connection=-s gismapdb -i esri_sde -D EMERGE -u EMERGE -p EMERGE

echo %TIME%
sderaster -o pyramid -l %table_name%,image -v 1 -L -1 %connection% -I bilinear

echo %TIME%
sderaster -o stats -l %table_name%,image -v 1 %connection%

echo %TIME%
sdetable -o update_dbms_stats -m “with fullscan” -t %table_name% %connection%

I hope this helps you use the data more quickly.

Leave a Reply