# accessing postgis raster data using Query layer in ArcGIS

I tried to load raster file (jpg) into post gis using the syntax provided here "C:\Program Files (x86)\PostgreSQL\9.1\bin>raster2pgsql -I -C -e -Y -F -s 102003 -t 300x300 ras_test.tif ras_test1 | psql .exe -U postgres -d raster_analysis -h localhost -p 5434"

I could see a raster table created in the database. It has three columns (rid,rast, filename), where you do not see any thing under "rast raster" column unless you click on a particular cell.

Here is my actual problem. How to view the raster layer from ArcGIS using sql query layer. When I tried adding this layer and validate it, it says "rast raster" column has invalid data type. How can I view the raster layer in ArcGIS?

I could however, able to see the raster layer in QGIS using a plugin. However, my work needs access from arcGIS 10.

It is mentioned in this link, http://trac.osgeo.org/postgis/wiki/WKTRaster, that You can not display rasters directly but a vectorization of them (in ArcGIS 10). How to do this?

-

To do this, you'll want to create a separate table containing the vectorized raster or a view. If your raster table is big (lots of rows), you're better using a separate table instead of a view do to the computation occurring every time the view is accessed. The SQL for creating the view could be...

CREATE VIEW myview AS
SELECT
rid,
(ST_DumpAsPolygons(rast)).*
FROM ras_test1


You should be able to view that in ArcGIS. It might complain about lacking an primary key, which you could always add if needed.

-
thank you dustmugs. I was successfully able to create a view. However, when I tried to add a view to the arcmap and validate the data, it freezes the window. What could be an issue –  GISTECH Jul 26 '12 at 16:13
Hi, I ran your code and I was able to successfully create a view. However, it took time. I ran a explain analyze query to see the data. It is taking a long time and displaying a list of "Ring self-intersection at or near point xxxx.xx xxxx.xx" I want to let you know that when I loaded a raster file I used 300x300 tiling. Does this has anything to do this? what does "Ring self-intersection at or near point xxxx.xx xxxx.xx" mean? How to fix it? Any help. Thanks. –  GISTECH Jul 27 '12 at 17:40
How big (width x height) is your original raster? It should be noted that when you tile your raster into PostGIS, you should keep the tile size anywhere between between 30 - 100 pixels for width and height. What the exact values for width and height should be based what is cleanly divisible (without remainder) from your raster's width and height. You're getting a ring intersection? ST_DumpAsPolygons automatically rectifies invalid polygons unless your version of GEOS is less than 3.3.0. It is strongly encouraged to have the latest GEOS, 3.3.5 I think. –  dustymugs Jul 27 '12 at 22:53
I have installed postgis 2.0.1. Still the problem persists. How to check if geos 3.3.5 is installed or not? Thanks. –  GISTECH Aug 2 '12 at 21:27
Run "SELECT postgis_full_version()" and in the output there will be the GEOS version info. –  dustymugs Aug 9 '12 at 23:38