Coverage for gws-app/gws/plugin/alkis/data/index.py: 88%
571 statements
« prev ^ index » next coverage.py v7.17.0, created at 2026-10-23 20:06 +0200
1"""ALKIS index tables: storage, search and loading of Flurstuecke and addresses."""
3from collections.abc import Iterable
5import re
6import datetime
8from sqlalchemy.dialects.postgresql import JSONB
10import gws
11import gws.lib.shape
12import gws.config.util
13import gws.lib.crs
14import gws.plugin.postgres.provider
15import gws.base.database.util
16import gws.lib.sa as sa
18from . import types as dt
20TABLE_PLACE = 'place'
21TABLE_FLURSTUECK = 'flurstueck'
22TABLE_BUCHUNGSBLATT = 'buchungsblatt'
23TABLE_LAGE = 'lage'
24TABLE_PART = 'part'
26TABLE_INDEXFLURSTUECK = 'indexflurstueck'
27TABLE_INDEXLAGE = 'indexlage'
28TABLE_INDEXBUCHUNGSBLATT = 'indexbuchungsblatt'
29TABLE_INDEXPERSON = 'indexperson'
30TABLE_INDEXGEOM = 'indexgeom'
33class Object(gws.Node):
34 """ALKIS index.
36 Manages the index tables in the index schema, reports their status, runs
37 Flurstueck and address searches against them and loads the found objects
38 with their related data.
39 """
41 VERSION = '85'
42 """Index version, part of the table names."""
44 TABLES_BASIC = [
45 TABLE_PLACE,
46 TABLE_FLURSTUECK,
47 TABLE_LAGE,
48 TABLE_PART,
49 TABLE_INDEXFLURSTUECK,
50 TABLE_INDEXLAGE,
51 TABLE_INDEXGEOM,
52 ]
53 """Tables of the basic index."""
55 TABLES_BUCHUNG = [
56 TABLE_BUCHUNGSBLATT,
57 TABLE_INDEXBUCHUNGSBLATT,
58 ]
59 """Tables with land register data."""
61 TABLES_EIGENTUEMER = [
62 TABLE_BUCHUNGSBLATT,
63 TABLE_INDEXBUCHUNGSBLATT,
64 TABLE_INDEXPERSON,
65 ]
66 """Tables with owner data."""
68 ALL_TABLES = TABLES_BASIC + TABLES_BUCHUNG + TABLES_EIGENTUEMER
69 """All index tables."""
71 db: gws.plugin.postgres.provider.Object
72 """Database provider."""
73 crs: gws.Crs
74 """CRS of the geometries."""
75 schema: str
76 """Schema of the index tables."""
77 excludeGemarkung: set[str]
78 """Gemarkung numbers excluded from indexing."""
79 gemarkungFilter: set[str]
80 """Gemarkung codes searches are restricted to."""
82 saMeta: sa.MetaData
83 """SQLAlchemy metadata for the index tables."""
84 tables: dict[str, sa.Table]
85 """Index tables by table id."""
87 columnDct = {}
88 """Column definitions by table id."""
90 def __getstate__(self):
91 """Return the state for pickling, without the SQLAlchemy objects."""
93 return gws.u.omit(vars(self), 'saMeta', 'tables', 'columnDct')
95 def configure(self):
96 gws.config.util.configure_database_provider_for(self, ext_type='postgres')
97 self.crs = gws.lib.crs.get(self.cfg('crs'))
98 self.schema = self.cfg('schema', default='public')
99 self.excludeGemarkung = set(self.cfg('excludeGemarkung', default=[]))
100 self.gemarkungFilter = set(self.cfg('gemarkungFilter', default=[]))
101 self._init_tables()
103 def activate(self):
104 self._init_tables()
106 def _init_tables(self):
107 """Create the SQLAlchemy metadata and the column definitions of the index tables."""
109 self.saMeta = sa.MetaData(schema=self.schema)
110 self.tables = {}
112 self.columnDct = {
113 TABLE_PLACE: [
114 sa.Column('uid', sa.Text, primary_key=True),
115 sa.Column('data', JSONB),
116 ],
117 TABLE_FLURSTUECK: [
118 sa.Column('uid', sa.Text, primary_key=True),
119 sa.Column('rc', sa.Integer),
120 sa.Column('fshistoric', sa.Boolean),
121 sa.Column('data', JSONB),
122 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
123 ],
124 TABLE_BUCHUNGSBLATT: [
125 sa.Column('uid', sa.Text, primary_key=True),
126 sa.Column('rc', sa.Integer),
127 sa.Column('data', JSONB),
128 ],
129 TABLE_LAGE: [
130 sa.Column('uid', sa.Text, primary_key=True),
131 sa.Column('rc', sa.Integer),
132 sa.Column('data', JSONB),
133 ],
134 TABLE_PART: [
135 sa.Column('n', sa.Integer, primary_key=True),
136 sa.Column('fs', sa.Text, index=True),
137 sa.Column('uid', sa.Text, index=True),
138 sa.Column('beginnt', sa.DateTime),
139 sa.Column('endet', sa.DateTime),
140 sa.Column('kind', sa.Integer),
141 sa.Column('name', sa.Text),
142 sa.Column('parthistoric', sa.Boolean),
143 sa.Column('data', JSONB),
144 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
145 ],
146 TABLE_INDEXFLURSTUECK: [
147 sa.Column('n', sa.Integer, primary_key=True),
148 sa.Column('fs', sa.Text, index=True),
149 sa.Column('fshistoric', sa.Boolean, index=True),
150 sa.Column('land', sa.Text, index=True),
151 sa.Column('land_t', sa.Text, index=True),
152 sa.Column('landcode', sa.Text, index=True),
153 sa.Column('regierungsbezirk', sa.Text, index=True),
154 sa.Column('regierungsbezirk_t', sa.Text, index=True),
155 sa.Column('regierungsbezirkcode', sa.Text, index=True),
156 sa.Column('kreis', sa.Text, index=True),
157 sa.Column('kreis_t', sa.Text, index=True),
158 sa.Column('kreiscode', sa.Text, index=True),
159 sa.Column('gemeinde', sa.Text, index=True),
160 sa.Column('gemeinde_t', sa.Text, index=True),
161 sa.Column('gemeindecode', sa.Text, index=True),
162 sa.Column('gemarkung', sa.Text, index=True),
163 sa.Column('gemarkung_t', sa.Text, index=True),
164 sa.Column('gemarkungcode', sa.Text, index=True),
165 sa.Column('amtlicheflaeche', sa.Float, index=True),
166 sa.Column('geomflaeche', sa.Float, index=True),
167 sa.Column('flurnummer', sa.Text, index=True),
168 sa.Column('zaehler', sa.Text, index=True),
169 sa.Column('nenner', sa.Text, index=True),
170 sa.Column('flurstuecksfolge', sa.Text, index=True),
171 sa.Column('flurstueckskennzeichen', sa.Text, index=True),
172 sa.Column('x', sa.Float, index=True),
173 sa.Column('y', sa.Float, index=True),
174 ],
175 TABLE_INDEXLAGE: [
176 sa.Column('n', sa.Integer, primary_key=True),
177 sa.Column('fs', sa.Text, index=True),
178 sa.Column('fshistoric', sa.Boolean, index=True),
179 sa.Column('land', sa.Text, index=True),
180 sa.Column('land_t', sa.Text, index=True),
181 sa.Column('landcode', sa.Text, index=True),
182 sa.Column('regierungsbezirk', sa.Text, index=True),
183 sa.Column('regierungsbezirk_t', sa.Text, index=True),
184 sa.Column('regierungsbezirkcode', sa.Text, index=True),
185 sa.Column('kreis', sa.Text, index=True),
186 sa.Column('kreis_t', sa.Text, index=True),
187 sa.Column('kreiscode', sa.Text, index=True),
188 sa.Column('gemeinde', sa.Text, index=True),
189 sa.Column('gemeinde_t', sa.Text, index=True),
190 sa.Column('gemeindecode', sa.Text, index=True),
191 sa.Column('gemarkung', sa.Text, index=True),
192 sa.Column('gemarkung_t', sa.Text, index=True),
193 sa.Column('gemarkungcode', sa.Text, index=True),
194 sa.Column('lageuid', sa.Text, index=True),
195 sa.Column('lagehistoric', sa.Boolean, index=True),
196 sa.Column('strasse', sa.Text, index=True),
197 sa.Column('strasse_t', sa.Text, index=True),
198 sa.Column('hausnummer', sa.Text, index=True),
199 sa.Column('hausnummer_k', sa.Text, index=True),
200 sa.Column('x', sa.Float, index=True),
201 sa.Column('y', sa.Float, index=True),
202 ],
203 TABLE_INDEXBUCHUNGSBLATT: [
204 sa.Column('n', sa.Integer, primary_key=True),
205 sa.Column('fs', sa.Text, index=True),
206 sa.Column('fshistoric', sa.Boolean, index=True),
207 sa.Column('buchungsblattuid', sa.Text, index=True),
208 sa.Column('buchungsblattbeginnt', sa.DateTime, index=True),
209 sa.Column('buchungsblattendet', sa.DateTime, index=True),
210 sa.Column('buchungsblattkennzeichen', sa.Text, index=True),
211 sa.Column('buchungsblatthistoric', sa.Boolean, index=True),
212 ],
213 TABLE_INDEXPERSON: [
214 sa.Column('n', sa.Integer, primary_key=True),
215 sa.Column('fs', sa.Text, index=True),
216 sa.Column('fshistoric', sa.Boolean, index=True),
217 sa.Column('personuid', sa.Text, index=True),
218 sa.Column('personhistoric', sa.Boolean, index=True),
219 sa.Column('name', sa.Text, index=True),
220 sa.Column('name_t', sa.Text, index=True),
221 sa.Column('vorname', sa.Text, index=True),
222 sa.Column('vorname_t', sa.Text, index=True),
223 ],
224 TABLE_INDEXGEOM: [
225 sa.Column('n', sa.Integer, primary_key=True),
226 sa.Column('fs', sa.Text, index=True),
227 sa.Column('fshistoric', sa.Boolean, index=True),
228 sa.Column('geomflaeche', sa.Float, index=True),
229 sa.Column('x', sa.Float, index=True),
230 sa.Column('y', sa.Float, index=True),
231 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
232 ],
233 }
235 ##
237 def table(self, table_id: str) -> sa.Table:
238 """Return an index table.
240 Args:
241 table_id: Table id, one of the ``TABLE_`` constants.
243 Returns:
244 The table object.
245 """
247 if table_id not in self.tables:
248 table_name = f'alkis_{self.VERSION}_{table_id}'
249 self.tables[table_id] = sa.Table(
250 table_name,
251 self.saMeta,
252 *self.columnDct[table_id],
253 schema=self.schema,
254 )
255 return self.tables[table_id]
257 def table_size(self, table_id) -> int:
258 """Return the number of rows in an index table.
260 Args:
261 table_id: Table id.
263 Returns:
264 The number of rows, 0 if the table does not exist.
265 """
267 sizes = self._table_size_map([table_id])
268 return sizes.get(table_id, 0)
270 def _table_size_map(self, table_ids):
271 """Return a dict of table id to the number of rows."""
273 d = {}
275 with self.db.begin():
276 for table_id in table_ids:
277 try:
278 with self.db.begin(nested=True):
279 d[table_id] = self.db.count(self.table(table_id))
280 except sa.exc.SQLAlchemyError:
281 d[table_id] = 0
283 return d
285 def has_schema(self) -> bool:
286 """Check whether the index schema exists.
288 Returns:
289 ``True`` if the schema exists.
290 """
292 return self.db.has_schema(self.schema)
294 def has_table(self, table_id: str) -> bool:
295 """Check whether an index table exists and has data.
297 Args:
298 table_id: Table id.
300 Returns:
301 ``True`` if the table has at least one row.
302 """
304 return self.table_size(table_id) > 0
306 def status(self) -> dt.IndexStatus:
307 """Return the index status.
309 Returns:
310 The status, computed from the number of rows in the index tables.
311 """
313 sizes = self._table_size_map(self.ALL_TABLES)
314 s = dt.IndexStatus(
315 basic=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BASIC),
316 buchung=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BUCHUNG),
317 eigentuemer=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_EIGENTUEMER),
318 )
319 s.complete = s.basic and s.buchung and s.eigentuemer
320 s.missing = all(v == 0 for v in sizes.values())
321 gws.log.debug(f'ALKIS: table sizes {sizes!r}')
322 return s
324 def drop_table(self, table_id: str):
325 """Drop an index table, if it exists.
327 Args:
328 table_id: Table id.
329 """
331 with self.db.begin() as conn:
332 self._drop_table(conn, table_id)
334 def drop(self):
335 """Drop all index tables."""
337 with self.db.begin() as conn:
338 for table_id in self.ALL_TABLES:
339 self._drop_table(conn, table_id)
341 def _drop_table(self, conn, table_id):
342 """Drop an index table using an open connection."""
344 tab = self.table(table_id)
345 conn.execute(sa.text(f'DROP TABLE IF EXISTS {self.schema}.{tab.name}'))
347 def create_table(self, table_id: str, values: list[dict]):
348 """Create an index table and fill it with rows.
350 Args:
351 table_id: Table id.
352 values: Rows as dicts of column names and values.
353 """
355 tab = self.table(table_id)
357 with self.db.begin() as conn:
358 self.saMeta.create_all(conn.saConn, tables=[tab])
359 if values:
360 conn.execute(sa.insert(tab), values)
362 ##
364 _defaultLand: dt.EnumPair = None
365 """Cached default Land."""
367 def default_land(self):
368 """Return the Land of a Gemarkung in the index.
370 The value is cached once found.
372 Returns:
373 The Land as an ``EnumPair``, or ``None`` if there are no Gemarkungen.
374 """
376 if self._defaultLand:
377 return self._defaultLand
379 with self.db.begin() as conn:
380 sel = sa.select(self.table(TABLE_PLACE)).where(sa.text("data->>'kind' = 'gemarkung'")).limit(1)
381 for r in conn.execute(sel):
382 p = unserialize(r.data)
383 self._defaultLand = p.land
384 gws.log.debug(f'ALKIS: defaultLand={vars(self._defaultLand)}')
385 return self._defaultLand
387 _strasseList: list[dt.Strasse] = []
388 """Cached street list."""
390 def strasse_list(self) -> list[dt.Strasse]:
391 """Return all streets in the index.
393 Each distinct combination of Gemeinde, Gemarkung and street name of
394 current Flurstuecke and Lage records is returned once. The list is cached.
396 Returns:
397 A list of streets.
398 """
400 if self._strasseList:
401 return self._strasseList
403 self._strasseList = []
405 indexlage = self.table(TABLE_INDEXLAGE)
406 cols = (
407 indexlage.c.gemeinde,
408 indexlage.c.gemeindecode,
409 indexlage.c.gemarkung,
410 indexlage.c.gemarkungcode,
411 indexlage.c.strasse,
412 )
414 sel = sa.select(*cols).where(~indexlage.c.fshistoric, ~indexlage.c.lagehistoric).group_by(*cols)
415 if self.gemarkungFilter:
416 sel = sel.where(indexlage.c.gemarkungcode.in_(self.gemarkungFilter))
418 with self.db.begin() as conn:
419 for r in conn.execute(sel):
420 self._strasseList.append(
421 dt.Strasse(
422 gemeinde=dt.EnumPair(r.gemeindecode, r.gemeinde),
423 gemarkung=dt.EnumPair(r.gemarkungcode, r.gemarkung),
424 name=r.strasse,
425 )
426 )
428 return self._strasseList
430 def find_adresse(self, q: dt.AdresseQuery) -> list[dt.Adresse]:
431 """Find addresses.
433 Args:
434 q: Search criteria and options.
436 Returns:
437 A list of addresses, in the order of the search, after applying offset and page size.
439 Raises:
440 ``gws.ResponseTooLargeError``: If there are more results than the configured limit.
441 ``gws.BadRequestError``: If the query is invalid, e.g. a house number without a street.
442 """
444 indexlage = self.table(TABLE_INDEXLAGE)
446 qo = q.options or dt.AdresseQueryOptions()
447 sel = self._make_adresse_select(q, qo)
449 lage_uids = []
450 adresse_map = {}
452 with self.db.begin() as conn:
453 for r in conn.execute(sel):
454 lage_uids.append(r[0])
456 if qo.limit and len(lage_uids) > qo.limit:
457 raise gws.ResponseTooLargeError(len(lage_uids))
459 if qo.offset:
460 lage_uids = lage_uids[qo.offset :]
461 if qo.pageSize:
462 lage_uids = lage_uids[: qo.pageSize]
464 sel = indexlage.select().where(indexlage.c.lageuid.in_(lage_uids))
466 for r in conn.execute(sel):
467 r = gws.u.to_dict(r)
468 uid = r['lageuid']
469 adresse_map[uid] = dt.Adresse(
470 uid=uid,
471 land=dt.EnumPair(r['landcode'], r['land']),
472 regierungsbezirk=dt.EnumPair(r['regierungsbezirkcode'], r['regierungsbezirk']),
473 kreis=dt.EnumPair(r['kreiscode'], r['kreis']),
474 gemeinde=dt.EnumPair(r['gemeindecode'], r['gemeinde']),
475 gemarkung=dt.EnumPair(r['gemarkungcode'], r['gemarkung']),
476 strasse=r['strasse'],
477 hausnummer=r['hausnummer'],
478 x=r['x'],
479 y=r['y'],
480 shape=gws.lib.shape.from_xy(r['x'], r['y'], crs=self.crs),
481 )
483 return gws.u.compact(adresse_map.get(uid) for uid in lage_uids)
485 def find_flurstueck(self, q: dt.FlurstueckQuery) -> list[dt.Flurstueck]:
486 """Find Flurstuecke and load them with their related data.
488 Args:
489 q: Search criteria and options.
491 Returns:
492 A list of Flurstuecke, in the order of the search, after applying offset and page size.
494 Raises:
495 ``gws.ResponseTooLargeError``: If there are more results than the configured limit.
496 ``gws.BadRequestError``: If the query is invalid, e.g. a house number without a street.
497 """
499 qo = q.options or dt.FlurstueckQueryOptions()
500 sel = self._make_flurstueck_select(q, qo)
502 fs_uids = []
504 with self.db.begin() as conn:
505 for r in conn.execute(sel):
506 uid = r[0].partition('_')[0]
507 if uid not in fs_uids:
508 fs_uids.append(uid)
510 if qo.limit and len(fs_uids) > qo.limit:
511 raise gws.ResponseTooLargeError(len(fs_uids))
513 if qo.offset:
514 fs_uids = fs_uids[qo.offset :]
515 if qo.pageSize:
516 fs_uids = fs_uids[: qo.pageSize]
518 fs_list = self._load_flurstueck(conn, fs_uids, qo)
520 return fs_list
522 def count_all(self, qo: dt.FlurstueckQueryOptions) -> int:
523 """Count all Flurstueck records in the index.
525 Args:
526 qo: Query options. Historic records are counted only with ``withHistorySearch``.
528 Returns:
529 The number of rows in the Flurstueck index table.
530 """
532 indexfs = self.table(TABLE_INDEXFLURSTUECK)
533 sel = sa.select(sa.func.count()).select_from(indexfs)
534 if not qo.withHistorySearch:
535 sel = sel.where(~indexfs.c.fshistoric)
537 with self.db.begin() as conn:
538 r = list(conn.execute(sel))
539 return r[0][0]
541 def iter_all(self, qo: dt.FlurstueckQueryOptions) -> Iterable[dt.Flurstueck]:
542 """Iterate over all Flurstuecke in the index.
544 Flurstuecke are loaded in chunks of ``qo.pageSize``, with a new
545 database connection for each chunk.
547 Args:
548 qo: Query options. ``pageSize`` must be set.
550 Yields:
551 Flurstuecke with their related data.
552 """
554 indexfs = self.table(TABLE_INDEXFLURSTUECK)
555 sel = sa.select(indexfs.c.fs).with_only_columns(indexfs.c.fs).order_by(indexfs.c.n)
556 if not qo.withHistorySearch:
557 sel = sel.where(~indexfs.c.fshistoric)
559 offset = 0
561 # NB the consumer might be slow, close connection on each chunk
563 while True:
564 with self.db.begin() as conn:
565 sel2 = sel.offset(offset).limit(qo.pageSize)
566 fs_uids = [r[0] for r in conn.execute(sel2)]
567 if not fs_uids:
568 break
569 fs_list = self._load_flurstueck(conn, fs_uids, qo)
571 yield from fs_list
573 offset += qo.pageSize
575 HAUSNUMMER_NOT_NULL_VALUE = '*'
576 """House number value that matches any non-empty house number."""
578 def _make_flurstueck_select(self, q: dt.FlurstueckQuery, qo: dt.FlurstueckQueryOptions):
579 """Build a select statement for matching Flurstueck uids."""
581 indexfs = self.table(TABLE_INDEXFLURSTUECK)
582 indexbuchungsblatt = self.table(TABLE_INDEXBUCHUNGSBLATT)
583 indexgeom = self.table(TABLE_INDEXGEOM)
584 indexlage = self.table(TABLE_INDEXLAGE)
585 indexperson = self.table(TABLE_INDEXPERSON)
587 where = []
589 has_buchungsblatt = False
590 has_geom = False
591 has_lage = False
592 has_person = False
594 where.extend(self._make_places_where(q, indexfs))
596 if q.uids:
597 where.append(indexfs.c.fs.in_(q.uids))
599 for f in 'flurnummer', 'flurstuecksfolge', 'zaehler', 'nenner':
600 val = getattr(q, f, None)
601 if val is not None:
602 where.append(getattr(indexfs.c, f.lower()) == val)
604 if q.flurstueckskennzeichen:
605 val = re.sub(r'[^0-9_]', '', q.flurstueckskennzeichen)
606 if not val:
607 raise gws.BadRequestError(f'invalid flurstueckskennzeichen {q.flurstueckskennzeichen!r}')
608 where.append(indexfs.c.flurstueckskennzeichen.like(val + '%'))
610 if q.flaecheVon:
611 try:
612 where.append(indexfs.c.amtlicheflaeche >= float(q.flaecheVon))
613 except ValueError:
614 raise gws.BadRequestError(f'invalid flaecheVon {q.flaecheVon!r}')
616 if q.flaecheBis:
617 try:
618 where.append(indexfs.c.amtlicheflaeche <= float(q.flaecheBis))
619 except ValueError:
620 raise gws.BadRequestError(f'invalid flaecheBis {q.flaecheBis!r}')
622 if q.buchungsblattkennzeichenList:
623 ws = []
625 for s in q.buchungsblattkennzeichenList:
626 w = gws.base.database.util.text_search_clause(
627 indexbuchungsblatt.c.buchungsblattkennzeichen,
628 s,
629 qo.buchungsblattSearchOptions,
630 )
631 if w is not None:
632 ws.append(w)
633 if ws:
634 has_buchungsblatt = True
635 where.append(sa.or_(*ws))
637 if q.strasse:
638 w = gws.base.database.util.text_search_clause(
639 indexlage.c.strasse_t,
640 strasse_key(q.strasse),
641 qo.strasseSearchOptions,
642 )
643 if w is not None:
644 has_lage = True
645 where.append(w)
647 if q.hausnummer:
648 if not has_lage:
649 raise gws.BadRequestError(f'hausnummer without strasse')
650 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:
651 where.append(indexlage.c.hausnummer != '')
652 else:
653 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))
655 if q.personName:
656 w = gws.base.database.util.text_search_clause(indexperson.c.name_t, text_key(q.personName), qo.nameSearchOptions)
657 if w is not None:
658 has_person = True
659 where.append(w)
661 if q.personVorname:
662 if not has_person:
663 raise gws.BadRequestError(f'personVorname without personName')
664 w = gws.base.database.util.text_search_clause(indexperson.c.vorname_t, text_key(q.personVorname), qo.nameSearchOptions)
665 if w is not None:
666 where.append(w)
668 if q.shape:
669 has_geom = True
670 where.append(gws.base.database.util.shape_intersects_clause(indexgeom.c.geom, q.shape, self.crs))
672 join = []
674 if has_buchungsblatt:
675 join.append([indexbuchungsblatt, indexbuchungsblatt.c.fs == indexfs.c.fs])
676 if not qo.withHistorySearch:
677 where.append(~indexbuchungsblatt.c.fshistoric)
678 where.append(~indexbuchungsblatt.c.buchungsblatthistoric)
680 if has_geom:
681 join.append([indexgeom, indexgeom.c.fs == indexfs.c.fs])
682 if not qo.withHistorySearch:
683 where.append(~indexgeom.c.fshistoric)
685 if has_lage:
686 join.append([indexlage, indexlage.c.fs == indexfs.c.fs])
687 if not qo.withHistorySearch:
688 where.append(~indexlage.c.fshistoric)
689 where.append(~indexlage.c.lagehistoric)
691 if has_person:
692 join.append([indexperson, indexperson.c.fs == indexfs.c.fs])
693 if not qo.withHistorySearch:
694 where.append(~indexperson.c.fshistoric)
695 where.append(~indexperson.c.personhistoric)
697 if not qo.withHistorySearch:
698 where.append(~indexfs.c.fshistoric)
700 sel = sa.select(indexfs.c.fs).distinct()
702 for tab, cond in join:
703 sel = sel.join(tab, cond)
705 sel = sel.where(*where)
707 return self._make_sort(sel, qo.sort, indexfs)
709 def _make_adresse_select(self, q: dt.AdresseQuery, qo: dt.AdresseQueryOptions):
710 """Build a select statement for matching Lage uids."""
712 indexlage = self.table(TABLE_INDEXLAGE)
713 where = []
715 where.extend(self._make_places_where(q, indexlage))
717 has_strasse = False
719 if q.strasse:
720 w = gws.base.database.util.text_search_clause(
721 indexlage.c.strasse_t,
722 strasse_key(q.strasse),
723 qo.strasseSearchOptions,
724 )
725 if w is not None:
726 has_strasse = True
727 where.append(w)
729 hausnummer_k = sa.collate(indexlage.c.hausnummer_k, 'C')
731 if q.hausnummer:
732 if not has_strasse:
733 raise gws.BadRequestError(f'hausnummer without strasse')
734 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:
735 where.append(indexlage.c.hausnummer != '')
736 elif q.bisHausnummer:
737 where.append(hausnummer_k >= _hausnummer_key_or_error(q.hausnummer))
738 else:
739 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))
741 if q.bisHausnummer:
742 if not has_strasse:
743 raise gws.BadRequestError(f'hausnummer without strasse')
744 where.append(hausnummer_k <= _hausnummer_key_or_error(q.bisHausnummer, upper=True))
746 if q.hausnummerNotNull:
747 if not has_strasse:
748 raise gws.BadRequestError(f'hausnummer without strasse')
749 where.append(indexlage.c.hausnummer != '')
751 if not qo.withHistorySearch:
752 where.append(~indexlage.c.fshistoric)
753 where.append(~indexlage.c.lagehistoric)
755 sel = sa.select(indexlage.c.lageuid).distinct()
757 sel = sel.where(*where)
759 return self._make_sort(sel, qo.sort, indexlage)
761 def _make_places_where(self, q: dt.FlurstueckQuery | dt.AdresseQuery, table: sa.Table):
762 """Build where clauses for place names and codes."""
764 where = []
765 land_code = ''
767 for f in 'land', 'regierungsbezirk', 'kreis', 'gemarkung', 'gemeinde':
768 val = getattr(q, f, None)
769 if val is not None:
770 where.append(getattr(table.c, f.lower() + '_t') == text_key(val))
772 val = getattr(q, f + 'Code', None)
773 if val is not None:
774 if f == 'land':
775 land_code = val
776 elif f == 'gemarkung' and len(val) <= 4:
777 if not land_code:
778 land = self.default_land()
779 if land:
780 land_code = land.code
781 val = land_code + val
783 where.append(getattr(table.c, f.lower() + 'code') == val)
785 if self.gemarkungFilter:
786 where.append(table.c.gemarkungcode.in_(self.gemarkungFilter))
788 return where
790 def _make_sort(self, sel, sort, table: sa.Table):
791 """Add the sort order to a select statement."""
793 if not sort:
794 return sel
796 order = []
797 for s in sort:
798 fn = sa.desc if s.reverse else sa.asc
799 order.append(fn(getattr(table.c, s.fieldName)))
800 sel = sel.order_by(*order)
802 return sel
804 def load_flurstueck(self, fs_uids: list[str], qo: dt.FlurstueckQueryOptions) -> list[dt.Flurstueck]:
805 """Load Flurstuecke with their related data.
807 Args:
808 fs_uids: Flurstueck uids.
809 qo: Query options, the display themes select the related data.
811 Returns:
812 A list of Flurstuecke in the order of ``fs_uids``. Missing and,
813 unless ``withHistoryDisplay`` is set, historic Flurstuecke are left out.
814 """
816 with self.db.begin() as conn:
817 return self._load_flurstueck(conn, fs_uids, qo)
819 def _load_flurstueck(self, conn, fs_uids, qo: dt.FlurstueckQueryOptions):
820 """Load Flurstuecke and related data using an open connection."""
822 with_lage = dt.DisplayTheme.lage in qo.displayThemes
823 with_gebaeude = dt.DisplayTheme.gebaeude in qo.displayThemes
824 with_nutzung = dt.DisplayTheme.nutzung in qo.displayThemes
825 with_festlegung = dt.DisplayTheme.festlegung in qo.displayThemes
826 with_bewertung = dt.DisplayTheme.bewertung in qo.displayThemes
827 with_buchung = dt.DisplayTheme.buchung in qo.displayThemes
828 with_eigentuemer = dt.DisplayTheme.eigentuemer in qo.displayThemes
830 tab = self.table(TABLE_FLURSTUECK)
831 sel = sa.select(tab).where(tab.c.uid.in_(set(fs_uids)))
833 hd = qo.withHistoryDisplay
835 fs_list = []
837 for r in conn.execute(sel):
838 fs = unserialize(r.data)
839 fs.geom = r.geom
840 fs_list.append(fs)
842 fs_list = self._remove_historic(fs_list, hd)
843 if not fs_list:
844 return []
846 fs_map = {fs.uid: fs for fs in fs_list}
848 for fs in fs_map.values():
849 fs.shape = gws.lib.shape.from_wkb_element(fs.geom, default_crs=self.crs)
851 fs.lageList = self._remove_historic(fs.lageList, hd) if with_lage else []
852 fs.gebaeudeList = self._remove_historic(fs.gebaeudeList, hd) if with_gebaeude else []
853 fs.buchungList = self._remove_historic(fs.buchungList, hd) if with_buchung else []
854 for bu in fs.buchungList:
855 bu.recs = [ref for ref in bu.recs if self._remove_historic([ref.buchungsstelle], hd)]
856 fs.buchungList = [bu for bu in fs.buchungList if bu.recs]
858 fs.bewertungList = []
859 fs.festlegungList = []
860 fs.nutzungList = []
862 if with_buchung:
863 bb_uids = set(bu.buchungsblattUid for fs in fs_map.values() for bu in fs.buchungList)
865 tab = self.table(TABLE_BUCHUNGSBLATT)
866 sel = sa.select(tab).where(tab.c.uid.in_(bb_uids))
867 bb_list = [unserialize(r.data) for r in conn.execute(sel)]
868 bb_list = self._remove_historic(bb_list, hd)
870 for bb in bb_list:
871 bb.buchungsstelleList = self._remove_historic(bb.buchungsstelleList, hd)
872 bb.namensnummerList = self._remove_historic(bb.namensnummerList, hd) if with_eigentuemer else []
873 for nn in bb.namensnummerList:
874 nn.personList = self._remove_historic(nn.personList, hd)
875 for pe in nn.personList:
876 pe.anschriftList = self._remove_historic(pe.anschriftList, hd)
878 bb_map = {bb.uid: bb for bb in bb_list}
880 for fs in fs_map.values():
881 for bu in fs.buchungList:
882 bu.buchungsblatt = bb_map.get(bu.buchungsblattUid)
883 fs.buchungList = [bu for bu in fs.buchungList if bu.buchungsblatt]
885 if with_nutzung or with_festlegung or with_bewertung:
886 tab = self.table(TABLE_PART)
887 sel = sa.select(tab).where(tab.c.fs.in_(list(fs_map)))
888 if not qo.withHistorySearch:
889 sel = sel.where(~tab.c.parthistoric)
890 pa_list = [unserialize(r.data) for r in conn.execute(sel)]
891 pa_list = self._remove_historic(pa_list, hd)
893 for pa in pa_list:
894 fs = fs_map[pa.fs]
895 if pa.kind == dt.PART_NUTZUNG and with_nutzung:
896 fs.nutzungList.append(pa)
897 if pa.kind == dt.PART_FESTLEGUNG and with_festlegung:
898 fs.festlegungList.append(pa)
899 if pa.kind == dt.PART_BEWERTUNG and with_bewertung:
900 fs.bewertungList.append(pa)
902 return gws.u.compact(fs_map.get(uid) for uid in fs_uids)
904 _historicKeys = ['vorgaengerFlurstueckskennzeichen']
905 """Record attributes removed when history is not displayed."""
907 def _remove_historic(self, objects, with_history_display: bool):
908 """Remove historic objects and records, unless history is displayed."""
910 if with_history_display:
911 return objects
913 out = []
915 for o in objects:
916 if o.isHistoric:
917 continue
919 o.recs = [r for r in o.recs if not r.isHistoric]
920 if not o.recs:
921 continue
923 for r in o.recs:
924 for k in self._historicKeys:
925 try:
926 delattr(r, k)
927 except AttributeError:
928 pass
930 out.append(o)
932 return out
935##
938def serialize(o: dt.Object, encode_enum_pairs=True) -> dict:
939 """Convert an object to a JSON-compatible structure.
941 Objects become dicts with sorted keys, dates become ``DD.MM.YYYY``
942 strings. Empty values are kept as they are.
944 Args:
945 o: Object to convert.
946 encode_enum_pairs: If ``True``, ``EnumPair`` values are encoded as
947 ``$code$text`` strings, otherwise as dicts.
949 Returns:
950 A dict.
951 """
953 def encode(r):
954 if not r:
955 return r
957 if isinstance(r, (int, float, str, bool)):
958 return r
960 if isinstance(r, (datetime.date, datetime.datetime)):
961 return f'{r.day:02}.{r.month:02}.{r.year:04}'
963 if isinstance(r, list):
964 return [encode(e) for e in r]
966 if isinstance(r, dt.EnumPair):
967 if encode_enum_pairs:
968 return f'${r.code}${r.text}'
969 return vars(r)
971 if isinstance(r, dt.Object):
972 return {k: encode(v) for k, v in sorted(vars(r).items())}
974 return str(r)
976 return encode(o)
979def unserialize(data: dict):
980 """Convert a structure created by ``serialize`` back to objects.
982 Dicts become ``types.Object`` instances and ``$code$text`` strings
983 become ``EnumPair`` values.
985 Args:
986 data: Serialized data.
988 Returns:
989 The object.
990 """
992 def decode(r):
993 if not r:
994 return r
995 if isinstance(r, str):
996 if r[0] == '$':
997 s = r.split('$')
998 return dt.EnumPair(s[1], s[2])
999 return r
1000 if isinstance(r, list):
1001 return [decode(e) for e in r]
1002 if isinstance(r, dict):
1003 d = {k: decode(v) for k, v in r.items()}
1004 return dt.Object(**d)
1005 return r
1007 return decode(data)
1010##
1013def text_key(s):
1014 """Normalize a string for text search.
1016 The string is lower-cased, umlauts are transliterated and punctuation is
1017 replaced by spaces.
1019 Args:
1020 s: String to normalize.
1022 Returns:
1023 The normalized string, an empty string for ``None``.
1024 """
1026 if s is None:
1027 return ''
1029 s = _text_umlauts(str(s).strip().lower())
1030 return _text_nopunct(s)
1033def strasse_key(s):
1034 """Normalize a street name for text search.
1036 Works like ``text_key``, but also expands the abbreviations ``str.`` and
1037 ``pl.`` and separates common street name suffixes, so that for example
1038 ``Hauptstr.`` and ``Haupt-Strasse`` give the same key.
1040 Args:
1041 s: Street name.
1043 Returns:
1044 The normalized street name, an empty string for ``None``.
1045 """
1047 if s is None:
1048 return ''
1050 s = _text_umlauts(str(s).strip().lower())
1052 s = re.sub(r'\s?str\.$', '.strasse', s)
1053 s = re.sub(r'\s?pl\.$', '.platz', s)
1054 s = re.sub(r'\s?(strasse|allee|damm|gasse|pfad|platz|ring|steig|wall|weg|zeile)$', r'.\1', s)
1056 return _text_nopunct(s)
1059def _text_umlauts(s):
1060 """Transliterate lower-case umlauts and sharp s."""
1062 s = s.replace('ä', 'ae')
1063 s = s.replace('ë', 'ee')
1064 s = s.replace('ö', 'oe')
1065 s = s.replace('ü', 'ue')
1066 s = s.replace('ß', 'ss')
1068 return s
1071def _text_nopunct(s):
1072 """Replace runs of non-word characters with a space."""
1074 return re.sub(r'\W+', ' ', s)
1077def normalize_hausnummer(s):
1078 """Normalize a house number by removing all whitespace.
1080 Args:
1081 s: House number, e.g. ``12 a``.
1083 Returns:
1084 The normalized house number, e.g. ``12a``, an empty string for ``None``.
1085 """
1087 if s is None:
1088 return ''
1090 # "12 a" -> "12a"
1091 s = re.sub(r'\s+', '', s.strip())
1092 return s
1095def hausnummer_key(s, upper=False):
1096 """Create a sort key for a house number.
1098 The key is the leading number, zero-padded to 6 digits, followed by the
1099 lower-cased rest, e.g. ``000002b`` for ``2 B``. Keys sort by number first,
1100 then by suffix, when compared bytewise (``COLLATE "C"``).
1102 Args:
1103 s: House number.
1104 upper: Create the key for the upper bound of a range. A house number
1105 without a suffix then gets a ``~`` suffix, which sorts after all
1106 letters, so that the bound ``5`` includes ``5a`` to ``5z``.
1108 Returns:
1109 The key, or ``None`` if the house number does not start with a number.
1110 """
1112 m = re.match(r'(\d+)(.*)$', normalize_hausnummer(s).lower())
1113 if not m:
1114 return None
1115 num, suffix = m.groups()
1116 if upper and not suffix:
1117 suffix = '~'
1118 return f'{int(num):06d}{suffix}'
1121def _hausnummer_key_or_error(s, upper=False):
1122 k = hausnummer_key(s, upper)
1123 if k is None:
1124 raise gws.BadRequestError(f'invalid hausnummer {s!r}')
1125 return k
1128def make_fsnummer(r: dt.FlurstueckRecord):
1129 """Create a display number for a Flurstueck.
1131 The format is ``<gemarkung> <flur>-<zaehler>/<nenner> (<folge>)``.
1132 The Flur, the denominator and the sequence number are omitted if empty,
1133 the sequence number also if it is ``00``.
1135 Args:
1136 r: Flurstueck record.
1138 Returns:
1139 The display number.
1140 """
1142 v = r.gemarkung.code + ' '
1144 s = r.flurnummer
1145 if s:
1146 v += str(s) + '-'
1148 v += str(r.zaehler)
1149 s = r.nenner
1150 if s:
1151 v += '/' + str(s)
1153 s = r.flurstuecksfolge
1154 if s and str(s) != '00':
1155 v += ' (' + str(s) + ')'
1157 return v
1160# parse a fsnummer in the above format, all parts are optional
1162_RE_FSNUMMER = r"""(?x)
1163 ^
1164 (
1165 (?P<gemarkungCode> [0-9]+)
1166 \s+
1167 )?
1168 (
1169 (?P<flurnummer> [0-9]+)
1170 -
1171 )?
1172 (
1173 (?P<zaehler> [0-9]+)
1174 (/
1175 (?P<nenner> \w+)
1176 )?
1177 )?
1178 (
1179 \s*
1180 \(
1181 (?P<flurstuecksfolge> [0-9]+)
1182 \)
1183 )?
1184 $
1185"""
1188def parse_fsnummer(s):
1189 """Parse a Flurstueck display number into its parts.
1191 The format is the one created by ``make_fsnummer``, all parts are optional.
1193 Args:
1194 s: Display number.
1196 Returns:
1197 A dict with the keys ``gemarkungCode``, ``flurnummer``, ``zaehler``,
1198 ``nenner`` and ``flurstuecksfolge`` for the parts found, or ``None``
1199 if the string does not match the format.
1200 """
1202 m = re.match(_RE_FSNUMMER, s.strip())
1203 if not m:
1204 return None
1205 return gws.u.compact(m.groupdict())
1208##
1211_FLATTEN_EXCLUDE_KEYS = {'fsUids', 'childUids', 'parentUids'}
1214def flatten_fs(fs: dt.Flurstueck, keys_to_extract: set[str]) -> list[dict]:
1215 """Flatten a Flurstueck into a list of dicts.
1217 The nested Flurstueck structure is turned into flat dicts whose keys are
1218 attribute paths joined with ``_``, starting with ``fs``, e.g.
1219 ``fs_recs_gemarkung_text``. ``EnumPair`` values give two keys, ``_code``
1220 and ``_text``. For list values, the dicts are repeated for each list item,
1221 so the result is the product of all lists, for example::
1223 record:
1224 a:x, b:[1,2], c:[3,4]
1226 flat list:
1227 a:x, b:1, c:3
1228 a:x, b:1, c:4
1229 a:x, b:2, c:3
1230 a:x, b:2, c:4
1232 Only paths leading to ``keys_to_extract`` are followed, which keeps the
1233 product small. Still, some combinations of keys produce many rows.
1235 Args:
1236 fs: Flurstueck to flatten.
1237 keys_to_extract: Flat keys to include.
1239 Returns:
1240 A list of dicts.
1241 """
1243 return _flatten(fs, 'fs', keys_to_extract, [{}])
1246def _flatten(val, key, keys_to_extract, flat_lst):
1247 """Recursively add a value under a key prefix to all dicts in a list."""
1249 if not any(k.startswith(key) for k in keys_to_extract):
1250 return flat_lst
1252 if isinstance(val, list):
1253 if not val:
1254 return flat_lst
1256 # create a cartesian product of all dicts in flat_dct with each item in val
1257 new_lst = []
1258 for v in val:
1259 for d2 in _flatten(v, key, keys_to_extract, [{}]):
1260 for d in flat_lst:
1261 new_lst.append(d | d2)
1262 return new_lst
1264 if isinstance(val, (dt.Object, dt.EnumPair)):
1265 for k, v in vars(val).items():
1266 if k not in _FLATTEN_EXCLUDE_KEYS:
1267 flat_lst = _flatten(v, f'{key}_{k}', keys_to_extract, flat_lst)
1268 return flat_lst
1270 for d in flat_lst:
1271 d[key] = val
1273 return flat_lst
1276def all_flat_keys():
1277 """Return all flat keys of the Flurstueck structure.
1279 The keys are derived from the type annotations in ``types``, see ``flatten_fs``.
1281 Returns:
1282 A dict of flat keys and their types, sorted by key.
1283 """
1285 return {k: typ for k, typ in sorted(set(_get_flat_keys(dt.Flurstueck, 'fs')))}
1288def _get_flat_keys(cls, key):
1289 """Yield flat keys and types for a class from its annotations."""
1291 if isinstance(cls, str):
1292 cls = getattr(dt, cls, None)
1294 if cls is dt.EnumPair:
1295 yield f'{key}_code', int
1296 yield f'{key}_text', str
1297 return
1299 if not cls or not hasattr(cls, '__annotations__'):
1300 yield key, cls or str
1301 return
1303 for k, typ in cls.__annotations__.items():
1304 if k in _FLATTEN_EXCLUDE_KEYS:
1305 continue
1306 if getattr(typ, '__origin__', None) is list:
1307 yield from _get_flat_keys(typ.__args__[0], f'{key}_{k}')
1308 else:
1309 yield from _get_flat_keys(typ, f'{key}_{k}')