Kilometerquadrate ETRS89 und GKK: Unterschied zwischen den Versionen

Aktuelle Version vom 26. Mai 2011, 10:35 Uhr

--Hschmidt 09:12, 20. Mai 2011 (CEST)
Die Scripte hat A. Thurm zur Verfügung gestellt, Danke!
Benötigte Datenbank Tabellen:

Für ETRS89:

CREATE TABLE kmq_25833
(
  id character varying(8),
  label_kurz character varying(4)
)
WITH (OIDS=TRUE);
SELECT AddGeometryColumn('public', 'kmq_25833','the_geom',25833,'POLYGON', 2);
CREATE INDEX gist_kmq_25833 ON kmq_25833 USING gist (the_geom);
ALTER TABLE kmq_25833 OWNER TO kvwmap;

Für GKK:

CREATE TABLE kmq_2398
(
  id character varying(8),
  label_kurz character varying(4)
)
WITH (OIDS=TRUE);
SELECT AddGeometryColumn('public', 'kmq_2398','the_geom',2398,'POLYGON', 2);
CREATE INDEX gist_kmq_2398 ON kmq_2398 USING gist (the_geom);
ALTER TABLE kmq_2398 OWNER TO kvwmap;

Die Tabellen werden mithilfe eines PHP-Scriptes gefüllt. Die Koordinatenbereiche und die ips sind entsprechend anzupassen:
connect_pgsql.php

 <?php
 $dbp="kvwmapsp";
 $userp="meindbuser";
 $hostp="localhost";
 $passwdp="meinpw";
 
    $conn = pg_connect ("dbname=$dbp user=$userp host=$hostp password=$passwdp")
    or die ("<div align=\"center\">Keine Verbindung zur PgSQL-DB m?glich oder die Datenbank \"<b>".$db."</b>\" existiert nicht!  <br><br>Wenden Sie sich an den Administrator.</div>");
 
    $dbqueryp = "pg_query";
    $fetcharrayp = "pg_fetch_array";
    $connectp = $conn;
 
 $ip=getenv('REMOTE_ADDR');
 $ip_array=explode(".",$ip);
 
 if (($ip_array[0]=='192' AND $ip_array[1]=='168') OR ($ip_array[0]=='128' AND $ip_array[1]=='1'))
    {
     define('URL','https://192.###.###.###/');
    }
    else
    {
     define('URL','https://194.##.###.##/');
    }
 
 ?>
 

Das Script zum Füllen der Tabelle für ETRS89. Koordinatenberiche anpassen!
kmq_25833.php

<?php
include ("connect_pgsql.php");

for ($rechts=311000; $rechts<=385000; $rechts=$rechts+1000)
    {
      for ($hoch=5885000; $hoch<=5970000; $hoch=$hoch+1000)
        {
          $label_kurz=substr($rechts,1,2).substr($hoch,2,2);
          $id=substr($rechts,0,1).substr($hoch,0,2).$label_kurz;
          $p1=$rechts." ".$hoch;
          $p2=$rechts." ".($hoch+1000);
          $p3=($rechts+1000)." ".($hoch+1000);
          $p4=($rechts+1000)." ".$hoch;
          
       $query="INSERT INTO kmq_25833 (id,label_kurz,the_geom) VALUES ('$id','$label_kurz',GeomFromText('POLYGON(($p1,$p2,$p3,$p4,$p1))',25833));";

          echo "<br>$query";
       
       $result = $dbqueryp($connectp,$query);

       }

   }

?>
 

Das Script zum Füllen der Tabelle für GKK. Koordinatenberiche anpassen!
kmq_2398.php

<?php
include ("connect_pgsql.php");

