Coverage for gws-app/gws/plugin/alkis/data/index.py: 0%
580 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-05 13:35 +0200
1"""ALKIS index tables: storage, search and loading of Flurstuecke and addresses."""
3from typing import Optional, Iterable
5import re
6import datetime
8from sqlalchemy.dialects.postgresql import JSONB
10import gws
11import gws.lib.shape
12import gws.base.database
13import gws.config.util
14import gws.lib.crs
15import gws.plugin.postgres.provider
16import gws.lib.sa as sa
17from gws.lib.cli import ProgressIndicator
19from . import types as dt
21TABLE_PLACE = 'place'
22TABLE_FLURSTUECK = 'flurstueck'
23TABLE_BUCHUNGSBLATT = 'buchungsblatt'
24TABLE_LAGE = 'lage'
25TABLE_PART = 'part'
27TABLE_INDEXFLURSTUECK = 'indexflurstueck'
28TABLE_INDEXLAGE = 'indexlage'
29TABLE_INDEXBUCHUNGSBLATT = 'indexbuchungsblatt'
30TABLE_INDEXPERSON = 'indexperson'
31TABLE_INDEXGEOM = 'indexgeom'
34class Object(gws.Node):
35 """ALKIS index.
37 Manages the index tables in the index schema, reports their status, runs
38 Flurstueck and address searches against them and loads the found objects
39 with their related data.
40 """
42 VERSION = '84'
43 """Index version, part of the table names."""
45 TABLES_BASIC = [
46 TABLE_PLACE,
47 TABLE_FLURSTUECK,
48 TABLE_LAGE,
49 TABLE_PART,
50 TABLE_INDEXFLURSTUECK,
51 TABLE_INDEXLAGE,
52 TABLE_INDEXGEOM,
53 ]
54 """Tables of the basic index."""
56 TABLES_BUCHUNG = [
57 TABLE_BUCHUNGSBLATT,
58 TABLE_INDEXBUCHUNGSBLATT,
59 ]
60 """Tables with land register data."""
62 TABLES_EIGENTUEMER = [
63 TABLE_BUCHUNGSBLATT,
64 TABLE_INDEXBUCHUNGSBLATT,
65 TABLE_INDEXPERSON,
66 ]
67 """Tables with owner data."""
69 ALL_TABLES = TABLES_BASIC + TABLES_BUCHUNG + TABLES_EIGENTUEMER
70 """All index tables."""
72 db: gws.plugin.postgres.provider.Object
73 """Database provider."""
74 crs: gws.Crs
75 """CRS of the geometries."""
76 schema: str
77 """Schema of the index tables."""
78 excludeGemarkung: set[str]
79 """Gemarkung numbers excluded from indexing."""
80 gemarkungFilter: set[str]
81 """Gemarkung codes searches are restricted to."""
83 saMeta: sa.MetaData
84 """SQLAlchemy metadata for the index tables."""
85 tables: dict[str, sa.Table]
86 """Index tables by table id."""
88 columnDct = {}
89 """Column definitions by table id."""
91 def __getstate__(self):
92 """Return the state for pickling, without the SQLAlchemy metadata."""
94 return gws.u.omit(vars(self), 'saMeta')
96 def configure(self):
97 gws.config.util.configure_database_provider_for(self, ext_type='postgres')
98 self.crs = gws.lib.crs.get(self.cfg('crs'))
99 self.schema = self.cfg('schema', default='public')
100 self.excludeGemarkung = set(self.cfg('excludeGemarkung', default=[]))
101 self.gemarkungFilter = set(self.cfg('gemarkungFilter', default=[]))
102 self.saMeta = sa.MetaData(schema=self.schema)
103 self.tables = {}
105 def activate(self):
106 self.saMeta = sa.MetaData(schema=self.schema)
107 self.tables = {}
109 self.columnDct = {
110 TABLE_PLACE: [
111 sa.Column('uid', sa.Text, primary_key=True),
112 sa.Column('data', JSONB),
113 ],
114 TABLE_FLURSTUECK: [
115 sa.Column('uid', sa.Text, primary_key=True),
116 sa.Column('rc', sa.Integer),
117 sa.Column('fshistoric', sa.Boolean),
118 sa.Column('data', JSONB),
119 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
120 ],
121 TABLE_BUCHUNGSBLATT: [
122 sa.Column('uid', sa.Text, primary_key=True),
123 sa.Column('rc', sa.Integer),
124 sa.Column('data', JSONB),
125 ],
126 TABLE_LAGE: [
127 sa.Column('uid', sa.Text, primary_key=True),
128 sa.Column('rc', sa.Integer),
129 sa.Column('data', JSONB),
130 ],
131 TABLE_PART: [
132 sa.Column('n', sa.Integer, primary_key=True),
133 sa.Column('fs', sa.Text, index=True),
134 sa.Column('uid', sa.Text, index=True),
135 sa.Column('beginnt', sa.DateTime),
136 sa.Column('endet', sa.DateTime),
137 sa.Column('kind', sa.Integer),
138 sa.Column('name', sa.Text),
139 sa.Column('parthistoric', sa.Boolean),
140 sa.Column('data', JSONB),
141 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
142 ],
143 TABLE_INDEXFLURSTUECK: [
144 sa.Column('n', sa.Integer, primary_key=True),
145 sa.Column('fs', sa.Text, index=True),
146 sa.Column('fshistoric', sa.Boolean, index=True),
147 sa.Column('land', sa.Text, index=True),
148 sa.Column('land_t', sa.Text, index=True),
149 sa.Column('landcode', sa.Text, index=True),
150 sa.Column('regierungsbezirk', sa.Text, index=True),
151 sa.Column('regierungsbezirk_t', sa.Text, index=True),
152 sa.Column('regierungsbezirkcode', sa.Text, index=True),
153 sa.Column('kreis', sa.Text, index=True),
154 sa.Column('kreis_t', sa.Text, index=True),
155 sa.Column('kreiscode', sa.Text, index=True),
156 sa.Column('gemeinde', sa.Text, index=True),
157 sa.Column('gemeinde_t', sa.Text, index=True),
158 sa.Column('gemeindecode', sa.Text, index=True),
159 sa.Column('gemarkung', sa.Text, index=True),
160 sa.Column('gemarkung_t', sa.Text, index=True),
161 sa.Column('gemarkungcode', sa.Text, index=True),
162 sa.Column('amtlicheflaeche', sa.Float, index=True),
163 sa.Column('geomflaeche', sa.Float, index=True),
164 sa.Column('flurnummer', sa.Text, index=True),
165 sa.Column('zaehler', sa.Text, index=True),
166 sa.Column('nenner', sa.Text, index=True),
167 sa.Column('flurstuecksfolge', sa.Text, index=True),
168 sa.Column('flurstueckskennzeichen', sa.Text, index=True),
169 sa.Column('x', sa.Float, index=True),
170 sa.Column('y', sa.Float, index=True),
171 ],
172 TABLE_INDEXLAGE: [
173 sa.Column('n', sa.Integer, primary_key=True),
174 sa.Column('fs', sa.Text, index=True),
175 sa.Column('fshistoric', sa.Boolean, index=True),
176 sa.Column('land', sa.Text, index=True),
177 sa.Column('land_t', sa.Text, index=True),
178 sa.Column('landcode', sa.Text, index=True),
179 sa.Column('regierungsbezirk', sa.Text, index=True),
180 sa.Column('regierungsbezirk_t', sa.Text, index=True),
181 sa.Column('regierungsbezirkcode', sa.Text, index=True),
182 sa.Column('kreis', sa.Text, index=True),
183 sa.Column('kreis_t', sa.Text, index=True),
184 sa.Column('kreiscode', sa.Text, index=True),
185 sa.Column('gemeinde', sa.Text, index=True),
186 sa.Column('gemeinde_t', sa.Text, index=True),
187 sa.Column('gemeindecode', sa.Text, index=True),
188 sa.Column('gemarkung', sa.Text, index=True),
189 sa.Column('gemarkung_t', sa.Text, index=True),
190 sa.Column('gemarkungcode', sa.Text, index=True),
191 sa.Column('lageuid', sa.Text, index=True),
192 sa.Column('lagehistoric', sa.Boolean, index=True),
193 sa.Column('strasse', sa.Text, index=True),
194 sa.Column('strasse_t', sa.Text, index=True),
195 sa.Column('hausnummer', sa.Text, index=True),
196 sa.Column('x', sa.Float, index=True),
197 sa.Column('y', sa.Float, index=True),
198 ],
199 TABLE_INDEXBUCHUNGSBLATT: [
200 sa.Column('n', sa.Integer, primary_key=True),
201 sa.Column('fs', sa.Text, index=True),
202 sa.Column('fshistoric', sa.Boolean, index=True),
203 sa.Column('buchungsblattuid', sa.Text, index=True),
204 sa.Column('buchungsblattbeginnt', sa.DateTime, index=True),
205 sa.Column('buchungsblattendet', sa.DateTime, index=True),
206 sa.Column('buchungsblattkennzeichen', sa.Text, index=True),
207 sa.Column('buchungsblatthistoric', sa.Boolean, index=True),
208 ],
209 TABLE_INDEXPERSON: [
210 sa.Column('n', sa.Integer, primary_key=True),
211 sa.Column('fs', sa.Text, index=True),
212 sa.Column('fshistoric', sa.Boolean, index=True),
213 sa.Column('personuid', sa.Text, index=True),
214 sa.Column('personhistoric', sa.Boolean, index=True),
215 sa.Column('name', sa.Text, index=True),
216 sa.Column('name_t', sa.Text, index=True),
217 sa.Column('vorname', sa.Text, index=True),
218 sa.Column('vorname_t', sa.Text, index=True),
219 ],
220 TABLE_INDEXGEOM: [
221 sa.Column('n', sa.Integer, primary_key=True),
222 sa.Column('fs', sa.Text, index=True),
223 sa.Column('fshistoric', sa.Boolean, index=True),
224 sa.Column('geomflaeche', sa.Float, index=True),
225 sa.Column('x', sa.Float, index=True),
226 sa.Column('y', sa.Float, index=True),
227 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),
228 ],
229 }
231 ##
233 def table(self, table_id: str) -> sa.Table:
234 """Return an index table.
236 Args:
237 table_id: Table id, one of the ``TABLE_`` constants.
239 Returns:
240 The table object.
241 """
243 if table_id not in self.tables:
244 table_name = f'alkis_{self.VERSION}_{table_id}'
245 self.tables[table_id] = sa.Table(
246 table_name,
247 self.saMeta,
248 *self.columnDct[table_id],
249 schema=self.schema,
250 )
251 return self.tables[table_id]
253 def table_size(self, table_id) -> int:
254 """Return the number of rows in an index table.
256 Args:
257 table_id: Table id.
259 Returns:
260 The number of rows, 0 if the table does not exist.
261 """
263 sizes = self._table_size_map([table_id])
264 return sizes.get(table_id, 0)
266 def _table_size_map(self, table_ids):
267 """Return a dict of table id to the number of rows."""
269 d = {}
271 with self.db.connect():
272 for table_id in table_ids:
273 try:
274 d[table_id] = self.db.count(self.table(table_id))
275 except sa.exc.SQLAlchemyError:
276 d[table_id] = 0
278 return d
280 def has_schema(self) -> bool:
281 """Check whether the index schema exists.
283 Returns:
284 ``True`` if the schema exists.
285 """
287 return self.db.has_schema(self.schema)
289 def has_table(self, table_id: str) -> bool:
290 """Check whether an index table exists and has data.
292 Args:
293 table_id: Table id.
295 Returns:
296 ``True`` if the table has at least one row.
297 """
299 return self.table_size(table_id) > 0
301 def status(self) -> dt.IndexStatus:
302 """Return the index status.
304 Returns:
305 The status, computed from the number of rows in the index tables.
306 """
308 sizes = self._table_size_map(self.ALL_TABLES)
309 s = dt.IndexStatus(
310 basic=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BASIC),
311 buchung=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BUCHUNG),
312 eigentuemer=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_EIGENTUEMER),
313 )
314 s.complete = s.basic and s.buchung and s.eigentuemer
315 s.missing = all(v == 0 for v in sizes.values())
316 gws.log.debug(f'ALKIS: table sizes {sizes!r}')
317 return s
319 def drop_table(self, table_id: str):
320 """Drop an index table, if it exists.
322 Args:
323 table_id: Table id.
324 """
326 with self.db.connect() as conn:
327 self._drop_table(conn, table_id)
328 conn.commit()
330 def drop(self):
331 """Drop all index tables."""
333 with self.db.connect() as conn:
334 for table_id in self.ALL_TABLES:
335 self._drop_table(conn, table_id)
336 conn.commit()
338 def _drop_table(self, conn, table_id):
339 """Drop an index table using an open connection."""
341 tab = self.table(table_id)
342 conn.execute(sa.text(f'DROP TABLE IF EXISTS {self.schema}.{tab.name}'))
344 INSERT_SIZE = 5000
345 """Number of rows inserted per statement."""
347 def create_table(
348 self,
349 table_id: str,
350 values: list[dict],
351 progress: Optional[ProgressIndicator] = None,
352 ):
353 """Create an index table and fill it with rows.
355 Args:
356 table_id: Table id.
357 values: Rows as dicts of column names and values.
358 progress: Progress indicator, updated after each chunk of rows.
359 """
361 tab = self.table(table_id)
362 self.saMeta.create_all(self.db.engine(), tables=[tab])
364 with self.db.connect() as conn:
365 for i in range(0, len(values), self.INSERT_SIZE):
366 vals = values[i : i + self.INSERT_SIZE]
367 conn.execute(sa.insert(tab).values(vals))
368 conn.commit()
369 if progress:
370 progress.update(len(vals))
372 ##
374 _defaultLand: dt.EnumPair = None
375 """Cached default Land."""
377 def default_land(self):
378 """Return the Land of a Gemarkung in the index.
380 The value is cached once found.
382 Returns:
383 The Land as an ``EnumPair``, or ``None`` if there are no Gemarkungen.
384 """
386 if self._defaultLand:
387 return self._defaultLand
389 with self.db.connect() as conn:
390 sel = sa.select(self.table(TABLE_PLACE)).where(sa.text("data->>'kind' = 'gemarkung'")).limit(1)
391 for r in conn.execute(sel):
392 p = unserialize(r.data)
393 self._defaultLand = p.land
394 gws.log.debug(f'ALKIS: defaultLand={vars(self._defaultLand)}')
395 return self._defaultLand
397 _strasseList: list[dt.Strasse] = []
398 """Cached street list."""
400 def strasse_list(self) -> list[dt.Strasse]:
401 """Return all streets in the index.
403 Each distinct combination of Gemeinde, Gemarkung and street name is
404 returned once. The list is cached.
406 Returns:
407 A list of streets.
408 """
410 if self._strasseList:
411 return self._strasseList
413 self._strasseList = []
415 indexlage = self.table(TABLE_INDEXLAGE)
416 cols = (
417 indexlage.c.gemeinde,
418 indexlage.c.gemeindecode,
419 indexlage.c.gemarkung,
420 indexlage.c.gemarkungcode,
421 indexlage.c.strasse,
422 )
424 sel = sa.select(*cols).group_by(*cols)
425 if self.gemarkungFilter:
426 sel = sel.where(indexlage.c.gemarkungcode.in_(self.gemarkungFilter))
428 with self.db.connect() as conn:
429 for r in conn.execute(sel):
430 self._strasseList.append(
431 dt.Strasse(
432 gemeinde=dt.EnumPair(r.gemeindecode, r.gemeinde),
433 gemarkung=dt.EnumPair(r.gemarkungcode, r.gemarkung),
434 name=r.strasse,
435 )
436 )
438 return self._strasseList
440 def find_adresse(self, q: dt.AdresseQuery) -> list[dt.Adresse]:
441 """Find addresses.
443 Args:
444 q: Search criteria and options.
446 Returns:
447 A list of addresses, in the order of the search, after applying offset and page size.
449 Raises:
450 ``gws.ResponseTooLargeError``: If there are more results than the configured limit.
451 ``gws.BadRequestError``: If the query is invalid, e.g. a house number without a street.
452 """
454 indexlage = self.table(TABLE_INDEXLAGE)
456 qo = q.options or dt.AdresseQueryOptions()
457 sel = self._make_adresse_select(q, qo)
459 lage_uids = []
460 adresse_map = {}
462 with self.db.connect() as conn:
463 for r in conn.execute(sel):
464 lage_uids.append(r[0])
466 if qo.limit and len(lage_uids) > qo.limit:
467 raise gws.ResponseTooLargeError(len(lage_uids))
469 if qo.offset:
470 lage_uids = lage_uids[qo.offset :]
471 if qo.pageSize:
472 lage_uids = lage_uids[: qo.pageSize]
474 sel = indexlage.select().where(indexlage.c.lageuid.in_(lage_uids))
476 for r in conn.execute(sel):
477 r = gws.u.to_dict(r)
478 uid = r['lageuid']
479 adresse_map[uid] = dt.Adresse(
480 uid=uid,
481 land=dt.EnumPair(r['landcode'], r['land']),
482 regierungsbezirk=dt.EnumPair(r['regierungsbezirkcode'], r['regierungsbezirk']),
483 kreis=dt.EnumPair(r['kreiscode'], r['kreis']),
484 gemeinde=dt.EnumPair(r['gemeindecode'], r['gemeinde']),
485 gemarkung=dt.EnumPair(r['gemarkungcode'], r['gemarkung']),
486 strasse=r['strasse'],
487 hausnummer=r['hausnummer'],
488 x=r['x'],
489 y=r['y'],
490 shape=gws.lib.shape.from_xy(r['x'], r['y'], crs=self.crs),
491 )
493 return gws.u.compact(adresse_map.get(uid) for uid in lage_uids)
495 def find_flurstueck(self, q: dt.FlurstueckQuery) -> list[dt.Flurstueck]:
496 """Find Flurstuecke and load them with their related data.
498 Args:
499 q: Search criteria and options.
501 Returns:
502 A list of Flurstuecke, in the order of the search, after applying offset and page size.
504 Raises:
505 ``gws.ResponseTooLargeError``: If there are more results than the configured limit.
506 ``gws.BadRequestError``: If the query is invalid, e.g. a house number without a street.
507 """
509 qo = q.options or dt.FlurstueckQueryOptions()
510 sel = self._make_flurstueck_select(q, qo)
512 fs_uids = []
514 with self.db.connect() as conn:
515 for r in conn.execute(sel):
516 uid = r[0].partition('_')[0]
517 if uid not in fs_uids:
518 fs_uids.append(uid)
520 if qo.limit and len(fs_uids) > qo.limit:
521 raise gws.ResponseTooLargeError(len(fs_uids))
523 if qo.offset:
524 fs_uids = fs_uids[qo.offset :]
525 if qo.pageSize:
526 fs_uids = fs_uids[: qo.pageSize]
528 fs_list = self._load_flurstueck(conn, fs_uids, qo)
530 return fs_list
532 def count_all(self, qo: dt.FlurstueckQueryOptions) -> int:
533 """Count all Flurstueck records in the index.
535 Args:
536 qo: Query options. Historic records are counted only with ``withHistorySearch``.
538 Returns:
539 The number of rows in the Flurstueck index table.
540 """
542 indexfs = self.table(TABLE_INDEXFLURSTUECK)
543 sel = sa.select(sa.func.count()).select_from(indexfs)
544 if not qo.withHistorySearch:
545 sel = sel.where(~indexfs.c.fshistoric)
547 with self.db.connect() as conn:
548 r = list(conn.execute(sel))
549 return r[0][0]
551 def iter_all(self, qo: dt.FlurstueckQueryOptions) -> Iterable[dt.Flurstueck]:
552 """Iterate over all Flurstuecke in the index.
554 Flurstuecke are loaded in chunks of ``qo.pageSize``, with a new
555 database connection for each chunk.
557 Args:
558 qo: Query options. ``pageSize`` must be set.
560 Yields:
561 Flurstuecke with their related data.
562 """
564 indexfs = self.table(TABLE_INDEXFLURSTUECK)
565 sel = sa.select(indexfs.c.fs).with_only_columns(indexfs.c.fs).order_by(indexfs.c.n)
566 if not qo.withHistorySearch:
567 sel = sel.where(~indexfs.c.fshistoric)
569 offset = 0
571 # NB the consumer might be slow, close connection on each chunk
573 while True:
574 with self.db.connect() as conn:
575 sel2 = sel.offset(offset).limit(qo.pageSize)
576 fs_uids = [r[0] for r in conn.execute(sel2)]
577 if not fs_uids:
578 break
579 fs_list = self._load_flurstueck(conn, fs_uids, qo)
581 yield from fs_list
583 offset += qo.pageSize
585 HAUSNUMMER_NOT_NULL_VALUE = '*'
586 """House number value that matches any non-empty house number."""
588 def _make_flurstueck_select(self, q: dt.FlurstueckQuery, qo: dt.FlurstueckQueryOptions):
589 """Build a select statement for matching Flurstueck uids."""
591 indexfs = self.table(TABLE_INDEXFLURSTUECK)
592 indexbuchungsblatt = self.table(TABLE_INDEXBUCHUNGSBLATT)
593 indexgeom = self.table(TABLE_INDEXGEOM)
594 indexlage = self.table(TABLE_INDEXLAGE)
595 indexperson = self.table(TABLE_INDEXPERSON)
597 where = []
599 has_buchungsblatt = False
600 has_geom = False
601 has_lage = False
602 has_person = False
604 where.extend(self._make_places_where(q, indexfs))
606 if q.uids:
607 where.append(indexfs.c.fs.in_(q.uids))
609 for f in 'flurnummer', 'flurstuecksfolge', 'zaehler', 'nenner':
610 val = getattr(q, f, None)
611 if val is not None:
612 where.append(getattr(indexfs.c, f.lower()) == val)
614 if q.flurstueckskennzeichen:
615 val = re.sub(r'[^0-9_]', '', q.flurstueckskennzeichen)
616 if not val:
617 raise gws.BadRequestError(f'invalid flurstueckskennzeichen {q.flurstueckskennzeichen!r}')
618 where.append(indexfs.c.flurstueckskennzeichen.like(val + '%'))
620 if q.flaecheVon:
621 try:
622 where.append(indexfs.c.amtlicheflaeche >= float(q.flaecheVon))
623 except ValueError:
624 raise gws.BadRequestError(f'invalid flaecheVon {q.flaecheVon!r}')
626 if q.flaecheBis:
627 try:
628 where.append(indexfs.c.amtlicheflaeche <= float(q.flaecheBis))
629 except ValueError:
630 raise gws.BadRequestError(f'invalid flaecheBis {q.flaecheBis!r}')
632 if q.buchungsblattkennzeichenList:
633 ws = []
635 for s in q.buchungsblattkennzeichenList:
636 w = text_search_clause(
637 indexbuchungsblatt.c.buchungsblattkennzeichen,
638 s,
639 qo.buchungsblattSearchOptions,
640 )
641 if w is not None:
642 ws.append(w)
643 if ws:
644 has_buchungsblatt = True
645 where.append(sa.or_(*ws))
647 if q.strasse:
648 w = text_search_clause(
649 indexlage.c.strasse_t,
650 strasse_key(q.strasse),
651 qo.strasseSearchOptions,
652 )
653 if w is not None:
654 has_lage = True
655 where.append(w)
657 if q.hausnummer:
658 if not has_lage:
659 raise gws.BadRequestError(f'hausnummer without strasse')
660 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:
661 where.append(indexlage.c.hausnummer.is_not(None))
662 else:
663 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))
665 if q.personName:
666 w = text_search_clause(indexperson.c.name_t, text_key(q.personName), qo.nameSearchOptions)
667 if w is not None:
668 has_person = True
669 where.append(w)
671 if q.personVorname:
672 if not has_person:
673 raise gws.BadRequestError(f'personVorname without personName')
674 w = text_search_clause(indexperson.c.vorname_t, text_key(q.personVorname), qo.nameSearchOptions)
675 if w is not None:
676 where.append(w)
678 if q.shape:
679 has_geom = True
680 where.append(
681 sa.func.st_intersects(
682 indexgeom.c.geom,
683 sa.cast(
684 q.shape.transformed_to(self.crs).to_ewkb_hex(),
685 sa.geo.Geometry(),
686 ),
687 )
688 )
690 join = []
692 if has_buchungsblatt:
693 join.append([indexbuchungsblatt, indexbuchungsblatt.c.fs == indexfs.c.fs])
694 if not qo.withHistorySearch:
695 where.append(~indexbuchungsblatt.c.fshistoric)
696 where.append(~indexbuchungsblatt.c.buchungsblatthistoric)
698 if has_geom:
699 join.append([indexgeom, indexgeom.c.fs == indexfs.c.fs])
700 if not qo.withHistorySearch:
701 where.append(~indexgeom.c.fshistoric)
703 if has_lage:
704 join.append([indexlage, indexlage.c.fs == indexfs.c.fs])
705 if not qo.withHistorySearch:
706 where.append(~indexlage.c.fshistoric)
707 where.append(~indexlage.c.lagehistoric)
709 if has_person:
710 join.append([indexperson, indexperson.c.fs == indexfs.c.fs])
711 if not qo.withHistorySearch:
712 where.append(~indexperson.c.fshistoric)
713 where.append(~indexperson.c.personhistoric)
715 if not qo.withHistorySearch:
716 where.append(~indexfs.c.fshistoric)
718 sel = sa.select(sa.distinct(indexfs.c.fs))
720 for tab, cond in join:
721 sel = sel.join(tab, cond)
723 sel = sel.where(*where)
725 return self._make_sort(sel, qo.sort, indexfs)
727 def _make_adresse_select(self, q: dt.AdresseQuery, qo: dt.AdresseQueryOptions):
728 """Build a select statement for matching Lage uids."""
730 indexlage = self.table(TABLE_INDEXLAGE)
731 where = []
733 where.extend(self._make_places_where(q, indexlage))
735 has_strasse = False
737 if q.strasse:
738 w = text_search_clause(
739 indexlage.c.strasse_t,
740 strasse_key(q.strasse),
741 qo.strasseSearchOptions,
742 )
743 if w is not None:
744 has_strasse = True
745 where.append(w)
747 if q.hausnummer:
748 if not has_strasse:
749 raise gws.BadRequestError(f'hausnummer without strasse')
750 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:
751 where.append(indexlage.c.hausnummer.is_not(None))
752 else:
753 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))
755 if q.bisHausnummer:
756 if not has_strasse:
757 raise gws.BadRequestError(f'hausnummer without strasse')
758 where.append(indexlage.c.hausnummer < normalize_hausnummer(q.bisHausnummer))
760 if q.hausnummerNotNull:
761 if not has_strasse:
762 raise gws.BadRequestError(f'hausnummer without strasse')
763 where.append(indexlage.c.hausnummer.is_not(None))
765 if not qo.withHistorySearch:
766 where.append(~indexlage.c.lagehistoric)
768 sel = sa.select(sa.distinct(indexlage.c.lageuid))
770 sel = sel.where(*where)
772 return self._make_sort(sel, qo.sort, indexlage)
774 def _make_places_where(self, q: dt.FlurstueckQuery | dt.AdresseQuery, table: sa.Table):
775 """Build where clauses for place names and codes."""
777 where = []
778 land_code = ''
780 for f in 'land', 'regierungsbezirk', 'kreis', 'gemarkung', 'gemeinde':
781 val = getattr(q, f, None)
782 if val is not None:
783 where.append(getattr(table.c, f.lower() + '_t') == text_key(val))
785 val = getattr(q, f + 'Code', None)
786 if val is not None:
787 if f == 'land':
788 land_code = val
789 elif f == 'gemarkung' and len(val) <= 4:
790 if not land_code:
791 land = self.default_land()
792 if land:
793 land_code = land.code
794 val = land_code + val
796 where.append(getattr(table.c, f.lower() + 'code') == val)
798 if self.gemarkungFilter:
799 where.append(table.c.gemarkungcode.in_(self.gemarkungFilter))
801 return where
803 def _make_sort(self, sel, sort, table: sa.Table):
804 """Add the sort order to a select statement."""
806 if not sort:
807 return sel
809 order = []
810 for s in sort:
811 fn = sa.desc if s.reverse else sa.asc
812 order.append(fn(getattr(table.c, s.fieldName)))
813 sel = sel.order_by(*order)
815 return sel
817 def load_flurstueck(self, fs_uids: list[str], qo: dt.FlurstueckQueryOptions) -> list[dt.Flurstueck]:
818 """Load Flurstuecke with their related data.
820 Args:
821 fs_uids: Flurstueck uids.
822 qo: Query options, the display themes select the related data.
824 Returns:
825 A list of Flurstuecke in the order of ``fs_uids``. Missing and,
826 unless ``withHistoryDisplay`` is set, historic Flurstuecke are left out.
827 """
829 with self.db.connect() as conn:
830 return self._load_flurstueck(conn, fs_uids, qo)
832 def _load_flurstueck(self, conn, fs_uids, qo: dt.FlurstueckQueryOptions):
833 """Load Flurstuecke and related data using an open connection."""
835 with_lage = dt.DisplayTheme.lage in qo.displayThemes
836 with_gebaeude = dt.DisplayTheme.gebaeude in qo.displayThemes
837 with_nutzung = dt.DisplayTheme.nutzung in qo.displayThemes
838 with_festlegung = dt.DisplayTheme.festlegung in qo.displayThemes
839 with_bewertung = dt.DisplayTheme.bewertung in qo.displayThemes
840 with_buchung = dt.DisplayTheme.buchung in qo.displayThemes
841 with_eigentuemer = dt.DisplayTheme.eigentuemer in qo.displayThemes
843 tab = self.table(TABLE_FLURSTUECK)
844 sel = sa.select(tab).where(tab.c.uid.in_(set(fs_uids)))
846 hd = qo.withHistoryDisplay
848 fs_list = []
850 for r in conn.execute(sel):
851 fs = unserialize(r.data)
852 fs.geom = r.geom
853 fs_list.append(fs)
855 fs_list = self._remove_historic(fs_list, hd)
856 if not fs_list:
857 return []
859 fs_map = {fs.uid: fs for fs in fs_list}
861 for fs in fs_map.values():
862 fs.shape = gws.lib.shape.from_wkb_element(fs.geom, default_crs=self.crs)
864 fs.lageList = self._remove_historic(fs.lageList, hd) if with_lage else []
865 fs.gebaeudeList = self._remove_historic(fs.gebaeudeList, hd) if with_gebaeude else []
866 fs.buchungList = self._remove_historic(fs.buchungList, hd) if with_buchung else []
868 fs.bewertungList = []
869 fs.festlegungList = []
870 fs.nutzungList = []
872 if with_buchung:
873 bb_uids = set(bu.buchungsblattUid for fs in fs_map.values() for bu in fs.buchungList)
875 tab = self.table(TABLE_BUCHUNGSBLATT)
876 sel = sa.select(tab).where(tab.c.uid.in_(bb_uids))
877 bb_list = [unserialize(r.data) for r in conn.execute(sel)]
878 bb_list = self._remove_historic(bb_list, hd)
880 for bb in bb_list:
881 bb.buchungsstelleList = self._remove_historic(bb.buchungsstelleList, hd)
882 bb.namensnummerList = self._remove_historic(bb.namensnummerList, hd) if with_eigentuemer else []
883 for nn in bb.namensnummerList:
884 nn.personList = self._remove_historic(nn.personList, hd)
885 for pe in nn.personList:
886 pe.anschriftList = self._remove_historic(pe.anschriftList, hd)
888 bb_map = {bb.uid: bb for bb in bb_list}
890 for fs in fs_map.values():
891 for bu in fs.buchungList:
892 bu.buchungsblatt = bb_map.get(bu.buchungsblattUid, hd)
894 if with_nutzung or with_festlegung or with_bewertung:
895 tab = self.table(TABLE_PART)
896 sel = sa.select(tab).where(tab.c.fs.in_(list(fs_map)))
897 if not qo.withHistorySearch:
898 sel.where(~tab.c.parthistoric)
899 pa_list = [unserialize(r.data) for r in conn.execute(sel)]
900 pa_list = self._remove_historic(pa_list, hd)
902 for pa in pa_list:
903 fs = fs_map[pa.fs]
904 if pa.kind == dt.PART_NUTZUNG and with_nutzung:
905 fs.nutzungList.append(pa)
906 if pa.kind == dt.PART_FESTLEGUNG and with_festlegung:
907 fs.festlegungList.append(pa)
908 if pa.kind == dt.PART_BEWERTUNG and with_bewertung:
909 fs.bewertungList.append(pa)
911 return gws.u.compact(fs_map.get(uid) for uid in fs_uids)
913 _historicKeys = ['vorgaengerFlurstueckskennzeichen']
914 """Record attributes removed when history is not displayed."""
916 def _remove_historic(self, objects, with_history_display: bool):
917 """Remove historic objects and records, unless history is displayed."""
919 if with_history_display:
920 return objects
922 out = []
924 for o in objects:
925 if o.isHistoric:
926 continue
928 o.recs = [r for r in o.recs if not r.isHistoric]
929 if not o.recs:
930 continue
932 for r in o.recs:
933 for k in self._historicKeys:
934 try:
935 delattr(r, k)
936 except AttributeError:
937 pass
939 out.append(o)
941 return out
944##
947def serialize(o: dt.Object, encode_enum_pairs=True) -> dict:
948 """Convert an object to a JSON-compatible structure.
950 Objects become dicts with sorted keys, dates become ``DD.MM.YYYY``
951 strings. Empty values are kept as they are.
953 Args:
954 o: Object to convert.
955 encode_enum_pairs: If ``True``, ``EnumPair`` values are encoded as
956 ``$code$text`` strings, otherwise as dicts.
958 Returns:
959 A dict.
960 """
962 def encode(r):
963 if not r:
964 return r
966 if isinstance(r, (int, float, str, bool)):
967 return r
969 if isinstance(r, (datetime.date, datetime.datetime)):
970 return f'{r.day:02}.{r.month:02}.{r.year:04}'
972 if isinstance(r, list):
973 return [encode(e) for e in r]
975 if isinstance(r, dt.EnumPair):
976 if encode_enum_pairs:
977 return f'${r.code}${r.text}'
978 return vars(r)
980 if isinstance(r, dt.Object):
981 return {k: encode(v) for k, v in sorted(vars(r).items())}
983 return str(r)
985 return encode(o)
988def unserialize(data: dict):
989 """Convert a structure created by ``serialize`` back to objects.
991 Dicts become ``types.Object`` instances and ``$code$text`` strings
992 become ``EnumPair`` values.
994 Args:
995 data: Serialized data.
997 Returns:
998 The object.
999 """
1001 def decode(r):
1002 if not r:
1003 return r
1004 if isinstance(r, str):
1005 if r[0] == '$':
1006 s = r.split('$')
1007 return dt.EnumPair(s[1], s[2])
1008 return r
1009 if isinstance(r, list):
1010 return [decode(e) for e in r]
1011 if isinstance(r, dict):
1012 d = {k: decode(v) for k, v in r.items()}
1013 return dt.Object(**d)
1014 return r
1016 return decode(data)
1019##
1022def text_key(s):
1023 """Normalize a string for text search.
1025 The string is lower-cased, umlauts are transliterated and punctuation is
1026 replaced by spaces.
1028 Args:
1029 s: String to normalize.
1031 Returns:
1032 The normalized string, an empty string for ``None``.
1033 """
1035 if s is None:
1036 return ''
1038 s = _text_umlauts(str(s).strip().lower())
1039 return _text_nopunct(s)
1042def strasse_key(s):
1043 """Normalize a street name for text search.
1045 Works like ``text_key``, but also expands the abbreviations ``str.`` and
1046 ``pl.`` and separates common street name suffixes, so that for example
1047 ``Hauptstr.`` and ``Haupt-Strasse`` give the same key.
1049 Args:
1050 s: Street name.
1052 Returns:
1053 The normalized street name, an empty string for ``None``.
1054 """
1056 if s is None:
1057 return ''
1059 s = _text_umlauts(str(s).strip().lower())
1061 s = re.sub(r'\s?str\.$', '.strasse', s)
1062 s = re.sub(r'\s?pl\.$', '.platz', s)
1063 s = re.sub(r'\s?(strasse|allee|damm|gasse|pfad|platz|ring|steig|wall|weg|zeile)$', r'.\1', s)
1065 return _text_nopunct(s)
1068def _text_umlauts(s):
1069 """Transliterate lower-case umlauts and sharp s."""
1071 s = s.replace('ä', 'ae')
1072 s = s.replace('ë', 'ee')
1073 s = s.replace('ö', 'oe')
1074 s = s.replace('ü', 'ue')
1075 s = s.replace('ß', 'ss')
1077 return s
1080def _text_nopunct(s):
1081 """Replace runs of non-word characters with a space."""
1083 return re.sub(r'\W+', ' ', s)
1086def normalize_hausnummer(s):
1087 """Normalize a house number by removing all whitespace.
1089 Args:
1090 s: House number, e.g. ``12 a``.
1092 Returns:
1093 The normalized house number, e.g. ``12a``, an empty string for ``None``.
1094 """
1096 if s is None:
1097 return ''
1099 # "12 a" -> "12a"
1100 s = re.sub(r'\s+', '', s.strip())
1101 return s
1104def make_fsnummer(r: dt.FlurstueckRecord):
1105 """Create a display number for a Flurstueck.
1107 The format is ``<gemarkung> <flur>-<zaehler>/<nenner> (<folge>)``.
1108 The Flur, the denominator and the sequence number are omitted if empty,
1109 the sequence number also if it is ``00``.
1111 Args:
1112 r: Flurstueck record.
1114 Returns:
1115 The display number.
1116 """
1118 v = r.gemarkung.code + ' '
1120 s = r.flurnummer
1121 if s:
1122 v += str(s) + '-'
1124 v += str(r.zaehler)
1125 s = r.nenner
1126 if s:
1127 v += '/' + str(s)
1129 s = r.flurstuecksfolge
1130 if s and str(s) != '00':
1131 v += ' (' + str(s) + ')'
1133 return v
1136# parse a fsnummer in the above format, all parts are optional
1138_RE_FSNUMMER = r"""(?x)
1139 ^
1140 (
1141 (?P<gemarkungCode> [0-9]+)
1142 \s+
1143 )?
1144 (
1145 (?P<flurnummer> [0-9]+)
1146 -
1147 )?
1148 (
1149 (?P<zaehler> [0-9]+)
1150 (/
1151 (?P<nenner> \w+)
1152 )?
1153 )?
1154 (
1155 \s*
1156 \(
1157 (?P<flurstuecksfolge> [0-9]+)
1158 \)
1159 )?
1160 $
1161"""
1164def parse_fsnummer(s):
1165 """Parse a Flurstueck display number into its parts.
1167 The format is the one created by ``make_fsnummer``, all parts are optional.
1169 Args:
1170 s: Display number.
1172 Returns:
1173 A dict with the keys ``gemarkungCode``, ``flurnummer``, ``zaehler``,
1174 ``nenner`` and ``flurstuecksfolge`` for the parts found, or ``None``
1175 if the string does not match the format.
1176 """
1178 m = re.match(_RE_FSNUMMER, s.strip())
1179 if not m:
1180 return None
1181 return gws.u.compact(m.groupdict())
1184def text_search_clause(column, val, tso: gws.TextSearchOptions):
1185 """Create a where clause that matches a column against a search string.
1187 Args:
1188 column: Column to match.
1189 val: Search string.
1190 tso: Text search options. Without options, the value is matched exactly.
1192 Returns:
1193 A clause, or ``None`` if the value is empty or shorter than the minimum length.
1194 """
1196 # @TODO merge with model_field/text
1198 if val is None:
1199 return
1201 val = str(val).strip()
1202 if len(val) == 0:
1203 return
1205 if not tso:
1206 return column == val
1208 if tso.minLength and len(val) < tso.minLength:
1209 return
1211 if tso.type == gws.TextSearchType.exact:
1212 return column == val
1214 if tso.type == gws.TextSearchType.any:
1215 val = '%' + _escape_like(val) + '%'
1216 if tso.type == gws.TextSearchType.begin:
1217 val = _escape_like(val) + '%'
1218 if tso.type == gws.TextSearchType.end:
1219 val = '%' + _escape_like(val)
1221 if tso.caseSensitive:
1222 return column.like(val, escape='\\')
1224 return column.ilike(val, escape='\\')
1227def _escape_like(s, escape='\\'):
1228 """Escape special characters for a LIKE pattern."""
1230 return s.replace(escape, escape + escape).replace('%', escape + '%').replace('_', escape + '_')
1233##
1236_FLATTEN_EXCLUDE_KEYS = {'fsUids', 'childUids', 'parentUids'}
1239def flatten_fs(fs: dt.Flurstueck, keys_to_extract: set[str]) -> list[dict]:
1240 """Flatten a Flurstueck into a list of dicts.
1242 The nested Flurstueck structure is turned into flat dicts whose keys are
1243 attribute paths joined with ``_``, starting with ``fs``, e.g.
1244 ``fs_recs_gemarkung_text``. ``EnumPair`` values give two keys, ``_code``
1245 and ``_text``. For list values, the dicts are repeated for each list item,
1246 so the result is the product of all lists, for example::
1248 record:
1249 a:x, b:[1,2], c:[3,4]
1251 flat list:
1252 a:x, b:1, c:3
1253 a:x, b:1, c:4
1254 a:x, b:2, c:3
1255 a:x, b:2, c:4
1257 Only paths leading to ``keys_to_extract`` are followed, which keeps the
1258 product small. Still, some combinations of keys produce many rows.
1260 Args:
1261 fs: Flurstueck to flatten.
1262 keys_to_extract: Flat keys to include.
1264 Returns:
1265 A list of dicts.
1266 """
1268 return _flatten(fs, 'fs', keys_to_extract, [{}])
1271def _flatten(val, key, keys_to_extract, flat_lst):
1272 """Recursively add a value under a key prefix to all dicts in a list."""
1274 if not any(k.startswith(key) for k in keys_to_extract):
1275 return flat_lst
1277 if isinstance(val, list):
1278 if not val:
1279 return flat_lst
1281 # create a cartesian product of all dicts in flat_dct with each item in val
1282 new_lst = []
1283 for v in val:
1284 for d2 in _flatten(v, key, keys_to_extract, [{}]):
1285 for d in flat_lst:
1286 new_lst.append(d | d2)
1287 return new_lst
1289 if isinstance(val, (dt.Object, dt.EnumPair)):
1290 for k, v in vars(val).items():
1291 if k not in _FLATTEN_EXCLUDE_KEYS:
1292 flat_lst = _flatten(v, f'{key}_{k}', keys_to_extract, flat_lst)
1293 return flat_lst
1295 for d in flat_lst:
1296 d[key] = val
1298 return flat_lst
1301def all_flat_keys():
1302 """Return all flat keys of the Flurstueck structure.
1304 The keys are derived from the type annotations in ``types``, see ``flatten_fs``.
1306 Returns:
1307 A dict of flat keys and their types, sorted by key.
1308 """
1310 return {k: typ for k, typ in sorted(set(_get_flat_keys(dt.Flurstueck, 'fs')))}
1313def _get_flat_keys(cls, key):
1314 """Yield flat keys and types for a class from its annotations."""
1316 if isinstance(cls, str):
1317 cls = getattr(dt, cls, None)
1319 if cls is dt.EnumPair:
1320 yield f'{key}_code', int
1321 yield f'{key}_text', str
1322 return
1324 if not cls or not hasattr(cls, '__annotations__'):
1325 yield key, cls or str
1326 return
1328 for k, typ in cls.__annotations__.items():
1329 if k in _FLATTEN_EXCLUDE_KEYS:
1330 continue
1331 if getattr(typ, '__origin__', None) is list:
1332 yield from _get_flat_keys(typ.__args__[0], f'{key}_{k}')
1333 else:
1334 yield from _get_flat_keys(typ, f'{key}_{k}')