On 7/7/2010 8:27 AM, Merlin Moncure wrote:
On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson<andy@xxxxxxxxxxxxxxx> wrote:
I have gis data in layers, and a pin might appear in either layer, or both
(parcelPoly, parcelPoint), or neither (and I dont care which I find)... so I
have this view:
create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
SELECT gid,
st_x(st_centroid(the_geom)) AS x,
st_y(st_centroid(the_geom)) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
FROM howardia.parcelPoly
UNION ALL
SELECT gid,
st_x(the_geom) AS x,
st_y(the_geom) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
FROM howardia.parcelPoint;
Which works fine for what I'm using.. it returns one or two records, and my
code just takes the first record and runs with it.
but now... I'm adding something new, and having it return multiple records
per pin is causing problems. I tried adding a limit inside the view but
then it never returns anything:
??? why not -- can you double check that?
create view l as select * from (select 'a' union all select 'b') q limit 1;
select * from l;
?column?
----------
a
(1 row)
merlin
Ok, I found it. I don't know why I was making it more difficult than it
needed to be. Once I stopped thinking about the view, and thought of it
just like any other table, then I have dups, and I want one of each..
and that can be solved with distinct on:
select distinct on (pin) *
from getpoint
where pin in ('110250821020000', '320770000010000');
Thanks for letting me think out loud.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