for ($rechts=4500000; $rechts<=4580000; $rechts=$rechts+1000)
    {
      for ($hoch=5885000; $hoch<=5970000; $hoch=$hoch+1000)
        {
          $label_kurz=substr($rechts,2,2).substr($hoch,2,2);
          $id=substr($rechts,0,2).substr($hoch,0,2).$label_kurz;
          $p1=$rechts." ".$hoch;
          $p2=$rechts." ".($hoch+1000);
          $p3=($rechts+1000)." ".($hoch+1000);
          $p4=($rechts+1000)." ".$hoch;
          
       $query="INSERT INTO kmq_2398 (id,label_kurz,the_geom) VALUES ('$id','$label_kurz',GeomFromText('POLYGON(($p1,$p2,$p3,$p4,$p1))',2398));";

          echo "<br>$query";
       
       $result = $dbqueryp($connectp,$query);

       }

   }

?>
 

Der ETRS89 Layer für kvwmap :

SET @group_id = 1;
SET @connection = 'user=xxxx password=xxxx dbname=kvwmapsp';

INSERT INTO layer (`Name`,`Datentyp`,`Gruppe`,`pfad`,`Data`,`schema`,`document_path`,`tileindex`,`tileitem`,`labelangleitem`,`labelitem`,`labelmaxscale`,`labelminscale`,`labelrequires`,`connection`,`printconnection`,`connectiontype`,`classitem`,`filteritem`,`tolerance`,`toleranceunits`,`epsg_code`,`template`,`queryable`,`transparency`,`drawingorder`,`minscale`,`maxscale`,`offsite`,`ows_srs`,`wms_name`,`wms_server_version`,`wms_format`,`wms_connectiontimeout`,`wms_auth_username`,`wms_auth_password`,`wfs_geom`,`selectiontype`,`querymap`,`logconsume`) VALUES('KMQ ETRS89','2',@group_id,'SELECT oid, * FROM kmq_25833 WHERE (1=1)','the_geom from (select oid, \'33\'||id as label, * from kmq_25833 where 1=1) as foo using unique oid using srid=25833','','','','','','label','50001','1','',@connection,'','6','oid','oid','3','pixels','25833','','0',NULL,'1410','1','1000000','','EPSG:25833','','1.1.0','image/png','60','','','','','0','');
SET @last_layer_id507=LAST_INSERT_ID();
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id507,'id','id','kmq_25833','kmq_25833','varchar','','','1','8','Text','','','','1');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id507,'label_kurz','label_kurz','kmq_25833','kmq_25833','varchar','','','1','4','Text','','','','2');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id507,'oid','oid','','','oid','','',NULL,NULL,'Text','','','','0');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id507,'the_geom','the_geom','kmq_25833','kmq_25833','geometry','POLYGON','','1',NULL,'Text','','','','3');
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Gitternetz_ETRS89',@last_layer_id507,'(1=1)','0','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES('9','','1','-1 -1 -1','','153 0 204','1','1',NULL,'',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);
INSERT INTO labels (`font`,`type`,`color`,`outlinecolor`,`shadowcolor`,`shadowsizex`,`shadowsizey`,`backgroundcolor`,`backgroundshadowcolor`,`backgroundshadowsizex`,`backgroundshadowsizey`,`size`,`minsize`,`maxsize`,`position`,`offsetx`,`offsety`,`angle`,`autoangle`,`buffer`,`antialias`,`minfeaturesize`,`maxfeaturesize`,`partials`,`wrap`,`the_force`) VALUES('verdana','0','153 0 204','255 255 255','',NULL,NULL,'','',NULL,NULL,'10','6','12',NULL,NULL,NULL,'0',NULL,NULL,'0',NULL,NULL,'0','0','1');
 SET @last_label_id=LAST_INSERT_ID();
INSERT INTO u_labels2classes (label_id, class_id) VALUES (@last_label_id, @last_class_id);

UPDATE layer_attributes SET options = REPLACE(options, '507', @last_layer_id507) WHERE layer_id IN(@last_layer_id507) AND form_element_type IN ('SubFormPK', 'SubFormFK', 'SubFormEmbeddedPK');
 

Der GKK Layer für kvwmap:

SET @group_id = 1;
SET @connection = 'user=xxxx password=xxxx dbname=kvwmapsp';

