source: trunk/info/info/alkis/alkisstrasse.php @ 330

Revision 330, 9.4 KB checked in by frank.jaeger, 10 years ago (diff)

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