Coverage for gws-app/gws/plugin/alkis/data/index.py: 0%

1from typing import Optional, Iterable

3import re

4import datetime

6from sqlalchemy.dialects.postgresql import JSONB

8import gws

9import gws.base.shape

10import gws.base.database

11import gws.config.util

12import gws.lib.crs

13import gws.plugin.postgres.provider

14import gws.lib.sa as sa

15from gws.lib.cli import ProgressIndicator

17from . import types as dt

19TABLE_PLACE = 'place'

20TABLE_FLURSTUECK = 'flurstueck'

21TABLE_BUCHUNGSBLATT = 'buchungsblatt'

22TABLE_LAGE = 'lage'

23TABLE_PART = 'part'

25TABLE_INDEXFLURSTUECK = 'indexflurstueck'

26TABLE_INDEXLAGE = 'indexlage'

27TABLE_INDEXBUCHUNGSBLATT = 'indexbuchungsblatt'

28TABLE_INDEXPERSON = 'indexperson'

29TABLE_INDEXGEOM = 'indexgeom'

32class Object(gws.Node):

33 VERSION = '82'

35 TABLES_BASIC = [

36 TABLE_PLACE,

37 TABLE_FLURSTUECK,

38 TABLE_LAGE,

39 TABLE_PART,

40 TABLE_INDEXFLURSTUECK,

41 TABLE_INDEXLAGE,

42 TABLE_INDEXGEOM,

43 ]

45 TABLES_BUCHUNG = [

46 TABLE_BUCHUNGSBLATT,

47 TABLE_INDEXBUCHUNGSBLATT,

48 ]

50 TABLES_EIGENTUEMER = [

51 TABLE_BUCHUNGSBLATT,

52 TABLE_INDEXBUCHUNGSBLATT,

53 TABLE_INDEXPERSON,

54 ]

56 ALL_TABLES = TABLES_BASIC + TABLES_BUCHUNG + TABLES_EIGENTUEMER

58 db: gws.plugin.postgres.provider.Object

59 crs: gws.Crs

60 schema: str

61 excludeGemarkung: set[str]

62 gemarkungFilter: set[str]

64 saMeta: sa.MetaData

65 tables: dict[str, sa.Table]

67 columnDct = {}

69 def __getstate__(self):

70 return gws.u.omit(vars(self), 'saMeta')

72 def configure(self):

73 gws.config.util.configure_database_provider_for(self, ext_type='postgres')

74 self.crs = gws.lib.crs.get(self.cfg('crs'))

75 self.schema = self.cfg('schema', default='public')

76 self.excludeGemarkung = set(self.cfg('excludeGemarkung', default=[]))

77 self.gemarkungFilter = set(self.cfg('gemarkungFilter', default=[]))

78 self.saMeta = sa.MetaData(schema=self.schema)

79 self.tables = {}

81 def activate(self):

82 self.saMeta = sa.MetaData(schema=self.schema)

83 self.tables = {}

85 self.columnDct = {

86 TABLE_PLACE: [

87 sa.Column('uid', sa.Text, primary_key=True),

88 sa.Column('data', JSONB),

89 ],

90 TABLE_FLURSTUECK: [

91 sa.Column('uid', sa.Text, primary_key=True),

92 sa.Column('rc', sa.Integer),

93 sa.Column('fshistoric', sa.Boolean),

94 sa.Column('data', JSONB),

95 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),

96 ],

97 TABLE_BUCHUNGSBLATT: [

98 sa.Column('uid', sa.Text, primary_key=True),

99 sa.Column('rc', sa.Integer),

100 sa.Column('data', JSONB),

101 ],

102 TABLE_LAGE: [

103 sa.Column('uid', sa.Text, primary_key=True),

104 sa.Column('rc', sa.Integer),

105 sa.Column('data', JSONB),

106 ],

107 TABLE_PART: [

108 sa.Column('n', sa.Integer, primary_key=True),

109 sa.Column('fs', sa.Text, index=True),

110 sa.Column('uid', sa.Text, index=True),

111 sa.Column('beginnt', sa.DateTime),

112 sa.Column('endet', sa.DateTime),

113 sa.Column('kind', sa.Integer),

114 sa.Column('name', sa.Text),

115 sa.Column('parthistoric', sa.Boolean),

116 sa.Column('data', JSONB),

117 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),

118 ],

