Query table based on column value 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... |
+----+------+--------------------+
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)