[postgis-users] Raster + Geometry Intersection

[Pierre Racine]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Ed Linde
> Sent: Monday, February 27, 2012 7:21 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Raster + Geometry Intersection
> 
> Hi,
> I am trying to compute elevations for a road network that I have downloaded
> and installed into my pg database using osm2pgsql. I also have loaded SRTM tif
> files and they are stored as type RASTER in my table.
> I am now trying to compute an intersection between the SRTM rasters and the
> OSM road geometries... with no luck! :( I am following this tutorial -->
> http://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01
> but I think it might be slightly outdated.

The base queries to do the intersection are still valid. Only the uploading changed. And not much.

>  SELECT o.osm_id,
>         ST_Intersection(s.rast, o.way)
>  FROM planet_osm_line o,
>       srtm_tiled s
>  WHERE ST_Intersects(s.rast, o.way);
> 
> way type -->   way geometry(LineString,900913)
> 
> CREATE TABLE public.srtm_tiled
> (
>   rid integer NOT NULL DEFAULT nextval('srtm_tiled_rid_seq'::regclass),
>   rast raster,
>   CONSTRAINT srtm_tiled_pkey PRIMARY KEY (rid)
> )
> WITH (
>   OIDS=FALSE
> );
> 
> performing the above query returns no rows. So I am not sure what is wrong.. or
> if I am doing this query correctly. Is the SRID set differently in the raster and
> geometry?

You should do ST_Intersection(o.way , s.rast) instead of ST_Intersection(s.rast, o.way). The first operates in the vector world and the second in the raster world. Let me know if that makes a difference in terms of results.

Are the SRIDs identical? Did you make sure that the raster were loaded properly by displaying values with ST_Value() or ST_DumpAsPolygons() or simply displaying the raster in QGIS? Does the raster align with the roads properly?

> I also loaded the tif files using the tutorial, but I used the C version not the
> python loader because I understand its been deprecated.

Should not make any difference.

Pierre