INSERT INTO layer (`Name`,`Datentyp`,`Gruppe`,`pfad`,`Data`,`schema`,`document_path`,`tileindex`,`tileitem`,`labelangleitem`,`labelitem`,`labelmaxscale`,`labelminscale`,`labelrequires`,`connection`,`printconnection`,`connectiontype`,`classitem`,`filteritem`,`tolerance`,`toleranceunits`,`epsg_code`,`template`,`queryable`,`transparency`,`drawingorder`,`minscale`,`maxscale`,`offsite`,`ows_srs`,`wms_name`,`wms_server_version`,`wms_format`,`wms_connectiontimeout`,`wms_auth_username`,`wms_auth_password`,`wfs_geom`,`selectiontype`,`querymap`,`logconsume`) VALUES('KMQ GKK','2',@group_id,'SELECT oid, * FROM kmq_2398 WHERE (1=1)','the_geom from (select oid, * from kmq_2398 where 1=1) as foo using unique oid using srid=2398','','','','','','id','50001','1','',@connection,'','6','oid','oid','3','pixels','2398','','0',NULL,'1400','1','1000000','','EPSG:2398','','1.1.0','image/png','60','','','','','0','');
SET @last_layer_id506=LAST_INSERT_ID();
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id506,'id','id','kmq_2398','kmq_2398','varchar','','','1','8','Text','','','','1');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id506,'label_kurz','label_kurz','kmq_2398','kmq_2398','varchar','','','1','4','Text','','','','2');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id506,'oid','oid','','','oid','','',NULL,NULL,'Text','','','','0');
INSERT INTO layer_attributes (`layer_id`,`name`,`real_name`,`tablename`,`table_alias_name`,`type`,`geometrytype`,`constraints`,`nullable`,`length`,`form_element_type`,`options`,`alias`,`tooltip`,`order`) VALUES(@last_layer_id506,'the_geom','the_geom','kmq_2398','kmq_2398','geometry','POLYGON','','1',NULL,'Text','','','','3');
INSERT INTO classes (`Name`,`Layer_ID`,`Expression`,`drawingorder`,`text`) VALUES('Gitternetz_GKK',@last_layer_id506,'(1=1)','0','');
SET @last_class_id=LAST_INSERT_ID();
INSERT INTO styles (`symbol`,`symbolname`,`size`,`color`,`backgroundcolor`,`outlinecolor`,`minsize`,`maxsize`,`angle`,`angleitem`,`antialias`,`width`,`minwidth`,`maxwidth`,`sizeitem`) VALUES('9','','1','-1 -1 -1','','0 0 255','1','1',NULL,'',NULL,NULL,NULL,NULL,'');
 SET @last_style_id=LAST_INSERT_ID();
INSERT INTO u_styles2classes (style_id, class_id) VALUES (@last_style_id, @last_class_id);
INSERT INTO labels (`font`,`type`,`color`,`outlinecolor`,`shadowcolor`,`shadowsizex`,`shadowsizey`,`backgroundcolor`,`backgroundshadowcolor`,`backgroundshadowsizex`,`backgroundshadowsizey`,`size`,`minsize`,`maxsize`,`position`,`offsetx`,`offsety`,`angle`,`autoangle`,`buffer`,`antialias`,`minfeaturesize`,`maxfeaturesize`,`partials`,`wrap`,`the_force`) VALUES('verdana','0','0 0 255','255 255 255','',NULL,NULL,'','',NULL,NULL,'10','6','12',NULL,NULL,NULL,'0',NULL,NULL,'0',NULL,NULL,'0','0','1');
 SET @last_label_id=LAST_INSERT_ID();
INSERT INTO u_labels2classes (label_id, class_id) VALUES (@last_label_id, @last_class_id);

UPDATE layer_attributes SET options = REPLACE(options, '506', @last_layer_id506) WHERE layer_id IN(@last_layer_id506) AND form_element_type IN ('SubFormPK', 'SubFormFK', 'SubFormEmbeddedPK');