orbisgis / orbisdata Goto Github PK

@ebocher @SPalominos
I have started to code indicators using the OrbisProcess syntax and I am currently facing several problems (you can find the entire code at the end of the message):

1. I code in the Groovy console within the OrbisGIS plat-form and when I run my code, the script is executed but nothing is actually performed. I have tried to print random words at the beginning of the script and they appear only if I remove the following lines:
   import org.orbisgis.datamanager.h2gis.H2GIS

2. I do not know what is the expected method to use SQL queries within the OrbisProcess. I have tried two different versions (in "process1" I recover the database from the input ITable whereas in "process2" I pass the database and the Table name as inputs. What do you think would be the best solution ?

// The OrbisData library is imported
@GrabResolver(name='orbisgis', root='http://repo.orbisgis.org/')
@Grab(group='org.orbisgis', module='data-manager', version='1.0-SNAPSHOT')

// The H2Gis module is also imported
import org.orbisgis.datamanager.h2gis.H2GIS



// Create the tables needed to test the functions
def h2GIS = H2GIS.open([databaseName: './target/loadH2GIS'])
h2GIS.execute("""
     DROP TABLE IF EXISTS building_test;
     DROP TABLE IF EXISTS rsu_test;
     CREATE TABLE building_test (id_build int, the_geom geometry, height_wall float, height_roof float);
     CREATE TABLE rsu_test (id_rsu int, the_geom geometry);
     INSERT INTO building_test VALUES (1, 'POLYGON((4 4, 10 4, 10 30, 4 30, 4 4))'::GEOMETRY, 8), (2, 'POLYGON((12 4, 20 4, 20 9, 12 9, 12 4))'::GEOMETRY, 10), (3, 'POLYGON((25 4, 45 4, 45 9, 25 9, 25 4))'::GEOMETRY, 8), (4, 'POLYGON((25 25, 40 25, 40 37, 25 37, 25 25))'::GEOMETRY, 5), (5, 'POLYGON((12 25, 25 25, 25 35, 12 35, 12 25))'::GEOMETRY, 12), (6, 'POLYGON((52 2, 54 2, 54 10, 52 10, 52 2))'::GEOMETRY, 15);
     INSERT INTO rsu_test VALUES (1, 'POLYGON((0 0, 50 0, 50 40, 0 40, 0 0))'::GEOMETRY), (2, 'POLYGON((50 0, 55 0, 55 30, 50 30, 50 0))'::GEOMETRY);
""")

def process1 = processFactory.create(
      "Building area without SQL passed",
      [inputA: ITable, inputB: String, inputC: String, inputD: String],
      [outputA : ITable],
      { inputA, inputB, inputC, inputD -> 
      // Recover the DataBase where is located the inputA Table
      sql = inputA.getDataBase()
      // Recover the name of the input Table in order to be used in the SQL queries
      inputA_name = inputA.getTableName()    // Note that I have not found any function to get the Table name...
      // Calculate the value of the indicator and store it in a new Table
      sql.execute("create table $inputB as select $inputD, st_area($inputD) as $inputC from $inputA_name")
      [outputA : sql.getSpatialTable("$inputA_name")] }
)

process1.execute([inputA: h2GIS.getSpatialTable("building_test"), inputB: "building_area", inputC: "building_area", inputD: "the_geom"])

def process2 = processFactory.create(
      "Building area with SQL passed",
      [inputA: String, inputB: String, inputC: String, inputD: String, inputE: Database],
      [outputA : ITable],
      { inputA, inputB, inputC, inputD, inputE -> 
      // Calculate the value of the indicator and store it in a new Table
      inputE.execute("create table $inputB as select $inputD, st_area($inputD) as $inputC from $inputA")
      [outputA : inputE.getSpatialTable("$inputB")] }
)

process2.execute([inputA: "building_test", inputB: "building_area", inputC: "building_area", inputD: "the_geom", inputE: h2GIS])

Add a method to return the parent folder of the datasource

Add a method to stop a process

Make JdbcDataSource.load() methods be able to recieve URI and File.

This method is recursive on the type Function. It separate the different type of expression : Function, ValueReference, Literal and Object.

For the type object, there are problems because there aren't any solution to know the structure of the type.
The only solution was that we add a condition for each type that we can find. Example: the GML.

• SpatialTable.extend() -> Return the full extend of the spatial table
• SpatialTable.estimatedExtend() -> Return the estimated extend
• SpatialTable.srid() -> Return the srid code of the spatial table
• SpatialTable.columns() -> Returns all column informations

Seee SFSUtilities.java in H2GIS

Syntactic sugar :

H2GIS.getSpatialTable -> H2GIS.spatialTable
H2GIS.getTable -> H2GIS.table
H2GIS.getDataSet -> H2GIS.dataSet

idem for PostGIS

Add test before and after the process execution inside a mapper with a syntax like :

mapper.before(pA).with(pA.inA1).check({inA1 -> inA1 == "t"}).stopOnFail("Message")
mapper.after(pA).with(pA.outA1).check({outA1 -> outA1 == "tutu"}).continueOnFail("Message").stopOnSuccess(Message)

What do you think if we add a short syntax to print values, something as

datasource.getTable("sylvain") as out

will print

+-----------+
| gamer |
+-----------+
| one |
| two |
| three |
+-----------+

the same for

datasource.getTable("sylvain").columnNames as out

Remove deprecated annotation before release :

• ProcessFactory.class

Write a cookbook/documentation for the usage of orbisdata.

In IProcesses, how should we deal with default values ?
For example, in geoclimate, I have the following function :

/**
 * This process is used to merge the geometries that touch each other
 *
 * @param datasource A connexion to a database (H2GIS, PostGIS, ...) where are stored the input Table and in which
 * the resulting database will be stored
 * @param inputTableName The input table tos create the block (group of geometries)
 * @param distance A distance to group the geometries
 * @param prefixName A prefix used to name the output table
 * @param outputTableName The name of the output table
 * @return A database table name and the name of the column ID
 */
static IProcess createBlocks(){
    return processFactory.create("Merge the geometries that touch each other",
            [inputTableName: String, distance : double, prefixName: String, datasource: JdbcDataSource],
            [outputTableName : String, outputIdBlock: String],
            { inputTableName,distance =0.0, prefixName="block", datasource ->
                logger.info("Merging the geometries...")

                def columnIdName = "id_block"

                // The name of the outputTableName is constructed
                String baseName = "created_blocks"
                String outputTableName = prefixName + "_" + baseName

                datasource.execute "DROP TABLE IF EXISTS $outputTableName".toString()
                datasource.execute "CREATE TABLE $outputTableName as select EXPLOD_ID as $columnIdName, the_geom ".toString()+
                        "from st_explode ('(select ST_UNION(ST_ACCUM(ST_BUFFER(THE_GEOM,$distance))) as the_geom".toString()+
                        " from $inputTableName)')".toString()
                logger.info("The geometries have been merged")
                [outputTableName: outputTableName, outputIdBlock: columnIdName]
            }
    )
}

When I call it, I would use the default value of distance. If I set it to null or if I do not declare it, it set the distance to null instead of my default value.

    @Test
    void createBlocksTest() {
        def h2GIS = H2GIS.open([databaseName: '/tmp/spatialunitsdb'])
        String sqlString = new File(this.class.getResource("data_for_tests.sql").toURI()).text
        h2GIS.execute(sqlString)
        h2GIS.execute("drop table if exists build_tempo; " +
                "create table build_tempo as select * from building_test where id_build <27")
        def  blockP =  Geoclimate.SpatialUnits.createBlocks()
        blockP.execute([inputTableName: "build_tempo",distance:null,
                     prefixName: "block", datasource: h2GIS])
        String outputTable = blockP.results.outputTableName
        def countRows =  h2GIS.firstRow("select count(*) as numberOfRows from $outputTable".toString())
        assertEquals 12 , countRows.numberOfRows
    }

To H2GIS add open(String path), open(String path, String user, String password)
To POSTGIS add open(String path, String user, String password)
And replace open(String fileName) by open(File fileName)

Use the asType(Class) method to convert Process into a OrbisWps process.

Add a new way to declare the process in/outputs in order to set additional information (WPS style) like the minOccurs, description, keywords ...

The syntax will look like :

.create("Process title",
    [inputA : String, 
        inputB : Literal.String.description("input description").minOccurs(0).maxOccurs(42)]
        inputB : Complex.JdbcTable.description("input description").minOccurs(0).maxOccurs(42)]
    ...
)