119 TABLE_INDEXFLURSTUECK: [

120 sa.Column('n', sa.Integer, primary_key=True),

121 sa.Column('fs', sa.Text, index=True),

122 sa.Column('fshistoric', sa.Boolean, index=True),

123 sa.Column('land', sa.Text, index=True),

124 sa.Column('land_t', sa.Text, index=True),

125 sa.Column('landcode', sa.Text, index=True),

126 sa.Column('regierungsbezirk', sa.Text, index=True),

127 sa.Column('regierungsbezirk_t', sa.Text, index=True),

128 sa.Column('regierungsbezirkcode', sa.Text, index=True),

129 sa.Column('kreis', sa.Text, index=True),

130 sa.Column('kreis_t', sa.Text, index=True),

131 sa.Column('kreiscode', sa.Text, index=True),

132 sa.Column('gemeinde', sa.Text, index=True),

133 sa.Column('gemeinde_t', sa.Text, index=True),

134 sa.Column('gemeindecode', sa.Text, index=True),

135 sa.Column('gemarkung', sa.Text, index=True),

136 sa.Column('gemarkung_t', sa.Text, index=True),

137 sa.Column('gemarkungcode', sa.Text, index=True),

138 sa.Column('amtlicheflaeche', sa.Float, index=True),

139 sa.Column('geomflaeche', sa.Float, index=True),

140 sa.Column('flurnummer', sa.Text, index=True),

141 sa.Column('zaehler', sa.Text, index=True),

142 sa.Column('nenner', sa.Text, index=True),

143 sa.Column('flurstuecksfolge', sa.Text, index=True),

144 sa.Column('flurstueckskennzeichen', sa.Text, index=True),

145 sa.Column('x', sa.Float, index=True),

146 sa.Column('y', sa.Float, index=True),

147 ],

148 TABLE_INDEXLAGE: [

149 sa.Column('n', sa.Integer, primary_key=True),

150 sa.Column('fs', sa.Text, index=True),

151 sa.Column('fshistoric', sa.Boolean, index=True),

152 sa.Column('land', sa.Text, index=True),

153 sa.Column('land_t', sa.Text, index=True),

154 sa.Column('landcode', sa.Text, index=True),

155 sa.Column('regierungsbezirk', sa.Text, index=True),

156 sa.Column('regierungsbezirk_t', sa.Text, index=True),

157 sa.Column('regierungsbezirkcode', sa.Text, index=True),

158 sa.Column('kreis', sa.Text, index=True),

159 sa.Column('kreis_t', sa.Text, index=True),

160 sa.Column('kreiscode', sa.Text, index=True),

161 sa.Column('gemeinde', sa.Text, index=True),

162 sa.Column('gemeinde_t', sa.Text, index=True),

163 sa.Column('gemeindecode', sa.Text, index=True),

164 sa.Column('gemarkung', sa.Text, index=True),

165 sa.Column('gemarkung_t', sa.Text, index=True),

166 sa.Column('gemarkungcode', sa.Text, index=True),

167 sa.Column('lageuid', sa.Text, index=True),

168 sa.Column('lagehistoric', sa.Boolean, index=True),

169 sa.Column('strasse', sa.Text, index=True),

170 sa.Column('strasse_t', sa.Text, index=True),

171 sa.Column('hausnummer', sa.Text, index=True),

172 sa.Column('x', sa.Float, index=True),

173 sa.Column('y', sa.Float, index=True),

174 ],

175 TABLE_INDEXBUCHUNGSBLATT: [

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('buchungsblattuid', sa.Text, index=True),

180 sa.Column('buchungsblattbeginnt', sa.DateTime, index=True),

181 sa.Column('buchungsblattendet', sa.DateTime, index=True),

182 sa.Column('buchungsblattkennzeichen', sa.Text, index=True),

183 sa.Column('buchungsblatthistoric', sa.Boolean, index=True),

184 ],

185 TABLE_INDEXPERSON: [

186 sa.Column('n', sa.Integer, primary_key=True),

187 sa.Column('fs', sa.Text, index=True),

188 sa.Column('fshistoric', sa.Boolean, index=True),

189 sa.Column('personuid', sa.Text, index=True),

190 sa.Column('personhistoric', sa.Boolean, index=True),

191 sa.Column('name', sa.Text, index=True),

192 sa.Column('name_t', sa.Text, index=True),

193 sa.Column('vorname', sa.Text, index=True),

194 sa.Column('vorname_t', sa.Text, index=True),

195 ],

196 TABLE_INDEXGEOM: [

197 sa.Column('n', sa.Integer, primary_key=True),

198 sa.Column('fs', sa.Text, index=True),

199 sa.Column('fshistoric', sa.Boolean, index=True),

200 sa.Column('geomflaeche', sa.Float, index=True),

201 sa.Column('x', sa.Float, index=True),

202 sa.Column('y', sa.Float, index=True),

203 sa.Column('geom', sa.geo.Geometry(srid=self.crs.srid)),

204 ],

205 }

206

207 ##

208

209 def table(self, table_id: str) -> sa.Table:

210 if table_id not in self.tables:

