0

I've those tables on my PostGIS db.

poi_list
+----+------+-----------------+---------------+--------------------+
| id |  nr  |  source_layer   | source_column |     source_id      |
+----+------+-----------------+---------------+--------------------+
|  1 | 523A | "EGiB"."GESBZO" | "XIDENTYFIK1" | 152000300000052278 |
|  2 | 658h | "EGiB"."GESBZO" | "XIDENTYFIK1" | 152000300000050514 |
| .. | ..   | ..              | ..            |                 .. |
| 50 | 694S | "EGiB"."GESDZI" | "ID"          | 152000300000048731 |
| 50 | 223C | "EGiB"."GESDZI" | "ID"          | 152000300000051678 |
+----+------+-----------------+---------------+--------------------+

GESBZO
+----+-------+---------------------------+--------------------+-----+--------------------+
| id | FNB_N |            IG5            |    XIDENTYFIK1     | ... |        geom        |
+----+-------+---------------------------+--------------------+-----+--------------------+
|  1 | g     | 300207_5.0001.869/1.1_BUD | 152000300000052278 | ... | 010600002080080... |
|  2 | b     | 300207_5.0001.68/2.1_BUD  | 152000300000050514 | ... | 010600002080080... |
+----+-------+---------------------------+--------------------+-----+--------------------+

GESDZI
+----+---------------------+--------------------+-----+--------------------+
| id |         IG5         |         ID         | ... |        geom        |
+----+---------------------+--------------------+-----+--------------------+
|  1 | 300207_5.0001.411   | 152000300000048731 | ... | 010600002080080... |
|  2 | 300207_5.0001.410/2 | 152000300000051678 | ... | 010600002080080... |
+----+---------------------+--------------------+-----+--------------------+

Is there way to select geometries from other tables based on values from query. I want to get something like this:

poi_geoms
+----+------+--------------------+
| id |  nr  |        geom        |
+----+------+--------------------+
|  1 | 523A | 010600002080080... |
|  2 | 658h | 010600002080080... |
| .. | ..   |                 .. |
| 50 | 694S | 010600002080080... |
| 50 | 223C | 010600002080080... |
+----+------+--------------------+
0

1 Answer 1

0

You can try to create a view that gathers data from both tables. Example:

create view v_bzo as
select poi_list.id, poi_list.nr, "GESBZO".geom
from poi_list
join "GESBZO" 
on source_id = "XIDENTYFIK1" 
where source_layer='"EGiB"."GESBZO"';
--
create view v_dzi as
select poi_list.id, poi_list.nr, "GESDZI".geom
from poi_list
join "GESDZI" 
on source_id  = "ID" 
where source_layer='"EGiB"."GESDZI"';
--
create view poi_geoms
as 
select * from v_bzo
union
select * from v_dzi; 

With test data (without postGIS):

select * from poi_list;
 id |  nr  |  source_layer   | source_column |     source_id      
----+------+-----------------+---------------+--------------------
  1 | 523A | "EGiB"."GESBZO" | "XIDENTYFIK1" | 152000300000052278
  2 | 658h | "EGiB"."GESBZO" | "XIDENTYFIK1" | 152000300000050514
 50 | 694S | "EGiB"."GESDZI" | "ID"          | 152000300000048731
 50 | 223C | "EGiB"."GESDZI" | "ID"          | 152000300000051678
(4 rows)

select * from "GESBZO";
 id | FNB_N |            IG5            |    XIDENTYFIK1     |      geom       
----+-------+---------------------------+--------------------+-----------------
  1 | g     | 300207_5.0001.869/1.1_BUD | 152000300000052278 | 010600002080080
  2 | b     | 300207_5.0001.68/2.1_BUD  | 152000300000050514 | 010600002080080
(2 rows)

select * from "GESDZI";
 id |         IG5         |         ID         |      geom       
----+---------------------+--------------------+-----------------
  1 | 300207_5.0001.411   | 152000300000048731 | 010600002080080
  2 | 300207_5.0001.410/2 | 152000300000051678 | 010600002080080
(2 rows)

select * from poi_geoms order by id;
 id |  nr  |      geom       
----+------+-----------------
  1 | 523A | 010600002080080
  2 | 658h | 010600002080080
 50 | 223C | 010600002080080
 50 | 694S | 010600002080080
(4 rows)
1
  • Thanks alot, but to ask on stackexchange I simplified case. In real there is more possibile tables to select geom from than just two, so this shouldby some how dynamic I think. Mar 17, 2020 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.