Hey, just managed to create one. It's as simple as i could create it.
Please follow the steps to reproduce the problem.
-- create the sequence, table and the spatial index
create sequence
SYSTEM_SEQUENCE_53100407_5C3D_4E15_B6A2_2C69FE3841F3_DUMMY_11 start with 0;
CREATE TABLE PUBLIC.DUMMY_11 (
"fid" INTEGER NOT NULL DEFAULT (NEXT VALUE FOR
PUBLIC.SYSTEM_SEQUENCE_53100407_5C3D_4E15_B6A2_2C69FE3841F3_DUMMY_11),
C01 INTEGER,
C02 INTEGER,
C03 VARCHAR(100),
C04 VARCHAR(100),
C05 INTEGER,
C06 VARCHAR(100),
C07 VARCHAR(100),
C08 VARCHAR(100),
C09 INTEGER,
C10 DOUBLE,
C11 VARCHAR(100),
C12 INTEGER,
C13 VARCHAR(100),
C14 INTEGER,
C15 INTEGER,
C16 INTEGER,
C17 INTEGER,
C18 INTEGER,
C19 TIMESTAMP,
C20 INTEGER,
C21 INTEGER,
C22 DOUBLE,
C23 INTEGER,
C24 INTEGER,
C25 INTEGER,
C26 INTEGER,
C27 INTEGER,
C28 VARCHAR(100),
C29 INTEGER,
C30 INTEGER,
C31 INTEGER,
GEOM GEOMETRY,
C32 INTEGER,
C33 INTEGER,
C34 VARCHAR(100),
C35 INTEGER,
C36 VARCHAR(100),
C37 INTEGER,
C38 VARCHAR(100),
C39 VARCHAR(100),
C40 VARCHAR(100),
C41 VARCHAR(100),
C42 INTEGER,
C43 INTEGER,
C44 VARCHAR(100),
C45 VARCHAR(100),
C46 INTEGER,
C47 VARCHAR(100),
CONSTRAINT CONSTRAINT_DUMMY_11 PRIMARY KEY ("fid")
);
CREATE SPATIAL INDEX PUBLIC_DUMMY_11_SPATIAL_INDEX on
PUBLIC.DUMMY_11("GEOM");
-- insert a dummy record with null geometry
insert into PUBLIC.DUMMY_11(C01, geom) values(-1,null);
-- i also tested with: insert into PUBLIC.DUMMY_10(C01, geom)
values(-1,'POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))');
-- same result as with null geometry, seems that the update does not update
the spatial index
-- select returns one record correctly
select C01, GEOM from DUMMY_11;
-- update the record with the valid geometry
update PUBLIC.DUMMY_11 set geom = 'POLYGON ((401492.78 4884493.95,
401491.68 4884520.74, 401502.41 4884505.58, 401492.78 4884493.95))' where
C01 = -1;
-- check the record, the geometry has been set correctly
select C01, GEOM from DUMMY_11;
-- now make a spatial query, NO RESULTS, notice that the query polygon is
the same as the updated one
select C01, GEOM from DUMMY_11 where "GEOM" && 'POLYGON ((401492.78
4884493.95, 401491.68 4884520.74, 401502.41 4884505.58, 401492.78
4884493.95))';
-- drop the spatial index
DROP INDEX PUBLIC_DUMMY_11_SPATIAL_INDEX;
-- make a spatial query, we get the result (no index used)
select C01, GEOM from DUMMY_11 where "GEOM" && 'POLYGON ((401492.78
4884493.95, 401491.68 4884520.74, 401502.41 4884505.58, 401492.78
4884493.95))';
-- now recreate the spatial index
CREATE SPATIAL INDEX PUBLIC_DUMMY_11_SPATIAL_INDEX on
PUBLIC.DUMMY_11("GEOM");
-- make a spatial query, again we get the correct result (spatial index
used)
select C01, GEOM from DUMMY_11 where "GEOM" && 'POLYGON ((401492.78
4884493.95, 401491.68 4884520.74, 401502.41 4884505.58, 401492.78
4884493.95))';
Dne ponedeljek, 25. april 2016 20.49.02 UTC+2 je oseba Thomas Mueller
napisala:
>
> Hi,
>
> Could you post a complete, reproducible, simple test case please, if
> possible just using SQL statements (a SQL script)?
>
> Regards,
> Thomas
>
>
> On Mon, Apr 25, 2016 at 4:37 PM, Blaž Repnik <[email protected]
> <javascript:>> wrote:
>
>> Another thing, these records don't show up only when using the spatial
>> query (or a mixture of spatial and attribute). If i use only the attribute
>> query, i get all the rows.
>>
>>
>>
>> Dne ponedeljek, 25. april 2016 16.35.11 UTC+2 je oseba Blaž Repnik
>> napisala:
>>
>>> 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] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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.
