I have a table of documents relating to several parcels of land. I have in this table the identifiers (numerals, sometimes interspaced by "/"- text field, delimited by commas). I also have a land division table that contains these same identifiers and geometries.
What I want is to assign geometry to the entries in this table, based on these identifiers. Where there is just one, no problem exists, because a simple "=" will do the trick. Where the problem is at, is in cases where there is more than one parcel I want to join them in one polygon.
What I have so far only produces an empty row:
SELECT o.*, st_union(a.geometry) as geometry
FROM "documents" o, parcels a
WHERE o.identifier like '%' || a.identifier || '%'
EDIT:
To clarify, my table that I wish to add geometry to (it has none to begin with) has a structure like this:
identifier | f1 | f2 | f3 | f4 ...
201,202,203/1,203/2 | x | x2 | x3 | x4 ...
206 | x | x2 | x3 | x4 ...
While the parcel table is always like:
identifier | f1 | f2 | f3 | geometry ...
206 | x | x2 | x3 | binaryGeometry ...
In the example above, parcels 201,202,203/1,203/2 need to be merged into one polygon. I feel like this kind of task should be easily achievable, yet I can't make it work.
like
, play more with regular expressions.