--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/java/de/intevation/lada/factory/OrtFactory.java	Tue Nov 29 15:59:38 2016 +0100
@@ -0,0 +1,128 @@
+/* Copyright (C) 2013 by Bundesamt fuer Strahlenschutz
+ * Software engineering by Intevation GmbH
+ *
+ * This file is Free Software under the GNU GPL (v>=3)
+ * and comes with ABSOLUTELY NO WARRANTY! Check out
+ * the documentation coming with IMIS-Labordaten-Application for details.
+ */
+package de.intevation.lada.factory;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import javax.inject.Inject;
+import javax.persistence.Query;
+
+import org.apache.log4j.Logger;
+import org.geotools.geometry.jts.JTS;
+import org.geotools.referencing.CRS;
+import org.opengis.referencing.FactoryException;
+import org.opengis.referencing.crs.CoordinateReferenceSystem;
+import org.opengis.referencing.operation.MathTransform;
+import org.opengis.referencing.operation.TransformException;
+
+import com.vividsolutions.jts.geom.Coordinate;
+import com.vividsolutions.jts.geom.GeometryFactory;
+import com.vividsolutions.jts.geom.Point;
+
+import de.intevation.lada.importer.ReportItem;
+import de.intevation.lada.model.stammdaten.Ort;
+import de.intevation.lada.util.annotation.RepositoryConfig;
+import de.intevation.lada.util.data.Repository;
+import de.intevation.lada.util.data.RepositoryType;
+
+public class OrtFactory {
+
+    @Inject
+    private Logger logger;
+
+    @Inject
+    @RepositoryConfig(type=RepositoryType.RO)
+    private Repository repository;
+
+    private List<ReportItem> errors;
+
+    public void transformCoordinates(Ort ort) {
+        errors = new ArrayList<ReportItem>();
+        int kda = ort.getKdaId();
+        String epsg = null;
+        String xCoord = null;
+        String yCoord = null;
+        switch(kda) {
+            case 4: epsg = "EPSG:4326";
+                    xCoord = ort.getKoordXExtern();
+                    yCoord = ort.getKoordYExtern();
+                    break;
+            case 5: epsg = getEpsgForWgsUtm(ort.getKoordXExtern());
+                    xCoord = ort.getKoordXExtern().length() == 7 ?
+                        ort.getKoordXExtern().substring(1, 7) :
+                        ort.getKoordXExtern().substring(2, 8);
+                    yCoord = ort.getKoordYExtern();
+                    break;
+            default: break;
+        }
+        try {
+            CoordinateReferenceSystem src = CRS.decode(epsg);
+            CoordinateReferenceSystem target = CRS.decode("EPSG:4326");
+
+            MathTransform transform = CRS.findMathTransform(src, target);
+            Coordinate srcCoord = new Coordinate();
+            srcCoord.x = Double.valueOf(xCoord);
+            srcCoord.y = Double.valueOf(yCoord);
+            Coordinate targetCoord = new Coordinate();
+            JTS.transform(srcCoord, targetCoord, transform);
+
+            ort.setGeom(generateGeom(targetCoord.y, targetCoord.x));
+        } catch (FactoryException |
+                TransformException e) {
+            ReportItem err = new ReportItem();
+            err.setCode(672);
+            err.setKey("coordinates");
+            err.setValue(ort.getKdaId() + " " +
+                ort.getKoordXExtern() + " " + ort.getKoordYExtern());
+            errors.add(err);
+            return;
+        }
+    }
+
+    public void findVerwaltungseinheit(Ort ort) {
+        if (ort.getGeom() == null) {
+            return;
+        }
+        Query q = repository.entityManager("stamm")
+            .createQuery("SELECT vg.gemId" +
+                         "FROM Verwaltungsgrenze vg" +
+                         "WHERE contains(vg.shape, :geom) = TRUE");
+        q.setParameter("geom", ort.getGeom());
+        List<Object> ret = q.getResultList();
+        if (!ret.isEmpty()) {
+            ort.setGemId(ret.get(0).toString());
+            ort.setStaatId(0);
+        }
+        return;
+    }
+
+    private Point generateGeom(Double x, Double y) {
+        GeometryFactory geomFactory = new GeometryFactory();
+        Coordinate coord = new Coordinate(x, y);
+        Point geom = geomFactory.createPoint(coord);
+        geom.setSRID(4326);
+        return geom;
+    }
+
+    private String getEpsgForWgsUtm(String x) {
+        String epsg = "EPSG:326";
+        String part = x.split(",")[0];
+        String zone = part.length() == 7 ? ("0" + part.substring(0, 1)) :
+            part.substring(0, 2);
+        return epsg + zone;
+    }
+
+    public List<ReportItem> getErrors() {
+        return errors;
+    }
+
+    public boolean hasErrors() {
+        return !errors.isEmpty();
+    }
+}

