azecHytQ

1CREATE TABLE public.gebaeude_polygone
2(
3  gid serial PRIMARY KEY,
4  id numeric,
5  geom geometry(LineString,25832)
6);
7
8
9CREATE TABLE ha_linien (
10 gid serial PRIMARY KEY,
11 name varchar(50),
12 geom geometry(Linestring, 25832)
13 );
14
15
16-- Procedure
17CREATE OR REPLACE FUNCTION check_ha_lines()
18RETURNS trigger AS
19$$
20BEGIN
21-- also tried IF NOT EXISTS instead of IF count >0
22IF (SELECT COUNT(*) FROM (SELECT gid FROM gebaeude AS g WHERE st_intersects(ST_ENDPOINT(NEW.geom), g.geom)) AS foo) <1
23
24THEN
25        RAISE Exception 'Line does not end at a building ';
26        --RETURN NULL;
27    END IF;
28
29
30
31RETURN NEW;
32END;
33$$
34LANGUAGE 'plpgsql';
35
36-- Trigger
37CREATE TRIGGER check_ha_lines_trigger
38BEFORE INSERT OR UPDATE ON ha_linien
39FOR EACH ROW EXECUTE PROCEDURE check_ha_lines();
40	
41Postgres-Version: "PostgreSQL 9.5.13 on x86_64-pc-linux-gnu (Debian 9.5.13-2.pgdg70+1), compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit"
42Postgis-Version: "2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
43QGIS-Version: 2.18.22
44	
45example building:
46LINESTRING (399622.008752193 5285661.23504165,399622.688532861 5285665.11365552,399620.989393588 5285665.41356583,399621.429250751 5285667.90267557,399623.048431379 5285667.62276057,399623.528278345 5285670.39177304,399622.288905144 5285670.6117046,399622.668781862 5285672.74094476,399633.933086617 5285670.76156211,399631.953705164 5285659.4855414,399629.784806594 5285659.87543057,399629.614859662 5285658.88578142,399626.796290549 5285659.37564352,399626.976232006 5285660.36529143,399622.008752193 5285661.23504165)
47
48example lines (all digitized in QGIS while snapping was activated):
49
50LINESTRING (399615.58284127 5285655.54518491,399622.008752193 5285661.23504165)
51LINESTRING (399621.303441253 5285653.56324476,399624.812305698 5285660.74417075)
52LINESTRING (399628.915893198 5285653.38306838,399627.609614462 5285655.6352731,399628.645628632 5285657.16677231,399628.382547276 5285659.09995503)
53LINESTRING (399635.942771917 5285656.85146365,399636.573389238 5285658.7883597,399640.402137258 5285662.61710772,399636.122948295 5285662.39188725,399633.6905672 5285659.32888884,399632.218495646 5285660.99398382)
54LINESTRING (399633.6905672 5285659.32888884,399631.953705164 5285659.4855414)
55LINESTRING (399640.137503205 5285666.53031342,399633.187016432 5285666.51139454)
56LINESTRING (399643.808596896 5285664.16549847,399639.394275648 5285664.61593941,399638.178085101 5285664.05288823,399636.218666996 5285664.90872603,399634.777255976 5285663.87271186,399632.924807673 5285665.01765952,399632.831270152 5285664.48480062)
57LINESTRING (399636.269341602 5285663.28713863,399632.610478752 5285663.22700951)
58	
59building:
60
61"010200000017000000273U3661030d30A"35412O307)TA!3021630132d30A315!FG310)TA275227#36523d30A321334wZ310)TAE20221526725d30A301o305371310)TA324376227134d30A&O333347310)TA2343773643436 (...)"
62
63lines:
64
65"010200000002000000323NZf372c30A353324+t310)TA275227#36523d30A321334wZ310)TA"
66"010200000002000000217#35112374c30A313/)266311)TA2343773643436d30A;31722231311)TA"
67"010200000005000000207d3413453d30A9201|253312)TA330E2513477d30A206P=335311)TA26d03241?d30Ay345205307311)TA30301c20Gd30Az?20372311)TA34116{273Gd30A01o275260311)TA"
68"010200000002000000212W324T376c30A@O344342305)TA273U3661030d30A"35412O307)TA"
69"010200000002000000306M271625d30A277314d305)TA23720315?#d30A\~240/307)TA"
70"010200000004000000263503372513d30A?1204X305)TA10306>p.d30A200P250350305)TA23254372252d30A301e254J306)TA%y2722071d30A315251e306306)TA"
71"010200000006000000_00f305Od30Aaa~6306)TAf214&KRd30AB|t262306)TA216336311233ad30Ae261~247307)TAa(346}Pd30AE25624231307)TAH14$303Fd30A30320314325306)TA231R275337@d30AUn235?307)TA"
72"010200000002000000H14$303Fd30A30320314325306)TA&26230320?d30A<3423337306)TA"
73"010200000002000000347243315214`d30A267247360241310)TAK<201277Dd30A+260272240310)TA"
74"0102000000070000003232300<od30A34520622712310)TA335376274223]d30A%215k'310)TA314360[266Xd30AU205b03310)TA260=352337Pd30A5221(:310)TA23437535033Kd30A324202332367307)TA^31000263Cd30AfU! (...)"
75"010200000002000000361H31623Qd30A262z`322307)TA207W!qBd30A341R207316307)TA"