<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
	<title type="html"><![CDATA[Forum PostgreSQL - TRIGGER i Zapytanie przestrzenne]]></title>
	<link rel="self" href="http://forum.postgresql.org.pl/extern.php?action=feed&amp;tid=14337&amp;type=atom"/>
	<updated>2016-11-08T20:38:56Z</updated>
	<generator>PunBB</generator>
	<id>https://forum.postgresql.org.pl/viewtopic.php?id=14337</id>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17868#p17868"/>
			<content type="html"><![CDATA[Działa ;). Wielkie dzięki ;)
To jest naprawdę mistrzowskie (chociaż jeszcze nie rozumiem kodu ;) ale powoli dojdę]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-08T20:38:56Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17868#p17868</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17867#p17867"/>
			<content type="html"><![CDATA[Zerknij na to:
create or replace function f_palenisko_tr() RETURNS trigger AS 
$body$BEGIN
         if tg_op = 'INSERT' then
            IF NEW.geom IS not NULL THEN
                new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) order by dom.wiek desc limit 1);
            END IF;
            RETURN NEW;
         elsif tg_op = 'UPDATE' then
                new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) order by dom.wiek desc limit 1);
             RETURN NEW;
         END IF;
END;$body$
LANGUAGE plpgsql;
CREATE TRIGGER tr_palenisko BEFORE INSERT OR UPDATE ON palenisko FOR EACH ROW EXECUTE PROCEDURE f_palenisko_tr();

create or replace function f_dom_tr() RETURNS trigger AS 
$body$ BEGIN
       raise notice '%,%,%',tg_table_name,tg_when,tg_op;
       UPDATE palenisko set nr_dom = null--d.nr_dom
        from (select p.id,d.id as nr_dom,row_number() over (partition by p.id order by d.wiek desc) as lp 
                from palenisko p 
                left join dom d on ST_WITHIN(p.geom,d.geom)
             ) d
       where d.id=palenisko.id and d.lp=1
         and coalesce(palenisko.nr_dom,0) != coalesce(d.nr_dom,0);
       return new;
END;$body$
LANGUAGE plpgsql;
CREATE TRIGGER tr_dom AFTER INSERT OR UPDATE OR DELETE ON dom FOR EACH STATEMENT EXECUTE PROCEDURE f_dom_tr();

PS
założyłem, że tabele wyglądają tak jak niżej
create table palenisko(id serial primary key ,nr_dom int ,geom geometry);
create table dom(id serial primary key, wiek varchar(10) ,geom geometry);

