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 = '83'

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_schema(self) -> bool:

237 return self.db.has_schema(self.schema)

238

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

240 return self.table_size(table_id) > 0

241

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

243 sizes = self._table_size_map(self.ALL_TABLES)

244 s = dt.IndexStatus(

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

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

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

248 )

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

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

251 gws.log.debug(f'ALKIS: table sizes {sizes!r}')

252 return s

253

254 def drop_table(self, table_id: str):

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

256 self._drop_table(conn, table_id)

257 conn.commit()

258

259 def drop(self):

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

261 for table_id in self.ALL_TABLES:

262 self._drop_table(conn, table_id)

263 conn.commit()

264

265 def _drop_table(self, conn, table_id):

266 tab = self.table(table_id)

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

268

269 INSERT_SIZE = 5000

270

271 def create_table(

272 self,

273 table_id: str,

274 values: list[dict],

275 progress: Optional[ProgressIndicator] = None,

276 ):

277 tab = self.table(table_id)

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

279

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

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

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

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

284 conn.commit()

285 if progress:

286 progress.update(len(vals))

287

288 ##

289

290 _defaultLand: dt.EnumPair = None

291

292 def default_land(self):

293 if self._defaultLand:

294 return self._defaultLand

295

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

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

298 for r in conn.execute(sel):

299 p = unserialize(r.data)

300 self._defaultLand = p.land

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

302 return self._defaultLand

303

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

305

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

307 if self._strasseList:

308 return self._strasseList

309

310 self._strasseList = []

311

312 indexlage = self.table(TABLE_INDEXLAGE)

313 cols = (

314 indexlage.c.gemeinde,

315 indexlage.c.gemeindecode,

316 indexlage.c.gemarkung,

317 indexlage.c.gemarkungcode,

318 indexlage.c.strasse,

319 )

320

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

322 if self.gemarkungFilter:

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

324

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

326 for r in conn.execute(sel):

327 self._strasseList.append(

328 dt.Strasse(

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

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

331 name=r.strasse,

332 )

333 )

334

335 return self._strasseList

336

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

338 indexlage = self.table(TABLE_INDEXLAGE)

339

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

341 sel = self._make_adresse_select(q, qo)

342

343 lage_uids = []

344 adresse_map = {}

345

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

347 for r in conn.execute(sel):

348 lage_uids.append(r[0])

349

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

351 raise gws.ResponseTooLargeError(len(lage_uids))

352

353 if qo.offset:

354 lage_uids = lage_uids[qo.offset :]

355 if qo.pageSize:

356 lage_uids = lage_uids[: qo.pageSize]

357

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

359

360 for r in conn.execute(sel):

361 r = gws.u.to_dict(r)

362 uid = r['lageuid']

363 adresse_map[uid] = dt.Adresse(

364 uid=uid,

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

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

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

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

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

370 strasse=r['strasse'],

371 hausnummer=r['hausnummer'],

372 x=r['x'],

373 y=r['y'],

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

375 )

376

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

378

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

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

381 sel = self._make_flurstueck_select(q, qo)

382

383 fs_uids = []

384

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

386 for r in conn.execute(sel):

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

388 if uid not in fs_uids:

389 fs_uids.append(uid)

390

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

392 raise gws.ResponseTooLargeError(len(fs_uids))

393

394 if qo.offset:

395 fs_uids = fs_uids[qo.offset :]

396 if qo.pageSize:

397 fs_uids = fs_uids[: qo.pageSize]

398

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

400

401 return fs_list

402

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

404 indexfs = self.table(TABLE_INDEXFLURSTUECK)

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

406 if not qo.withHistorySearch:

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

408

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

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

411 return r[0][0]

412

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

414 indexfs = self.table(TABLE_INDEXFLURSTUECK)

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

416 if not qo.withHistorySearch:

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

418

419 offset = 0

420

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

422

423 while True:

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

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

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

427 if not fs_uids:

428 break

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

430

431 yield from fs_list

432

433 offset += qo.pageSize

434

435 HAUSNUMMER_NOT_NULL_VALUE = '*'

436

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

438 indexfs = self.table(TABLE_INDEXFLURSTUECK)

439 indexbuchungsblatt = self.table(TABLE_INDEXBUCHUNGSBLATT)

440 indexgeom = self.table(TABLE_INDEXGEOM)

441 indexlage = self.table(TABLE_INDEXLAGE)

442 indexperson = self.table(TABLE_INDEXPERSON)

443

444 where = []

445

446 has_buchungsblatt = False

447 has_geom = False

