Spatial index for XYZ geometries

[c...@margo.co]

Hi Sandro and all,

 

          I am dealing with an issue that may be rightfully without an easy solution, but I decided to ask in hopes there is an easy solution.

 

In a nutshell, I am working with two separate layers (links and nodes/points), where the Point layer is an XYZ layer and the Link Layer is XY only. The issue arises when I try to do searches using the spatial index. One example for getting the node_id for a node that is in the same position of the start of the linestring when such node exists is provided below.

 

create trigger if not exists network_updated_link_geo after update of geo on link
  begin
    update link
    set node_a = (
      select node_id
      from node
      where CastToXY(node.geo) = StartPoint(new.geo) and
      (node.rowid in (
          select rowid from SpatialIndex where f_table_name = 'node' and
          search_frame = StartPoint(new.geo)) or
        node.node = new.node_a))
    where link.rowid = new.rowid;

end;

 

 

The search_frame for the query (in bold) seems to be the problem, as that is on the plane and the nodes are not. I could not find any such reference in the documentation, so I wonder if there is a way to tell SpatiaLite to make a 2D search instead of 3D one.

 

Cheers,

Pedro

[mj10777]

 Assuming I understand your question correctly (can a XYZ geometry be used as input in search_frame?), the SpatialIndex itsself is XY, so only the XY values of search_frame will be used and any Z value ignored, so a XYZ can be used as input. Does this answer your main question?

Note: there is no need to compare node.geo and new.geo unless new.geo is within the BoundingBox of node.geo. So only when found in the SpatialIndex, should the CastToXY(node.geo) = StartPoint(new.geo) be done. So place it after the SpatialIndex (not before as it is now). It will also ve swifter, since it has less to do. As it is now, ONLY when both are the same, will the SpatialIndex be queried, which defeats the purpose. If they are the same, then it will ALLWAYS be found in the SpatialIndex.

(node.rowid in 

 (

  select rowid from SpatialIndex where 

  f_table_name = 'node' and

  search_frame = StartPoint(new.geo)

 ) OR

 node.node = new.node_a

) AND

( --- execute only if the previous condition is true [StartPoint within BoundingBox of node.geo]

 CastToXY(node.geo) = StartPoint(new.geo)

)

where link.rowid = new.rowid;

 

Cheers,

Pedro

[c...@margo.co]

Well… This is embarrassing, but the spatial index search was working perfectly, but I had issues elsewhere in the pipeline.

 

Thanks!

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/be757395-a0b7-4b6d-a089-46ec8a2df4f1n%40googlegroups.com.

[mj10777]

On Thursday, 15 September 2022 at 06:58:56 UTC+2 c...@margo.co wrote:

Well… This is embarrassing,

Don't worry about it, for we were the ones that caused this problem since our original samples were incorrect. I was only during the rewriting of the Cookbook 5 that this was noticed and all samples changed. But there are still code sample outthere that use the incorrect order.

 

but the spatial index search was working perfectly, but I had issues elsewhere in the pipeline.

It would. But the goal is to ignore everything that is out of range and execute the time costly comparisons for cases where it is likly true.

[a.fu...@lqt.it]

just for the sake of curiosity.

the R*Tree module of SQLite, the fundamental basis on which
the whole implementation of the Spatial Index relies, can
support up to a maximum af 5 dimensions.

few years ago for an experimental research project I've
personnally tested a 4-dimensions R*Tree (XYZ + Time)
and it worked perfectly.

bye Sandro

[Artur Krawczyk]

Hi all, 

I work with spatialite_gui 2.1.0-beta0 (on win10, Xeon) with a geopackage file. The first opening of the file goes without a problem - in the gpkg file I can change the values in the tables. However, after the work is finished and closed. And then after I reopen the gpkg file, there is a problem with the database. First, the information appears 

and then the spatialite_gui window with the object branch is frozen. Is it possible to fix it somehow?

Regards, 

Artur K, 

[a.fu...@lqt.it]

On Mon, 19 Sep 2022 21:27:53 +0200, Artur Krawczyk wrote:
> Hi all, 
>
> I work with spatialite_gui 2.1.0-beta0 (on win10, Xeon) with a
> geopackage file. The first opening of the file goes without a problem
> - in the gpkg file I can change the values in the tables. However,
> after the work is finished and closed. And then after I reopen the
> gpkg file, there is a problem with the database. First, the
> information appears 
>

> and then the spatialite_gui window with the object branch is frozen.
> Is it possible to fix it somehow?
>

Hi Artur,

Sorry, but I can't confirm this issue.

using the latest development version of the GUI tool to open
a GeoPackage, modifying some values and then closing and
reopening the connection works smoothly.

note well: the support of the GUI for the GeoPackage is
very limited, and is expected to support just basic
operations.

many advanced functions of SpatiaLite can correctly
work only if the underlying DB has a genuine SpatiaLite
layout; working on a GPKS could easily produce
unpredictable issues.

said in other words; the only way for safely working
on GPKG is as follows:

a) create a genuine SpatiaLite DB
b) import into it the GPKG data
c) apply any processing, editing, transformation
and alike
d) and finally export anything into a GPKG

directly working on GPKG is never an encouraged
option and can cause problems.

bye Sandro

[Artur Krawczyk]

Hi Sandro and all,

Thank you for the hint, in fact it is better to work normally in spatialite and only then export the database itself. 

Best regards, 

Artur K ,

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/bc9cc2ce1129573dd9ac95395e71a495%40lqt.it.