--- a/src/main/java/de/intevation/lada/importer/laf/LafObjectMapper.java	Tue Nov 29 15:57:09 2016 +0100
+++ b/src/main/java/de/intevation/lada/importer/laf/LafObjectMapper.java	Tue Nov 29 15:59:38 2016 +0100
@@ -17,6 +17,7 @@
 import org.apache.commons.lang.StringUtils;
 import org.apache.log4j.Logger;
 
+import de.intevation.lada.factory.OrtFactory;
 import de.intevation.lada.factory.ProbeFactory;
 import de.intevation.lada.importer.Identified;
 import de.intevation.lada.importer.Identifier;
@@ -94,6 +95,8 @@
     @Inject
     private ProbeFactory factory;
 
+    @Inject OrtFactory ortFactory;
+
     private Map<String, List<ReportItem>> errors;
     private Map<String, List<ReportItem>> warnings;
     private List<ReportItem> currentErrors;
@@ -629,9 +632,6 @@
     private Ort findOrCreateOrt(Map<String, String> attributes, String type, Probe probe) {
         // If laf contains coordinates, find a ort with matching coordinates or
         // create one.
-        for (Entry<String, String> entry : attributes.entrySet()) {
-            logger.debug(entry.getKey() + ": " + entry.getValue());
-        }
         if ((attributes.get(type + "KOORDINATEN_ART") != null ||
              attributes.get(type + "KOORDINATEN_ART_S") != null) &&
             attributes.get(type + "KOORDINATEN_X") != null &&
@@ -659,16 +659,11 @@
                     currentErrors.add(err);
                     return null;
                 }
-                logger.debug("kda: " + arten.get(0).getId());
                 builder.and("kdaId", arten.get(0).getId());
             }
             builder.and("koordXExtern", attributes.get(type + "KOORDINATEN_X"));
             builder.and("koordYExtern", attributes.get(type + "KOORDINATEN_Y"));
             List<Ort> orte = repository.filterPlain(builder.getQuery(), "stamm");
-            logger.debug(attributes.get(type + "KOORDINATEN_ART_S"));
-            logger.debug(attributes.get(type + "KOORDINATEN_X"));
-            logger.debug(attributes.get(type + "KOORDINATEN_Y"));
-            logger.debug(orte.size());
             if (orte != null && orte.size() > 0) {
                 return orte.get(0);
             }
@@ -761,6 +756,7 @@
         else if (attributes.get(type + "GEMEINDESCHLUESSEL") != null) {
             gemId = attributes.get(type + "GEMEINDESCHLUESSEL");
         }
+
         if (gemId != null) {
             ort.setGemId(gemId);
             hasGem = true;
@@ -775,9 +771,9 @@
             }
             if (!hasKoord) {
                 ort.setMpArt("V");
-                ort.setKdaId(v.getKdaId());
-                ort.setKoordYExtern(v.getKoordYExtern());
-                ort.setKoordXExtern(v.getKoordXExtern());
+                ort.setKdaId(4);
+                ort.setKoordYExtern(String.valueOf(v.getMittelpunkt().getY()));
+                ort.setKoordXExtern(String.valueOf(v.getMittelpunkt().getX()));
             }
             ort.setKurztext(v.getBezeichnung());
             ort.setLangtext(v.getBezeichnung());