@test
void testColumnEmptyRow() {
def h2GIS = H2GIS.open([databaseName: './target/loadH2GIS'])
h2GIS.execute(""" DROP TABLE IF EXISTS h2gis;
CREATE TABLE h2gis (id int, the_geom geometry(point));""")
assertTrue(h2GIS.getSpatialTable("h2gis")."THE_GEOM")
assertTrue(h2GIS.getSpatialTable("h2gis").the_geom)
}

return MissingProperty No such property: THE_GEOM

must return a JDBCColumn

Proposals :

h2gis.getSpatialTable("buildings").row{ meta ->
    meta.tableName == 'buildings'
   meta.columnCount 
   meta.srid //Return the SRID of the table
 
    //First one
    meta.getColumnLabel(1)
    meta.getColumnName(1)
    meta.getColumnTypeName(1) 
    //All
    meta*.columnName
    //Filtering names
    metaData.any{ it.columnName.contains('geom') }
})

or

h2gis.getSpatialTable("buildings").columnNames
//-> related to println sql.firstRow('select * from buildings').keySet()

Add doc, especially here
https://github.com/orbisgis/orbisdata/blob/master/process-manager/src/main/java/org/orbisgis/processmanager/ProcessMapper.java#L69

Each class should have the same behaviour for the GroovyObject methods and missingMethod :

