[GIS] How to get Lon/Lat values from the postgis raster type column

geotiff-tiffpostgisraster

I have a tiff raster in my PostGIS 2.0 database. I want to get the value lat/lon and extra data like temperature from the raster type column in postgis table.

I want the table with columns lon,lat,temperature etc.. from raster type column(rast).
For each corresponding lat and lon threre is a temperature value.

The tiff file details by gdalinfo as:

Driver: GTiff/GeoTIFF
Files: atm_ncep_gfs_temp_20130422_003.tif
atm_ncep_gfs_temp_20130422_003.tif.aux.xml
Size is 720, 361
Coordinate System is:
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84",6378137,298.257223563,
         AUTHORITY["EPSG","7030"]],
       AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433],
AUTHORITY["EPSG","4326"]]
Origin = (-0.250000000000000,90.250000000000000)
Pixel Size = (0.500000000000000,-0.500000000000000)
Metadata:
AREA_OR_POINT=Area
latitude#long_name=latitude
latitude#units=degrees_north
longitude#long_name=longitude
longitude#units=degrees_east
NC_GLOBAL#Conventions=COARDS
NC_GLOBAL#GRIB2_grid_template=0
NC_GLOBAL#History=created by wgrib2
time#_FillValue=9.999e+020
time#long_name=verification time generated by wgrib2 function verftime()
time#reference_date=2013.04.22 00:00:00 UTC
time#reference_time=1366588800
time#reference_time_description=forecast or accumulated, reference date is fixed
time#reference_time_type=3
time#time_step=0
time#time_step_setting=auto
time#units=seconds since 1970-01-01 00:00:00.0 0:00
TMP_2maboveground#_FillValue=9.9990003e+020
TMP_2maboveground#level=2 m above ground
TMP_2maboveground#long_name=Temperature
TMP_2maboveground#short_name=TMP_2maboveground
TMP_2maboveground#units=K
Image Structure Metadata:
INTERLEAVE=BAND
Corner Coordinates:
Upper Left  (  -0.2500000,  90.2500000) (  0d15' 0.00"W, 90d15' 0.00"N)
Lower Left  (  -0.2500000, -90.2500000) (  0d15' 0.00"W, 90d15' 0.00"S)
Upper Right (     359.750,      90.250) (359d45' 0.00"E, 90d15' 0.00"N)
Lower Right (     359.750,     -90.250) (359d45' 0.00"E, 90d15' 0.00"S)
Center      ( 179.7500000,   0.0000000) (179d45' 0.00"E,  0d 0' 0.01"N)
Band 1 Block=720x2 Type=Float32, ColorInterp=Gray
 Min=211.200 Max=311.600
 Minimum=211.200, Maximum=311.600, Mean=277.878, StdDev=21.574
 NoData Value=9.9990002605540088e+020
 Metadata:
_FillValue=9.9990003e+020
 level=2 m above ground
 long_name=Temperature
NETCDF_DIMENSION_time=1366599600
NETCDF_time_units=seconds since 1970-01-01 00:00:00.0 0:00
NETCDF_VARNAME=TMP_2maboveground
short_name=TMP_2maboveground
STATISTICS_MAXIMUM=311.60000610352
STATISTICS_MEAN=277.87782601544
STATISTICS_MINIMUM=211.19999694824
STATISTICS_STDDEV=21.573645315608
units=K

I have:

postgresql-9.2
postgis-2.0 raster support
raster imported into PostGIS with SRID 4326

How can i do this? Thanks in advance!

Best Answer

Yo can access to the centroid of each pixel as doc says with ST_PixelAsCentroids (postgis 2.1)

SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM dummy_rast WHERE rid = 2) foo;
 x | y | val |           st_astext            
---+---+-----+--------------------------------
 1 | 1 | 253 | POINT(3427927.775 5793243.975)
 2 | 1 | 254 | POINT(3427927.825 5793243.975)

Now you have the geom , and its trivial to split the lat/lon into separate columns if you want.

Related Question