[postgis-users] Intersection query problem

[Ben Madin]

George,

You need to put source.track_cl into the table list for your query...

hint - it is in the table list for your sub-select, but not for the main query, I know some people scorn indenting, but :

SELECT  gid,  name, track_use, st_astext(clipped_geom)
FROM  (
	SELECT  source.track_cl.gid,  source.track_cl.name,  source.track_cl.track_use, 
	(ST_Dump(ST_Intersection(
		extents.tiles.the_geom,  source.track_cl.the_geom
	))).geom  As  clipped_geom
	FROM  source.track_cl
	INNER  JOIN  extents.tiles
	ON  ST_Intersects(extents.tiles.the_geom,  source.track_cl.the_geom) 
	WHERE extents.tiles.name='BP33' 
) As  clipped 
WHERE  st_geometrytype(clipped.clipped_geom) = st_geometrytype(source.track_cl.the_geom);

cheers

Ben



On 11/05/2011, at 11:15 AM, George Washington wrote:

> Hi, I have the following query:
> 
> SELECT  gid,  name, track_use, st_astext(clipped_geom)
> FROM  (SELECT  source.track_cl.gid,  source.track_cl.name,  source.track_cl.track_use,(ST_Dump(ST_Intersection(extents.tiles.the_geom,  source.track_cl.the_geom))).geom  As  clipped_geom
> FROM  source.track_cl
> INNER  JOIN  extents.tiles
> ON  ST_Intersects(extents.tiles.the_geom,  source.track_cl.the_geom) where extents.tiles.name='BP33' ) As  clipped 
> WHERE  st_geometrytype(clipped.clipped_geom) = st_geometrytype(source.track_cl.the_geom);
> 
> which gives me:
> 
> ERROR:  missing FROM-clause entry for table "track_cl"
> LINE 6: ...metrytype(clipped.clipped_geom) = st_geometrytype(source.tra...
> SQL state: 42P01
> Character: 471
> 
> 
> On the other hand this version of the same query works (only the last line differs):
> 
> SELECT  gid,  name, track_use, st_astext(clipped_geom)
> FROM  (SELECT  source.track_cl.gid,  source.track_cl.name,  source.track_cl.track_use,(ST_Dump(ST_Intersection(extents.tiles.the_geom,  source.track_cl.the_geom))).geom  As  clipped_geom
> FROM  source.track_cl
> INNER  JOIN  extents.tiles
> ON  ST_Intersects(extents.tiles.the_geom,  source.track_cl.the_geom) where tiles.name='BP33' ) As  clipped
> WHERE  ST_Dimension(clipped.clipped_geom)  >0;
> 
> I cannot figure out what is wrong with the first query.
> Many thanks.
> George
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110511/88b76aea/attachment.html>