448 has_lage = False

449 has_person = False

450

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

452

453 if q.uids:

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

455

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

457 val = getattr(q, f, None)

458 if val is not None:

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

460

461 if q.flurstueckskennzeichen:

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

463 if not val:

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

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

466

467 if q.flaecheVon:

468 try:

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

470 except ValueError:

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

472

473 if q.flaecheBis:

474 try:

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

476 except ValueError:

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

478

479 if q.buchungsblattkennzeichenList:

480 ws = []

481

482 for s in q.buchungsblattkennzeichenList:

483 w = text_search_clause(

484 indexbuchungsblatt.c.buchungsblattkennzeichen,

485 s,

486 qo.buchungsblattSearchOptions,

487 )

488 if w is not None:

489 ws.append(w)

490 if ws:

491 has_buchungsblatt = True

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

493

494 if q.strasse:

495 w = text_search_clause(

496 indexlage.c.strasse_t,

497 strasse_key(q.strasse),

498 qo.strasseSearchOptions,

499 )

500 if w is not None:

501 has_lage = True

502 where.append(w)

503

504 if q.hausnummer:

505 if not has_lage:

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

507 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

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

509 else:

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

511

512 if q.personName:

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

514 if w is not None:

515 has_person = True

516 where.append(w)

517

518 if q.personVorname:

519 if not has_person:

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

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

522 if w is not None:

523 where.append(w)

524

525 if q.shape:

526 has_geom = True

527 where.append(

528 sa.func.st_intersects(

529 indexgeom.c.geom,

530 sa.cast(

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

532 sa.geo.Geometry(),

533 ),

534 )

535 )

536

537 join = []

538

539 if has_buchungsblatt:

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

541 if not qo.withHistorySearch:

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

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

544

545 if has_geom:

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

547 if not qo.withHistorySearch:

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

549

550 if has_lage:

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

552 if not qo.withHistorySearch:

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

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

555

556 if has_person:

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

558 if not qo.withHistorySearch:

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

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

561

562 if not qo.withHistorySearch:

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

564

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

566

567 for tab, cond in join:

568 sel = sel.join(tab, cond)

569

570 sel = sel.where(*where)

571

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

573

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

575 indexlage = self.table(TABLE_INDEXLAGE)

576 where = []

577

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

579

580 has_strasse = False

581

582 if q.strasse:

583 w = text_search_clause(

584 indexlage.c.strasse_t,

585 strasse_key(q.strasse),

586 qo.strasseSearchOptions,

587 )

588 if w is not None:

589 has_strasse = True

590 where.append(w)

591

592 if q.hausnummer:

593 if not has_strasse:

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

595 if q.hausnummer == self.HAUSNUMMER_NOT_NULL_VALUE:

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

597 else:

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

599

600 if q.bisHausnummer:

601 if not has_strasse:

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

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

604

605 if q.hausnummerNotNull:

606 if not has_strasse:

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

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

609

610 if not qo.withHistorySearch:

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

612

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

614

615 sel = sel.where(*where)

616

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

618

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

620 where = []

621 land_code = ''

622

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

624 val = getattr(q, f, None)

625 if val is not None:

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

627

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

629 if val is not None:

630 if f == 'land':

631 land_code = val

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

633 if not land_code:

634 land = self.default_land()

635 if land:

636 land_code = land.code

637 val = land_code + val

638

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

640

641 if self.gemarkungFilter:

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

643

644 return where

645

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

647 if not sort:

648 return sel

649

650 order = []

651 for s in sort:

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

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

654 sel = sel.order_by(*order)

655

656 return sel

657

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

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

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

661

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

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

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

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

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

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

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

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

670

671 tab = self.table(TABLE_FLURSTUECK)

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

673

674 hd = qo.withHistoryDisplay

675

676 fs_list = []

677

678 for r in conn.execute(sel):

679 fs = unserialize(r.data)

680 fs.geom = r.geom

681 fs_list.append(fs)

682

683 fs_list = self._remove_historic(fs_list, hd)

684 if not fs_list:

685 return []

686

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

688

689 for fs in fs_map.values():

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

691

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

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

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

695

696 fs.bewertungList = []

697 fs.festlegungList = []

698 fs.nutzungList = []

699

700 if with_buchung:

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

702

703 tab = self.table(TABLE_BUCHUNGSBLATT)

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

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

706 bb_list = self._remove_historic(bb_list, hd)

707

708 for bb in bb_list:

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

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

711 for nn in bb.namensnummerList:

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

713 for pe in nn.personList:

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

715

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

717