CREATE INDEX palenisko_dom_ix ON public.palenisko(nr_dom);]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2016-11-08T11:32:30Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17867#p17867</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17866#p17866"/>
			<content type="html"><![CDATA[Wydaje mi się że już prawie wszystko się udało. Ale jeszcze nie.

Stworzyłem triggera jak poniżej tylko jest kolejny problem . Jeśli przesunę dom i palenisko znajdzie się poza budynkiem to mój trigger nie zwróci żadnej wartości ale poprzednia zostanie. Aby sobie z tym poradzić najpierw chciałbym nadać TEJ KONKRETNEJ KOMORCE  wartość NULL a dopiero później uaktualnić palenisko.nr_dom.
Więc niestety kolejne pytanie 

create or replace function fn_zmianadom_palenisko() RETURNS trigger AS
$body$ BEGIN
	if (tg_op = 'INSERT') then 
			UPDATE palenisko set nr_dom = new.id where ST_WITHIN(palenisko.geom,new.geom);
		RETURN NEW;
	elsif (tg_op = 'DELETE') then
-- aby usunąc poligon również muszę wyzerować palenisko.nr_dom (mam FOREIGN KEY)
			UPDATE palenisko set nr_dom = new.id where ST_WITHIN(palenisko.geom,new.geom);
		RETURN NEW;
	elsif (tg_op = 'UPDATE') then
 --  zeruje mi wszystkie wartości w kolumnie palenisko.nr_dom bo w sumie to napisałem, jak zaznaczyć że chce dać wartość NULL dla konkretnego wiersza
-- i czy można zrobić 2 razy UPDATE 
		UPDATE palenisko set nr_dom = NULL;
		UPDATE palenisko set nr_dom= new.id where ST_WITHIN(palenisko.geom,new.geom);
		RETURN NEW;
	END IF;
END;$body$
LANGUAGE plpgsql;
CREATE TRIGGER tfn_zmianadom_palenisko AFTER INSERT OR UPDATE OR DELETE ON dom FOR EACH ROW EXECUTE PROCEDURE fn_zmianadom_palenisko();

Dzięki ;)]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-07T17:22:51Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17866#p17866</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17865#p17865"/>
			<content type="html"><![CDATA[najpierw mała uwaga do twojego posta
Linia "new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) ORDER BY dom.wiek DESC LIMIT 1)"
zawsze ci dopisze id domu nie zależnie czy dom jest "stary" czy "młody", (w pierwszej kolejności wstawi id domu "starego")

Z tego co napisałeś wynika, że jak ktoś w tabeli dom przestawi pole wiek z młody na stary to trigger ma wykonać update na tabeli palenisko i ustawić pole nr_domu na właśnie ten edytowany rekord z tabeli dom.

Aby to zrealizować musisz wykonać nowy trigger działający na tabeli dom po zmianie wartości rekordu (AFTER UPDATE), który wykona 
UPDATE palenisko set nr_dom= new.id where  ST_WITHIN(palenisko.geom,new.geom);

Mając powyższy przykład poradzisz sobie z tym bez problemów.]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2016-11-07T13:23:39Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17865#p17865</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17864#p17864"/>
			<content type="html"><![CDATA[Chciałbym aby jeśli palenisko zawiera się w 2 domach to w palenisko.nr_dom był wpisywany dom.wiek = 'stary'.
To mi się udało new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) ORDER BY dom.wiek DESC LIMIT 1)
Ale problem pojawia się dla budynku drugiego gdy zmienia atrybut dom.wiek = 'stary' Jak wtedy uruchomić triggera?

Mam nadzieje że teraz trochę jaśniej to wytłumaczyłem

Link do "rysunku" ;)
[url]https://1drv.ms/i/s!AhBpQSAaQjxvrA1xBfaMwLg0ZEhu[/url]]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-07T12:34:53Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17864#p17864</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17863#p17863"/>
			<content type="html"><![CDATA[Czy dobrze cię rozumiem?
jeśli masz 2 rekordy spełniające warunek ST_WITHIN(new.geom,dom.geom) z czego:
 - pierwszy rekord ma pole dom.wiek = 'stary'
 - drugi rekord ma pole dom.wiek = 'młody'
to trigger ma ma zwracać id dla pierszego rekordu

A jak zmienisz w pierszym rekordzie pole dom.wiek z wartości 'stary' na warosc 'mlody' to bedziesz miał 2 rekordy z wartoscią ' młody' i pewnie chcesz by podstawiać id z rekordu drugiego jako starszego w bazie?]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2016-11-07T10:59:05Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17863#p17863</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17862#p17862"/>
			<content type="html"><![CDATA[W rekordzie palenisko.nr_dom ma pojawić się wynik zapytania new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) AND dom.wiek = 'stary'). 

Tak się dzieje.
Ale pytanie jest jak sprawić by wraz ze zmianą wartości z 'stary' na 'młody' w polu dom.wiek uaktualniała mi się wartość w palenisko.nr_dom]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-07T10:51:51Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17862#p17862</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17861#p17861"/>
			<content type="html"><![CDATA[Musisz to dokładniej opisać co ma się pojawić w polu "palenisko.nr_dom" gdy zapytanie select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) znajdzie więcej niż jeden rekord. Z czego część rekordów będzie miała pole "dom.wiek" ustawione na "stary", a część na "młody"

Najlepiej opisz to na jakimś przykładzie]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2016-11-07T10:34:51Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17861#p17861</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17860#p17860"/>
			<content type="html"><![CDATA[Dzięki wielkie właśnie o to mi chodziło.
Co do nakładających się obszarów będą one występować ale wtedy dodatkowo filtruje po kolumnie dom.wiek (stary,mlody) dom.wiek = 'stary'. Stąd pojawia się moje kolejne pytanie. Poniższy trigger działa ale jak zabezpieczyć się przed wypadkiem gdy:
Mam palenisko które znajduję się w np. w dom.id = '3' gdzie dom.wiek = 'mlody'. Zgodnie z wyzwalaczem palenisko.nr_dom zostanie puste. Ale co w wypadku gdy zmienie dom.wiek = 'stary' lub w drugą strone.

Dzięki ;)]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-07T09:49:35Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17860#p17860</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17859#p17859"/>
			<content type="html"><![CDATA[Zerknij czy to rozwiązuje twój problem 
Założyłem, że polu "geom" w tabeli dom masz jakiś obszar, a w palenisko jakieś punkty
Ponieważ nie wiem czy w tabeli dom kontrolujesz by nie było rekordów z nakładającymi się obszarami to dodałem wszędzie limit 1 by zwracał zawsze tylko jeden rekord (pomiń to jeśli tak nie jest)

UWAGA ST_WITHIN zwraca true jeśli punkt zawiera się dokładnie w obszarze, jeśli punkt jest na krawędzi obszaru to dostaniesz false

create or replace function f_palenisko_tr() RETURNS trigger AS
$body$BEGIN
        if tg_op = 'INSERT' then
           IF NEW.geom IS not NULL THEN
               new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) limit 1);
           END IF;
           RETURN NEW;
        elsif tg_op = 'UPDATE' then
            IF (old.geom is null and NEW.geom IS not NULL) or st_distance (old.geom,new.geom) >0 THEN
               new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) limit 1);
            END IF;
            RETURN NEW;
        END IF;
END;$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_palenisko BEFORE INSERT OR UPDATE ON palenisko FOR EACH ROW EXECUTE PROCEDURE f_palenisko_tr();]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2016-11-07T08:09:21Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17859#p17859</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[TRIGGER i Zapytanie przestrzenne]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=17858#p17858"/>
			<content type="html"><![CDATA[Witam,
Mam następujący problem. W bazie mam tabele palenisko(id,nr_dom,geom) oraz dom(id,geom)
Chciałbym znaleźć w jakim poligonie znajduję się punkt.
To mi się udało.
UPDATE palenisko
SET nr_dom = dom.id
FROM dom
WHERE ST_WITHIN(palenisko.geom,dom.geom)

Chciałbym aby działo się to automatyczne za pomocą TRIGGERA. Niestety nie mam pojęcia jak to zrobić.
Dziękuje za każdą wskazówkę.]]></content>
			<author>
				<name><![CDATA[cogi94]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=2987</uri>
			</author>
			<updated>2016-11-06T14:16:26Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=17858#p17858</id>
		</entry>
</feed>
