A list of unique parcels within 1m? Try this. Better to do distinct on a primary key than on a geometry value.
SELECT DISTINCT ON (par_parcels.gid) par_parcels.the_geom FROM par_parcles, hyd_usgsstrm WHERE ST_DWithin(par_parcels.the_geom, hyd_usgsstrm.the_geom, 1); On Tue, Jan 4, 2011 at 8:53 AM, Dan Lyke <danl...@flutterby.com> 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
