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

1from typing import Optional

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.gis.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 Status(gws.Data):

33 """Index status"""

35 complete: bool

36 basic: bool

37 eigentuemer: bool

38 buchung: bool

41class Object(gws.Node):

42 VERSION = '8'

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 ]

54 TABLES_BUCHUNG = [

55 TABLE_BUCHUNGSBLATT,

56 TABLE_INDEXBUCHUNGSBLATT,

57 ]

59 TABLES_EIGENTUEMER = [

60 TABLE_BUCHUNGSBLATT,

61 TABLE_INDEXBUCHUNGSBLATT,

62 TABLE_INDEXPERSON,

63 ]

65 ALL_TABLES = TABLES_BASIC + TABLES_BUCHUNG + TABLES_EIGENTUEMER

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

68 crs: gws.Crs

69 schema: str

70 excludeGemarkung: set[str]

72 saMeta: sa.MetaData

73 tables: dict[str, sa.Table]

75 columnDct = {}

77 def __getstate__(self):

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

80 def configure(self):

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

82 self.crs = gws.gis.crs.get(self.cfg('crs'))

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

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

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

86 self.tables = {}

88 def activate(self):

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

90 self.tables = {}

92 self.columnDct = {

93 TABLE_PLACE: [

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

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

96 ],

97 TABLE_FLURSTUECK: [

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

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

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

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

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

103 ],

104 TABLE_BUCHUNGSBLATT: [

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

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

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

108 ],

109 TABLE_LAGE: [

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

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

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

113 ],

114 TABLE_PART: [

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

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

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

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

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

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

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

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

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

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

125 ],

126 TABLE_INDEXFLURSTUECK: [

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

128

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

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

131

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

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

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

135

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

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

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

139

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

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

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

143

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

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

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

147

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

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

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

151

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

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

154

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

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

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

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

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

160

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

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

163 ],

164 TABLE_INDEXLAGE: [

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

166

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

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

169

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

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

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

173

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

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

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

177

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

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

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

181

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

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

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

185

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

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

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

189

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

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

192

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

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

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

199 ],

200 TABLE_INDEXBUCHUNGSBLATT: [

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

202

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

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

205

206 sa.Column('buchungsblattuid', sa.Text, index=True),

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

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

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

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

211 ],

212 TABLE_INDEXPERSON: [

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

214

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

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

217

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

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

220

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

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

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

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

225 ],

226 TABLE_INDEXGEOM: [

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

228

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

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

231

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

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

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

235

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

237 ],

238 }

239

240 ##

241

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

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]

252

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

254 sizes = self._table_size_map([table_id])

255 return sizes.get(table_id, 0)

256

257 def _table_size_map(self, table_ids):

258 d = {}

259

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

261 for table_id in table_ids:

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

263

264 return d

265

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

267 return self.table_size(table_id) > 0

268

269 def status(self) -> Status:

270 sizes = self._table_size_map(self.ALL_TABLES)

271 s = Status(

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

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

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

275 )

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

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

278 return s

279

280 def drop_table(self, table_id: str):

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

282 self._drop_table(conn, table_id)

283 conn.commit()

284

285 def drop(self):

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

287 for table_id in self.ALL_TABLES:

288 self._drop_table(conn, table_id)

289 conn.commit()

290

291 def _drop_table(self, conn, table_id):

292 tab = self.table(table_id)

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

294

295 INSERT_SIZE = 5000

296

297 def create_table(

298 self,

299 table_id: str,

300 values: list[dict],

301 progress: Optional[ProgressIndicator] = None

302 ):

303 tab = self.table(table_id)

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

305

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

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

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

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

310 conn.commit()

311 if progress:

312 progress.update(len(vals))

313

314 ##

315

316 _defaultLand: dt.EnumPair = None

317

318 def default_land(self):

319 if self._defaultLand:

320 return self._defaultLand

321

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

323 sel = (

324 sa

325 .select(self.table(TABLE_PLACE))

326 .where(sa.text("data->>'kind' = 'gemarkung'"))

327 .limit(1)

328 )

329 for r in conn.execute(sel):

330 p = unserialize(r.data)

331 self._defaultLand = p.land

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

333 return self._defaultLand

334

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

336

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

338

339 if self._strasseList:

340 return self._strasseList

341

342 indexlage = self.table(TABLE_INDEXLAGE)

343 cols = (

344 indexlage.c.gemeinde,

345 indexlage.c.gemeindecode,

346 indexlage.c.gemarkung,

347 indexlage.c.gemarkungcode,

348 indexlage.c.strasse,

349 )

350

351 self._strasseList = []

352

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

354 for r in conn.execute(sa.select(*cols).group_by(*cols)):

355 self._strasseList.append(dt.Strasse(

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

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

358 name=r.strasse

359 ))

360

361 return self._strasseList

362

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

364 indexlage = self.table(TABLE_INDEXLAGE)

365

366 qo = q.options or gws.Data()

367 sel = self._make_adresse_select(q, qo)

368

369 lage_uids = []

370 adresse_map = {}

371

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

373 for r in conn.execute(sel):

374 lage_uids.append(r[0])

