org.jgrasstools.gears.io.las.spatialite.LasCellsTable Maven / Gradle / Ivy
The newest version!
/*
* This file is part of JGrasstools (http://www.jgrasstools.org)
* (C) HydroloGIS - www.hydrologis.com
*
* JGrasstools is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see cells ) throws Exception {
String sql = "INSERT INTO " + TABLENAME//
+ " (" + //
COLUMN_GEOM + "," + //
COLUMN_SOURCE_ID + "," + //
COLUMN_POINTS_COUNT + "," + //
COLUMN_AVG_ELEV + "," + //
COLUMN_MIN_ELEV + "," + //
COLUMN_MAX_ELEV + "," + //
COLUMN_POSITION_BLOB + "," + //
COLUMN_AVG_INTENSITY + "," + //
COLUMN_MIN_INTENSITY + "," + //
COLUMN_MAX_INTENSITY + "," + //
COLUMN_INTENS_CLASS_BLOB + "," + //
COLUMN_RETURNS_BLOB + "," + //
COLUMN_MIN_GPSTIME + "," + //
COLUMN_MAX_GPSTIME + "," + //
COLUMN_GPSTIME_BLOB + "," + //
COLUMN_COLORS_BLOB + //
") VALUES (GeomFromText(?, " + srid + "),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
IJGTConnection conn = db.getConnection();
boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try (IJGTPreparedStatement pStmt = conn.prepareStatement(sql)) {
for( LasCell cell : cells ) {
int i = 1;
pStmt.setString(i++, cell.polygon.toText());
pStmt.setLong(i++, cell.sourceId);
pStmt.setInt(i++, cell.pointsCount);
pStmt.setDouble(i++, cell.avgElev);
pStmt.setDouble(i++, cell.minElev);
pStmt.setDouble(i++, cell.maxElev);
pStmt.setBytes(i++, cell.xyzs);
pStmt.setShort(i++, cell.avgIntensity);
pStmt.setShort(i++, cell.minIntensity);
pStmt.setShort(i++, cell.maxIntensity);
pStmt.setBytes(i++, cell.intensitiesClassifications);
pStmt.setBytes(i++, cell.returns);
pStmt.setDouble(i++, cell.minGpsTime);
pStmt.setDouble(i++, cell.maxGpsTime);
pStmt.setBytes(i++, cell.gpsTimes);
pStmt.setBytes(i++, cell.colors);
pStmt.addBatch();
}
pStmt.executeBatch();
conn.commit();
conn.setAutoCommit(autoCommit);
}
}
/**
* Query the las cell table.
*
* @param db the db to use.
* @param envelope an optional {@link Envelope} to query spatially.
* @param exactGeometry an optional exact geometry. If available it is used instead of the envelope.
* @param doPosition if true position info is extracted.
* @param doIntensity if true intensity and classification info is extracted.
* @param doReturns if true return info is extracted.
* @param doTime if true time info is extracted.
* @param doColor if true color info is extracted.
* @param limitTo limit the cells to a value if != -1
* @return the list of extracted points
* @throws Exception
*/
public static List getLasCells( ASpatialDb db, Envelope envelope, Geometry exactGeometry, boolean doPosition,
boolean doIntensity, boolean doReturns, boolean doTime, boolean doColor, int limitTo ) throws Exception {
List lasCells = new ArrayList<>();
String sql = "SELECT ST_AsBinary(" + COLUMN_GEOM + ") AS " + COLUMN_GEOM + "," + COLUMN_ID + "," + COLUMN_SOURCE_ID + ","
+ COLUMN_POINTS_COUNT;
if (doPosition)
sql += "," + COLUMN_AVG_ELEV + "," + //
COLUMN_MIN_ELEV + "," + //
COLUMN_MAX_ELEV + "," + //
COLUMN_POSITION_BLOB;//
if (doIntensity)
sql += "," + COLUMN_AVG_INTENSITY + "," + //
COLUMN_MIN_INTENSITY + "," + //
COLUMN_MAX_INTENSITY + "," + //
COLUMN_INTENS_CLASS_BLOB;//
if (doReturns)
sql += "," + COLUMN_RETURNS_BLOB;
if (doTime)
sql += "," + COLUMN_MIN_GPSTIME + "," + //
COLUMN_MAX_GPSTIME + "," + //
COLUMN_GPSTIME_BLOB;
if (doColor)
sql += "," + COLUMN_COLORS_BLOB;
sql += " FROM " + TABLENAME;
if (exactGeometry != null) {
sql += " WHERE " + db.getSpatialindexGeometryWherePiece(TABLENAME, null, exactGeometry);
} else if (envelope != null) {
double x1 = envelope.getMinX();
double y1 = envelope.getMinY();
double x2 = envelope.getMaxX();
double y2 = envelope.getMaxY();
sql += " WHERE " + db.getSpatialindexBBoxWherePiece(TABLENAME, null, x1, y1, x2, y2);
}
if (limitTo > 0) {
sql += " LIMIT " + limitTo;
}
IJGTConnection conn = db.getConnection();
WKBReader wkbReader = new WKBReader();
try (IJGTStatement stmt = conn.createStatement(); IJGTResultSet rs = stmt.executeQuery(sql)) {
while( rs.next() ) {
LasCell lasCell = resultSetToCell(doPosition, doIntensity, doReturns, doTime, doColor, wkbReader, rs);
lasCells.add(lasCell);
}
return lasCells;
}
}
/**
* Query the las cell table on a geometry intersection.
*
* @param db the db to use.
* @param geometry an optional {@link Geometry} to query spatially.
* @param doPosition if true position info is extracted.
* @param doIntensity if true intensity and classification info is extracted.
* @param doReturns if true return info is extracted.
* @param doTime if true time info is extracted.
* @param doColor if true color info is extracted.
* @return the list of extracted points
* @throws Exception
*/
public static List getLasCells( ASpatialDb db, Geometry geometry, boolean doPosition, boolean doIntensity,
boolean doReturns, boolean doTime, boolean doColor ) throws Exception {
List lasCells = new ArrayList<>();
String sql = "SELECT ST_AsBinary(" + COLUMN_GEOM + ") AS " + COLUMN_GEOM + "," + COLUMN_ID + "," + COLUMN_SOURCE_ID + ","
+ COLUMN_POINTS_COUNT;
if (doPosition)
sql += "," + COLUMN_AVG_ELEV + "," + //
COLUMN_MIN_ELEV + "," + //
COLUMN_MAX_ELEV + "," + //
COLUMN_POSITION_BLOB;//
if (doIntensity)
sql += "," + COLUMN_AVG_INTENSITY + "," + //
COLUMN_MIN_INTENSITY + "," + //
COLUMN_MAX_INTENSITY + "," + //
COLUMN_INTENS_CLASS_BLOB;//
if (doReturns)
sql += "," + COLUMN_RETURNS_BLOB;
if (doTime)
sql += "," + COLUMN_MIN_GPSTIME + "," + //
COLUMN_MAX_GPSTIME + "," + //
COLUMN_GPSTIME_BLOB;
if (doColor)
sql += "," + COLUMN_COLORS_BLOB;
sql += " FROM " + TABLENAME;
if (geometry != null) {
sql += " WHERE " + db.getSpatialindexGeometryWherePiece(TABLENAME, null, geometry);
}
IJGTConnection conn = db.getConnection();
WKBReader wkbReader = new WKBReader();
try (IJGTStatement stmt = conn.createStatement(); IJGTResultSet rs = stmt.executeQuery(sql)) {
while( rs.next() ) {
LasCell lasCell = resultSetToCell(doPosition, doIntensity, doReturns, doTime, doColor, wkbReader, rs);
lasCells.add(lasCell);
}
return lasCells;
}
}
/**
* Query the las cell table based on source.
*
* @param db the db to use.
* @param geometry an optional {@link Geometry} to query spatially.
* @param doPosition if true position info is extracted.
* @param doIntensity if true intensity and classification info is extracted.
* @param doReturns if true return info is extracted.
* @param doTime if true time info is extracted.
* @param doColor if true color info is extracted.
* @return the list of extracted points
* @throws Exception
*/
public static List getLasCellsBySource( ASpatialDb db, long sourceId, boolean doPosition, boolean doIntensity,
boolean doReturns, boolean doTime, boolean doColor ) throws Exception {
List lasCells = new ArrayList<>();
String sql = "SELECT ST_AsBinary(" + COLUMN_GEOM + ") AS " + COLUMN_GEOM + "," + COLUMN_ID + "," + COLUMN_SOURCE_ID + ","
+ COLUMN_POINTS_COUNT;
if (doPosition)
sql += "," + COLUMN_AVG_ELEV + "," + //
COLUMN_MIN_ELEV + "," + //
COLUMN_MAX_ELEV + "," + //
COLUMN_POSITION_BLOB;//
if (doIntensity)
sql += "," + COLUMN_AVG_INTENSITY + "," + //
COLUMN_MIN_INTENSITY + "," + //
COLUMN_MAX_INTENSITY + "," + //
COLUMN_INTENS_CLASS_BLOB;//
if (doReturns)
sql += "," + COLUMN_RETURNS_BLOB;
if (doTime)
sql += "," + COLUMN_MIN_GPSTIME + "," + //
COLUMN_MAX_GPSTIME + "," + //
COLUMN_GPSTIME_BLOB;
if (doColor)
sql += "," + COLUMN_COLORS_BLOB;
sql += " FROM " + TABLENAME;
sql += " WHERE " + COLUMN_SOURCE_ID + "=" + sourceId;
IJGTConnection conn = db.getConnection();
WKBReader wkbReader = new WKBReader();
try (IJGTStatement stmt = conn.createStatement(); IJGTResultSet rs = stmt.executeQuery(sql)) {
while( rs.next() ) {
LasCell lasCell = resultSetToCell(doPosition, doIntensity, doReturns, doTime, doColor, wkbReader, rs);
lasCells.add(lasCell);
}
return lasCells;
}
}
private static LasCell resultSetToCell( boolean doPosition, boolean doIntensity, boolean doReturns, boolean doTime,
boolean doColor, WKBReader wkbReader, IJGTResultSet rs ) throws Exception, ParseException {
LasCell lasCell = new LasCell();
int i = 1;
byte[] geomBytes = rs.getBytes(i++);
Geometry tmpGeometry = wkbReader.read(geomBytes);
if (tmpGeometry instanceof Polygon) {
Polygon polygon = (Polygon) tmpGeometry;
lasCell.polygon = polygon;
lasCell.id = rs.getLong(i++);
lasCell.sourceId = rs.getLong(i++);
lasCell.pointsCount = rs.getInt(i++);
if (doPosition) {
lasCell.avgElev = rs.getDouble(i++);
lasCell.minElev = rs.getDouble(i++);
lasCell.maxElev = rs.getDouble(i++);
lasCell.xyzs = rs.getBytes(i++);
}
if (doIntensity) {
lasCell.avgIntensity = rs.getShort(i++);
lasCell.minIntensity = rs.getShort(i++);
lasCell.maxIntensity = rs.getShort(i++);
lasCell.intensitiesClassifications = rs.getBytes(i++);
}
if (doReturns)
lasCell.returns = rs.getBytes(i++);
if (doTime) {
lasCell.minGpsTime = rs.getDouble(i++);
lasCell.maxGpsTime = rs.getDouble(i++);
lasCell.gpsTimes = rs.getBytes(i++);
}
if (doColor)
lasCell.colors = rs.getBytes(i++);
return lasCell;
}
return null;
}
public static double[][] getCellPositions( LasCell cell ) {
int points = cell.pointsCount;
if (points == 0) {
return null;
}
double[][] xyzPoints = new double[points][3];
ByteBuffer buffer = ByteBuffer.wrap(cell.xyzs);
for( int i = 0; i < points; i++ ) {
xyzPoints[i][0] = buffer.getDouble();
xyzPoints[i][1] = buffer.getDouble();
xyzPoints[i][2] = buffer.getDouble();
}
return xyzPoints;
}
public static short[][] getCellIntensityClass( LasCell cell ) {
int points = cell.pointsCount;
if (points == 0) {
return null;
}
short[][] intensClassPoints = new short[points][2];
ByteBuffer buffer = ByteBuffer.wrap(cell.intensitiesClassifications);
for( int i = 0; i < points; i++ ) {
intensClassPoints[i][0] = buffer.getShort();
intensClassPoints[i][1] = buffer.getShort();
}
return intensClassPoints;
}
public static short[][] getCellColors( LasCell cell ) {
int points = cell.pointsCount;
if (points == 0) {
return null;
}
short[][] colorPoints = new short[points][3];
ByteBuffer buffer = ByteBuffer.wrap(cell.colors);
for( int i = 0; i < points; i++ ) {
colorPoints[i][0] = buffer.getShort();
colorPoints[i][1] = buffer.getShort();
colorPoints[i][2] = buffer.getShort();
}
return colorPoints;
}
}