[postgis-users] Newbie Q: More efficient query for polygons near lines

[Benjamin Juhn]

This should give you what you're looking for:
SELECT 
	(SELECT the_geom FROM hyd_usgsstrm WHERE ST_DWithin(p.the_geom, the_geom, 1) ORDER BY ST_Distance(p.the_geom, the_geom) ASC LIMIT 1), p.*
FROM par_parcels p;

If you need more than the_geom returned from hyd_usgsstrm you can retrieve the gid in the subquery and then turn the whole query into a derived table and join back on hyd_usgsstrm for other fields:

SELECT p.*, u.a, u.b, u.the_geom
	SELECT 
		(SELECT gid FROM hyd_usgsstrm WHERE ST_DWithin(p.the_geom, the_geom, 1) ORDER BY ST_Distance(p.the_geom, the_geom) ASC LIMIT 1) AS u_gid, p.*
	FROM par_parcels p
) p
JOIN hyd_usgsstrm u ON u.gid = p.u_gid;


On Jan 4, 2011, at 8:53 AM, Dan Lyke wrote:

> I have a silly question that I can't wrap my head around doing more
> efficiently (and it may not actually matter).
> 
> My example is that I have two tables, tax parcels from the county, and
> the USGS streams center lines.
> 
> Say I want to select all parcels within a meter of a stream center line
> (yeah, it's a silly query, as at least one of those "streams" is 25
> meters wide). I can do:
> 
>  SELECT par_parcels.the_geom FROM par_parcels , hyd_usgsstrm 
>    WHERE ST_DWithin(par_parcels.the_geom, hyd_usgsstrm.the_geom, 1)
> 
> But that, of course, gives me multiple par_parcels.
> 
> I can also do:
> 
>  SELECT DISTINCT(par_parcels.the_geom) FROM par_parcels , hyd_usgsstrm 
>    WHERE ST_DWithin(par_parcels.the_geom, hyd_usgsstrm.the_geom, 1)
> 
> But what I really want is something more like:
> 
>  SELECT the_geom FROM par_parcels  
>    WHERE ST_DWithin(the_geom, (SELECT the_geom FROM hyd_usgsstrm), 1);
> 
> but that, of course, both doesn't let the query optimizer do anything
> intelligent and gives me:
> 
>  ERROR:  more than one row returned by a subquery used as an expression
> 
> What's the right approach for this kind of query?
> 
> Thanks!
> 
> Dan
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users