| @@ -9,6 +9,9 @@ |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| +import java.util.ArrayList; |
| +import java.util.Random; |
| +import java.util.concurrent.ThreadLocalRandom; |
| import java.util.logging.Level; |
| import java.util.logging.Logger; |
| import org.testng.Assert; |
| @@ -27,6 +30,9 @@ |
| public class TestDriver { |
| |
| private static Connection conn; |
| + private static ArrayList<RandomMapunit> testMapunits = new ArrayList<>(); |
| + private static int MAX_RANDOM_MAPUNITS = 5; |
| + private static double MAX_AREA_DELTA = 0.1; |
| |
| public TestDriver() throws ClassNotFoundException, SQLException { |
| //1395701 |
| @@ -37,14 +43,13 @@ |
| // |
| // @Test |
| // public void hello() {} |
| - |
| @Test |
| public void testResultSetIteration() { |
| - String query = "SELECT top 100 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit " |
| - + "INNER JOIN legend ON mapunit.lkey=legend.lkey " |
| - + "INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey WHERE " |
| - + " legend.areatypename like 'Non-MLRA%' ORDER BY mapunit.mukey;"; |
| - |
| + String query = "SELECT top 100 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit " |
| + + " INNER JOIN legend ON mapunit.lkey=legend.lkey " |
| + + " INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey WHERE " |
| + + " legend.areatypename like 'Non-MLRA%' ORDER BY NEWID();"; |
| + |
| try { |
| ResultSet results; |
| Statement statement = conn.createStatement(); |
| @@ -58,22 +63,22 @@ |
| String muname = results.getString("muname"); |
| double muacres = results.getDouble("muacres"); |
| double brockdepmin = results.getDouble("brockdepmin"); |
| - count++; |
| + count++; |
| } |
| - Assert.assertTrue(count==100); |
| + Assert.assertTrue(count == 100); |
| } catch (SQLException ex) { |
| Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex); |
| Assert.assertTrue(false); |
| } |
| } |
| - |
| - @Test |
| - public void testBadColumnName(){ |
| - String query = "SELECT top 1 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit " |
| + |
| + @Test |
| + public void testBadColumnName() { |
| + String query = "SELECT top 1 legend.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muname, muacres, brockdepmin FROM mapunit " |
| + "INNER JOIN legend ON mapunit.lkey=legend.lkey " |
| + "INNER JOIN muaggatt ON mapunit.mukey=muaggatt.mukey WHERE " |
| + " legend.areatypename like 'Non-MLRA%' ORDER BY mapunit.mukey;"; |
| - |
| + |
| try { |
| ResultSet results; |
| Statement statement = conn.createStatement(); |
| @@ -87,38 +92,59 @@ |
| String muname = results.getString("muname"); |
| double muacres = results.getDouble("muacres"); |
| double brockdepmin = results.getDouble("brockdepmin"); |
| - double area = results.getDouble("area"); |
| + double area = results.getDouble("area"); |
| count++; |
| } |
| Assert.assertFalse(count > 0); //We should have recived an SQLException. |
| } catch (SQLException ex) { |
| Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, ex.getMessage()); |
| Assert.assertTrue(true); |
| - } |
| + } |
| } |
| - |
| + |
| @Test |
| - public void testComplexIntersectionQuery(){ |
| - String query = "SELECT areasymbol, musym, mukey, muname, muacres, brockdepmin, geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea() / 4046.86 AS area FROM (SELECT m.areasymbol, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, m.mupolygongeo.STIntersection(geometry::STGeomFromText ('POLYGON ((-72.54863 42.4476, -72.54835 42.44746, -72.54756 42.44709, -72.54863 42.44709, -72.54863 42.4476))', 4326)).MakeValid() as intersectPoly FROM dbo.mupolygon as m WITH (index(SI_mupolygon_24876)) INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('POLYGON ((-72.54863 42.4476, -72.54835 42.44746, -72.54756 42.44709, -72.54863 42.44709, -72.54863 42.4476))', 4326)) = 1 AND m.mupolygongeo.STIsValid()=1 AND (geometry:: STGeomFromText ('POLYGON ((-72.54863 42.4476, -72.54835 42.44746, -72.54756 42.44709, -72.54863 42.44709, -72.54863 42.4476))', 4326).STIsValid())=1 AND legend.areatypename like 'Non-MLRA%' ) as a ORDER BY mukey;"; |
| - |
| - try { |
| - ResultSet results; |
| - Statement statement = conn.createStatement(); |
| - results = statement.executeQuery(query); |
| + public void testComplexIntersectionQuery() { |
| + String bufferWKT; |
| |
| - while (results.next()) { |
| - String areasymbol = results.getString("areasymbol"); |
| - String musym = results.getString("musym"); |
| - String mukey = results.getString("mukey"); |
| - String muname = results.getString("muname"); |
| - double muacres = results.getDouble("muacres"); |
| - double brockdepmin = results.getDouble("brockdepmin"); |
| - double area = results.getDouble("area"); |
| + for (RandomMapunit tMapunit : testMapunits) { |
| + bufferWKT = tMapunit.getBufferWKT(); |
| + |
| + String query = "SELECT areasymbol, musym, mukey, muname, muacres, brockdepmin, " |
| + + " geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea() / 4046.86 AS area " |
| + + " FROM (" |
| + + " SELECT m.areasymbol, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, " |
| + + " m.mupolygongeo.STIntersection(geometry::STGeomFromText ('" + bufferWKT + "', 4326)).MakeValid() as intersectPoly " |
| + + " FROM dbo.mupolygon as m WITH (index(SI_mupolygon_24876)) " |
| + + " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey " |
| + + " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey " |
| + + " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey " |
| + + " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + bufferWKT + "', 4326)) = 1 " |
| + + " AND m.mupolygongeo.STIsValid()=1 " |
| + + " AND (geometry:: STGeomFromText ('" + bufferWKT + "', 4326).STIsValid())=1 " |
| + + " AND legend.areatypename like 'Non-MLRA%' ) as a ORDER BY mukey;"; |
| + |
| + try { |
| + ResultSet results; |
| + Statement statement = conn.createStatement(); |
| + results = statement.executeQuery(query); |
| + double area = 0.0; |
| + |
| + while (results.next()) { |
| + String areasymbol = results.getString("areasymbol"); |
| + String musym = results.getString("musym"); |
| + String mukey = results.getString("mukey"); |
| + String muname = results.getString("muname"); |
| + double muacres = results.getDouble("muacres"); |
| + double brockdepmin = results.getDouble("brockdepmin"); |
| + area += results.getDouble("area"); |
| + } |
| + |
| + Assert.assertEquals(area, tMapunit.getArea(), MAX_AREA_DELTA); |
| + } catch (SQLException ex) { |
| + Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex); |
| + Assert.assertTrue(false); |
| } |
| - } catch (SQLException ex) { |
| - Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex); |
| - Assert.assertTrue(false); |
| - } |
| + } |
| } |
| |
| @BeforeClass |
| @@ -127,6 +153,36 @@ |
| Reporter.log("Driver was loaded for: org.csu.csip.sdm.SDMDrive"); |
| conn = DriverManager.getConnection("jdbc:sdm:rest://SDMDataAccess.sc.egov.usda.gov/Tabular/post.rest"); |
| Reporter.log("Connection to remote SDM REST Service was successful"); |
| + |
| + //fill randomMapunits |
| + String query = "SELECT mupolygon.mupolygonkey, mupolygon.mukey, legend.areasymbol, mupolygon.mupolygongeo.STCentroid().STAsText() as centerPoint " |
| + + "FROM mupolygon, legend, mapunit " |
| + + " WHERE mupolygon.mupolygonkey in " |
| + + " (" |
| + + " SELECT TOP " + MAX_RANDOM_MAPUNITS + " mupolygonkey " |
| + + " FROM mupolygon, legend, mapunit " |
| + + " WHERE legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey AND legend.areatypename like 'Non-MLRA%' " |
| + + " ORDER BY newid() " |
| + + " ) " |
| + + " AND legend.lkey=mapunit.lkey AND mapunit.mukey=mupolygon.mukey; "; |
| + |
| + try { |
| + ResultSet results; |
| + Statement statement = conn.createStatement(); |
| + results = statement.executeQuery(query); |
| + |
| + while (results.next()) { |
| + RandomMapunit tMapunit = new RandomMapunit(results.getString("mupolygonkey"), results.getString("mukey"), results.getString("areasymbol"), results.getString("centerPoint")); |
| + testMapunits.add(tMapunit); |
| + } |
| + } catch (SQLException ex) { |
| + Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex); |
| + Assert.assertTrue(false); |
| + } |
| + |
| + for (RandomMapunit tMapunit : testMapunits) { |
| + tMapunit.getBuffers(conn); |
| + } |
| } |
| |
| @AfterClass |
| @@ -140,4 +196,53 @@ |
| @AfterMethod |
| public void tearDownMethod() throws Exception { |
| } |
| + |
| + private static class RandomMapunit { |
| + |
| + private String mupolygonKey; |
| + private String mukey; |
| + private String areasymbol; |
| + private String centroidWKT; |
| + private String bufferWKT; |
| + private double area; |
| + private double bufferSize; |
| + |
| + public RandomMapunit(String mupolygonKey, String mukey, String areasymbol, String centroidWKT) { |
| + this.mupolygonKey = mupolygonKey; |
| + this.mukey = mukey; |
| + this.areasymbol = areasymbol; |
| + this.centroidWKT = centroidWKT; |
| + bufferSize = ThreadLocalRandom.current().nextDouble(50, 100); |
| + } |
| + |
| + public void getBuffers(Connection conn) { |
| + String query = "SELECT geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STAsText() as bufferWKT, " |
| + + " geography::STGeomFromText('" + centroidWKT + "', 4326).STBuffer(" + bufferSize + ").STArea() / 4046.86 as area " |
| + + "FROM mupolygon" |
| + + " WHERE mupolygonkey=" + mupolygonKey + "; "; |
| + |
| + try { |
| + ResultSet results; |
| + Statement statement = conn.createStatement(); |
| + results = statement.executeQuery(query); |
| + |
| + if (results.next()) { |
| + bufferWKT = results.getString("bufferWKT"); |
| + area = results.getDouble("area"); |
| + } |
| + } catch (SQLException ex) { |
| + Logger.getLogger(TestDriver.class.getName()).log(Level.SEVERE, null, ex); |
| + Assert.assertTrue(false); |
| + } |
| + } |
| + |
| + public String getBufferWKT() { |
| + return bufferWKT; |
| + } |
| + |
| + public double getArea() { |
| + return area; |
| + } |
| + } |
| + |
| } |