[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

Update (just that this ) won't happen:

The issue could be resolved, the pull request 
https://github.com/h2database/h2database/pull/391/ 

 has 
been merged into master.

Adding/removing null spatial keys into MVSpatialIndex 

 is 
pointless, as they cannot be found based on a bounding box. Therefore, they 
are ignored. Thanks for watching.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

I have created a pull request on this 
issue: https://github.com/h2database/h2database/pull/391/
I would appreciate some feedback on this. 

Basically, the whole isNull() funtionality in *SpatialDataType *is not 
working at all. Maybe Null Geometries can be added (and retrieved with 
hacks) to an R-Tree this way, but nodes can never be split in a defined 
manner. So in a large tree with many null and not-null geometries, it is 
best to give at least some bogus bounding box in which entries can then be 
arranged.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

Any idea anyone? The example script I provided makes it quite easy to 
reproduce the error...

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

..forgot to mention, the *get* function takes place in MVRTreeMap.java.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

Hi,

Nicolas, thanks for the remark, and sorry for not providing an example. I 
do have the latest version so the handling of null geometries is up to 
date. One test function that has been added in the pull request you 
mentioned only checks for a small table (size one I believe). However, in 
that case, the spatial key is not used for lookup because the MVTree is 
small enough to contain only leaves.


Here is a small example that yields the error I mean. Note that the test 
script *passes for small table sizes.*

public void testIndexUpdateNullGeometry2() throws SQLException {
deleteDb("spatial");
Connection conn = getConnection(URL);
Statement stat = conn.createStatement();
stat.execute("drop table if exists DUMMY_11;");
stat.execute(
"CREATE TABLE PUBLIC.DUMMY_11 (fid serial,  GEOM GEOMETRY, Name 
varchar(255));");
stat.execute("CREATE SPATIAL INDEX PUBLIC_DUMMY_11_SPATIAL_INDEX on"
+ " PUBLIC.DUMMY_11(GEOM);");
for (int i = 0; i < 100; i++) {
stat.execute("insert into PUBLIC.DUMMY_11(geom) values(null);");
}
stat.execute("update PUBLIC.DUMMY_11 set Name='test' where fid = 5");
conn.close();
deleteDb("spatial");
}



The row fails to be found here, because with larger tables, the root Page 
is not a leaf any more, relying on the above mentioned contains relation...

protected Object get(Page p, Object key) {
if (!p.isLeaf()) {
List l = new ArrayList();
for (int i = 0; i < p.getKeyCount(); i++) {
Object o = get(p.getChildPage(i), key);
if (o != null) {
l.add(o);
}
}
for (int i = 0; i < p.getKeyCount(); i++) {
if (contains(p, i, key)) {
Object o = get(p.getChildPage(i), key);
if (o != null) {
return o;
}
}
}
} else
{
for (int i = 0; i < p.getKeyCount(); i++) {
if (keyType.equals(p.getKey(i), key)) {
return p.getValue(i);
}
}
}
return null;
}


kind regards,

Sven

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Nicolas Fortin (OrbisGIS)]

Hi,

A Pull request has been merged about the handling of null geometries:

https://github.com/h2database/h2database/pull/267/files

You don't provide any sql sample of the query and the expected result. So I 
don't understand your question.

Regards,

Le mardi 18 octobre 2016 08:40:35 UTC+2, Sven a écrit :
>
> I forgot another way to find such keys, maybe this is even safer. Couldn't 
> the *Page *be passed down to the *contains *function and perform a binary 
> search on the key in question?
>
> public boolean contains(Page p, int index, Object key) {
> SpatialKey a = (SpatialKey) p.getKey(index);
> SpatialKey b = (SpatialKey) key;
>
> if (a.isNull() || b.isNull()) {
> return p.binarySearch(a) >= 0; // <
> }
> for (int i = 0; i < dimensions; i++) {
> if (a.min(i) > b.min(i) || a.max(i) < b.max(i)) {
> return false;
> }
> }
> return true;
> }
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: Entries in a table containing Geometries cannot be found when the Geometry is empty ( Row not found when trying to delete from index )

[Sven]

I forgot another way to find such keys, maybe this is even safer. Couldn't 
the *Page *be passed down to the *contains *function and perform a binary 
search on the key in question?

public boolean contains(Page p, int index, Object key) {
SpatialKey a = (SpatialKey) p.getKey(index);
SpatialKey b = (SpatialKey) key;

if (a.isNull() || b.isNull()) {
return p.binarySearch(a) >= 0; // <
}
for (int i = 0; i < dimensions; i++) {
if (a.min(i) > b.min(i) || a.max(i) < b.max(i)) {
return false;
}
}
return true;
}



-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.