718 for fs in fs_map.values():

719 for bu in fs.buchungList:

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

721

722 if with_nutzung or with_festlegung or with_bewertung:

723 tab = self.table(TABLE_PART)

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

725 if not qo.withHistorySearch:

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

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

728 pa_list = self._remove_historic(pa_list, hd)

729

730 for pa in pa_list:

731 fs = fs_map[pa.fs]

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

733 fs.nutzungList.append(pa)

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

735 fs.festlegungList.append(pa)

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

737 fs.bewertungList.append(pa)

738

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

740

741 _historicKeys = ['vorgaengerFlurstueckskennzeichen']

742

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

744 if with_history_display:

745 return objects

746

747 out = []

748

749 for o in objects:

750 if o.isHistoric:

751 continue

752

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

754 if not o.recs:

755 continue

756

757 for r in o.recs:

758 for k in self._historicKeys:

759 try:

760 delattr(r, k)

761 except AttributeError:

762 pass

764 out.append(o)

766 return out

769##

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

773 def encode(r):

774 if not r:

775 return r

776

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

778 return r

779

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

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

782

783 if isinstance(r, list):

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

785

786 if isinstance(r, dt.EnumPair):

787 if encode_enum_pairs:

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

789 return vars(r)

790

791 if isinstance(r, dt.Object):

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

794 return str(r)

796 return encode(o)

799def unserialize(data: dict):

800 def decode(r):

801 if not r:

802 return r

803 if isinstance(r, str):

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

805 s = r.split('$')

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

807 return r

808 if isinstance(r, list):

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

810 if isinstance(r, dict):

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

812 return dt.Object(**d)

813 return r

814

815 return decode(data)

818##

821def text_key(s):

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

823

824 if s is None:

825 return ''

826

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

828 return _text_nopunct(s)

829

830

831def strasse_key(s):

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

833

834 if s is None:

835 return ''

836

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

838

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

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

842

843 return _text_nopunct(s)

844

845

846def _text_umlauts(s):

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

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

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

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

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

852

853 return s

854

855

856def _text_nopunct(s):

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

858

859

860def normalize_hausnummer(s):

861 """Clean up house number formatting."""

862

863 if s is None:

864 return ''

865

866 # "12 a" -> "12a"

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

868 return s

869

870

871def make_fsnummer(r: dt.FlurstueckRecord):

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

873

874 v = r.gemarkung.code + ' '

875

876 s = r.flurnummer

877 if s:

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

879

880 v += str(r.zaehler)

881 s = r.nenner

882 if s:

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

884

885 s = r.flurstuecksfolge

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

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

888

889 return v

890

891

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

893

894_RE_FSNUMMER = r"""(?x)

895 ^

896 (

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

898 \s+

899 )?

900 (

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

902 -

903 )?

904 (

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

906 (/

907 (?P<nenner> \w+)

908 )?

909 )?

910 (

911 \s*

912 \(

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

914 \)

915 )?

916 $

917"""

918

919

920def parse_fsnummer(s):

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

922

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

924 if not m:

925 return None

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

927

928

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

930 # @TODO merge with model_field/text

931

932 if val is None:

933 return

934

935 val = str(val).strip()

936 if len(val) == 0:

937 return

938

939 if not tso:

940 return column == val

941

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

943 return

944

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

946 return column == val

947

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

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

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

951 val = _escape_like(val) + '%'

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

953 val = '%' + _escape_like(val)

954

955 if tso.caseSensitive:

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

957

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

959

960

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

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

965##

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

969

970

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

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

973

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

975

976

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

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

979 return flat_lst

980

981 if isinstance(val, list):

982 if not val:

983 return flat_lst

984

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

986 new_lst = []

987 for v in val:

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

989 for d in flat_lst:

990 new_lst.append(d | d2)

991 return new_lst

992

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

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

995 if k not in _FLATTEN_EXCLUDE_KEYS:

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

997 return flat_lst

998

999 for d in flat_lst:

1000 d[key] = val

1001

1002 return flat_lst

1003

1004

1005def all_flat_keys():

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

1007

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

1009

1010

1011def _get_flat_keys(cls, key):

1012 if isinstance(cls, str):

1013 cls = getattr(dt, cls, None)

1014

1015 if cls is dt.EnumPair:

1016 yield f'{key}_code', int

1017 yield f'{key}_text', str

1018 return

1019

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

1021 yield key, cls or str

1022 return

1023

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

1025 if k in _FLATTEN_EXCLUDE_KEYS:

1026 continue

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

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

1029 else:

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

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

569 statements