Ok, i finally managed to create a working sample to demonstrate my problem:
drop table if exists PUBLIC.DUMMY_12;
CREATE TABLE PUBLIC.DUMMY_12 (
"fid" serial,
Z_ID INTEGER,
GEOM GEOMETRY,
CONSTRAINT CONSTRAINT_DUMMY_12 PRIMARY KEY ("fid")
);
CREATE INDEX PRIMARY_KEY_DUMMY_12 ON PUBLIC.DUMMY_12 ("fid");
CREATE spatial INDEX PUBLIC_DUMMY_12_SPATIAL_INDEX_ ON PUBLIC.DUMMY_12
(GEOM);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (123,3125163,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (124,3125164,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (125,3125173,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (126,3125174,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (127,3125175,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (128,3125176,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (129,3125177,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (130,3125178,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (131,3125179,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (132,3125180,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (133,3125335,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (134,3125336,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (135,3125165,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (136,3125337,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (137,3125338,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (138,3125339,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (139,3125340,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (140,3125341,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (141,3125342,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (142,3125343,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (143,3125344,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (144,3125345,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (145,3125346,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (146,3125166,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (147,3125347,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (148,3125348,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (149,3125349,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (150,3125350,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (151,3125351,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (152,3125352,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (153,3125353,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (154,3125354,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (155,3125355,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (156,3125356,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (157,3125167,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (158,3125357,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (159,3125358,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (160,3125359,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (161,3125360,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (162,3125361,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (163,3125362,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (164,3125363,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (165,3125364,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (166,3125365,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (167,3125366,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (168,3125168,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (169,3125367,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (170,3125368,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (171,3125369,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (172,3125370,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (173,3125169,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (174,3125170,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (175,3125171,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (176,3125172,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (177,-2,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (178,-1,NULL);
INSERT INTO PUBLIC.DUMMY_12 ("fid",Z_ID,GEOM) VALUES (179,-1,NULL);
-- returns ZERO results
select z_id from PUBLIC.DUMMY_12;
drop INDEX PUBLIC_DUMMY_12_SPATIAL_INDEX_;
-- returns 57 (correct) results
select z_id from PUBLIC.DUMMY_12;
Dne torek, 26. april 2016 13.37.41 UTC+2 je oseba Nicolas Fortin (OrbisGIS)
napisala:
>
> I'm sorry but I can't fix an issue that I can't reproduce on my computer.
>
> Le mardi 26 avril 2016 12:26:31 UTC+2, Blaž Repnik a écrit :
>>
>> I updated my local code with:
>>
>> if (p.isLeaf()) { for (int i = 0; i < p.getKeyCount(); i++) { if (keyType
>> .equals(p.getKey(i), key)) { + p.setKey(i, key); return p.setValue(i,
>> value); } }
>> while it does fix the problem with demo sql script, it does not fix the
>> problem with my actual table. It works fine without the spatial index, but
>> not with it.
>>
>>
>>
>> Dne torek, 26. april 2016 11.12.43 UTC+2 je oseba Nicolas Fortin
>> (OrbisGIS) napisala:
>>>
>>> Hi,
>>>
>>> The issue has been created and the pull request is done. Waiting for the
>>> check and the possible merge on the h2 team side:
>>>
>>> https://github.com/h2database/h2database/pull/267
>>>
>>> Best regards,
>>>
>>> Nicolas Fortin,
>>>
>>> Développeur d’applications - AI
>>> Laboratoire d’Acoustique Environnementale (LAE)
>>> Ifsttar Nantes, Route de Bouaye, CS 4, 44344 Bouguenais Cedex
>>>
>>> Activités du Laboratoire d’Acoustique Environnementale :
>>> www.lae.ifsttar.fr
>>> Un logiciel open-source pour l’acoustique des salles : I-Simpa (
>>> i-simpa.ifsttar.fr)
>>> Un logiciel open-source pour des études d’impact en milieu extérieur :
>>> NoiseM@p (noisemap.orbisgis.org)
>>>
>>>
>>>
>>>
>>> Le mardi 26 avril 2016 11:02:56 UTC+2, Blaž Repnik a écrit :
>>>>
>>>> Excellent. Any info about when the patch will be commited? Thanks!
>>>>
>>>>
>>>> Dne torek, 26. april 2016 09.09.14 UTC+2 je oseba Nicolas Fortin
>>>> (OrbisGIS) napisala:
>>>>>
>>>>> Hi,
>>>>>
>>>>> Yes it seems that update on null geometry does not update the index.
>>>>> Using the following query
>>>>>
>>>>> drop table if exists DUMMY_11;CREATE TABLE PUBLIC.DUMMY_11 (fid serial,
>>>>> GEOM GEOMETRY);CREATE SPATIAL INDEX PUBLIC_DUMMY_11_SPATIAL_INDEX on
>>>>> PUBLIC.DUMMY_11(GEOM);insert into PUBLIC.DUMMY_11(geom)
>>>>> values(null);update PUBLIC.DUMMY_11 set geom = 'POLYGON((1 1,5 1,5 5,1
>>>>> 5,1 1))';select fid, GEOM from DUMMY_11 where GEOM && 'POLYGON((1 1,5
>>>>> 1,5 5,1 5,1 1))';
>>>>>
>>>>> I will add this on unit test of h2 and test it.
>>>>>
>>>>> Thank you for the report.
>>>>>
>>>>> Best regards,
>>>>>
>>>>> Nicolas Fortin,
>>>>>
>>>>> Développeur d’applications - AI
>>>>> Laboratoire d’Acoustique Environnementale (LAE)
>>>>> Ifsttar Nantes, Route de Bouaye, CS 4, 44344 Bouguenais Cedex
>>>>>
>>>>> Activités du Laboratoire d’Acoustique Environnementale :
>>>>> www.lae.ifsttar.fr
>>>>> Un logiciel open-source pour l’acoustique des salles : I-Simpa (
>>>>> i-simpa.ifsttar.fr)
>>>>> Un logiciel open-source pour des études d’impact en milieu extérieur :
>>>>> NoiseM@p (noisemap.orbisgis.org)
>>>>>
>>>>> Le lundi 25 avril 2016 16:35:11 UTC+2, Blaž Repnik a écrit :
>>>>>
>>>>> Hey!
>>>>>>
>>>>>> I have a table into which i insert records with polygonal geometry.
>>>>>> And the problem is that some records do not show in the resultset when
>>>>>> using the spatial query (&& WKT_GEOM). I checked manually that the
>>>>>> geometry
>>>>>> of the inserted features overlaps (actually the are completely within)
>>>>>> the
>>>>>> query geometry. If i drop the spatial index and recreate it, i get the
>>>>>> missing records!
>>>>>>
>>>>>> I was not able to produce a working example on a simple table (my
>>>>>> table has about 30 columns). But this is what i do:
>>>>>>
>>>>>> First i insert the record with null geometry, then i update the
>>>>>> geometry column with "normal" geometry. So i guess the problem lies in
>>>>>> updating the spatial index? Any ideas?
>>>>>>
>>>>>> Also, i'm using the 191 version.
>>>>>>
>>>>>> Thanks!
>>>>>>
>>>>>
>>>>>
>>>>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