@@ -839,6 +835,14 @@
                 ort.setOzId(zusatz.getOzsId());
             }
         }
+        ortFactory.transformCoordinates(ort);
+        if (hasKoord && !hasGem) {
+            logger.debug("find Verwaltungseinheit");
+            ortFactory.findVerwaltungseinheit(ort);
+        }
+        if (ortFactory.hasErrors()) {
+            currentErrors.addAll(ortFactory.getErrors());
+        }
 
         repository.create(ort, "stamm");
         return ort;

--- a/src/main/java/de/intevation/lada/model/stammdaten/Ort.java	Tue Nov 29 15:57:09 2016 +0100
+++ b/src/main/java/de/intevation/lada/model/stammdaten/Ort.java	Tue Nov 29 15:59:38 2016 +0100
@@ -8,16 +8,22 @@
 import javax.persistence.GeneratedValue;
 import javax.persistence.GenerationType;
 import javax.persistence.Id;
-import javax.persistence.NamedQuery;
+import javax.persistence.Table;
 import javax.persistence.Transient;
 
+import org.hibernate.annotations.Type;
+
+import com.fasterxml.jackson.annotation.JsonIgnore;
+import com.vividsolutions.jts.geom.Point;
+
+
 
 /**
  * The persistent class for the ort database table.
  * 
  */
 @Entity
-@NamedQuery(name="Ort.findAll", query="SELECT o FROM Ort o")
+@Table(name="ort")
 public class Ort implements Serializable {
     private static final long serialVersionUID = 1L;
 
@@ -51,13 +57,9 @@
 
     private String langtext;
 
-    private Double latitude;
-
     @Column(name="letzte_aenderung")
     private Timestamp letzteAenderung;
 
-    private Double longitude;
-
     @Column(name="mp_art")
     private String mpArt;
 
@@ -90,6 +92,10 @@
     @Column(name="kda_id")
     private Integer kdaId;
 
+    @Column(columnDefinition="geometry(Point, 4326)")
+    @Type(type = "org.hibernate.spatial.GeometryType")
+    private Point geom;
+
     @Transient
     private boolean readonly;
 
@@ -185,11 +191,7 @@
     }
 
     public Double getLatitude() {
-        return this.latitude;
-    }
-
-    public void setLatitude(Double latitude) {
-        this.latitude = latitude;
+        return this.geom.getY();
     }
 
     public Timestamp getLetzteAenderung() {
@@ -201,11 +203,7 @@
     }
 
     public Double getLongitude() {
-        return this.longitude;
-    }
-
-    public void setLongitude(Double longitude) {
-        this.longitude = longitude;
+        return this.geom.getX();
     }
 
     public String getMpArt() {
@@ -304,6 +302,16 @@
         this.kdaId = kdaId;
     }
 
+    @JsonIgnore
+    public Point getGeom() {
+        return geom;
+    }
+
+    @JsonIgnore
+    public void setGeom(Point geom) {
+        this.geom = geom;
+    }
+
     public boolean isReadonly() {
         return readonly;
     }

--- a/src/main/java/de/intevation/lada/model/stammdaten/Verwaltungseinheit.java	Tue Nov 29 15:57:09 2016 +0100
+++ b/src/main/java/de/intevation/lada/model/stammdaten/Verwaltungseinheit.java	Tue Nov 29 15:59:38 2016 +0100
@@ -6,6 +6,11 @@
 import javax.persistence.Entity;
 import javax.persistence.Id;
 
