Sqlite:在Intellij中执行Spatialite函数 [英] Sqlite: Execute Spatialite Functions in Intellij

问题描述

有人知道在intellij的sqlite数据库上是否可以执行spacespaceite函数? 空间函数的参考: http://www.gaia- gis.it/gaia-sins/spatialite-sql-4.2.0.html

Does anyone know whether it is possible to execute spatialite functions on sqlite-databases in intellij? Reference of spatialite functions: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html

特别是，我对使用Point类型的函数感兴趣.

In particular, I would be interested in using functions on type Point.

谢谢！

函数在正式的space排行榜gui中起作用，但是我认为没有办法以编程方式使用space排行榜，是吗?

Functions do work within the official spatialite-gui, however I don't think there is a way to use the spatialite-gui programmatically, is there?

这是到目前为止我尝试过的事情:在intellij中，我连接了Java JDBC库，并尝试使用功能ST_X(point)和ST_Y(point)失败了:

Here is what I tried so far: In intellij I connected the Java JDBC library and tried using function ST_X(point) and ST_Y(point) with no success:

        Connection c = null;
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:" + path + databaseName);
        c.setAutoCommit(false);
        System.out.println("Opened database \"" + databaseName + "\" successfully");

        String sql = "SELECT id, ST_Y(point), ST_X(point) from tablename;";

        Statement stmt =  c.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while ( rs.next() ) {
            String msg = "Id: ";
            msg += rs.getInt(1);
            msg += " , Latitude: ";
            msg += rs.getDouble(2);
            msg += " , Longitude: ";
            msg += rs.getDouble(3);
            System.out.println(msg);
        }
        rs.close();
        stmt.close();
        c.close();

这将引发以下异常:

Exception in thread "main" java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: ST_Y)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.throwex(DB.java:868)
    at org.sqlite.core.NativeDB.prepare(Native Method)
    at org.sqlite.core.DB.prepare(DB.java:211)
    at org.sqlite.jdbc3.JDBC3Statement.executeQuery(JDBC3Statement.java:81)
    at com.company.Test.main(Test.java:77)

我迷路了.看来我需要加载扩展程序才能使其正常工作.这不包括在JDBC连接器中吗? (我通过Maven拉了JDBC连接器.) 在哪里可以找到正确的扩展名? 是那些吗? https://www.gaia-gis.it/fossil/libspatialite/index 还是那些? http://www.gaia-gis.it/gaia-sins/index.html 我该如何使用它们?有人做过吗?

Edit 2: I'm lost. It seems that I need to load extensions to make it work. Is this not included in the JDBC connector? (I pulled the JDBC connector through Maven.) Where do I find the correct extensions? Are those the ones? https://www.gaia-gis.it/fossil/libspatialite/index Or those? http://www.gaia-gis.it/gaia-sins/index.html How do I use them? Has anybody done this before?

推荐答案

执行空间查询之前，您需要先加载spacespaceite模块

Before performing spatial queries you need to load the spatialite module doing

SELECT load_extension('mod_spatialite');

您可以在此处找到文档:动态加载SpatiaLite作为扩展模块.

You can find the documentation here: Dynamically loading SpatiaLite as an extension module.

请注意，要加载的模块必须位于系统路径(文档)上，并且路径分隔符必须为/.还应考虑到spacespaceite模块和Java必须兼容(32位或64位).

Note that the module to load must be on the system path (documentation) and the path separator must be /. Also take into account that the spatialite module and Java must be compatibles (both 32 or 64 bits).

这篇关于Sqlite:在Intellij中执行Spatialite函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！