//getters
obj.getValue()
obj.value()
obj.value
//setters
obj.setValue("toto")
obj.value("toto")
obj.value = "toto"

Add on IJdbcDataSource interface two methods import and export.

Signatures

IJdbcDataSource.import('/tmp/file.shp');
IJdbcDataSource.import('/tmp/file.shp', 'tableName');
IJdbcDataSource.import('/tmp/file.shp', 'tableName', 'encoding', 'delete');

IJdbcDataSource.export('/tmp/file.shp', 'tableName');
IJdbcDataSource.export('/tmp/file.shp', 'tableName', 'encoding');

I created a class SqlToFes. This class has a public stactic method which takes a String and return an JaxB Object (Filter or Sort By).

But for the parameter String from the method, I don't know his structure at beginnig :
SELECT FROM WHERE ORDER BY (full request) or only the line of the WHERE (or the ORDER BY).

In this part, we must get back parameters of the command 'Where' from a xml file.

@test
void testArrayInput(){
def process = processFactory.create(
"title",
[inputA : ['st_area', 'st_type', 'st_perimeter']],
[outputA : String],
{ inputA ->[outputA : inputA[0]+'the_geom) as area']}
)
}

@j3r3m1

In order to filter the columns (as for the SQL DSL builder), I propose to add two new methods on IJdbcDataSource interface

ISpatialTable getSpatialTable(String tableName, String... fields);

ITable getTable(String tableName, String... fields);