375

376 if qo.hardLimit and len(lage_uids) > qo.hardLimit:

377 raise gws.ResponseTooLargeError(len(lage_uids))

378

379 if qo.offset:

380 lage_uids = lage_uids[qo.offset:]

381 if qo.limit:

382 lage_uids = lage_uids[:qo.limit]

383

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

385

386 for r in conn.execute(sel):

387 r = gws.u.to_dict(r)

388 uid = r['lageuid']

389 adresse_map[uid] = dt.Adresse(

390 uid=uid,

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

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

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

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

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

396 strasse=r['strasse'],

397 hausnummer=r['hausnummer'],

398 x=r['x'],

399 y=r['y'],

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

401 )

402

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

404

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

406 qo = q.options or gws.Data()

407 sel = self._make_flurstueck_select(q, qo)

408

409 fs_uids = []

410

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

412 for r in conn.execute(sel):

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

414 if uid not in fs_uids:

415 fs_uids.append(uid)

416

417 if qo.hardLimit and len(fs_uids) > qo.hardLimit:

418 raise gws.ResponseTooLargeError(len(fs_uids))

419

420 if qo.offset:

421 fs_uids = fs_uids[qo.offset:]

422 if qo.limit:

423 fs_uids = fs_uids[:qo.limit]

424

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

426

427 return fs_list

428

429 HAUSNUMMER_NOT_NULL_VALUE = '*'

430

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

432

433 indexfs = self.table(TABLE_INDEXFLURSTUECK)

434 indexbuchungsblatt = self.table(TABLE_INDEXBUCHUNGSBLATT)

435 indexgeom = self.table(TABLE_INDEXGEOM)

436 indexlage = self.table(TABLE_INDEXLAGE)

437 indexperson = self.table(TABLE_INDEXPERSON)

438

439 where = []

440

441 has_buchungsblatt = False

442 has_geom = False

443 has_lage = False

444 has_person = False

445

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

447

448 if q.uids:

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

450

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

452 val = getattr(q, f, None)

453 if val is not None:

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

455

456 if q.flurstueckskennzeichen:

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

458 if not val:

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

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

461

462 if q.flaecheVon:

463 try:

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

465 except ValueError:

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

467

468 if q.flaecheBis:

469 try:

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

471 except ValueError:

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

473

474 if q.buchungsblattkennzeichenList:

475 ws = []

476

477 for s in q.buchungsblattkennzeichenList:

478 w = text_search_clause(indexbuchungsblatt.c.buchungsblattkennzeichen, s, qo.buchungsblattSearchOptions)

479 if w is not None:

480 ws.append(w)

481 if ws:

482 has_buchungsblatt = True

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

484

485 if q.strasse:

486 w = text_search_clause(indexlage.c.strasse_t, strasse_key(q.strasse), qo.strasseSearchOptions)

487 if w is not None:

488 has_lage = True

489 where.append(w)

490

491 if q.hausnummer:

492 if not has_lage:

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

494 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

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

496 else:

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

498

499 if q.personName:

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

501 if w is not None:

502 has_person = True

503 where.append(w)

504

505 if q.personVorname:

506 if not has_person:

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

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

509 if w is not None:

510 where.append(w)

511

512 if q.shape:

513 has_geom = True

514 where.append(sa.func.st_intersects(

515 indexgeom.c.geom,

516 sa.cast(q.shape.transformed_to(self.crs).to_ewkb_hex(), sa.geo.Geometry())))

517

518 join = []

519

520 if has_buchungsblatt:

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

522 if not qo.withHistorySearch:

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

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

525

526 if has_geom:

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

528 if not qo.withHistorySearch:

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

530

531 if has_lage:

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

533 if not qo.withHistorySearch:

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

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

536

537 if has_person:

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

539 if not qo.withHistorySearch:

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

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

542

543 if not qo.withHistorySearch:

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

545

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

547

548 for tab, cond in join:

549 sel = sel.join(tab, cond)

550

551 sel = sel.where(*where)

552

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

554

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

556 indexlage = self.table(TABLE_INDEXLAGE)

557 where = []

558

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

560

561 has_strasse = False

562

563 if q.strasse:

564 w = text_search_clause(indexlage.c.strasse_t, strasse_key(q.strasse), qo.strasseSearchOptions)

565 if w is not None:

566 has_strasse = True

567 where.append(w)

568

569 if q.hausnummer:

570 if not has_strasse:

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

572 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

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

574 else:

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

576

577 if q.bisHausnummer:

578 if not has_strasse:

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

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

581

582 if q.hausnummerNotNull:

583 if not has_strasse:

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

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

586

587 if not qo.withHistorySearch:

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

589

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

591

592 sel = sel.where(*where)

593

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

595

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

597 where = []

598 land_code = ''

599

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

601 val = getattr(q, f, None)

602 if val is not None:

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

604

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

606 if val is not None:

607 if f == 'land':

608 land_code = val

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

610 if not land_code:

611 land = self.default_land()

612 if land:

613 land_code = land.code

614 val = land_code + val

615

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

617

618 return where

619

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

621 if not sort:

622 return sel