211 table_name = f'alkis_{self.VERSION}_{table_id}'

212 self.tables[table_id] = sa.Table(

213 table_name,

214 self.saMeta,

215 *self.columnDct[table_id],

216 schema=self.schema,

217 )

218 return self.tables[table_id]

219

220 def table_size(self, table_id) -> int:

221 sizes = self._table_size_map([table_id])

222 return sizes.get(table_id, 0)

223

224 def _table_size_map(self, table_ids):

225 d = {}

226

227 with self.db.connect():

228 for table_id in table_ids:

229 try:

230 d[table_id] = self.db.count(self.table(table_id))

231 except sa.exc.SQLAlchemyError:

232 d[table_id] = 0

233

234 return d

235

236 def has_table(self, table_id: str) -> bool:

237 return self.table_size(table_id) > 0

238

239 def status(self) -> dt.IndexStatus:

240 sizes = self._table_size_map(self.ALL_TABLES)

241 s = dt.IndexStatus(

242 basic=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BASIC),

243 buchung=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_BUCHUNG),

244 eigentuemer=all(sizes.get(tid, 0) > 0 for tid in self.TABLES_EIGENTUEMER),

245 )

246 s.complete = s.basic and s.buchung and s.eigentuemer

247 s.missing = all(v == 0 for v in sizes.values())

248 gws.log.info(f'ALKIS: table sizes {sizes!r}')

249 return s

250

251 def drop_table(self, table_id: str):

252 with self.db.connect() as conn:

253 self._drop_table(conn, table_id)

254 conn.commit()

255

256 def drop(self):

257 with self.db.connect() as conn:

258 for table_id in self.ALL_TABLES:

259 self._drop_table(conn, table_id)

260 conn.commit()

261

262 def _drop_table(self, conn, table_id):

263 tab = self.table(table_id)

264 conn.execute(sa.text(f'DROP TABLE IF EXISTS {self.schema}.{tab.name}'))

265

266 INSERT_SIZE = 5000

267

268 def create_table(

269 self,

270 table_id: str,

271 values: list[dict],

272 progress: Optional[ProgressIndicator] = None,

273 ):

274 tab = self.table(table_id)

275 self.saMeta.create_all(self.db.engine(), tables=[tab])

276

277 with self.db.connect() as conn:

278 for i in range(0, len(values), self.INSERT_SIZE):

279 vals = values[i : i + self.INSERT_SIZE]

280 conn.execute(sa.insert(tab).values(vals))

281 conn.commit()

282 if progress:

283 progress.update(len(vals))

284

285 ##

286

287 _defaultLand: dt.EnumPair = None

288

289 def default_land(self):

290 if self._defaultLand:

291 return self._defaultLand

292

293 with self.db.connect() as conn:

294 sel = sa.select(self.table(TABLE_PLACE)).where(sa.text("data->>'kind' = 'gemarkung'")).limit(1)

295 for r in conn.execute(sel):

296 p = unserialize(r.data)

297 self._defaultLand = p.land

298 gws.log.debug(f'ALKIS: defaultLand={vars(self._defaultLand)}')

299 return self._defaultLand

300

301 _strasseList: list[dt.Strasse] = []

302

303 def strasse_list(self) -> list[dt.Strasse]:

304 if self._strasseList:

305 return self._strasseList

306

307 self._strasseList = []

308

309 indexlage = self.table(TABLE_INDEXLAGE)

310 cols = (

311 indexlage.c.gemeinde,

312 indexlage.c.gemeindecode,

313 indexlage.c.gemarkung,

314 indexlage.c.gemarkungcode,

315 indexlage.c.strasse,

316 )

317

318 sel = sa.select(*cols).group_by(*cols)

319 if self.gemarkungFilter:

320 sel = sel.where(indexlage.c.gemarkungcode.in_(self.gemarkungFilter))

321

322 with self.db.connect() as conn:

323 for r in conn.execute(sel):

324 self._strasseList.append(

325 dt.Strasse(

326 gemeinde=dt.EnumPair(r.gemeindecode, r.gemeinde),

327 gemarkung=dt.EnumPair(r.gemarkungcode, r.gemarkung),

328 name=r.strasse,

329 )

330 )

331

332 return self._strasseList

333

334 def find_adresse(self, q: dt.AdresseQuery) -> list[dt.Adresse]:

335 indexlage = self.table(TABLE_INDEXLAGE)

336

337 qo = q.options or dt.AdresseQueryOptions()

338 sel = self._make_adresse_select(q, qo)

339

340 lage_uids = []

341 adresse_map = {}

342

343 with self.db.connect() as conn:

344 for r in conn.execute(sel):

345 lage_uids.append(r[0])

346

