intersection function doesn't work

[luis901101]

The intersection function doesn't return anything when intersecting two MULTILINESTRING, however the spatialite_gui application do returns. I've tested the same query with the same database and the intersection with android-spatialite doesn't show anything even when there are multiple rows as result. This is my query, which returns 18 rows, but the column asd which represents the intersections as texts, only shows nulls

select name, astext(intersection(geometry, (select geometry from ln_data where name like '%desagü%'))) as asd from ln_data where intersects(geometry, (select geometry from ln_data where name like '%desagü%'))

[sevar83]

Hi, I have some questions:

1. Does any geometry function work for you or none at all?
2. Would you try without the umlaut to see if it's some kind of Unicode error?

On Wed, Jun 20, 2018, 20:20 luis901101 notifications@github.com wrote:

The intersection function doesn't return anything when intersecting two MULTILINESTRING, however the spatialite_gui application do returns. I've tested the same query with the same database and the intersection with android-spatialite doesn't show anything even when there are multiple rows as result. This is my query, which returns 18 rows, but the column asd which represents the intersections as texts, only shows nulls

select name, astext(intersection(geometry, (select geometry from ln_data where name like '%desagü%'))) as asd from ln_data where intersects(geometry, (select geometry from ln_data where name like '%desagü%'))

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/5, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1XjRI2Fjsxdk0Un_sZA5zsrQL7Xsks5t-oR0gaJpZM4UvrIG .

[luis901101]

1. The intersects function does work, in fact the example query posted in the issue returns 18 rows.
2. "umlaut" ??? don't understand what you mean??? if you are referring to the like '%desagü%' I tested with other conditions and the intersection function still doesn't work.

[sevar83]

Would you please post some example data and code to try with in the weekend.

On Thu, Jun 21, 2018, 19:43 luis901101 notifications@github.com wrote:

1. The intersects function does work, in fact the example query posted in the issue returns 18 rows.
2. "umlaut" ??? don't understand what you mean??? if you are referring to the like '%desagü%' I tested with other conditions and the intersection function still doesn't work.

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/5#issuecomment-399169080, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1dgWPEoSo3KTUKovPd81xKV_d1IFks5t-81LgaJpZM4UvrIG .

[luis901101]

What I need to do is a geocoding search using spatialite, that's why I need the intersection function, I made a small spatialite database for testing purposes, cause my app database is really big. This database has an only table with 18 rows representing streets, each row has a name and a MULTILINESTRING geometry.

Link to download database: https://mega.nz/#!BY8QEI5Z!FSW6rQkBHQk8LMnNq6ph5TwCN7sUJy0a6tEsqmzfO00

Running this query using spatialite_gui app:

select name, geometry, astext(geometry) textgeometry, astext(intersection(geometry, (select geometry
from ln_data where id=62175
))) as textintersection
from ln_data
where 
intersects(geometry, (select geometry
from ln_data where id=62175
))

will return 18 rows which are the intersections of the street with id=62175 and the rest of the streets in database including the same. In the spatialite_gui app this query results is OK, 18 rows and each column has a non null value as expected, but using android_spatialite the 18 rows has a null value in the conlumn of textintersection.

This is the java code I used:

SQLiteDatabase spatialiteDatabase = SQLiteDatabase.openDatabase("/sdcard/map/test.sqlite", null, SQLiteDatabase.OPEN_READONLY);

Cursor cursor = spatialiteDatabase.rawQuery("select name, geometry, astext(geometry) textgeometry, astext(intersection(geometry, (select geometry\n" + "from ln_data where id=62175\n" + "))) as textintersection\n" + "from ln_data\n" + "where \n" + "intersects(geometry, (select geometry\n" + "from ln_data where id=62175\n" + "))\n", null);

if(cursor != null && cursor.moveToFirst())
{
    do
    {
        String name = cursor.getString(cursor.getColumnIndex("name"));
        byte[] geometry = cursor.getBlob(cursor.getColumnIndex("geometry"));
        String textgeometry = cursor.getString(cursor.getColumnIndex("textgeometry"));
        String textintersection = cursor.getString(cursor.getColumnIndex("textintersection"));
        Log.d("test", "name: " + name);
        Log.d("test", "geometry: " + geometry);
        Log.d("test", "textgeometry: " + textgeometry);
        Log.d("test", "textintersection: " + textintersection);
    }while(cursor.moveToNext());
}

spatialiteDatabase.close();

So this query uses 3 spatialite functions which are: astext(...), intersection(...) and intersects(...), and I have 18 rows as result, so the intersects(...) function is working OK, and the astext(...) function is working OK, but the intersection(...) function is not working at all.

