Обсуждение: make view with union return one record
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:
create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
select * from (
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
) as x limit 1;
I dont think I can put the limit outside the view, because the "new
stuff" is going to select multiple parcels like:
select * from getPoint where pin in ('123', '456', '789);
I thought of changing it to a function, but its in use in many places in
the code as:
select * from getPoint where pin = '12345';
So I've run out of ideas now. Any hints or pointers on how I can get
the limit to work inside the view? Or some other way?
Thanks for your time,
-Andy
On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson <andy@squeakycode.net> 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
On 7/7/2010 8:27 AM, Merlin Moncure wrote:
> On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson<andy@squeakycode.net> 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
Humm.. yea, had to look at that a little closer... but no, it does not
work when you put a where clause on it:
gis=# create view l as select * from (select 'a' as cname union all
select 'b') q limit 1;
CREATE VIEW
-- This works as you noted:
gis=# select * from l ;
cname
-------
a
(1 row)
-- And as long as you only ever want the first row, it works too:
gis=# select * from l where cname = 'a';
cname
-------
a
(1 row)
-- But this is more akin to what I'm doing:
gis=# select * from l where cname = 'b';
cname
-------
(0 rows)
In my example, if I put the limit on the view, then I can:
gis=# select x,y,pin from getpoint;
x | y | pin
------------------+------------------+-----------------
5185561.61663698 | 3906727.90184405 | 190161634020000
(1 row)
So the entire view is now just one record.
I'm guessing the limit and the where are being applied in the wrong
order. (Correction, not the wrong order, rather not the order I want)
Hum... I wonder if I subselect the table enough times if I can swap the
where and the limit... I'll have to play around with that.
-Andy
On 7/7/2010 8:27 AM, Merlin Moncure wrote:
> On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson<andy@squeakycode.net> 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