3

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!

Improve this question
6
  • So basically you want to convert the raster to a vector point layer with one point per cell?
    – underdark
    May 10, 2013 at 13:57
  • yes ,for the exact i have a netcdf file and i converted to geotiff using the gdal_translate from GDAL .And i used raster2pgsql to upload it into postgis by the command 'raster2pgsql -s 4326 -I -C -t 100x100 -F project/index_u.tif public.tendral_test > test.sql | psql -d template_postgis_20 -h localhost -p 5432'.
    – AshokDev
    May 10, 2013 at 14:06
  • what is the format that stored in when we import raster through raster2pgsql or by default it stored in it as lat,lon.So how to get lat,long and associated values like temperature. along with values. each row of the should contain latitude,longitude and temperature.
    – AshokDev
    May 10, 2013 at 14:13
  • Seems like you have to do the sampling manually (e.g. gis.stackexchange.com/questions/40371/…). A function to dump points would make sense, but I only found ST_DumpAsPolygons (postgis.net/docs/RT_ST_DumpAsPolygons.html)
    – underdark
    May 10, 2013 at 16:21
  • By the use of ST_DumpAsPolygon() we can extract the values and corresponding polygon co-ordinates,but i want only the latitude and longitude and its corresponding values.
    – AshokDev
    May 14, 2013 at 9:08

1 Answer 1

Reset to default
8

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.

Improve this answer
1
  • ST_PixelAsCentroids is a function bundled with postgis 2.1
    – nickves
    Jul 12, 2013 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.