I hope this is enough to reproduce the problem I've been facing.

Thanks in advance.

[sevar83]

I'll try to see it this weekend. BTW, did you compare the versions of Spatialite and GEOS used in the lib and GUI to see if the lib versions are older and probably some function is missing?

On Fri, Jun 22, 2018, 01:33 luis901101 notifications@github.com wrote:

What I need to do is a geocoding search using spatialite, that's why I need the intersection function, I made a small spatialite database for testing purposes, cause my app database is really big. This database has an only table with 18 rows representing streets, each row has a name and a MULTILINESTRING geometry.

Link to download database: https://mega.nz/#!BY8QEI5Z!FSW6rQkBHQk8LMnNq6ph5TwCN7sUJy0a6tEsqmzfO00

Running this query using spatialite_gui app:

select name, geometry, astext(geometry) textgeometry, astext(intersection(geometry, (select geometry from ln_data where id=62175 ))) as textintersection from ln_data where intersects(geometry, (select geometry from ln_data where id=62175 ))

will return 18 rows which are the intersections of the street with id=62175 and the rest of the streets in database including the same. In the spatialite_gui app this query results is OK, 18 rows and each column has a non null value as expected, but using android_spatialite the 18 rows has a null value in the conlumn of textintersection.

This is the java code I used:

SQLiteDatabase spatialiteDatabase = SQLiteDatabase.openDatabase("/sdcard/map/test.sqlite", null, SQLiteDatabase.OPEN_READONLY);

Cursor cursor = spatialiteDatabase.rawQuery("select name, geometry, astext(geometry) textgeometry, astext(intersection(geometry, (select geometry\n" + "from ln_data where id=62175\n" + "))) as textintersection\n" + "from ln_data\n" + "where \n" + "intersects(geometry, (select geometry\n" + "from ln_data where id=62175\n" + "))\n", null);

if(cursor != null && cursor.moveToFirst()) { do { String name = cursor.getString(cursor.getColumnIndex("name")); byte[] geometry = cursor.getBlob(cursor.getColumnIndex("geometry")); String textgeometry = cursor.getString(cursor.getColumnIndex("textgeometry")); String textintersection = cursor.getString(cursor.getColumnIndex("textintersection")); Log.d("test", "name: " + name); Log.d("test", "geometry: " + geometry); Log.d("test", "textgeometry: " + textgeometry); Log.d("test", "textintersection: " + textintersection); }while(cursor.moveToNext()); }

spatialiteDatabase.close();

So this query uses 3 spatialite functions which are: astext(...), intersection(...) and intersects(...), and I have 18 rows as result, so the intersects(...) function is working OK, and the astext(...) function is working OK, but the intersection(...) function is not working at all.

I hope this is enough to reproduce the problem I've been facing.

Thanks in advance.

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/5#issuecomment-399264106, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1cDHxURkpBoiwTiWY72qATot6zZAks5t_B8zgaJpZM4UvrIG .

[luis901101]

I checked and the Spatialite version is the same, the GEOS version it is different, it is the v3.5.0 different to the v3.4.2 included in the lib... But i don't think the problem is a missing function cause there is not any Sqlite Exception about any invalid function...

[sevar83]

Hi again, I confirm all of the above. I've manually tested 8 functions in Spatialite GUI and in an example app: Distance(), Equals(), IsValid(), Buffer(), GUnion(), ConvexHull(), Difference() and Intersection(). The last two (Difference and Intersection) functions are not working in the library but work in the GUI. I'm not yet clear about the reason for this problem because debugging the native side of the library is nearly impossible with Android Studio. I ran the Spatialite tests where there are test cases for the problematic functions and strangely - they pass. I need time to get to the bottom of this but I'm quite busy these days. If I find some time to set up the DS-5 debugger then finding the problem is a matter of minutes.

On Fri, 22 Jun 2018 at 21:15 luis901101 notifications@github.com wrote:

I checked and the Spatialite version is the same, the GEOS version it is different, it is the v3.5.0 different to the v3.4.2 included in the lib... But i don't think the problem is a missing function cause there is not any Sqlite Exception about any invalid function...

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/sevar83/android-spatialite/issues/5#issuecomment-399532658, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHS1aLSusgrWp3b7Z40NGgayuRJlVfIks5t_TQ4gaJpZM4UvrIG .

[luis901101]

Ok thanks, I will be aware.

[yuechaoyue666]

Is this problem solved?

[luis901101]

Is this problem solved?

No idea, it has been a long time

[yuechaoyue666]

Is it possible that intersects=1 but intersection=null?