Kilometerquadrate ETRS89 und GKK
Aus kvwmap
--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');
