Would it be possible a new function SetSeveralPoints?

[Maurizio Trevisani]

Would it be possible a new funcion

SetSeveralPoints( line LineString , table String , column String ,
position String) : Linestring

that updates a linestring substituting vertexes using a table having a
Point geometry column and an Integer position column such that the
result should be as iterating several

SetPoint( line LineString , position Integer , point Point )

?

If I find vertexes of a linestring which should snap to specific
points, I would like a function allowing to update all of such
vertexes iterating the positions and the new geometries cointained in
that table.

Such a function would allow to SNAP a linestring to some points having
control of which vertexes are modified and which not (not depending on
a snap distance tolerance to be used to control where snapping is
performed).

Evidently geometries of table are not null, are of the same srid and
positions identify existing vertices of the original linestring.


Thank you,
Maurizio

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

Hi Maurizio,

a practical example just to check better your request:

1. we'll suppose a Linestring geometry having exactly 10 vertices

2. then we'll imagine a spatial table like the following one:

CREATE TABLE my_points (
position INTEGER PRIMARY KEY);
SELECT AddGeometryColumn('my_points', 'geom', someSrid, 'POINT',
'XY');

and we'll suppose that this table has been populated in this way:

INSERT INTO my_points VALUES( 3, MakePoint(x1, y1, srid));
INSERT INTO my_points VALUES( 7, MakePoint(x2, y2, srid));
INSERT INTO my_points VALUES( 12, MakePoint(x2, y3, srid));
INSERT INTO my_points VALUES( 37, MakePoint(x4, y4, srid));

3. the new function should return a new Linestring in which:

3a, the vertices corresponding to indices 0,1,2,4,5,6,8,9
will be the same of the input Linestring

3b. while the vertices corresponding to indices 3 and 7
will be replaced by the Points read from table "my_points"
and such substitution will unconditionally apply without
any check about the distance radius between the old
and the new Point.

3c. the two rows of "my_points" corresponding to positions
12 and 37 will be silently ignored because their indices
are outside the Linestring's range.

is all this correct ? I've correctly understood you request ?


preliminary checks to be performed by the new function:

a. if the input geometry is not a Linestring an exception
will be raised.

b. if the reference table does not exist an exception
will be raised.

c. if the reference table does not containt the requested
geometry column an exception will be raised.

d. if such geometry column is not of the POINT type,
or has not the same dimensions and/or the same SRID
of the Linestring an exception will be raised.

e. and finally if the above table contains two or
more rows declaring the same "position index"
an exception will be raised.

any further consideration ?

bye Sandro

[Maurizio Trevisani]

Perfect!

Since the table will be produced comparing linestring's vertexes against surrounding nearest edges, It won't have points not corresponding to original vertexes, but the implementation you suggest Is more robust. I wish to reproduce the not correctly working topo_snap function.

Thanks a lot!

Bye,

Maurizio

--
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/6c319a57265f4dbacea6ef4e0225e7d0%40lqt.it.

[Andrea Peri]

Hi Sandro and Maurizio,

Interesting approach, but i guess could be more errore prone.

It is dependent from the correct count of vertex of a line.

Not always is simple to do this.

Also more often a linestring could have thousand of vertexs.

And be aware to closed lines where first and last vertex are the same.

Also this function could trasform a simply line in a complex one.

But however the more complexity could be in the correct count of the vertex to move.

Could a function that build the description table be an help in this ?

A 

Il dom 4 lug 2021, 12:32 <a.fu...@lqt.it> ha scritto:

[Andrea Peri]

I'm not sure how easy could be for you retriece the vertex number for any point you need move.

I remember in the topology suite there is a function to retrieve the inode of a point. 

Perhaps could be need an analogue function to retrieve the vertex number of the more near vertex of the line to a given couple coordinate x,y.

A.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/CABjWwHx7F4p%2B8Uw%2BTaivZUPCOOy-kHx9tihH4cwQVSXw4mi69A%40mail.gmail.com.

[Maurizio Trevisani]