The default value is now taken into account (#93). However, I got a problem in the following example:

public static IProcess computeRSUIndicators() {
    return processFactory.create("Compute the geoindicators at block scale",
            [datasource                 : JdbcDataSource, buildingTable              : String,
             rsuTable                   : String,         prefixName                 : "rsu_indicators"],
[output: String],
{datasource, buildingTable, prefixName, rsuTable}
...

The prefixName and rsuTable are inverted in the closure and the default prefixName value is assigned to rsuTable. Is it a normal behaviour ? Then I have to verify that the inputs are in the same order in the declaration and in the closure ?

about orbisgis/geoclimate#105

Add a PostGIS datasource entry.

Increase the coverage of :

• data-manager
• process-manager

and duplicate the test in groovy

In order to ensure no API changes, it would be great to release a first version of OrbisData.
@ebocher Do you agree ?

It would be nice if we could automatically log the name of the process that is executed

I have tried the commands you have proposed to create indexes on spatial tables and I got the following error message:
'No such property: the_geom for class: org.orbisgis.datamanager.h2gis.H2gisSpatialTable'

Here is what I have executed:

H2gisSpatialTable rsuSpatialTable = datasource.getSpatialTable(rsuTable)
if(!(rsuSpatialTable[geometricColumnRsu].spatialIndexed)){
    rsuSpatialTable[geometricColumnRsu].createSpatialIndex()

Note that it is the same problem if I write rsuSpatialTable.the_geom and that I have performed a mvn clean install in a new terminal outside IntelliJ.

Provide a customized groovy console that pre-load the OrbisData libraries.
It can be used to execute Geoclimate scripts.

Add a module which provides a Groovy console preloading the classes H2GIS, POSTGIS, ProcessFactory

println(h2GIS.getSpatialTable("parcelle").columnNames)
-> Takes 2 s

println((h2GIS.getSpatialTable("parcelle").limit(0) as ITable).columnNames)

-> Takes 8 ms

So it could be nice to add a workaround to accelerate the getColumnNames

Returns
intelij error:java:invalid target release:11

@j3r3m1

Add a logger.info to prevent when a IProcess is done

The idea is to add a process manager to manager several process factories, register, delete a process.

Use cases

ProcessManager pm = new ProcessManager()

ps.getFactory("idOfTheFactory") -> Return a factory
ps.getFactory() -> Return the default factory

a factory can be protected so it's not possible to remove or delete a process.

ps.factories() -> List the factory ids

Add the generated javadoc to the site goal.
Add missing javadoc.

on OrbisDATA create a module filter and add a submodule fes-2.0.2
This submodule must contains methods to read and write the FES xml encoding.
@Vquillien, @SPalominos

In order to simplify the acces of process results, add the syntax simplification process.resultName like :

process.getResults().output -> process.output

This is a proposal to implement a method to check if a column exists and its type.

h2GIS.getSpatialTable("mytable").exists(THE_GEOM:GEOMETRY, ID : String)

the_geom -> Column name
Geometry -> Column type

@SPalominos

The syntax in orbisdata is not always the same. Sometimes we use syntax like :

fct(A, B, C, D)

and other times we use :

obj.a(A).b(B).c(C).d(D)

We should be consistant.

Add

        <dependency>
            <groupId>org.orbisgis</groupId>
            <artifactId>h2gis-network</artifactId>
        </dependency>

as a dependency to OrbisData

Add a module which provides a Groovy console preloading the classes H2GIS, POSTGIS, ProcessFactory

In this part, we must get back parameters of the command 'order by' from a xml file.

Add a methods to ITable to get the first row values.

I share here some examples about orbisprocess library. A library to write process based on orbiswps job and the famous geoscript library.

//Declare
Process p = new Process([inputA: String],[outputA: String], { inputA ->
          [outputA: inputA.trim]
         }
         )
//Execute
 p.execute([inputA : 'OrbisGIS is nice'])

Note that the input and output parameters must be controlled.

DataSource.getSpatialTable("...") -> Create a resultset

DataSource.getSpatialTable("...") where "..." -> Create a resultset

So it seems two resultsets are opened to apply a where clause.

Right ?

Add a method to cast a SQL query to a SpatialTable

Proposal

h2gis.executeQuery("select the_geom from buildings limit 10) As SpatialTable

A user can write this

H2GIS.SELECT().FROM().WHERE() but also H2GIS.LIMIT().FROM().SELECT()

In order to avoid syntax errors, the SQL terms call must be more structured.

See http://iciql.com/ or https://www.jooq.org/learn/ as examples