Anyone had any luck replicating the problem?
Dne sobota, 30. april 2016 15.11.06 UTC+2 je oseba Blaž Repnik napisala:
>
> 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.