drop table if exists numeropunti;
CREATE TABLE IF NOT EXISTS numeropunti (np integer primary key);
create unique index if not exists temp.idx_numeropunti_k on numeropunti(np);
-- numeropunti contiene il massimo numero di punti tra tutte le
linestrings di linestringtobeloaded (serve per il successivo loop per
l'analisi dei singoli vertici)
delete from numeropunti;
insert or ignore into numeropunti(np) select np from (WITH RECURSIVE
next(n) AS (SELECT 1 UNION ALL select n+1 from next where n <= (select
ST_NumPoints(geometry)+1 from linestringtobeloaded)) select n as np
from next) as d;

-- scan of vertexes of the linestrings

select zz.pk_linestringtobeloaded as pk_linestringtobeloaded, b.np as
vertexnum, st_pointn(zz.geometry, b.np)
FROM linestringtobeloaded as zz join numeropunti as b on
(st_numpoints(zz.geometry) > b.np and b.np > 1);


I treat first and last points as NODES, which I snap with the
SetStartPoint and SetEndPoint functions.

In effects maybe the table I produce is relative to more linestrings,
let's listen Sandro if could be added another string field
corresponding to the primary key of the linestring to be updated, when
the "linestringtobeloaded" contains more than one record.
Or if there are other suggestions.

So it would be something like:

SetSeveralPoints( line LineString , table String , column String ,

tablepk string, position String) : Linestring

where line is the linestring to be updated, "table" is the table
consisting of 3 fields ("column" geometry of point type, "tablepk"
integer corresponding to the rowid of the line being updated (IS IT
POSSIBLE????), "position" integer corresponding to the vertex to be
substituted with the new geometry point).

If there was a scripting language, I would have written something like this:

FOR K = 1 TO (SELECT COUNT(*) FROM linestringtobeloaded)
FOR J = 1 TO (SELECT ST_NumPoints(geometry) FROM linestringtobeloaded
WHERE rowid = (SELECT tablepk FROM table WHERE rowid = K))

UPDATE linestringtobeloaded SET geometry = (SELECT SetPoint(
linestringtobeloaded.geometry, table.position -1, table.column) FROM
table AS table WHERE table.tablepk = K and linestringtobeloaded.rowid
= K);

NEXT J;
NEXT K;


Sorry if it seems confused.

Thanks,
bye,
Maurizio

>> https://groups.google.com/d/msgid/spatialite-users/CABjWwHx7F4p%2B8Uw%2BTaivZUPCOOy-kHx9tihH4cwQVSXw4mi69A%40mail.gmail.com
>> <https://groups.google.com/d/msgid/spatialite-users/CABjWwHx7F4p%2B8Uw%2BTaivZUPCOOy-kHx9tihH4cwQVSXw4mi69A%40mail.gmail.com?utm_medium=email&utm_source=footer>
>> .

>>
>
> --
> 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/CABqTJk8osj7t8VQTLnb9b%3DgZrVEoYX0tHhtb-eRKQ6Ryv_Z2zw%40mail.gmail.com.
>

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

On Sun, 4 Jul 2021 14:10:52 +0200, Andrea Peri wrote:
> I'm not sure how easy could be for you retriece the vertex number for
> any point you need move.
>

Hi Andrea,

we already support SetPoint() and RemovePoint(), two SQL Functions
respectively allowing to change or to suppress a Linestring's vertex
indentified by its relative position (index).

the new Function suggested by Maurizio simply seems to be a glorified
version of SetPoint(), and as such does not adds any further
complexity.

> I remember in the topology suite there is a function to retrieve the
> inode of a point. 
> Perhaps could be need an analogue function to retrieve the vertex
> number of the more near vertex of the line to a given couple
> coordinate x,y.
>

we already support ST_ClosestPoint(geom1, geom2), an SQL Function
returrning a POINT geometry corresponding to the vertex of geom1
closest to geom2.

we could easily add an hipothetic GetPointIndex(line, point)
returning the index corresponding to the vertex of "line"
closest to "point".

bye Sandro

[Andrea Peri]

Ok. I guess it could be useful to find the right vertex index.

Please paybattention at the case of a cross line with two vertex in the crossing point.

The function could return the wrong index. 

A.

--
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/1ea5f791441f532e1be0dd2904b8dc20%40lqt.it.

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

On Sun, 4 Jul 2021 14:55:48 +0200, Maurizio Trevisani wrote:
> In effects maybe the table I produce is relative to more linestrings,
> let's listen Sandro if could be added another string field
> corresponding to the primary key of the linestring to be updated,
> when
> the "linestringtobeloaded" contains more than one record.
> Or if there are other suggestions.
>

Maurizio,

seems to be a smart solution.

CREATE TABLE my_points (
pk_uid INTEGER NOT NULL,
idx_no INTEGER NOT NULL,
CONSTRAINT pk_mypts PRIMARY KEY (pk_uid, idx_no));
SELECT AddGeometryColumn('my_points', 'geom', someSRID, 'POINT', 'XY');

an helper table created this way will make simple and easy
supporting the following Function:

SetMultiplePoints(
line LineString , -- the input Linestring
pk_value Integer , -- the PK value corresponding to "line"
table_name String , -- name of the helper table (always assumed on
MAIN)
pt_name String , -- name of the column containing POINTs
pk_name String, -- name of the column containing PK values
pos_name String -- name of the column containing index/position
) : Linestring -- return the edited Linestring or raise an
exception

implementation details: (ignoring any validation check)
the new Function will be internally based on a SQL query
like the following one:

SELECT @pos_name@, @pt_column@
FROM MAIN.@table_name@
WHERE @pk_name@ = @pk_value@
ORDER BY @pos_name@;

bye Sandro

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

On Mon, 5 Jul 2021 08:24:52 +0200, Andrea Peri wrote:
> Ok. I guess it could be useful to find the right vertex index.
> Please paybattention at the case of a cross line with two vertex in
> the crossing point.
>
> The function could return the wrong index. 
>

Andrea,

this is a not so common corner case, but a dangerous one.

I foresee two alternative solutions:

======================= solution A)

