search

orbisgis / h2gis

A spatial extension of the H2 database.
http://www.h2gis.org
GNU Lesser General Public License v3.0
208 stars 63 forks source link

Cannot convert error when using ST_DENSIFY #1272

Closed nicolas-f closed 2 years ago

nicolas-f commented 2 years ago

When running this:

SELECT st_densify('SRID=2154;MULTILINESTRING Z ((683926.2 6565576.2 626.8, 683959.3 6565557.1 630.4, 684021.6 6565520.7 632.9, 684070 6565491 633.5, 684101.8 6565469.6 632.1, 684128.7 6565450.2 630.4))'::geometry, 5);

I got this:

Erreur lors de la conversion de données "LINESTRING (683926.2 6565576.2, 683930.3374999999 6565573.8125, 683934.475 6565571.425, 683938.6125 6565569.0375, 683942.75 6565566.65, 683946.8875 6565564.2625, 683951.025 6565561.875, 683955.1625000001 6565559.4875, 683959.3 6565557.1, 683963.4533333334 6565554.673333333, 683967.6066666667 6565552.246666666, 683971.76 6565549.819999999, 683975.9133333333 6565547.393333334, 683980.0666666667 6565544.966666667, 683984.22 6565542.54, 683988.3733333333 6565540.113333333, 683992.5266666667 6565537.6866666665, 683996.68 6565535.26, 684000.8333333334 6565532.833333333, 684004.9866666667 6565530.406666666, 684009.14 6565527.98, 684013.2933333333 6565525.553333334, 684017.4466666667 6565523.126666667, 684021.6 6565520.7, 684025.6333333333 6565518.225000001, 684029.6666666666 6565515.75, 684033.7 6565513.275, 684037.7333333333 6565510.8, 684041.7666666666 6565508.325, 684045.7999999999 6565505.850000001, 684049.8333333334 6565503.375, 684053.8666666667 6565500.9, 684057.9 6565498.425, 684061.9333333333 6565495.95, 684065.9666666667 6565493.475, 684070 6565491, 684073.975 6565488.325, 684077.95 6565485.65, 684081.925 6565482.975, 684085.9 6565480.3, 684089.875 6565477.625, 684093.8500000001 6565474.949999999, 684097.8250000001 6565472.274999999, 684101.8 6565469.6, 684105.6428571428 6565466.828571429, 684109.4857142858 6565464.0571428565, 684113.3285714285 6565461.285714285, 684117.1714285715 6565458.514285714, 684121.0142857142 6565455.742857143, 684124.8571428572 6565452.971428571, 684128.7 6565450.2)"
Data conversion error converting "LINESTRING (683926.2 6565576.2, 683930.3374999999 6565573.8125, 683934.475 6565571.425, 683938.6125 6565569.0375, 683942.75 6565566.65, 683946.8875 6565564.2625, 683951.025 6565561.875, 683955.1625000001 6565559.4875, 683959.3 6565557.1, 683963.4533333334 6565554.673333333, 683967.6066666667 6565552.246666666, 683971.76 6565549.819999999, 683975.9133333333 6565547.393333334, 683980.0666666667 6565544.966666667, 683984.22 6565542.54, 683988.3733333333 6565540.113333333, 683992.5266666667 6565537.6866666665, 683996.68 6565535.26, 684000.8333333334 6565532.833333333, 684004.9866666667 6565530.406666666, 684009.14 6565527.98, 684013.2933333333 6565525.553333334, 684017.4466666667 6565523.126666667, 684021.6 6565520.7, 684025.6333333333 6565518.225000001, 684029.6666666666 6565515.75, 684033.7 6565513.275, 684037.7333333333 6565510.8, 684041.7666666666 6565508.325, 684045.7999999999 6565505.850000001, 684049.8333333334 6565503.375, 684053.8666666667 6565500.9, 684057.9 6565498.425, 684061.9333333333 6565495.95, 684065.9666666667 6565493.475, 684070 6565491, 684073.975 6565488.325, 684077.95 6565485.65, 684081.925 6565482.975, 684085.9 6565480.3, 684089.875 6565477.625, 684093.8500000001 6565474.949999999, 684097.8250000001 6565472.274999999, 684101.8 6565469.6, 684105.6428571428 6565466.828571429, 684109.4857142858 6565464.0571428565, 684113.3285714285 6565461.285714285, 684117.1714285715 6565458.514285714, 684121.0142857142 6565455.742857143, 684124.8571428572 6565452.971428571, 684128.7 6565450.2)"; SQL statement:
SELECT st_densify('SRID=2154;MULTILINESTRING Z ((683926.2 6565576.2 626.8, 683959.3 6565557.1 630.4, 684021.6 6565520.7 632.9, 684070 6565491 633.5, 684101.8 6565469.6 632.1, 684128.7 6565450.2 630.4))'::geometry, 5) [22018-210] 22018/22018 [(Aide)](https://h2database.com/javadoc/org/h2/api/ErrorCode.html#c22018)
org.h2.jdbc.JdbcSQLDataException: Erreur lors de la conversion de données "LINESTRING (683926.2 6565576.2, 683930.3374999999 6565573.8125, 683934.475 6565571.425, 683938.6125 6565569.0375, 683942.75 6565566.65, 683946.8875 6565564.2625, 683951.025 6565561.875, 683955.1625000001 6565559.4875, 683959.3 6565557.1, 683963.4533333334 6565554.673333333, 683967.6066666667 6565552.246666666, 683971.76 6565549.819999999, 683975.9133333333 6565547.393333334, 683980.0666666667 6565544.966666667, 683984.22 6565542.54, 683988.3733333333 6565540.113333333, 683992.5266666667 6565537.6866666665, 683996.68 6565535.26, 684000.8333333334 6565532.833333333, 684004.9866666667 6565530.406666666, 684009.14 6565527.98, 684013.2933333333 6565525.553333334, 684017.4466666667 6565523.126666667, 684021.6 6565520.7, 684025.6333333333 6565518.225000001, 684029.6666666666 6565515.75, 684033.7 6565513.275, 684037.7333333333 6565510.8, 684041.7666666666 6565508.325, 684045.7999999999 6565505.850000001, 684049.8333333334 6565503.375, 684053.8666666667 6565500.9, 684057.9 6565498.425, 684061.9333333333 6565495.95, 684065.9666666667 6565493.475, 684070 6565491, 684073.975 6565488.325, 684077.95 6565485.65, 684081.925 6565482.975, 684085.9 6565480.3, 684089.875 6565477.625, 684093.8500000001 6565474.949999999, 684097.8250000001 6565472.274999999, 684101.8 6565469.6, 684105.6428571428 6565466.828571429, 684109.4857142858 6565464.0571428565, 684113.3285714285 6565461.285714285, 684117.1714285715 6565458.514285714, 684121.0142857142 6565455.742857143, 684124.8571428572 6565452.971428571, 684128.7 6565450.2)"
Data conversion error converting "LINESTRING (683926.2 6565576.2, 683930.3374999999 6565573.8125, 683934.475 6565571.425, 683938.6125 6565569.0375, 683942.75 6565566.65, 683946.8875 6565564.2625, 683951.025 6565561.875, 683955.1625000001 6565559.4875, 683959.3 6565557.1, 683963.4533333334 6565554.673333333, 683967.6066666667 6565552.246666666, 683971.76 6565549.819999999, 683975.9133333333 6565547.393333334, 683980.0666666667 6565544.966666667, 683984.22 6565542.54, 683988.3733333333 6565540.113333333, 683992.5266666667 6565537.6866666665, 683996.68 6565535.26, 684000.8333333334 6565532.833333333, 684004.9866666667 6565530.406666666, 684009.14 6565527.98, 684013.2933333333 6565525.553333334, 684017.4466666667 6565523.126666667, 684021.6 6565520.7, 684025.6333333333 6565518.225000001, 684029.6666666666 6565515.75, 684033.7 6565513.275, 684037.7333333333 6565510.8, 684041.7666666666 6565508.325, 684045.7999999999 6565505.850000001, 684049.8333333334 6565503.375, 684053.8666666667 6565500.9, 684057.9 6565498.425, 684061.9333333333 6565495.95, 684065.9666666667 6565493.475, 684070 6565491, 684073.975 6565488.325, 684077.95 6565485.65, 684081.925 6565482.975, 684085.9 6565480.3, 684089.875 6565477.625, 684093.8500000001 6565474.949999999, 684097.8250000001 6565472.274999999, 684101.8 6565469.6, 684105.6428571428 6565466.828571429, 684109.4857142858 6565464.0571428565, 684113.3285714285 6565461.285714285, 684117.1714285715 6565458.514285714, 684121.0142857142 6565455.742857143, 684124.8571428572 6565452.971428571, 684128.7 6565450.2)"; SQL statement:
SELECT st_densify('SRID=2154;MULTILINESTRING Z ((683926.2 6565576.2 626.8, 683959.3 6565557.1 630.4, 684021.6 6565520.7 632.9, 684070 6565491 633.5, 684101.8 6565469.6 632.1, 684128.7 6565450.2 630.4))'::geometry, 5) [22018-210]
    at [org.h2.message.DbException.getJdbcSQLException(DbException.java:525](https://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=525&build=210))
    at [org.h2.message.DbException.getJdbcSQLException(DbException.java:496](https://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=496&build=210))
    at [org.h2.message.DbException.get(DbException.java:227](https://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=227&build=210))
    at [org.h2.message.DbException.get(DbException.java:203](https://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=203&build=210))
    at [org.h2.value.ValueGeometry.getFromGeometry(ValueGeometry.java:85](https://h2database.com/html/source.html?file=org/h2/value/ValueGeometry.java&line=85&build=210))
    at [org.h2.value.ValueToObjectConverter.objectToValue(ValueToObjectConverter.java:144](https://h2database.com/html/source.html?file=org/h2/value/ValueToObjectConverter.java&line=144&build=210))
    at [org.h2.schema.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:349](https://h2database.com/html/source.html?file=org/h2/schema/FunctionAlias.java&line=349&build=210))
    at [org.h2.expression.function.JavaFunction.getValue(JavaFunction.java:40](https://h2database.com/html/source.html?file=org/h2/expression/function/JavaFunction.java&line=40&build=210))
    at [org.h2.expression.function.JavaFunction.optimize(JavaFunction.java:64](https://h2database.com/html/source.html?file=org/h2/expression/function/JavaFunction.java&line=64&build=210))
    at [org.h2.command.query.Select.prepare(Select.java:1177](https://h2database.com/html/source.html?file=org/h2/command/query/Select.java&line=1177&build=210))
    at [org.h2.command.Parser.prepareCommand(Parser.java:557](https://h2database.com/html/source.html?file=org/h2/command/Parser.java&line=557&build=210))
    at [org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:615](https://h2database.com/html/source.html?file=org/h2/engine/SessionLocal.java&line=615&build=210))
    at [org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:553](https://h2database.com/html/source.html?file=org/h2/engine/SessionLocal.java&line=553&build=210))
    at [org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116](https://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.java&line=1116&build=210))
    at [org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237](https://h2database.com/html/source.html?file=org/h2/jdbc/JdbcStatement.java&line=237&build=210))
    at [org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223](https://h2database.com/html/source.html?file=org/h2/jdbc/JdbcStatement.java&line=223&build=210))
    at [org.h2.server.web.WebApp.getResult(WebApp.java:1339](https://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1339&build=210))
    at [org.h2.server.web.WebApp.query(WebApp.java:1137](https://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1137&build=210))
    at [org.h2.server.web.WebApp$1.next(WebApp.java:1103](https://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1103&build=210))
    at [org.h2.server.web.WebApp$1.next(WebApp.java:1090](https://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1090&build=210))
    at [org.h2.server.web.WebThread.process(WebThread.java:189](https://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=189&build=210))
    at [org.h2.server.web.WebThread.run(WebThread.java:102](https://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=102&build=210))
    at java.base/java.lang.Thread.run(Thread.java:829)
ebocher commented 2 years ago

Thanks @nicolas-f It seems a bug on JTS that doesn't interpolate the z value and create coordinate with NaN. H2 has a restrictive test on coordinate dimension and it doesn't support MULTILINESTRING Z with NaN z You can push an issue on JTS

nicolas-f commented 2 years ago

I could make a PR for JTS. But what to do ?

Interpolate Z but keeping a 2D distance between points ? Or convert the input/output into 2D points ?

ebocher commented 2 years ago

One way will be to talk with @dr-jts

nicolas-f commented 2 years ago

Could do that https://github.com/nicolas-f/jts/commit/65f840f3733e697a29cfdd7594c92d0be2c51b27 Could also change pointAlong instead but there would be side-effect on libraries ?

dr-jts commented 2 years ago

Could do that nicolas-f/jts@65f840f

This looks good to me. Want to make a JTS PR?

ebocher commented 2 years ago

@nicolas-f green flag from @dr-jts @nicolas-f You are right about pointAlong. There will certainly be other functions to check ;-)

ebocher commented 2 years ago

Fixed with JTS 1.19.0 @nicolas-f