Python Alkis.query示例

示例#1

文件： kkg_plugin.py 项目： werkuh/kkg_plugin

    def _get_fsn_from_gml_id(self, feature_list):
        """
        Ruft die Eigentümerinformaionen anhand der gml_id der Flurstücke aus der ALKIS-DB ab.
        :param features: Liste der Features, deren Eigentümerinformationen abgerufen werden sollen
        :type list
        :return: Eigentümerinformationen
        :rtype list
        """
        db = Alkis()
        query = """
        SELECT *
        FROM
	      (SELECT
		    gml_id,
		    (land ||
            gemarkungsnummer ||
            '-' ||
            trim(to_char(flurnummer, '000')) ||
            '-' || trim(to_char(zaehler, '00000')) ||
            '/000') AS flsnr
		    FROM ax_flurstueck) flur
        WHERE flur.gml_id = %s
        """
        return db.query(query, (feature_list,))

示例#2

文件： kkg_plugin.py 项目： werkuh/kkg_plugin

    def _build_html(self, fs):
        """
        Weitestgehend aus dem ALKIS-Plugin der Firma norBIT übernommen und auf die eigene Datenbankverbindung
        umgeschriebene Methode zum Abruf der Flurstücksinformationen, wenn die Daten aus der Datenbank ausgespielt
        worden sind.
        Credits to jef-n (https://github.com/jef-n)
        :param fs: Flurstücksnummer des betroffenen Flurstücks
        :type String
        :return: HTML-Repräsentation der Flurstücksinformationen
        :rtype String
        """
        fs = fs.rstrip(' ')
        db = Alkis()

        res = db.query("SELECT 1 FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE "
                       "relname='eignerart') AND attname='anteil'", None)

        if res:
            exists_ea_anteil = (res[0]['?column?'] == 1)
        else:
            exists_ea_anteil = False

        html = ""

        qry = """
            SELECT
                ea.bvnr,
                '' as pz,
                (SELECT eignerart FROM eign_shl WHERE ea.b=b) as eignerart,
                %s as anteil,
                ea.ff_stand AS zhist,
                b.bestdnr,
                b.gbbz,
                b.gbblnr,
                b.bestfl,
                b.ff_stand AS bhist
            FROM eignerart ea
            JOIN bestand b ON ea.bestdnr = b.bestdnr
            WHERE ea.flsnr = '%s'
            ORDER BY zhist,bhist,b""" % ("ea.anteil" if exists_ea_anteil else "''", fs)

        best = db.query(qry, None)

        res = db.query(
            "SELECT f.*,g.gemarkung FROM flurst f LEFT OUTER JOIN gema_shl g ON (f.gemashl=g.gemashl) WHERE "
            "f.flsnr='%s' AND f.ff_stand=0" % fs, None)
        if len(res) == 1:
            res = res[0]
        else:
            QMessageBox.information(None, "Fehler", u"Flurstück %s nicht gefunden.\n[%s]" % (fs, repr(fs)))
            return

        res['datum'] = QDate.currentDate().toString("d. MMMM yyyy")
        res['hist'] = 0

        res['str'] = db.query(
            "SELECT sstr.strname,str.hausnr FROM str_shl sstr JOIN strassen str ON "
            "str.strshl=sstr.strshl WHERE str.flsnr='%s' AND str.ff_stand=0" % fs, None)
        res['nutz'] = db.query(
            "SELECT n21.*, nu.nutzshl, nu.nutzung FROM nutz_21 n21, nutz_shl nu WHERE "
            "n21.flsnr='%s' AND n21.nutzsl=nu.nutzshl AND n21.ff_stand=0" % fs, None)
        res['klas'] = db.query(
            "SELECT kl.*, kls.klf_text FROM klas_3x kl, kls_shl kls WHERE kl.flsnr='%s' AND "
            "kl.klf=kls.klf AND kl.ff_stand=0" % fs, None)
        res['afst'] = db.query(
            "SELECT au.*, af.afst_txt FROM ausfst au,afst_shl af WHERE au.flsnr='%s' AND "
            "au.ausf_st=af.ausf_st AND au.ff_stand=0" % fs, None)
        res['best'] = db.query(
            "SELECT ea.bvnr,'' as pz,(SELECT eignerart FROM eign_shl WHERE ea.b = b) as eignerart,%s as anteil,ea.ff_stand AS zhist,b.bestdnr,b.gbbz,b.gbblnr,b.bestfl,b.ff_stand AS bhist FROM eignerart ea JOIN bestand b ON ea.bestdnr = b.bestdnr WHERE ea.flsnr='%s' ORDER BY zhist,bhist,b" %
            ("ea.anteil" if exists_ea_anteil else "''", fs), None)

        for b in res['best']:
            b['bse'] = db.query("SELECT * FROM eigner WHERE bestdnr='%s' AND ff_stand=0" % b['bestdnr'], None)

        html = u"""
    <HTML xmlns="http://www.w3.org/1999/xhtml">
      <HEAD>
          <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      </HEAD>
      <BODY>
    <style>
    .fls_tab{width:100%%;empty-cells:show}
    .fls_time{text-align:right;width:100%%}
    .fls_headline_col{background-color:#EEEEEE;width:100%%;height:30px;text-align:center;}
    .fls_headline{font-weight:bold;font-size:24px;}
    .fls_col_names{font-weight:bold;}
    .fls_col_values{vertical-align:top;}
    .fls_bst{width:100%%;empty-cells:show}
    .fls_hr{border:dotted 1px;color:#080808;}
    </style>

    <TABLE class="fls_tab" border="0">
        <TR><TD>Flurst&uuml;cksnachweis</TD><TD class="fls_time" colspan="6"><span>%(datum)s</TD></TR>
        <TR><TD colspan="7"><hr style="width:100%%"></TD></TR>
        <TR class="fls_headline_col">
            <TD colspan="7"><span class="fls_headline">Flurst&uuml;cksnachweis<span></TD>
        </TR>
        <TR><TD colspan="7">&nbsp;</TD></TR>
        <TR>
            <TD colspan="7"><h3>Flurst&uuml;ck<hr style="width:100%%"></h3></TD>
        </TR>
        <TR class="fls_col_names">
            <TD width="15%%">Gemarkung</TD>
            <TD width="6%%">Flur</TD>
            <TD width="15%%">Flurst&uuml;ck</TD>
            <TD width="20%%">Flurkarte</TD>
            <TD width="17%%">Entstehung</TD>
            <TD width="17%%">Fortf&uuml;hrung</TD>
            <TD width="5%%">Fl&auml;che</TD>
        </TR>
        <TR class="fls_col_values">
            <TD>%(gemashl)s<br>%(gemarkung)s</TD>
            <TD>%(flr)s</TD>
            <TD>%(flsnrk)s</TD>
            <TD>%(flurknr)s</TD>
            <TD>%(entst)s</TD>
            <TD>%(fortf)s</TD>
            <TD nowrap>%(flsfl)s m&sup2;</TD>
        </TR>
    </TABLE>
    """ % res

        if res['blbnr']:
            html += """
<TABLE border="0" class="fls_tab">
    <TR class="fls_col_names">
        <TD width="21%%"></TD>
        <TD width="79%%">Baulastenblattnr.</TD>
    </TR>
    <TR class="fls_col_values">
        <TD></TD>
        <TD>%(blbnr)s</TD>
    </TR>
</TABLE>
""" % res

        if res['lagebez'] or res['anl_verm']:
            html += """
<TABLE border="0" class="fls_tab">
    <TR class="fls_col_names">
        <TD width="21%%"></TD>
        <TD width="52%%">Lage</TD>
        <TD width="27%%">Anliegervermerk</TD>
    </TR>
    <TR class="fls_col_values">
        <TD></TD>
        <TD>%(lagebez)s</TD>
        <TD>%(anl_verm)s</TD>
    </TR>
</TABLE>
""" % res

        if res['str']:
            html += """
<TABLE border="0" class="fls_tab">
    <TR class="fls_col_names">
        <TD></TD><TD>Strasse</TD><TD>Hausnummer</TD>
    </TR>
"""

            for strres in res['str']:
                html += """
    <TR class="fls_col_values">
        <TD></TD><TD>%(strname)s</TD><TD>%(hausnr)s</TD></TR>
    </TR>
""" % strres

            html += """
</TABLE>
"""

        if res['nutz']:
            html += """
<TABLE border="0" class="fls_tab">
        <TR class="fls_col_names"><TD width="21%%"></TD><TD width="69%%">Nutzung</TD><TD width="10%%">Fl&auml;che</TD></TR>
"""

            for nutz in res['nutz']:
                html += """
        <TR class="fls_col_values"><TD></TD><TD>21%(nutzshl)s - %(nutzung)s</TD><TD nowrap>%(fl)s m&sup2;</TD></TR>
""" % nutz

            html += """
</TABLE>
"""
        else:
            html += """
        <p>Keine Nutzungen.</p>
"""

        if res['klas']:
            html += """
<TABLE border="0" class="fls_tab">
        <TR class="fls_col_names"><TD width="21%%"></TD><TD width="69%%">Klassifizierung</TD><TD width="10%%">Fl&auml;che</TD></TR>
"""

            for klas in res['klas']:
                html += """
        <TR class="fls_col_values"><TD></TD><TD>%(klf_text)s</TD><TD nowrap>%(fl)s m&sup2;</TD></TR>
""" % klas

            html += """
</TABLE>
"""
        else:
            html += """
        <p>Keine Klassifizierungen.</p>
"""

        if res['afst']:
            html += """
<TABLE border="0" class="fls_tab">
        <TR class="fls_col_names"><TD width="21%%"></TD><TD width="79%%">Ausf&uuml;hrende Stelle</TD></TR>
"""

            for afst in res['afst']:
                html += """
        <TR class="fls_col_values"><TD></TD><TD>%(afst_txt)s</TD></TR>
""" % afst

            html += """
</TABLE>
"""
        else:
            html += """
        <p>Keine ausf&uuml;hrenden Stellen.</p>
"""

        if res['best']:
            html += """
<TABLE border="0" class="fls_bst">
        <TR><TD colspan="6"><h3>Best&auml;nde<hr style="width:100%%"></h3></TD></TR>
"""

            for best in res['best']:
                html += """
        <TR class="fls_col_names">
                <TD>Bestandsnummer</TD>
                <TD>Grundbuchbezirk</TD>
                <TD colspan="2">Grundbuchblattnr.</TD>
                <TD>Anteil</TD>
        </TR>
        <TR class="fls_col_values">
                <TD>%(bestdnr)s</TD>
                <TD>%(gbbz)s</TD>
                <TD colspan="2">%(gbblnr)s</TD>
                <TD>%(anteil)s</TD>
        </TR>
        <TR class="fls_col_names">
                <TD></TD>
                <TD>Buchungskennz.</TD>
                <TD>BVNR</TD>
                <TD>PZ</TD>
""" % best

                if res['hist']:
                    html += """
                <TD>Hist. Bestand</TD><TD>Hist. Zuordnung</TD>
"""
                else:
                    html += """
                <TD></TD><TD></TD>
"""

                html += """
        </TR>
        <TR class="fls_col_values">
                <TD></TD>
                <TD>%(eignerart)s</TD>
                <TD>%(bvnr)s</TD>
                <TD>%(pz)s</TD>
""" % best

                html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['bhist'] else "")
                html += "<TD>%s</TD>" % ("ja" if res['hist'] and best['zhist'] else "")

                html += """
        </TR>
"""

                if best['bse']:
                    html += """
        <TR class="fls_col_names"><TD>Anteil</TD><TD colspan="5">Namensinformation</TD></TR>
"""

                    for bse in best['bse']:
                        html += """
        <TR class="fls_col_values">
                <TD>%(antverh)s</TD>
                <TD colspan="5">%(name1)s %(name2)s<br>%(name3)s<br>%(name4)s</TD>
        </TR>
""" % bse
                else:
                    html += """
        <p>Keine Eigner gefunden.</p>
"""

                html += """
        <TR><TD colspan="6"><hr class="fls_hr"></TD></TR>
"""

        html += """
        </TABLE>
</BODY>
</HTML>
"""
        return html