Mercurial > lada > lada-server
changeset 1196:fffd66b6ee8a
merged.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Tue, 29 Nov 2016 16:00:27 +0100 |
| parents | 768a4c957a18 (diff) 3923b41f8783 (current diff) |
| children | 4c6f8984ff03 |
| files | db_schema/setup-db.sh db_schema/stammdaten_data.sql |
| diffstat | 9 files changed, 268 insertions(+), 319 deletions(-) [+] |
line wrap: on
line diff
--- a/db_schema/setup-db.sh Fri Nov 25 22:25:13 2016 +0100 +++ b/db_schema/setup-db.sh Tue Nov 29 16:00:27 2016 +0100 @@ -121,13 +121,14 @@ echo downlaod and import german administrative borders TS="0101" + cd /tmp if [ ! -f vg250_${TS}.utm32s.shape.ebenen.zip ]; then curl -O \ http://sg.geodatenzentrum.de/web_download/vg/vg250_${TS}/utm32s/shape/vg250_${TS}.utm32s.shape.ebenen.zip fi unzip -u vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*" # cd vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/ - shp2pgsql vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME + shp2pgsql -s 25832:4326 vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME # rm -rf vg250_${TS}.utm32s.shape.ebenen echo fille stammdaten.verwaltungsgrenze @@ -138,3 +139,5 @@ psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_auth.sql fi fi + +exec /opt/lada_sql/alter_owner.sh
--- a/db_schema/stammdaten_schema.sql Fri Nov 25 22:25:13 2016 +0100 +++ b/db_schema/stammdaten_schema.sql Tue Nov 29 16:00:27 2016 +0100 @@ -167,7 +167,7 @@ CREATE TABLE verwaltungsgrenze ( id serial PRIMARY KEY, gem_id character varying(8) NOT NULL, - shape public.geometry(MultiPolygon) + shape public.geometry(MultiPolygon, 4326) ); CREATE TABLE netz_betreiber ( @@ -236,26 +236,6 @@ ); CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -CREATE TABLE de_vg ( - id serial PRIMARY KEY, - use double precision, - rs character varying(12), - gf double precision, - rau_rs character varying(12), - gen character varying(50), - des character varying(75), - isn double precision, - bemerk character varying(75), - nambild character varying(16), - ags character varying(12), - rs_alt character varying(20), - wirksamkei date, - debkg_id character varying(16), - length numeric, - shape_area numeric, - geom public.geometry(MultiPolygon,4326) -); - CREATE TABLE deskriptor_umwelt ( id serial PRIMARY KEY,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/factory/OrtFactory.java Tue Nov 29 16:00:27 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 Fri Nov 25 22:25:13 2016 +0100 +++ b/src/main/java/de/intevation/lada/importer/laf/LafObjectMapper.java Tue Nov 29 16:00:27 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/DeVg.java Fri Nov 25 22:25:13 2016 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,205 +0,0 @@ -package de.intevation.lada.model.stammdaten; - -import java.io.Serializable; -import java.math.BigDecimal; -import java.util.Date; - -import javax.persistence.Column; -import javax.persistence.Entity; -import javax.persistence.Id; -import javax.persistence.Table; -import javax.persistence.Temporal; -import javax.persistence.TemporalType; - -import com.vividsolutions.jts.geom.MultiPolygon; - - -/** - * The persistent class for the de_vg database table. - * - */ -@Entity -@Table(name="de_vg") -public class DeVg implements Serializable { - private static final long serialVersionUID = 1L; - - @Id - private Integer id; - - private String ags; - - private String bemerk; - - @Column(name="debkg_id") - private String debkgId; - - private String des; - - private String gen; - - private MultiPolygon geom; - - private double gf; - - private double isn; - - private BigDecimal length; - - private String nambild; - - @Column(name="rau_rs") - private String rauRs; - - private String rs; - - @Column(name="rs_alt") - private String rsAlt; - - @Column(name="shape_area") - private BigDecimal shapeArea; - - private double use; - - @Temporal(TemporalType.DATE) - private Date wirksamkei; - - public DeVg() { - } - - public Integer getId() { - return this.id; - } - - public void setId(Integer id) { - this.id = id; - } - - public String getAgs() { - return this.ags; - } - - public void setAgs(String ags) { - this.ags = ags; - } - - public String getBemerk() { - return this.bemerk; - } - - public void setBemerk(String bemerk) { - this.bemerk = bemerk; - } - - public String getDebkgId() { - return this.debkgId; - } - - public void setDebkgId(String debkgId) { - this.debkgId = debkgId; - } - - public String getDes() { - return this.des; - } - - public void setDes(String des) { - this.des = des; - } - - public String getGen() { - return this.gen; - } - - public void setGen(String gen) { - this.gen = gen; - } - - public MultiPolygon getGeom() { - return this.geom; - } - - public void setGeom(MultiPolygon geom) { - this.geom = geom; - } - - public double getGf() { - return this.gf; - } - - public void setGf(double gf) { - this.gf = gf; - } - - public double getIsn() { - return this.isn; - } - - public void setIsn(double isn) { - this.isn = isn; - } - - public BigDecimal getLength() { - return this.length; - } - - public void setLength(BigDecimal length) { - this.length = length; - } - - public String getNambild() { - return this.nambild; - } - - public void setNambild(String nambild) { - this.nambild = nambild; - } - - public String getRauRs() { - return this.rauRs; - } - - public void setRauRs(String rauRs) { - this.rauRs = rauRs; - } - - public String getRs() { - return this.rs; - } - - public void setRs(String rs) { - this.rs = rs; - } - - public String getRsAlt() { - return this.rsAlt; - } - - public void setRsAlt(String rsAlt) { - this.rsAlt = rsAlt; - } - - public BigDecimal getShapeArea() { - return this.shapeArea; - } - - public void setShapeArea(BigDecimal shapeArea) { - this.shapeArea = shapeArea; - } - - public double getUse() { - return this.use; - } - - public void setUse(double use) { - this.use = use; - } - - public Date getWirksamkei() { - return this.wirksamkei; - } - - public void setWirksamkei(Date wirksamkei) { - this.wirksamkei = wirksamkei; - } - -}
--- a/src/main/java/de/intevation/lada/model/stammdaten/Ort.java Fri Nov 25 22:25:13 2016 +0100 +++ b/src/main/java/de/intevation/lada/model/stammdaten/Ort.java Tue Nov 29 16:00:27 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 Fri Nov 25 22:25:13 2016 +0100 +++ b/src/main/java/de/intevation/lada/model/stammdaten/Verwaltungseinheit.java Tue Nov 29 16:00:27 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; + } + }
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/intevation/lada/model/stammdaten/Verwaltungsgrenze.java Tue Nov 29 16:00:27 2016 +0100 @@ -0,0 +1,57 @@ +package de.intevation.lada.model.stammdaten; + +import java.io.Serializable; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.Table; + +import com.vividsolutions.jts.geom.MultiPolygon; + + +/** + * The persistent class for the verwaltungsgrenze database table. + * + */ +@Entity +@Table(name="verwaltungsgrenze") +public class Verwaltungsgrenze implements Serializable { + private static final long serialVersionUID = 1L; + + @Id + private Integer id; + + @Column(name="gem_id") + private String gemId; + + private MultiPolygon shape; + + public Verwaltungsgrenze() { + } + + public Integer getId() { + return this.id; + } + + public void setId(Integer id) { + this.id = id; + } + + public String getGemId() { + return this.gemId; + } + + public void setGemId(String gemId) { + this.gemId = gemId; + } + + public MultiPolygon getShape() { + return this.shape; + } + + public void setShape(MultiPolygon shape) { + this.shape = shape; + } + +}
--- a/src/main/java/de/intevation/lada/validation/rules/ort/CoordinatesInVE.java Fri Nov 25 22:25:13 2016 +0100 +++ b/src/main/java/de/intevation/lada/validation/rules/ort/CoordinatesInVE.java Tue Nov 29 16:00:27 2016 +0100 @@ -17,9 +17,9 @@ import com.vividsolutions.jts.geom.PrecisionModel; import de.intevation.lada.model.land.Ortszuordnung; -import de.intevation.lada.model.stammdaten.DeVg; import de.intevation.lada.model.stammdaten.Ort; import de.intevation.lada.model.stammdaten.Verwaltungseinheit; +import de.intevation.lada.model.stammdaten.Verwaltungsgrenze; import de.intevation.lada.util.annotation.RepositoryConfig; import de.intevation.lada.util.data.QueryBuilder; import de.intevation.lada.util.data.Repository; @@ -65,11 +65,11 @@ return violation; } Verwaltungseinheit ve = ((List<Verwaltungseinheit>)ver.getData()).get(0); - QueryBuilder<DeVg> vg = - new QueryBuilder<DeVg>(repository.entityManager("stamm"), DeVg.class); + QueryBuilder<Verwaltungsgrenze> vg = + new QueryBuilder<Verwaltungsgrenze>(repository.entityManager("stamm"), Verwaltungsgrenze.class); vg.and("ags", ve.getId()); Response rvg = repository.filter(vg.getQuery(), "stamm"); - List<DeVg> vgs = (List<DeVg>)rvg.getData(); + List<Verwaltungsgrenze> vgs = (List<Verwaltungsgrenze>)rvg.getData(); if (vgs == null || vgs.isEmpty()) { Violation violation = new Violation(); violation.addWarning("verwaltungseinheit", 653); @@ -81,8 +81,8 @@ GeometryFactory gf = new GeometryFactory(new PrecisionModel(PrecisionModel.FLOATING), 4326); Point p = gf.createPoint(c); boolean hit = false; - for (DeVg singlevg : vgs) { - if(singlevg.getGeom().contains(p)) { + for (Verwaltungsgrenze singlevg : vgs) { + if(singlevg.getShape().contains(p)) { hit = true; } }