1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
/* :file: This file is part of the pgRouting project.
:copyright: Copyright (c) 2025-2026 pgRouting developers
:license: Creative Commons Attribution-Share Alike 3.0 https://creativecommons.org/licenses/by-sa/3.0 */
BEGIN;
SELECT CASE WHEN min_version('3.8.0') THEN plan(4) ELSE plan(3) END;
CREATE SCHEMA houses;
CREATE TABLE houses.simplenet (
id serial PRIMARY KEY,
house_id integer,
road_id integer NOT NULL,
component BIGINT,
geom public.geometry(LineString,2180) NOT NULL,
CONSTRAINT enforce_dims_geom CHECK ((public.st_ndims(geom) = 2)),
CONSTRAINT enforce_srid_geom CHECK ((public.st_srid(geom) = 2180))
);
CREATE INDEX simplenet_geom_gidx ON houses.simplenet USING gist (geom);
INSERT INTO houses.simplenet (road_id, geom) VALUES
(3123, '010200002084080000090000009E4FAC37BE15104170D5C1E469331B41EE4EF9273B1610412093A8BE55331B41EE322587E016104130995D1265331B41D035DAFB2B17104100AA967956341B418C12FF7E5017104130AE5E5FE3341B41629D49D99A171041C0A4D90228361B41E220A6D2AE171041A000B7C03F361B4156E20D22D117104100DCF57D3E361B411032CCC42D1B10416091A6F3EE341B41'),
(3124, '0102000020840800000B0000007E1B3C4FC40A1041707FFFC64A371B4181B74F32000C104150F83D3AA8361B416CC34CF55D0D1041C0771EB209361B417E25DCBDFC0D10419090F914D6351B41650C1EAE9A0E104130CFA8B7B7351B4155F75A1C641010417059BF592C351B41FA1DEB7187111041E0C064BBD8341B41944CE7957A12104120A70CE7A1341B41A220271D8914104110356671CF331B4199D042E63D151041205987AD8A331B419E4FAC37BE15104170D5C1E469331B41'),
(3155, '010200002084080000030000006A367411BC1910419047A0C595321B41F8CE972A8E17104190B22AB959331B41EE322587E016104130995D1265331B41'),
(3156, '010200002084080000090000002205178EBB1B104100FEDA5827331B412E210491431B104150D3DD3025321B416A367411BC1910419047A0C595321B412205178EBB1B104100FEDA5827331B416440E156701C1041D09F808D2F341B4180601352DE1B1041F0772E345A341B41DEA8489B991B1041E0851EE191341B41DE8BB9D46B1B1041004C3B14CA341B411032CCC42D1B10416091A6F3EE341B41'),
(3157, '0102000020840800001700000008B69F8512351041B07566350D3F1B4190A0C357ED33104150DE1A65563D1B418506C174A8331041808307ED2A3D1B41BA80AD9D8E32104140F4B5ED083C1B416035D5E4843110415027964B793B1B41E3DC72CAFA2F1041C06F793FA43A1B41FAF8ACF6192D1041A01A55A192381B4190472FCCF5291041906C843846361B411461FB89D0231041E0DD41BDD6301B41DCD49BE216231041607D264549301B414CE523867F221041509D860F43301B418F09CAE514221041807991AB62301B414067E2B4AE211041709E641282301B41E26D4201C42010416069679510311B41CB1643997F20104160FE72F129311B41E0A667F442201041906707532B311B41728E5CCEAC1F1041D0DEA72E1F311B41103001C72E1F104170DF4A3E1C311B412F762ABE921E1041202930DC30311B41CA768984091E104150B2F5C75A311B4189EC377E931D104170BBAFCC83311B41623D4BEE601C1041A02E3DF8C2311B412E210491431B104150D3DD3025321B41'),
(11629, '01020000208408000010000000E4559FCFC30A104160A6C9C3D0361B41C85D3E9C340A104100594D90E1361B41E03537D06C09104130DD0337FC361B416A55E1C3B9081041D01D1D5F26371B41D0969F7964061041B0CD8F05BE371B41C16FEDE4E004104160FF9F7409381B412A0C8EF67B03104100BABB9747381B416C8F42F4F402104190BD67344A381B41DEBD390FEE0010416068FFB82E381B41D1D66FF12D001041E0CD3A5825381B4115579F3493FF0F41A06CA0312A381B417A0A35F6F4FE0F41F001B9853E381B41AAAAC4B7A7FD0F4130E231608A381B4117FC17DCBAFC0F41F0BA5AE3B4381B4117A3750153FB0F4150400C65E5381B419E0D9D39F5F90F41E0573C430C391B41'),
(27140, '0102000020840800000B0000006440E156701C1041D09F808D2F341B41962C2D8FCF1C104150431B6DCD341B413C21BC3B301D1041306941E187351B41043C43DA721D10414016C6153C361B41AC55646C891D104170F19E2F59361B41EB9D4768B01D10416089EEA05B361B412FC6993F6D1F104170A5E43EA9351B412071A6FF9A1F1041E060AB0C8C351B418B2FE98AB81F1041C00B1C4164351B41E6F49314CD1F1041E06552AF34351B41C60EE257CC1F104140957050EA341B41'),
(27144, '01020000208408000002000000B009023DB90A104170DD29D5EB371B419CA8C04DA60C10418060A9C78F381B41'),
(27148, '0102000020840800000600000001867F68CCF50F41903B02EF572E1B4102DE048279F70F41F002EF7431311B4130CA8702EBF80F41E01862A335331B41602026E4F5FA0F4110101BF91A361B41CEB0CD845EFB0F41F053807269361B417A0A35F6F4FE0F41F001B9853E381B41'),
(39074, '0102000020840800001300000030CA8702EBF80F41E01862A335331B412273D1D7E7F70F4110B3E39EE5321B4119AD3C8671F60F415039E5C49B321B41780B4850E1F40F41D03CF2F078321B41971B2558EBF10F4150ADD61C00321B41CC8BB9C909F00F4140C32FF3AF311B41F411E74CF4EC0F41307ECE3A61311B413D3AF6B941EC0F41C027DDD032311B4114D34A31EDEB0F415073BAD98C301B4197BBD4F6DBEA0F4100A1B87708301B41641FD7ABB5E80F411095039CD12F1B412AE9CD4340E60F41F0C8101101301B4171D7C741D6E20F41C060D7CD5C301B41305E5A54A3E00F41906F917B99301B414A58854463DD0F413013401EFA301B41EE415C7BF1DB0F41E032DCB475311B413B0CF20F0EDB0F4120020EA79B311B4153C10E7EB7D90F41006246C2A6311B418E21E616B6D70F4100B2B1DDBB311B41'),
(53368, '01020000208408000008000000E4453D7293031041E00E8BDB963B1B41D3148F6811041041908F08AE87391B4178950459AB041041A0DC3F9077391B41AE9F9C457C051041C095E8CD78391B411EF543057C061041A035FEE60E391B417E4D7AC816081041D0F557C28D381B41945FEE7D520A1041D0B261B472381B41CAA99E938F0A10417018DDB981381B41'),
(53372, '01020000208408000009000000D3148F6811041041908F08AE87391B41245A112986021041C07C149A8C391B418EE494D45CFE0F4140BD0B97673A1B41F7937D9594FD0F41E05DA26EB43A1B413B8DCE5EABFC0F41F095BE2CCF3A1B419CE05DD625FC0F4190EA86EDCB3A1B419DFB0F5F67FB0F41E04835428D3A1B41B82B1B46D4FA0F41A0E44050673A1B419E0D9D39F5F90F41E0573C430C391B41'),
(53617, '0102000020840800000E0000009E0D9D39F5F90F41E0573C430C391B41490F4BA9DEF50F41B025D25A8E391B416BEBD9795BF30F41505252E7D3391B410A3CFDF781E90F41309B13370B3C1B41EFD18A0E4EE50F41D092264AF13C1B41AE5FA42B0BE30F4180BFC24C623D1B41A701E42A2AE20F41707B84745D3D1B41830290E13CE10F41B06F96E4423D1B41B697C4F243E00F4120CA4692603D1B41E9C16A1F46DE0F4190592ED6D03D1B416E443DD827DD0F41A0D45A68EF3D1B41136EA5BD30DC0F4100DDA4DCF83D1B4120574A6F7CDA0F4150A19C173B3E1B41D83CB7FE21D30F41809557F8283F1B41'),
(59137, '0102000020840800001C0000003343C1036D0B104180664E4E85471B41707EB0604D0B1041F095F09245471B4174994D7AC5091041E05585984C451B41A4BFB7246C09104170F80C6A5B441B412575C0F2FB081041E01B9A7F34421B4119F87927B008104170F6787884401B41C877EC3ED7081041D070E71A2D3F1B41D2B5DC063509104110111819C33C1B41C73677C0CA0910418050F29AE73A1B41CAA99E938F0A10417018DDB981381B419969B3F5A60A1041D0DF17CC38381B41B009023DB90A104170DD29D5EB371B4152244E84C30A1041D0E6557986371B417E1B3C4FC40A1041707FFFC64A371B41E4559FCFC30A104160A6C9C3D0361B4129F6CBD7CD0A10417064357B22361B41E2C120F6D70A104130BFC8E5C1351B41FE1E00EDEB0A1041A0D0A3814B351B4185A43F1E150B1041A0B8D0FCD7341B415AB19FC7350B1041403112B877341B41747D5B214A0B104160AD70FA13341B41AE1E93FA4D0B104180D4C67BC2331B41AA7D9335470B104100B9B2A377331B41D228C7A3DA0A104110992EF693311B41DAD535E0750A104190865C07A82F1B41AC629EB25C0A1041106A101E8E2E1B4186FB0FC3170A1041D079ADA41D2C1B417585AF49C8091041E07729C1DE281B41');
SELECT is((SELECT count(*)::INTEGER FROM houses.simplenet), 14, '14 original edges');
CREATE TABLE houses.input_points(
id SERIAL NOT NULL PRIMARY KEY,
geom geometry(Point,2180) NOT NULL,
CONSTRAINT enforce_dims_geom CHECK ((public.st_ndims(geom) = 2)),
CONSTRAINT enforce_srid_geom CHECK ((public.st_srid(geom) = 2180))
);
CREATE INDEX input_points_geom_gidx ON houses.input_points USING gist (geom);
INSERT INTO houses.input_points (geom) VALUES
('010100002084080000544435CF4F091041501BB34633371B41'), ('010100002084080000E2E942DAC3111041709C079D53351B41'),
('0101000020840800005083B0CECB15104150462BE427331B41'), ('0101000020840800009CBA36C51F051041E0172FFCAB371B41'),
('01010000208408000089AB6AB9B31110413055649F8B351B41'), ('010100002084080000D4774B88AF191041B0B5B14BF7351B41'),
('01010000208408000037AD0ADAC7171041F0512CFDF3351B41'), ('010100002084080000C4757BBEA7081041202A8FEEBB391B41'),
('010100002084080000B09DF8F8C30C10417018C2DC9F361B41'), ('010100002084080000622FFAC1D30E1041A08CFFD8E3351B41'),
('0101000020840800009CFBE9DD53131041E0E179536F341B41'), ('01010000208408000040150288AB171041C06D6641FB311B41'),
('010100002084080000A4D095E81B16104130F491EA3B341B41'), ('010100002084080000C622A5CA4F05104130974E9E43391B41'),
('010100002084080000088EE7BF33141041F061046D63341B41'), ('010100002084080000F185E18E9F191041D04E043F1B361B41'),
('0101000020840800002827AEE02B0A104170781832BB381B41'), ('01010000208408000054B934847F0C1041002CFA3E2B361B41'),
('0101000020840800001E0C78B587031041A0F6E23DCF3B1B41'), ('0101000020840800003DB1EDAF73151041E0DEAEA6F3331B41'),
('010100002084080000EDB37C0307FD0F412060C52ACF391B41'), ('0101000020840800006A1E4BE1AB1C104180107DDB4B351B41'),
('010100002084080000747528E5331B104170F1113473341B41'), ('0101000020840800002390AEB2470A104180476C5A3F381B41'),
('010100002084080000E8D677BC63121041F089818A2B351B41'), ('01010000208408000039A9CF986B0A1041600CCE88B3371B41'),
('010100002084080000302B68D36B1110410065A6D16B351B41'), ('010100002084080000E3DA2CC6A7051041B01C50CAAF371B41'),
('0101000020840800005ADD30738B0F104180FAC29D8F361B41'), ('0101000020840800008A8D60838F121041B082E576CB341B41'),
('01010000208408000094A0E2F61B0810416013FFE903371B41'), ('010100002084080000F4F174B77307104150DFE907FC371B41'),
('010100002084080000360F77EBE3061041501CDB2077381B41'), ('0101000020840800003190C1DE6B051041B0171D617B371B41'),
('01010000208408000026BCAD7C370C104130D975E1B7361B41'), ('010100002084080000FFA8EC75EFFD0F4140BC7AA103381B41'),
('01010000208408000090DB9AE5B70A1041402EA6C2D3381B41'), ('0101000020840800005D63A5911F0A104100E49FC4B7381B41'),
('010100002084080000AC24BD889708104110E40F05C43A1B41'), ('0101000020840800001C6C11B5130B1041903F099443361B41'),
('010100002084080000C63BD9D7130E104130F3699133351B41'), ('010100002084080000B64CF8EB330F1041B0D145F02B361B41'),
('010100002084080000073164CE2F111041C0643E3C3F341B41'), ('0101000020840800009A2D8FB1E7151041000D8526A7321B41'),
('01010000208408000046E421DBAB171041702DFD285F351B41'), ('010100002084080000C0608271B7171041A0FCAA6A6B321B41'),
('0101000020840800000ECEE2DC77181041F06171819F351B41'), ('010100002084080000946302F7D31E1041B022FBD87F351B41'),
('0101000020840800006AFCC4C7FB19104190210FA01F351B41'), ('010100002084080000D0A88DD3870A1041B0B82D5663371B41'),
('01010000208408000082480BF00F20104130D19F731F351B41'), ('01010000208408000037877FBFC717104160995657CF321B41'),
('010100002084080000AFFCA0EB030E10415055DF1F1F361B41'), ('0101000020840800002A489E970F131041C07D7803A8341B41'),
('0101000020840800008EB812C7C7091041C04FACB7CF391B41'), ('0101000020840800002E8F5CCA8B1010410013A83557351B41'),
('010100002084080000BA9401D93B071041006195EB27371B41'), ('010100002084080000E8D9E7F78B1A1041F038B651CF341B41'),
('0101000020840800000B7941E06B0A1041402641F143361B41'), ('010100002084080000B8311E8FD70B1041203B89A17F351B41'),
('0101000020840800009C02F17A070B104190E590B847361B41'), ('010100002084080000036C2CC587FC0F4150AF81D0E3381B41'),
('01010000208408000068D729B2CB0C104190C8EB803B351B41'), ('010100002084080000B1D9318C1B101041007EB5366B351B41'),
('010100002084080000F49FCEB4D3071041E0485C4F63381B41'), ('0101000020840800000ACE01D57F101041A014F03777341B41'),
('01010000208408000082F7D6E61F0F1041A0DE15CB27351B41'), ('01010000208408000027DE51F646FE0F416054E1DDD7381B41'),
('010100002084080000EA5039D2D3061041706A649E57371B41'), ('010100002084080000933FCAA21F16104180DC129563351B41'),
('0101000020840800003C97A6F9C3141041D0A448C56B341B41'), ('0101000020840800005E8AD2D9C30B1041B01D5BA5DB361B41'),
('010100002084080000E35D7FD207061041D060A9AAA3371B41'), ('010100002084080000FD63A9FC83071041A03CFB9B87381B41'),
('0101000020840800003E3BD3C0230F10419028123233351B41'), ('0101000020840800000A0CC2D0BB08104170048F70A7371B41'),
('01010000208408000093D09BF8DF051041A0E1C2A727381B41'), ('0101000020840800002651BCB3B30F104190F8D179D3351B41'),
('010100002084080000920E7D78CF05104130923227EF381B41'), ('01010000208408000053B3E4173FFC0F4190BDE8C6EB381B41'),
('010100002084080000F4BF0CD8DF1E104180348FC38B351B41'), ('010100002084080000AA5D01E9E7151041002E74B43F341B41');
SELECT is((SELECT count(*)::INTEGER FROM houses.input_points), 82, '82 houses');
INSERT INTO houses.simplenet (house_id, road_id, geom)
SELECT id, road_id, ST_ShortestLine(ip.geom, r.geom)
FROM
(SELECT road_id, st_transform(geom, 2180) geom FROM houses.simplenet) r,
(SELECT id, geom FROM houses.input_points) ip
WHERE road_id = (
SELECT rt.gid
FROM
(SELECT road_id gid, st_transform(geom, 2180) geom FROM houses.simplenet) rt,
(SELECT id, geom FROM houses.input_points) ipt
WHERE ipt.id = ip.id
ORDER BY ST_Distance(ipt.geom, rt.geom)
limit 1);
CREATE OR REPLACE FUNCTION issue_test()
RETURNS SETOF TEXT AS
$BODY$
BEGIN
IF NOT min_version('3.8.0') THEN
RETURN QUERY SELECT skip(1, 'pgr_separateCrossing new on 3.8.0');
RETURN;
END IF;
CREATE TABLE i623_r AS
SELECT *
FROM pgr_separateCrossing('SELECT id, geom FROM houses.simplenet');
RETURN QUERY
SELECT has_total_edges_vertices('i623_r', 3, 4);
END;
$BODY$ LANGUAGE plpgsql;
SELECT issue_test();
SELECT finish();
ROLLBACK;
|
