Hausübung 4 Lösung PDF

Preview

Summary

easy ihr keks...

Description

Hausübung 4 Jan Kreuz 225630 Aufgabe 1: CREATE TABLE haltestelle ( id serial PRIMARY KEY, haltestellenname CHARACTER(30) UNIQUE, geom GEOMETRY(POINT, 31467) );

INSERT INTO haltestelle (haltestellenname, geom) VALUES ('Darmstadt TU-Lichtwiese', ST_GeomFromText('Point(3477101.78 5525015.26)', 31467));

Aufgabe 2: CREATE TABLE gebaeude ( id serial PRIMARY KEY, gebaeudenummer CHARACTER(20) UNIQUE, geom GEOMETRY(POLYGON, 31467) ); INSERT INTO gebaeude (gebaeudenummer, geom) VALUES ('L1/01', ST_GeomFromText('POLYGON((3477244.98 5525103.74, 3477275.26 5525103.74, 3477275.26 5524960.04, 3477244.98 5524960.04, 3477244.98 5525103.74))', 31467)); INSERT INTO gebaeude (gebaeudenummer, geom) VALUES ('L1/06', ST_GeomFromText('POLYGON((3477287.39 5525018.66, 3477332.42 5525018.66, 3477332.42 5524989.04, 3477391.51 5524989.04, 3477391.51 5524957.69, 3477287.39 5524957.69, 3477287.39 5525018.66))', 31467));

—————— Hilfsskript———— INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES

('L1/02',ST_GeomFromText('POLYGON((3477287.81 5525069.15, 3477373.08 5525069.15, 3477373.08 5525030.77,3477287.81 5525030.77,3477287.81 5525069.15))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L2/01',ST_GeomFromText('POLYGON((3477181.38 5524958.98,3477196.66 5524958.98,3477196.66 5524883.15, 3477181.38 5524883.15,3477167.06 5524902.13,3477167.06 5524864.99,3477150.75 5524864.99, 3477150.75 5524939.96,3477167.06 5524939.96,3477181.38 5524919.35,3477181.38 5524958.98))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L2/02', ST_GeomFromText('POLYGON((3477119.00 5524937.70, 3477119.00 5524910.13, 3477103.92 5524910.13,3477103.92 5524854.11, 3477021.46 5524854.11, 3477021.46 5524884.89,3477078.19 5524884.89,3477078.19 5524904.69, 3477059.41 5524904.69, 3477059.40 5524922.16, 3477085.16 5524922.16, 3477085.16 5524937.70,3477119.00 5524937.70 ))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L4/01', ST_GeomFromText('POLYGON(( 3477023.72 5525088.43, 3477057.74 5525088.43, 3477057.74 5525079.15, 3477063.99 5525079.15, 3477063.99 5525062.58, 3477072.53 5525062.58, 3477072.53 5525047.74, 3477063.99 5525047.74, 3477063.99 5525020.71, 3477023.72 5525020.71, 3477023.72 5525033.37, 3477003.91 5525033.37, 3477003.91 5525079.15, 3477023.72 5525079.15, 3477023.72 5525088.43))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/01', ST_GeomFromText('POLYGON(( 3476917.90 5525133.95, 3477002.80 5525133.95, 3477002.80 5525109.07, 3476917.90 5525109.07, 3476917.90 5525133.95))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/02', ST_GeomFromText('POLYGON(( 3476947.76 5525254.28, 3476972.65 5525254.28, 3476972.65 5525140.39, 3476947.76 5525140.39, 3476947.76 5525254.28 ))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/03', ST_GeomFromText('POLYGON(( 3476918.49 5525193.09, 3476942.49 5525193.09, 3476942.49 5525169.08, 3476918.49 5525169.08,

3476918.49 5525193.09))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/04', ST_GeomFromText('POLYGON(( 3476918.49 5525223.24, 3476942.49 5525223.24, 3476942.49 5525199.82, 3476918.49 5525199.82, 3476918.49 5525223.24))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/05', ST_GeomFromText('POLYGON(( 3476918.78 5525253.69, 3476941.91 5525253.69, 3476941.91 5525229.98, 3476918.78 5525229.98, 3476918.78 5525253.69))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/06', ST_GeomFromText('POLYGON(( 3477064.89 5525166.28, 3477075.62 5525166.28, 3477075.62 5525114.63, 3477064.89 5525114.63, 3477064.89 5525166.28))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L5/07', ST_GeomFromText('POLYGON(( 3476980.37 5525164.27, 3477046.33 5525164.27, 3477046.33 5525140.12, 3476980.37 5525140.12, 3476980.37 5525164.27))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L2/03', ST_GeomFromText('POLYGON(( 3477039.02 5524965.43, 3477076.99 5524965.43, 3477076.99 5524949.06, 3477085.16 5524949.06, 3477085.16 5524922.16, 3477053.30 5524922.16, 3477053.30 5524900.88, 3477033.96 5524900.88, 3477033.96 5524922.16, 3477021.45 5524922.16, 3477021.45 5524949.06, 3477039.02 5524949.06, 3477039.02 5524965.43))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L2/04', ST_GeomFromText('POLYGON(( 3476995.55 5524953.54, 3477021.45 5524953.54, 3477021.45 5524868.16, 3476995.55 5524868.16, 3476995.55 5524953.54))',31467));

INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L2/05', ST_GeomFromText('POLYGON(( 3476950.58 5524910.28, 3476972.49 5524910.28, 3476972.49 5524827.32, 3476950.58 5524827.32, 3476950.58 5524910.28))',31467)); INSERT INTO gebaeude(gebaeudenummer,gebaeude_geom) VALUES ('L3/01', ST_GeomFromText('POLYGON(( 3476762.46 5525049.17, 3476881.42 5525049.17, 3476881.42 5524982.00, 3476762.46 5524982.00, 3476762.46 5525049.17), (3476821.61 5525039.73, 3476843.52 5525039.73, 3476843.52 5524991.43, 3476821.61 5524991.43, 3476821.61 5525039.73))',31467)); Aufgabe 3: CREATE TABLE strasse ( id serial PRIMARY KEY, strassenname CHARACTER(70) UNIQUE, geom_strasse GEOMETRY(LINESTRING, 31467) );

INSERT INTO strasse (strassenname, geom_strasse) VALUES ('Jovanka-Bontschits-Str.', ST_GeomFromText('LINESTRING(3477107.54 5525112.04, 3477206.62 5525112.04)', 31467));

INSERT INTO strasse (strassenname, geom_strasse) VALUES ('Otto-Berndt-Str.', ST_GeomFromText('LINESTRING(3477206.62 5525112.04, 3477206.62 5524865.35)', 31467));

INSERT INTO strasse (strassenname, geom_strasse) VALUES ('Alarich-Weiss-Str.', ST_GeomFromText('LINESTRING(3476890.48 5524979.31, 3477206.62 5524979.31)', 31467)); INSERT INTO strasse (strassenname, geom_strasse) VALUES ('Eugen-Kogon-Str.', ST_GeomFromText('LINESTRING(3477107.54 5524979.31, 3477107.54 5525255.37)', 31467));

Aufgabe 4: ALTER TABLE student ADD COLUMN hauskoordinate GEOMETRY(POINT, 25832); UPDATE STUDENT SET hauskoordinate = (ST_GeomFromText('POINT(474037.9 5524647.7)', 25832)) WHERE student.matrikelnr = '1234561'; UPDATE STUDENT SET hauskoordinate = (ST_GeomFromText('POINT(474973.9 5524788.4)', 25832)) WHERE student.matrikelnr = '1234562';...