Converting SQL Server Rasters to ASCII Grid?

Question

I have 25km by 25km squares of the world in a relational database. Each square has coordinates for the top left and lower right corner as latitudes and longitudes. Each square also has an index ilat and ilong value starting at the lower left corner with (0,0) to (maxlong, maxlat) for the top right corner. Each square also has measurements (X, Y, Z). I would like to transform this data into the ASCII Grid Format:

link

for each measurement. Here is some example data taken from the Internet:

ncols         419
nrows         407
xllcorner     823678.99884033
yllcorner     1151760.4752197
cellsize      14.314150820378
NODATA_value  -9999
-9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999 

I understand what ncol and nrows is but I am not too sure about xllcorner, yllcorner, cellsize. Could someone please advise me what these values would be in my situation? Do you reckon I can transform my data from my tile/square format into the ASCII Grid Format.

---

Actually my grid is as follows:

Latitude is in the range [-90 ... 90] and longitude [-180 ... 180]. A square’s cell’s size is 2.5 degrees

I have been told that corresponds to 25 square kilometres.

Answer 1

Here is a two step solution, using GDAL >= 1.8 (e.g., OSGeo4W for MS Windows).

The first step is to make an ASCII Gridded XYZ file:

...with (at least) 3 columns, each line containing the X and Y coordinates of the center of the cell and the value of the cell.

The spacing between each cell must be constant and no missing value is supported. Cells with same Y coordinates must be placed on consecutive lines. For a same Y coordinate value, the lines in the dataset must be organized by increasing X values. The value of the Y coordinate can increase or decrease however. The supported column separators are space, comma, semicolon and tabulations.

assuming you have no gaps/missing data, something like this should produce the appropriate XYZ file:

sqlcmd -S myServer -d myDB -E -o "raster.xyz" ^
    -Q "SELECT lon, lat, alt FROM foo ORDER BY lat, lon" ^
    -W -w 999 -s","

(not tested! more details here)

Then convert the XYZ ASCII format to an Esri ASCII grid file (raster.asc) using gdal_translate:

gdal_translate -of AAIGrid raster.xyz raster.asc