Trimming polygons that extend into the sea

[ckgoo...@gmail.com]

Hello.  I think this question is really just a general ‘how to write an update query’ question rather than being specific to Spatialite but I would like some advice on how to debug it. 

I have a table of polygons in spt_PPolygons representing countries but some of these polygons extend into the sea a bit.

So I want to find the intersection with land polygons in spt_LandPolygons which follow the coastline precisely.

So I wrote:

<U P D A T > E spt_polygons

 SET p.polygon = ST_Intersection( p.polygon, l.lpolygon )

FROM spt_Polygons AS p

 JOIN spt_LandPolygons AS l

 WHERE  ST_Contains( l.lpolygon, ST_Centroid( p.polygon ) );

 

[put the first bit in a funny way to stop Google blocking the email]

But the Spatialite CLI just gives me “Syntax error near ‘.’”. 

How can I get richer information on what the error is through the CLI?

And is my approach correct for what I want to do?

Best, Chris

 

[Mike Castle]

Country borders do generally extend into the surrounding waters a bit
(6 to 12 nautical miles/11 to 22 km).

Coastlines are both fractal and changing. The more you zoom in, the
more detail you have to deal with. Also, tides change where the
coastline is a couple of times a day. So, you may not really gain all
that much by changing the countries' borders.

Anyway, does the intersection work as a SELECT first?

mrc

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

On Tue, 15 Oct 2024 22:03:36 +0100, ckgoo...@gmail.com wrote:
> Hello. I think this question is really just a general 'how to write
> an
> update query' question rather than being specific to Spatialite but I
> would like some advice on how to debug it.
>

> ------------------- snip ---------------------

>
> How can I get richer information on what the error is through the
> CLI?
>

Sorry, there's no way: the error message you read is all that comes
directly from SQLite; the SQL code parser encountered something that
doesn't add up in the syntax, but it's up to you to figure out what
and where.

in this specific case the violation of SQL syntax is quite evident.
you are using a JOIN, but the expected syntax for UPDATE exclusively
supports FROM, you absolutely cannot use a JOIN in that context.

> And is my approach correct for what I want to do?
>

no, your approach is wrong because SQL does not support operations
like the one you would like to do.
you have to take a much more indirect approach.

A) the easy way (my favorite solution in all these cases)
you create and populate a new derived table: when it's ready
you are free to drop the old table that is no longer needed.
something like this:

CREATE TABLE new_table (....);
INSERT INTO new_table
SELECT ....
FROM ...
JOIN ...
WHERE ....;


B) a decidedly more creative approach.
using SQL to autogenerate a SQL script ;-)
someting like this:

SELECT 'UPDATE my_table SET fld1 = ' ||
fnct(a,fldA, b.fldB) || ' WHERE idx = ' ||
a.idx || ';'
FROM my_table AS a
JOIN other_table AS b ON (...);

Note: the || operator means "concatenation"
and allows you to build very complex
text strings chaining all together several
smaller strings.

in the end you'ill get a result set of many rows
each of which is an atomic UPDATE statement of a
single row of the target table.

at this point you simply need to save this resultset
in a text file and then run it as a SQL script.
possibly after enclosing it between a BEGIN and a COMMIT.

It may seem very complicated, but you'll see that if you
try you'll discover that it's actually very simple (and
also quite fun) ;-)

lesson to learn: in SQL the shortest way from A to B is
almost never a straight line.
much more often it's an artistic arabesque ;-)

bye Sandro

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

This is a very wise comment.

Golden rule: never venture into complex operations without first having
checked in the most scrupulous way that every single elementary step
works as expected.

in the event that something goes wrong it is the only way to hope to
identify and correct any errors.

bye Sandro

[ckgoo...@gmail.com]

Hi Mike, Sandro,

I want to trim my polygons which include maritime borders because my application uses the closest point to a polygon to tell the user how far they are from the polygon. This figure will be wrong by the distances you quoted because of this maritime border around the polygons. Using the high water tide mark is far better.

As an example, as my user approaches Grenada from the west, the closest point function may return 2km but actually they are 22km from the actual land of Grenada which is misleading.

And Sandro, I like your idea of constructing lots of statements. However, I am struggling to walk let alone run. This select statement behaves oddly from the CLI as it displays a sensible answer immediately but without returning me to the spatialite prompt. This suggests the query is still running but I still don't get a prompt back after 10 minutes.

SELECT p.id, ST_Area ( ST_Intersection( l.lpolygon, p.polygon ) )

FROM spt_Polygons AS p
JOIN spt_LandPolygons AS l

WHERE p.id=6666
AND ST_Contains( l.lpolygon, ST_Centroid( p.polygon ));

Note that 6666 happens to be the id in my database representing the UK mainland polygon.
There are 778,000 land polygons and 1400 plain polygons. So is my join statement not specific enough?
Many thanks for any guidance you can give.
Best, Chris

--
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/CA%2Bt9iMzCmyXnSTkpxcLnueP14JmwLV49TdXsC9UjSfGB59XUyQ%40mail.gmail.com.

[ckgoo...@gmail.com]

I wrote:

There are 778,000 land polygons and 1400 plain polygons.  So is my join statement not specific enough?

 

Think I need to add a filter in my where clause to exclude polygons that don’t overlap and use the spatial index to do this.

Best, Chris

 

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

excellent: you got there on your own ;-)

bye Sandro