+import org.hibernate.annotations.Type;
+
+import com.fasterxml.jackson.annotation.JsonIgnore;
+import com.vividsolutions.jts.geom.Point;
+
 
 /**
  * The persistent class for the verwaltungseinheit database table.
@@ -23,38 +28,29 @@
     private String bundesland;
 
     @Column(name="is_bundesland")
-    private String isBundesland;
+    private Boolean isBundesland;
 
     @Column(name="is_gemeinde")
-    private String isGemeinde;
+    private Boolean isGemeinde;
 
     @Column(name="is_landkreis")
-    private String isLandkreis;
+    private Boolean isLandkreis;
 
     @Column(name="is_regbezirk")
-    private String isRegbezirk;
-
-    @Column(name="kda_id")
-    private Integer kdaId;
-
-    @Column(name="koord_x_extern")
-    private String koordXExtern;
-
-    @Column(name="koord_y_extern")
-    private String koordYExtern;
+    private Boolean isRegbezirk;
 
     private String kreis;
 
-    private Double latitude;
-
-    private Double longitude;
-
     private String nuts;
 
     private String plz;
 
     private String regbezirk;
 
+    @Column(columnDefinition="geometry(Point, 4326)")
+    @Type(type = "org.hibernate.spatial.GeometryType")
+    private Point mittelpunkt;
+
     public Verwaltungseinheit() {
     }
 
@@ -82,62 +78,38 @@
         this.bundesland = bundesland;
     }
 
-    public String getIsBundesland() {
+    public Boolean getIsBundesland() {
         return this.isBundesland;
     }
 
-    public void setIsBundesland(String isBundesland) {
+    public void setIsBundesland(Boolean isBundesland) {
         this.isBundesland = isBundesland;
     }
 
-    public String getIsGemeinde() {
+    public Boolean getIsGemeinde() {
         return this.isGemeinde;
     }
 
-    public void setIsGemeinde(String isGemeinde) {
+    public void setIsGemeinde(Boolean isGemeinde) {
         this.isGemeinde = isGemeinde;
     }
 
-    public String getIsLandkreis() {
+    public Boolean getIsLandkreis() {
         return this.isLandkreis;
     }
 
-    public void setIsLandkreis(String isLandkreis) {
+    public void setIsLandkreis(Boolean isLandkreis) {
         this.isLandkreis = isLandkreis;
     }
 
-    public String getIsRegbezirk() {
+    public Boolean getIsRegbezirk() {
         return this.isRegbezirk;
     }
 
-    public void setIsRegbezirk(String isRegbezirk) {
+    public void setIsRegbezirk(Boolean isRegbezirk) {
         this.isRegbezirk = isRegbezirk;
     }
 
-    public Integer getKdaId() {
-        return this.kdaId;
-    }
-
-    public void setKdaId(Integer kdaId) {
-        this.kdaId = kdaId;
-    }
-
-    public String getKoordXExtern() {
-        return this.koordXExtern;
-    }
-
-    public void setKoordXExtern(String koordXExtern) {
-        this.koordXExtern = koordXExtern;
-    }
-
-    public String getKoordYExtern() {
-        return this.koordYExtern;
-    }
-
-    public void setKoordYExtern(String koordYExtern) {
-        this.koordYExtern = koordYExtern;
-    }
-
     public String getKreis() {
         return this.kreis;
     }
@@ -147,19 +119,11 @@
     }
 
     public Double getLatitude() {
-        return this.latitude;
-    }
-
-    public void setLatitude(Double latitude) {
-        this.latitude = latitude;
+        return this.mittelpunkt.getY();
     }
 
     public Double getLongitude() {
-        return this.longitude;
-    }
-
-    public void setLongitude(Double longitude) {
-        this.longitude = longitude;
+        return this.mittelpunkt.getX();
     }
 
     public String getNuts() {
@@ -186,4 +150,14 @@
         this.regbezirk = regbezirk;
     }
 
+    @JsonIgnore
+    public Point getMittelpunkt() {
+        return mittelpunkt;
+    }
+
+    @JsonIgnore
+    public void setMittelpunkt(Point mittelpunkt) {
+        this.mittelpunkt = mittelpunkt;
+    }
+
 }