GetPointIndex(line, point) will return:
* NULL on wrong arguments
* -1 if two (or more) occurences of the same vertex
were found (repeated points, self intersections).


======================= solution B)

adopting a more sophisticated signature as:

GetPointIndex(ine, point [ , check_multiple] );

the optional <check_multiple> argument will
be set to 0 (FALSE) by default.

* when <check_multiple> is FALSE the function
will always return the index of the first
vertex found ignoring any eventual other
positive match

* when <check_multiple> is TRUE the function
will return -1 if the closest vertex occurs
more than a single time.

bye Sandro

[Andrea Peri]

Hi Sandro,

Te be solution seem more powerful,  

However sometimes the function could help to resolve the case of more vertex repeated.

Infact Two or more vertexes at the same coordinates, could not be necessary a problem for this kind of function.

The only problem  is if the repeated vertexes are not all sequentially ordered.

An example:

Index 

0                                     1,2,3,4                                  5

*--------------------------------*------------------------------------*

In this example 1,2,3,4 are four vertex all in the same coordinate point.

This is good for the function because allow to move or delete one by one of them to correct the right situation.

infact the four vertex not necessary are 1 good and 3 bad to remove, but they could be only at wrong coordinate. So a move of them is the good solution. The function could help to resolve this case.

Instead in this other example:

0                                     1,2,3,4 ,8                               5

*--------------------------------*------------------------------------*

                                         \                                         /

                                        7 *------------------------------*6

In the middle coordinate point there is five vertex, but the last is not sequentially continuos to the other four.

This is more complex because the user could not know what is the right vertex to stay and what vertex move or delete.

So this necessary should return -1 because otherwise it is ambiguos.

--
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/3dd6e91464c29dd7aa54b85ab88ad911%40lqt.it.

--

-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------

[Maurizio Trevisani]

I remove all repeaed vertexes!

> https://groups.google.com/d/msgid/spatialite-users/CABqTJk8u_yk_MJTjDBuDnUnER7N%2BsKDQwcB4MeyZEA68%2BJ001Q%40mail.gmail.com.
>

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

On Mon, 5 Jul 2021 10:57:58 +0200, Maurizio Trevisani wrote:
> I remove all repeaed vertexes!
>

and I easily understand that your Linestrings will never have
self-intersecting Points because you are implicitly assuming that
all them are candidate Edges of some Topology.

Nontheless the precautions suggested by Andrea have a sound logic
in a most generic approach.

bye Sandro

[Maurizio Trevisani]

Perfect.
Thanks,
Maurizio

> --
> 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/03dd86f0c6cff5a56daeaf2cfa5c1825%40lqt.it.
>