source: trunk/info/info/alkisn/alkisstrasse.php @ 362

Revision 362, 9.3 KB checked in by frank.jaeger, 8 years ago (diff)

Anpassung der ALKIS-Buchauskunft an die Datenbank-Struktur des norGIS-ALKIS-Importer.
Line 
1<?php
2/*      alkisstrasse.php
3
4        ALKIS-Buchauskunft, Kommunales Rechenzentrum Minden-Ravensberg/Lippe (Lemgo).
5        Alle FlurstÃŒcke an einer Strasse anzeigen, egal ob "mit" oder "ohne" Hausnummer
6        Parameter: "gml_id" aus der Tabelle "ax_lagebezeichnungkatalogeintrag"
7
8        Version:
9        2016-02-24 Version fuer norGIS-ALKIS-Import
10*/
11session_start();
12$id="n";
13$cntget = extract($_GET);
14require_once("alkis_conf_location.php");
15if ($auth == "mapbender") {require_once($mapbender);}
16include("alkisfkt.php");
17
18$keys = isset($_GET["showkey"]) ? $_GET["showkey"] : "n";
19if ($keys == "j") {$showkey=true;} else {$showkey=false;}
20?>
21<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
22<html>
23<head>
24        <meta name="author" content="b600352" >
25        <meta http-equiv="cache-control" content="no-cache">
26        <meta http-equiv="pragma" content="no-cache">
27        <meta http-equiv="expires" content="0">
28        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
29        <title>ALKIS Stra&szlig;e</title>
30        <link rel="stylesheet" type="text/css" href="alkisauszug.css">
31        <link rel="shortcut icon" type="image/x-icon" href="ico/Strassen.ico">
32        <script type="text/javascript">
33                function ALKISexport() {
34                        window.open(<?php echo "'alkisexport.php?gkz=".$gkz."&tabtyp=strasse&gmlid=".$gmlid."'"; ?>);
35                }
36        </script>
37        <style type='text/css' media='print'>
38                .noprint {visibility: hidden;}
39        </style>
40</head>
41<body>
42<?php
43$con = pg_connect("host=".$dbhost." port=" .$dbport." dbname=".$dbname." user=".$dbuser." password=".$dbpass);
44if (!$con) echo "<p class='err'>Fehler beim Verbinden der DB</p>\n";
45
46$sql ="SELECT s.land, s.regierungsbezirk, s.kreis, s.gemeinde, s.lage, s.bezeichnung AS snam,
47b.bezeichnung AS bnam, r.bezeichnung AS rnam, k.bezeichnung AS knam, g.bezeichnung AS gnam, o.gml_id AS ogml
48FROM ax_lagebezeichnungkatalogeintrag s JOIN ax_bundesland b ON s.land=b.land
49JOIN ax_regierungsbezirk r ON s.land=r.land AND s.regierungsbezirk=r.regierungsbezirk
50JOIN ax_kreisregion k ON s.land=k.land AND s.regierungsbezirk=k.regierungsbezirk AND s.kreis=k.kreis
51JOIN ax_gemeinde g ON s.land=g.land AND s.regierungsbezirk=g.regierungsbezirk AND s.kreis=g.kreis AND s.gemeinde=g.gemeinde
52LEFT JOIN ax_lagebezeichnungohnehausnummer o ON s.land=o.land AND s.regierungsbezirk=o.regierungsbezirk AND s.kreis=o.kreis AND s.gemeinde=o.gemeinde AND s.lage=o.lage
53WHERE s.gml_id= $1 AND s.endet IS NULL AND o.endet IS NULL ;";
54
55$v=array($gmlid);
56$res=pg_prepare("", $sql);
57$res=pg_execute("", $v);
58if (!$res) {
59        echo "\n<p class='err'>Fehler bei Lagebezeichnungskatalogeintrag.</p>\n";
60        if ($debug > 2) {echo "<p class='err'>SQL=<br>".$sql."<br>$1 = gml_id = '".$gmlid."'</p>";}
61}
62
63if ($row = pg_fetch_array($res)) {
64        $lage=$row["lage"]; // Strassenschluessel
65        $snam=$row["snam"]; // Strassenname
66        $gem=$row["gemeinde"];
67        // Balken
68        $kennz=$gem."-".$lage." (".$snam.")"; // Schluessel als Sucheingabe in NAV brauchbar?
69        echo "<p class='strasse'>ALKIS Stra&szlig;e ".$kennz."&nbsp;</p>\n";
70} else {
71        echo "\n<p class='err'>Kein Treffer bei Lagebezeichnungskatalogeintrag.</p>\n";
72}
73
74echo "\n<h2><img src='ico/Strassen.png' width='16' height='16' alt=''> Stra&szlig;e</h2>\n";
75
76// Tabelle Kennzeichen
77echo "\n<table class='outer'>\n<tr>\n\t<td>"
78."\n\t<table class='kennzstra' title='Lage'>"
79        ."\n\t<tr>"
80                ."\n\t\t<td class='head'>Land</td>"
81                ."\n\t\t<td class='head'>Reg.-Bez.</td>"
82                ."\n\t\t<td class='head'>Kreis</td>"
83                ."\n\t\t<td class='head'>Gemeinde</td>"
84                ."\n\t\t<td class='head'>Stra&szlig;e</td>"
85        ."\n\t</tr>"
86        ."\n\t<tr>";
87
88                echo "\n\t\t<td title='Bundesland'>";
89                if ($showkey) {echo "<span class='key'>".$row["land"]."</span><br>";}
90                echo $row["bnam"]."&nbsp;</td>";
91
92                echo "\n\t\t<td title='Regierungsbezirk'>";
93                if ($showkey) {echo "<span class='key'>".$row["regierungsbezirk"]."</span><br>";}
94                echo $row["rnam"]."&nbsp;</td>";
95
96                echo "\n\t\t<td title='Kreis'>";
97                if ($showkey) {echo "<span class='key'>".$row["kreis"]."</span><br>";}
98                echo $row["knam"]."&nbsp;</td>";
99
100                echo "\n\t\t<td title='Gemeinde'>";
101                if ($showkey) {echo "<span class='key'>".$gem."</span><br>";}
102                echo $row["gnam"]."&nbsp;</td>";
103
104                echo "\n\t\t<td title='Stra&szlig;e'>";
105                if ($showkey) {echo "<span class='key'>".$lage."</span><br>";}
106                echo "<span class='wichtig'>".$snam."</span>";
107
108                echo "&nbsp;</td>"
109        ."\n\t</tr>"
110."\n\t</table>";
111
112echo "\n\t</td>\n\t<td>";
113
114// Kopf Rechts:
115$ogml=$row["ogml"]; // ID von "Lage Ohne HsNr"
116if ($ogml != "") {
117        echo "\n\t\t<p class='nwlink noprint'>"
118                ."\n\t\t<a href='alkislage.php?gkz=".$gkz."&amp;ltyp=o&amp;gmlid=".$ogml;
119                if ($showkey)   {echo "&amp;showkey=j";}
120                echo "' title='Lage Ohne Hausnummer'>Lage <img src='ico/Lage_an_Strasse.png' width='16' height='16' alt=''></a>"
121        ."\n\t\t</p>";
122}
123
124echo "\n\t</td>\n</tr>\n</table>";
125pg_free_result($res);
126// Ende Seitenkopf
127
128// F L U R S T U E C K E
129echo "\n\n<a name='fs'></a><h3><img src='ico/Flurstueck.png' width='16' height='16' alt=''> Flurst&uuml;cke</h3>\n";
130echo "\n<p>Zusammenfassung von 'Lage mit Hausnummer' und 'Lage ohne Hausnummer' an dieser Straße</p>";
131
132// ax_Flurstueck >weistAuf> ax_LagebezeichnungMitHausnummer  > = Hauptgebaeude
133// ax_Flurstueck >zeigtAuf> ax_LagebezeichnungOhneHausnummer > = Strasse
134// Suchkriterium: gml_id aus Katalog
135$subquery = "SELECT f1.gml_id AS fsgml, lm.gml_id AS lgml, lm.land, lm.regierungsbezirk, lm.kreis, lm.gemeinde, lm.lage, lm.hausnummer
136 FROM ax_flurstueck f1 JOIN ax_lagebezeichnungmithausnummer lm ON lm.gml_id=ANY(f1.weistAuf)
137 WHERE f1.endet IS NULL AND lm.endet IS NULL
138UNION SELECT f2.gml_id AS fsgml, '' AS lgml, lo.land, lo.regierungsbezirk, lo.kreis, lo.gemeinde, lo.lage, '' AS hausnummer
139 FROM ax_flurstueck f2 JOIN ax_lagebezeichnungohnehausnummer lo ON lo.gml_id=ANY(f2.zeigtauf)
140 WHERE f2.endet IS NULL AND lo.endet IS NULL";
141
142$sql="SELECT g.gemarkungsnummer, g.bezeichnung, f.gml_id, f.flurnummer, f.zaehler, f.nenner, f.amtlicheflaeche, duett.lgml, duett.hausnummer
143 FROM ax_flurstueck f JOIN ( ".$subquery." ) AS duett ON f.gml_id=duett.fsgml
144 JOIN ax_gemarkung g ON f.land=g.land AND f.gemarkungsnummer=g.gemarkungsnummer
145 JOIN ax_lagebezeichnungkatalogeintrag s ON duett.land=s.land AND duett.regierungsbezirk=s.regierungsbezirk AND duett.kreis=s.kreis AND duett.gemeinde=s.gemeinde AND duett.lage=s.lage
146WHERE s.gml_id = $1 AND f.endet IS NULL AND s.endet IS NULL
147ORDER BY f.gemarkungsnummer, f.flurnummer, f.zaehler, f.nenner;";
148
149$v=array($gmlid);
150$resf=pg_prepare("", $sql);
151$resf=pg_execute("", $v);
152if (!$resf) {
153        echo "<p class='err'>Fehler bei Flurst&uuml;ck.</p>\n";
154        if ($debug > 2) {echo "<p class='err'>SQL=<br>".$sql."<br>$1 = gml_id = '".$gmlid."'</p>";}     
155}
156
157echo "\n<table class='fs'>"
158."\n<tr>"
159        ."\n\t<td class='head' title='Name der Gemarkung (Ortsteil)'>Gemarkung</td>"
160        ."\n\t<td class='head' title='Flur-Nummer'>Flur</td>"
161        ."\n\t<td class='head' title='Flurst&uuml;cksnummer (Z&auml;hler / Nenner)'>Flurst.</td>"
162        ."\n\t<td class='head fla' title='amtliche Fl&auml;che in Quadratmeter'>Fl&auml;che</td>"
163        ."\n\t<td class='head hsnr' title='Hausnummer aus der Lagebezeichnung des Flurst&uuml;cks'>HsNr.</td>"
164        ."\n\t<td class='head nwlink noprint' title='Link: weitere Auskunft'>weit. Auskunft</td>"
165."\n</tr>";
166$j=0;
167$cnths=0; // Count Haus
168$gwgmkg=""; // Gruppenwechsel
169$gwflur="";
170
171while($rowf = pg_fetch_array($resf)) {
172        $gmkg=$rowf["bezeichnung"];
173        $flur=str_pad($rowf["flurnummer"], 3, "0", STR_PAD_LEFT);
174        $fskenn=$rowf["zaehler"]; // Bruchnummer
175        if ($rowf["nenner"] != "") {$fskenn.="/".$rowf["nenner"];}
176        $flae=number_format($rowf["amtlicheflaeche"],0,",",".") . " m&#178;";
177        $lgml=$rowf["lgml"]; // ID von "Lage Mit" oder leer
178
179        echo "\n<tr>"
180                ."\n\t<td>";
181                if ($showkey) {echo "<span class='key'>".$rowf["gemarkungsnummer"]."</span> ";}
182                if ($gwgmkg != $gmkg) {
183                        echo "<b>".$gmkg."</b></td>";
184                        $gwgmkg=$gmkg;
185                        $gwflur="";
186                } else {
187                        echo $gmkg."</td>";
188                }
189
190                if ($gwflur != $flur) {
191                        echo "\n\t<td><b>".$flur."</b></td>";
192                        $gwflur=$flur;
193                } else {
194                        echo "\n\t<td>".$flur."</td>";
195                }
196
197                echo "\n\t<td><span class='wichtig'>".$fskenn."</span></td>"
198                ."\n\t<td class='fla'>".$flae."</td>"
199                ."\n\t<td class='hsnr'>".$rowf["hausnummer"]."</td>"
200                ."\n\t<td>\n\t\t<p class='nwlink noprint'>";
201
202                        if ($lgml != '') {
203                                echo "\n\t\t<a href='alkislage.php?gkz=".$gkz."&amp;ltyp=m&amp;gmlid=".$lgml;
204                                if ($showkey) {echo "&amp;showkey=j";}
205                                echo "' title='Lage Mit Hausnummer'>Lage <img src='ico/Lage_mit_Haus.png' width='16' height='16' alt=''></a>&nbsp;";
206                                $cnths++;
207                        }
208
209                        echo "\n\t\t<a href='alkisfsnw.php?gkz=".$gkz."&amp;gmlid=".$rowf["gml_id"]."&amp;eig=n";
210                        if ($showkey) {echo "&amp;showkey=j";}
211                        echo "' title='Flurst&uuml;cksnachweis'>Flurst&uuml;ck <img src='ico/Flurstueck_Link.png' width='16' height='16' alt=''></a>";
212
213                echo "\n\t\t</p>\n\t</td>"
214        ."\n</tr>";
215        $j++;
216}
217echo "\n</table>";
218if ($j > 6) {
219        echo "<p class='cnt'>".$j." Flurst&uuml;cke";
220        if ($cnths > 4) {echo ", ".$cnths." H&auml;user";}
221        echo "</p>";
222}
223pg_free_result($resf);
224?>
225
226<form action=''>
227        <div class='buttonbereich noprint'>
228        <hr>
229                <a title="zur&uuml;ck" href='javascript:history.back()'><img src="ico/zurueck.png" width="16" height="16" alt="zur&uuml;ck"></a>&nbsp;
230                <a title="Drucken" href='javascript:window.print()'><img src="ico/print.png" width="16" height="16" alt="Drucken"></a>&nbsp;
231                <a title="Export als CSV" href='javascript:ALKISexport()'><img src="ico/download.png" width="16" height="16" alt="Export"></a>&nbsp;
232        </div>
233</form>
234
235<?php footer($gmlid, $_SERVER['PHP_SELF']."?", ""); ?>
236
237</body>
238</html>
Note: See TracBrowser for help on using the repository browser.