347 if qo.limit and len(lage_uids) > qo.limit:

348 raise gws.ResponseTooLargeError(len(lage_uids))

349

350 if qo.offset:

351 lage_uids = lage_uids[qo.offset :]

352 if qo.pageSize:

353 lage_uids = lage_uids[: qo.pageSize]

354

355 sel = indexlage.select().where(indexlage.c.lageuid.in_(lage_uids))

356

357 for r in conn.execute(sel):

358 r = gws.u.to_dict(r)

359 uid = r['lageuid']

360 adresse_map[uid] = dt.Adresse(

361 uid=uid,

362 land=dt.EnumPair(r['landcode'], r['land']),

363 regierungsbezirk=dt.EnumPair(r['regierungsbezirkcode'], r['regierungsbezirk']),

364 kreis=dt.EnumPair(r['kreiscode'], r['kreis']),

365 gemeinde=dt.EnumPair(r['gemeindecode'], r['gemeinde']),

366 gemarkung=dt.EnumPair(r['gemarkungcode'], r['gemarkung']),

367 strasse=r['strasse'],

368 hausnummer=r['hausnummer'],

369 x=r['x'],

370 y=r['y'],

371 shape=gws.base.shape.from_xy(r['x'], r['y'], crs=self.crs),

372 )

373

374 return gws.u.compact(adresse_map.get(uid) for uid in lage_uids)

375

376 def find_flurstueck(self, q: dt.FlurstueckQuery) -> list[dt.Flurstueck]:

377 qo = q.options or dt.FlurstueckQueryOptions()

378 sel = self._make_flurstueck_select(q, qo)

379

380 fs_uids = []

381

382 with self.db.connect() as conn:

383 for r in conn.execute(sel):

384 uid = r[0].partition('_')[0]

385 if uid not in fs_uids:

386 fs_uids.append(uid)

387

388 if qo.limit and len(fs_uids) > qo.limit:

389 raise gws.ResponseTooLargeError(len(fs_uids))

390

391 if qo.offset:

392 fs_uids = fs_uids[qo.offset :]

393 if qo.pageSize:

394 fs_uids = fs_uids[: qo.pageSize]

395

396 fs_list = self._load_flurstueck(conn, fs_uids, qo)

397

398 return fs_list

399

400 def count_all(self, qo: dt.FlurstueckQueryOptions) -> int:

401 indexfs = self.table(TABLE_INDEXFLURSTUECK)

402 sel = sa.select(sa.func.count()).select_from(indexfs)

403 if not qo.withHistorySearch:

404 sel = sel.where(~indexfs.c.fshistoric)

405

406 with self.db.connect() as conn:

407 r = list(conn.execute(sel))

408 return r[0][0]

409

410 def iter_all(self, qo: dt.FlurstueckQueryOptions) -> Iterable[dt.Flurstueck]:

411 indexfs = self.table(TABLE_INDEXFLURSTUECK)

412 sel = sa.select(indexfs.c.fs).with_only_columns(indexfs.c.fs).order_by(indexfs.c.n)

413 if not qo.withHistorySearch:

414 sel = sel.where(~indexfs.c.fshistoric)

415

416 offset = 0

417

418 # NB the consumer might be slow, close connection on each chunk

419

420 while True:

421 with self.db.connect() as conn:

422 sel2 = sel.offset(offset).limit(qo.pageSize)

423 fs_uids = [r[0] for r in conn.execute(sel2)]

424 if not fs_uids:

425 break

426 fs_list = self._load_flurstueck(conn, fs_uids, qo)

427

428 yield from fs_list

429

430 offset += qo.pageSize

431

432 HAUSNUMMER_NOT_NULL_VALUE = '*'

433

434 def _make_flurstueck_select(self, q: dt.FlurstueckQuery, qo: dt.FlurstueckQueryOptions):

435 indexfs = self.table(TABLE_INDEXFLURSTUECK)

436 indexbuchungsblatt = self.table(TABLE_INDEXBUCHUNGSBLATT)

437 indexgeom = self.table(TABLE_INDEXGEOM)

438 indexlage = self.table(TABLE_INDEXLAGE)

439 indexperson = self.table(TABLE_INDEXPERSON)

440

441 where = []

442

443 has_buchungsblatt = False

444 has_geom = False

445 has_lage = False

446 has_person = False

447

448 where.extend(self._make_places_where(q, indexfs))

449

450 if q.uids:

451 where.append(indexfs.c.fs.in_(q.uids))

452

453 for f in 'flurnummer', 'flurstuecksfolge', 'zaehler', 'nenner':

454 val = getattr(q, f, None)

455 if val is not None:

456 where.append(getattr(indexfs.c, f.lower()) == val)

457

458 if q.flurstueckskennzeichen:

459 val = re.sub(r'[^0-9_]', '', q.flurstueckskennzeichen)

460 if not val:

461 raise gws.BadRequestError(f'invalid flurstueckskennzeichen {q.flurstueckskennzeichen!r}')

462 where.append(indexfs.c.flurstueckskennzeichen.like(val + '%'))

463

464 if q.flaecheVon:

465 try:

466 where.append(indexfs.c.amtlicheflaeche >= float(q.flaecheVon))

467 except ValueError:

468 raise gws.BadRequestError(f'invalid flaecheVon {q.flaecheVon!r}')

469

470 if q.flaecheBis:

471 try:

472 where.append(indexfs.c.amtlicheflaeche <= float(q.flaecheBis))

473 except ValueError:

474 raise gws.BadRequestError(f'invalid flaecheBis {q.flaecheBis!r}')

475

476 if q.buchungsblattkennzeichenList:

477 ws = []

478

479 for s in q.buchungsblattkennzeichenList:

480 w = text_search_clause(

481 indexbuchungsblatt.c.buchungsblattkennzeichen,

482 s,

483 qo.buchungsblattSearchOptions,

484 )

485 if w is not None:

486 ws.append(w)

487 if ws:

488 has_buchungsblatt = True

489 where.append(sa.or_(*ws))

490

491 if q.strasse:

492 w = text_search_clause(

493 indexlage.c.strasse_t,

494 strasse_key(q.strasse),

495 qo.strasseSearchOptions,

496 )

497 if w is not None:

498 has_lage = True

499 where.append(w)

500

501 if q.hausnummer:

502 if not has_lage:

503 raise gws.BadRequestError(f'hausnummer without strasse')

504 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

505 where.append(indexlage.c.hausnummer.is_not(None))

506 else:

507 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))

508

509 if q.personName:

510 w = text_search_clause(indexperson.c.name_t, text_key(q.personName), qo.nameSearchOptions)

511 if w is not None:

512 has_person = True

513 where.append(w)

514

515 if q.personVorname:

516 if not has_person:

517 raise gws.BadRequestError(f'personVorname without personName')

518 w = text_search_clause(indexperson.c.vorname_t, text_key(q.personVorname), qo.nameSearchOptions)

519 if w is not None:

520 where.append(w)

521

522 if q.shape:

523 has_geom = True

524 where.append(

525 sa.func.st_intersects(

526 indexgeom.c.geom,

527 sa.cast(

528 q.shape.transformed_to(self.crs).to_ewkb_hex(),

529 sa.geo.Geometry(),

530 ),

531 )

532 )

533

534 join = []

535

536 if has_buchungsblatt:

537 join.append([indexbuchungsblatt, indexbuchungsblatt.c.fs == indexfs.c.fs])

538 if not qo.withHistorySearch:

539 where.append(~indexbuchungsblatt.c.fshistoric)

540 where.append(~indexbuchungsblatt.c.buchungsblatthistoric)

541

542 if has_geom:

543 join.append([indexgeom, indexgeom.c.fs == indexfs.c.fs])

544 if not qo.withHistorySearch:

545 where.append(~indexgeom.c.fshistoric)

546

547 if has_lage:

548 join.append([indexlage, indexlage.c.fs == indexfs.c.fs])

549 if not qo.withHistorySearch:

550 where.append(~indexlage.c.fshistoric)

551 where.append(~indexlage.c.lagehistoric)

552

553 if has_person:

554 join.append([indexperson, indexperson.c.fs == indexfs.c.fs])

555 if not qo.withHistorySearch:

556 where.append(~indexperson.c.fshistoric)

557 where.append(~indexperson.c.personhistoric)

558

559 if not qo.withHistorySearch:

560 where.append(~indexfs.c.fshistoric)

561

562 sel = sa.select(sa.distinct(indexfs.c.fs))

563

564 for tab, cond in join:

565 sel = sel.join(tab, cond)

566

567 sel = sel.where(*where)

568

569 return self._make_sort(sel, qo.sort, indexfs)

570

571 def _make_adresse_select(self, q: dt.AdresseQuery, qo: dt.AdresseQueryOptions):

572 indexlage = self.table(TABLE_INDEXLAGE)

573 where = []

574

575 where.extend(self._make_places_where(q, indexlage))

576

577 has_strasse = False

578

579 if q.strasse:

580 w = text_search_clause(

581 indexlage.c.strasse_t,

582 strasse_key(q.strasse),

583 qo.strasseSearchOptions,

584 )

585 if w is not None:

586 has_strasse = True

587 where.append(w)

588

589 if q.hausnummer:

590 if not has_strasse:

591 raise gws.BadRequestError(f'hausnummer without strasse')

592 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