623

624 order = []

625 for s in sort:

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

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

628 sel = sel.order_by(*order)

629

630 return sel

631

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

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

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

635

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

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

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

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

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

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

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

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

644

645 tab = self.table(TABLE_FLURSTUECK)

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

647

648 hd = qo.withHistoryDisplay

649

650 fs_list = []

651

652 for r in conn.execute(sel):

653 fs = unserialize(r.data)

654 fs.geom = r.geom

655 fs_list.append(fs)

656

657 fs_list = self._remove_historic(fs_list, hd)

658 if not fs_list:

659 return []

660

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

662

663 for fs in fs_map.values():

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

665

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

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

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

669

670 fs.bewertungList = []

671 fs.festlegungList = []

672 fs.nutzungList = []

673

674 if with_buchung:

675 bb_uids = set(

676 bu.buchungsblattUid

677 for fs in fs_map.values()

678 for bu in fs.buchungList

679 )

680

681 tab = self.table(TABLE_BUCHUNGSBLATT)

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

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

684 bb_list = self._remove_historic(bb_list, hd)

685

686 for bb in bb_list:

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

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

689 for nn in bb.namensnummerList:

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

691 for pe in nn.personList:

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

693

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

695

696 for fs in fs_map.values():

697 for bu in fs.buchungList:

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

699

700 if with_nutzung or with_festlegung or with_bewertung:

701 tab = self.table(TABLE_PART)

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

703 if not qo.withHistorySearch:

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

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

706 pa_list = self._remove_historic(pa_list, hd)

707

708 for pa in pa_list:

709 fs = fs_map[pa.fs]

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

711 fs.nutzungList.append(pa)

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

713 fs.festlegungList.append(pa)

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

715 fs.bewertungList.append(pa)

716

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

718

719 _historicKeys = [

720 'vorgaengerFlurstueckskennzeichen'

721 ]

722

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

724 if with_history_display:

725 return objects

726

727 out = []

728

729 for o in objects:

730 if o.isHistoric:

731 continue

732

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

734 if not o.recs:

735 continue

736

737 for r in o.recs:

738 for k in self._historicKeys:

739 setattr(r, k, None)

741 out.append(o)

743 return out

745##

747def serialize(o: dt.Object) -> dict:

748 def encode(r):

749 if not r:

750 return r

751

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

753 return r

754

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

756 # return str(r)

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

758

759 if isinstance(r, list):

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

761

762 if isinstance(r, dt.EnumPair):

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

764

765 if isinstance(r, dt.Object):

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

767

768 raise ValueError(f'unserializable object type: {r!r}')

769

770 return encode(o)

771

772

773def unserialize(data: dict):

774 def decode(r):

775 if not r:

776 return r

777 if isinstance(r, str):

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

779 s = r.split('$')

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

781 return r

782 if isinstance(r, list):

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

784 if isinstance(r, dict):

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

786 return dt.Object(**d)

787 return r

788

789 return decode(data)

792##

795def text_key(s):

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

797

798 if s is None:

799 return ''

800

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

802 return _text_nopunct(s)

803

804

805def strasse_key(s):

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

807

808 if s is None:

809 return ''

810

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

812

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

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

816

817 return _text_nopunct(s)

818

819

820def _text_umlauts(s):

821 s = s.replace(u'ä', 'ae')

822 s = s.replace(u'ë', 'ee')

823 s = s.replace(u'ö', 'oe')

824 s = s.replace(u'ü', 'ue')

825 s = s.replace(u'ß', 'ss')

826

827 return s

828

829

830def _text_nopunct(s):

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

832

833

834def normalize_hausnummer(s):

835 """Clean up house number formatting."""

836

837 if s is None:

838 return ''

839

840 # "12 a" -> "12a"

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

842 return s

843

844

845def make_fsnummer(r: dt.FlurstueckRecord):

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

847

848 v = r.gemarkung.code + ' '

849

850 s = r.flurnummer

851 if s:

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

853

854 v += str(r.zaehler)

855 s = r.nenner

856 if s:

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

858

859 s = r.flurstuecksfolge

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

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

862

863 return v

864

865

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

867

868_RE_FSNUMMER = r'''(?x)

869 ^

870 (

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

872 \s+

873 )?

874 (

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

876 -

877 )?

878 (

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

880 (/

881 (?P<nenner> \w+)

882 )?

883 )?

884 (

885 \s*

886 \(

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

888 \)

889 )?

890 $

891'''

892

893

894def parse_fsnummer(s):

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

896

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

898 if not m:

899 return None

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

901

902

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

904 # @TODO merge with model_field/text

905

906 if val is None:

907 return

908

909 val = str(val).strip()

910 if len(val) == 0:

911 return

912

913 if not tso:

914 return column == val

915

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

917 return

918

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

920 return column == val

921

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

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

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

925 val = _escape_like(val) + '%'

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

927 val = '%' + _escape_like(val)

928

929 if tso.caseSensitive:

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

931

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

933

934

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

936 return (

937 s

938 .replace(escape, escape + escape)

939 .replace('%', escape + '%')

940 .replace('_', escape + '_'))