Postgis DB Structure: Identical tables for multiple years?

Question

I have multiple datasets for different years as a shapefile and converted them to postgres tables, which confronts me with the following situation:

I got tables boris2018, boris2017, boris2016 and so on. They all share an identical schema, for now let's focus on the following columns (example is one row out of the boris2018 table). The rows represent actual postgis geometry with certain properties.

 brw | brwznr |  gema   | entw | nuta
-----+--------+---------+------+------
 290 | 285034 | Sieglar | B    | W

the 'brwznr' column is an ID of some kind, but it does not seem to be entirely consistent across all years for each geometry. Then again, most of the tables contain duplicate information. The geometry should be identical in every year, although this is not guaranteed, too.

What I first did was to match the brwznr of each year with the 2018 data, adding a brw17, brw2016, ... column to my boris2018 data, like so:

 brw18 | brw17 | brw16 | brwznr |  gema   | entw | nuta
-------+-------+-------+--------+---------+------+------
  290  |  260  |  250  | 285034 | Sieglar | B    | W

This led to some data getting lost (because there was no matching brwznr found), some data wrongly matched (because some matching was wrong due to inconsistencies in the data) and it didn't feel right.

What I actually want to achieve is having fast queries that get me the different brw values for a certain coordinate, something around the lines of

SELECT ortst, brw, gema, gena 
FROM boris2018, boris2017, boris2016
WHERE ST_Intersects(geom,ST_SetSRID(ST_Point(7.130577, 50.80292),4326));

or

SELECT ortst, brw18, brw17, brw16, gema, gena 
FROM boris
WHERE ST_Intersects(geom,ST_SetSRID(ST_Point(7.130577, 50.80292),4326));

although this obviously wrong/has its deficits. Since I am new to databases in general, I can't really tell whether this is a querying problem or a database structure problem.

I hope anyone can help, your time and effort is highly appreciated! Tim

Answer 1

Have you tried using a CTE?

WITH j AS (
   SELECT ortst, brw, gema, gena FROM boris2016
UNION 
   SELECT ortst, brw, gema, gena FROM boris2017
UNION 
    SELECT ortst, brw, gema, gena FROM boris2018)
SELECT * FROM j
WHERE ST_Intersects(j.geom,ST_SetSRID(ST_Point(7.130577, 50.80292),4326));

Depending on your needs, you might wanna use UNION ALL. Note that this approach might not be fastest one when dealing with very large tables. If it is the case, consider merging the result of these three queries into another table and create an index using the geom field. Let me know in the comments if it is the case.