593 where.append(indexlage.c.hausnummer.is_not(None))

594 else:

595 where.append(indexlage.c.hausnummer == normalize_hausnummer(q.hausnummer))

596

597 if q.bisHausnummer:

598 if not has_strasse:

599 raise gws.BadRequestError(f'hausnummer without strasse')

600 where.append(indexlage.c.hausnummer < normalize_hausnummer(q.bisHausnummer))

601

602 if q.hausnummerNotNull:

603 if not has_strasse:

604 raise gws.BadRequestError(f'hausnummer without strasse')

605 where.append(indexlage.c.hausnummer.is_not(None))

606

607 if not qo.withHistorySearch:

608 where.append(~indexlage.c.lagehistoric)

609

610 sel = sa.select(sa.distinct(indexlage.c.lageuid))

611

612 sel = sel.where(*where)

613

614 return self._make_sort(sel, qo.sort, indexlage)

615

616 def _make_places_where(self, q: dt.FlurstueckQuery | dt.AdresseQuery, table: sa.Table):

617 where = []

618 land_code = ''

619

620 for f in 'land', 'regierungsbezirk', 'kreis', 'gemarkung', 'gemeinde':

621 val = getattr(q, f, None)

622 if val is not None:

623 where.append(getattr(table.c, f.lower() + '_t') == text_key(val))

624

625 val = getattr(q, f + 'Code', None)

626 if val is not None:

627 if f == 'land':

628 land_code = val

629 elif f == 'gemarkung' and len(val) <= 4:

630 if not land_code:

631 land = self.default_land()

632 if land:

633 land_code = land.code

634 val = land_code + val

635

636 where.append(getattr(table.c, f.lower() + 'code') == val)

637

638 if self.gemarkungFilter:

639 where.append(table.c.gemarkungcode.in_(self.gemarkungFilter))

640

641 return where

642

643 def _make_sort(self, sel, sort, table: sa.Table):

644 if not sort:

645 return sel

646

647 order = []

648 for s in sort:

649 fn = sa.desc if s.reverse else sa.asc

650 order.append(fn(getattr(table.c, s.fieldName)))

651 sel = sel.order_by(*order)

652

653 return sel

654

655 def load_flurstueck(self, fs_uids: list[str], qo: dt.FlurstueckQueryOptions) -> list[dt.Flurstueck]:

656 with self.db.connect() as conn:

657 return self._load_flurstueck(conn, fs_uids, qo)

658

659 def _load_flurstueck(self, conn, fs_uids, qo: dt.FlurstueckQueryOptions):

660 with_lage = dt.DisplayTheme.lage in qo.displayThemes

661 with_gebaeude = dt.DisplayTheme.gebaeude in qo.displayThemes

662 with_nutzung = dt.DisplayTheme.nutzung in qo.displayThemes

663 with_festlegung = dt.DisplayTheme.festlegung in qo.displayThemes

664 with_bewertung = dt.DisplayTheme.bewertung in qo.displayThemes

665 with_buchung = dt.DisplayTheme.buchung in qo.displayThemes

666 with_eigentuemer = dt.DisplayTheme.eigentuemer in qo.displayThemes

667

668 tab = self.table(TABLE_FLURSTUECK)

669 sel = sa.select(tab).where(tab.c.uid.in_(set(fs_uids)))

670

671 hd = qo.withHistoryDisplay

672

673 fs_list = []

674

675 for r in conn.execute(sel):

676 fs = unserialize(r.data)

677 fs.geom = r.geom

678 fs_list.append(fs)

679

680 fs_list = self._remove_historic(fs_list, hd)

681 if not fs_list:

682 return []

683

684 fs_map = {fs.uid: fs for fs in fs_list}

685

686 for fs in fs_map.values():

687 fs.shape = gws.base.shape.from_wkb_element(fs.geom, default_crs=self.crs)

688

689 fs.lageList = self._remove_historic(fs.lageList, hd) if with_lage else []

690 fs.gebaeudeList = self._remove_historic(fs.gebaeudeList, hd) if with_gebaeude else []

691 fs.buchungList = self._remove_historic(fs.buchungList, hd) if with_buchung else []

692

693 fs.bewertungList = []

694 fs.festlegungList = []

695 fs.nutzungList = []

696

697 if with_buchung:

698 bb_uids = set(bu.buchungsblattUid for fs in fs_map.values() for bu in fs.buchungList)

699

700 tab = self.table(TABLE_BUCHUNGSBLATT)

701 sel = sa.select(tab).where(tab.c.uid.in_(bb_uids))

702 bb_list = [unserialize(r.data) for r in conn.execute(sel)]

703 bb_list = self._remove_historic(bb_list, hd)

704

705 for bb in bb_list:

706 bb.buchungsstelleList = self._remove_historic(bb.buchungsstelleList, hd)

707 bb.namensnummerList = self._remove_historic(bb.namensnummerList, hd) if with_eigentuemer else []

708 for nn in bb.namensnummerList:

709 nn.personList = self._remove_historic(nn.personList, hd)

710 for pe in nn.personList:

711 pe.anschriftList = self._remove_historic(pe.anschriftList, hd)

712

713 bb_map = {bb.uid: bb for bb in bb_list}

714

715 for fs in fs_map.values():

716 for bu in fs.buchungList:

717 bu.buchungsblatt = bb_map.get(bu.buchungsblattUid, hd)

718

719 if with_nutzung or with_festlegung or with_bewertung:

720 tab = self.table(TABLE_PART)

721 sel = sa.select(tab).where(tab.c.fs.in_(list(fs_map)))

722 if not qo.withHistorySearch:

723 sel.where(~tab.c.parthistoric)

724 pa_list = [unserialize(r.data) for r in conn.execute(sel)]

725 pa_list = self._remove_historic(pa_list, hd)

726

727 for pa in pa_list:

728 fs = fs_map[pa.fs]

729 if pa.kind == dt.PART_NUTZUNG and with_nutzung:

730 fs.nutzungList.append(pa)

731 if pa.kind == dt.PART_FESTLEGUNG and with_festlegung:

732 fs.festlegungList.append(pa)

733 if pa.kind == dt.PART_BEWERTUNG and with_bewertung:

734 fs.bewertungList.append(pa)

735

736 return gws.u.compact(fs_map.get(uid) for uid in fs_uids)

737

738 _historicKeys = ['vorgaengerFlurstueckskennzeichen']

739

740 def _remove_historic(self, objects, with_history_display: bool):

741 if with_history_display:

742 return objects

743

744 out = []

745

746 for o in objects:

747 if o.isHistoric:

748 continue

749

750 o.recs = [r for r in o.recs if not r.isHistoric]

751 if not o.recs:

752 continue

753

754 for r in o.recs:

755 for k in self._historicKeys:

756 try:

757 delattr(r, k)

758 except AttributeError:

759 pass

761 out.append(o)

763 return out

766##

769def serialize(o: dt.Object, encode_enum_pairs=True) -> dict:

770 def encode(r):

771 if not r:

772 return r

773

774 if isinstance(r, (int, float, str, bool)):

775 return r

776

777 if isinstance(r, (datetime.date, datetime.datetime)):

778 return f'{r.day:02}.{r.month:02}.{r.year:04}'

779

780 if isinstance(r, list):

781 return [encode(e) for e in r]

782

783 if isinstance(r, dt.EnumPair):

784 if encode_enum_pairs:

785 return f'${r.code}${r.text}'

786 return vars(r)

787

788 if isinstance(r, dt.Object):

789 return {k: encode(v) for k, v in sorted(vars(r).items())}

791 return str(r)

793 return encode(o)

796def unserialize(data: dict):

797 def decode(r):

798 if not r:

799 return r

800 if isinstance(r, str):

801 if r[0] == '$':

802 s = r.split('$')

803 return dt.EnumPair(s[1], s[2])

804 return r

805 if isinstance(r, list):

806 return [decode(e) for e in r]

807 if isinstance(r, dict):

808 d = {k: decode(v) for k, v in r.items()}

809 return dt.Object(**d)

810 return r

811

812 return decode(data)

815##

818def text_key(s):

819 """Normalize a text string for full-text search."""

820

821 if s is None:

822 return ''

823

824 s = _text_umlauts(str(s).strip().lower())

825 return _text_nopunct(s)

826

827

828def strasse_key(s):

829 """Normalize a steet name for full-text search."""

830

831 if s is None:

832 return ''

833

834 s = _text_umlauts(str(s).strip().lower())

835

836 s = re.sub(r'\s?str\.$', '.strasse', s)

837 s = re.sub(r'\s?pl\.$', '.platz', s)

839

840 return _text_nopunct(s)

841

842

843def _text_umlauts(s):

844 s = s.replace('ä', 'ae')

845 s = s.replace('ë', 'ee')

846 s = s.replace('ö', 'oe')

847 s = s.replace('ü', 'ue')

848 s = s.replace('ß', 'ss')

849

850 return s

851

852

853def _text_nopunct(s):

854 return re.sub(r'\W+', ' ', s)

855

856

857def normalize_hausnummer(s):

858 """Clean up house number formatting."""

859

860 if s is None:

861 return ''

862

863 # "12 a" -> "12a"

864 s = re.sub(r'\s+', '', s.strip())

865 return s

866

867

868def make_fsnummer(r: dt.FlurstueckRecord):

869 """Create a 'fsnummer' for a Flurstueck, which is 'flur-zaeher/nenner (folge)'."""

870

871 v = r.gemarkung.code + ' '

872

873 s = r.flurnummer

874 if s:

875 v += str(s) + '-'

876

877 v += str(r.zaehler)

878 s = r.nenner

879 if s:

880 v += '/' + str(s)

881

882 s = r.flurstuecksfolge

883 if s and str(s) != '00':

884 v += ' (' + str(s) + ')'

885

886 return v

887

888

889# parse a fsnummer in the above format, all parts are optional

890

891_RE_FSNUMMER = r"""(?x)

892 ^

893 (

894 (?P<gemarkungCode> [0-9]+)

895 \s+

896 )?

897 (

898 (?P<flurnummer> [0-9]+)

899 -

900 )?

901 (

902 (?P<zaehler> [0-9]+)

903 (/

904 (?P<nenner> \w+)

905 )?

906 )?

907 (

908 \s*

909 \(

910 (?P<flurstuecksfolge> [0-9]+)

911 \)

912 )?

913 $

914"""

915

916

917def parse_fsnummer(s):

918 """Parse a Flurstueck fsnummer into parts."""

919

920 m = re.match(_RE_FSNUMMER, s.strip())

921 if not m:

922 return None

923 return gws.u.compact(m.groupdict())

924

925

926def text_search_clause(column, val, tso: gws.TextSearchOptions):

927 # @TODO merge with model_field/text

928

929 if val is None:

930 return

931

932 val = str(val).strip()

933 if len(val) == 0:

934 return

935

936 if not tso:

937 return column == val

938

939 if tso.minLength and len(val) < tso.minLength:

940 return

941

942 if tso.type == gws.TextSearchType.exact:

943 return column == val

944

945 if tso.type == gws.TextSearchType.any:

946 val = '%' + _escape_like(val) + '%'

947 if tso.type == gws.TextSearchType.begin:

948 val = _escape_like(val) + '%'

949 if tso.type == gws.TextSearchType.end:

950 val = '%' + _escape_like(val)

951

952 if tso.caseSensitive:

953 return column.like(val, escape='\\')

954

955 return column.ilike(val, escape='\\')

956

957

958def _escape_like(s, escape='\\'):

959 return s.replace(escape, escape + escape).replace('%', escape + '%').replace('_', escape + '_')

962##

965_FLATTEN_EXCLUDE_KEYS = {'fsUids', 'childUids', 'parentUids'}

966

967

968def flatten_fs(fs: dt.Flurstueck, keys_to_extract: set[str]) -> list[dict]:

969 """Flatten a Flurstueck into a list of dicts with keys from keys_to_extract."""

970

971 return _flatten(fs, 'fs', keys_to_extract, [{}])

972

973

974def _flatten(val, key, keys_to_extract, flat_lst):

975 if not any(k.startswith(key) for k in keys_to_extract):

976 return flat_lst

977

978 if isinstance(val, list):

979 if not val:

980 return flat_lst

981

982 # create a cartesian product of all dicts in flat_dct with each item in val

983 new_lst = []

984 for v in val:

985 for d2 in _flatten(v, key, keys_to_extract, [{}]):

986 for d in flat_lst:

987 new_lst.append(d | d2)

988 return new_lst

989

990 if isinstance(val, (dt.Object, dt.EnumPair)):

991 for k, v in vars(val).items():

992 if k not in _FLATTEN_EXCLUDE_KEYS:

993 flat_lst = _flatten(v, f'{key}_{k}', keys_to_extract, flat_lst)

994 return flat_lst

995

996 for d in flat_lst:

997 d[key] = val

998

999 return flat_lst

1000

1001

1002def all_flat_keys():

1003 """Return a dict key->type for all flat keys in the Flurstueck structure."""

1004

1005 return {k: typ for k, typ in sorted(set(_get_flat_keys(dt.Flurstueck, 'fs')))}

1006

1007

1008def _get_flat_keys(cls, key):

1009 if isinstance(cls, str):

1010 cls = getattr(dt, cls, None)

1011

1012 if cls is dt.EnumPair:

1013 yield f'{key}_code', int

1014 yield f'{key}_text', str

1015 return

1016

1017 if not cls or not hasattr(cls, '__annotations__'):

1018 yield key, cls or str

1019 return

1020

1021 for k, typ in cls.__annotations__.items():

1022 if k in _FLATTEN_EXCLUDE_KEYS:

1023 continue

1024 if getattr(typ, '__origin__', None) is list:

1025 yield from _get_flat_keys(typ.__args__[0], f'{key}_{k}')

1026 else:

1027 yield from _get_flat_keys(typ, f'{key}_{k}')