<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi Sandro,</div>
<div> </div>
<div>that's really great to hear! Thank you for the feedback. Two questions:</div>
<div> </div>
<div>- Should I create one or better two separate tickets/pull requests (for each of the two proposed functions)?</div>
<div>- Do you know if it is possible to run the tests and build+view the documentation html pages directly in my postgis github fork? Otherwise I'd likely have to set up the missing pieces locally.</div>
<div> </div>
<div>Sam
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Mittwoch, 06. März 2024 um 11:05 Uhr<br/>
<b>Von:</b> "Sandro Santilli" <strk@kbt.io><br/>
<b>An:</b> gluser1357@gmx.de<br/>
<b>Cc:</b> postgis-devel@lists.osgeo.org<br/>
<b>Betreff:</b> Re: Proposal: st_remove_irrelevant_points_for_view() and st_remove_small_geometries()</div>
<div name="quoted-content">Hi gluser,<br/>
I think the functionality of removing segments not within a bounding<br/>
box is a valuable one and not only for rendering. Previous work in<br/>
this area resulted in the ST_Clip function but that function is adding<br/>
new vertices and thus may move segments in the view due to the numerical<br/>
grid being limited.<br/>
<br/>
In order to accept your functions into core they'll need to be:<br/>
<br/>
1. Properly documented (including corner cases)<br/>
2. Fully tested (including corner cases)<br/>
<br/>
I didn't look at the commits to tell if they are, but if you are<br/>
interested in bringing them in I suggest you file a ticket as the<br/>
first thing, so help set a milestone: <a href="https://trac.osgeo.org/postgis" target="_blank">https://trac.osgeo.org/postgis</a><br/>
And then file a pull request which makes it easier to review:<br/>
<a href="https://git.osgeo.org/gitea/postgis/postgis" target="_blank">https://git.osgeo.org/gitea/postgis/postgis</a><br/>
<br/>
The credentials are the same for doing both things, and if you don't<br/>
have them yet can be obtained from <a href="https://id.osgeo.org/ldap/create" target="_blank">https://id.osgeo.org/ldap/create</a><br/>
<br/>
Than you for your contribution !<br/>
<br/>
--strk;<br/>
<br/>
On Wed, Feb 28, 2024 at 10:35:08AM +0100, gluser1357@gmx.de wrote:<br/>
> Dear Postgis community,<br/>
><br/>
> I’d like to contribute two new simple and useful functions to postgis, offering functionality that we use for more than 10 years now as crucial part of an network-based GIS application:<br/>
><br/>
> 1. st_remove_irrelevant_points_for_view (the_geom, bbox)<br/>
> 2. st_remove_small_geometries (the_geom, dx, dy) and st_remove_small_geometries (the_geom, area)<br/>
><br/>
><br/>
> Key point for this GIS application is a fast, view-based rendering of relevant data objects (point, multilinestring, multipolyon) being stored in a postgis database. Constraints are:<br/>
><br/>
> - IO bandwidth between client, webserver and database is crucial. Geometry objects should be as small as possible in size when leaving the database and should contain only coordinates being relevant to the end user.<br/>
> - Geometries must be kept as objects to allow object-based user interactions and object-based caching. Pre-rendering as images in database or webserver is out of the question.<br/>
> - Viewport-based clipping of geometry objects is done by the client as part of the rendering process. The database has no need to do that, e. g. by using st_intersection().<br/>
><br/>
><br/>
> With this in mind, it turns out that the following three preprocessing steps are essential:<br/>
><br/>
> 1. Remove irrelevant points: This is what the first proposed new function, st_remove_irrelevant_points_for_view(), does: It removes all coordinates being irrelevant for rendering data within a given view. In contrast to st_intersection(), no new coordinates will be computed. Using a test dataset containing the state borders in Europe, preprocessing with st_remove_irrelevant_points_for_view() leads to the same rendering result as with st_intersection() but is up to 20 times faster which is advantageous for real-time applications.<br/>
><br/>
> 2. Remove small geometries: Often, small geometries (like the thousands of islands along the Norway coastline) make a map cluttered and harder to read at small scales. Also the rendering process will slow down noticeably. Removing those small geometries (that is, small exterior or interior rings of polygons, or small lines) is actually a quite simple operation, but as far as I can see, there is no simple postgis function to achive this goal, just more or less complex approaches using st_dump()/st_collect(), e. g. described in <a href="https://gis.stackexchange.com/questions/198987/how-can-i-remove-only-small-inner-rings-in-postgis" target="_blank">https://gis.stackexchange.com/questions/198987/how-can-i-remove-only-small-inner-rings-in-postgis</a>. Because of this I want to propose a second function, st_remove_small_geometries(). According to my measures this approach is up to 50-100 times faster than the tested complex one and thus advantageous for real-time applications.<br/>
><br/>
> 3. Reduce spatial resolution: This can already be effectively done with st_snaptogrid().<br/>
><br/>
><br/>
> I think that integrating the two proposed functions can help other people to significantly improve performance and simplify spatial queries. Besides, it would us to simplify the set-up for customers since we don't have to create and deliver a non-standard postgis build in future.<br/>
><br/>
> You can find my code here (two .c files, linked in Makefile.in and postgis.sql.in):<br/>
><br/>
> <a href="https://github.com/gluser1357/postgis-fork/tree/remove-small-and-irrelevant-coords" target="_blank">https://github.com/gluser1357/postgis-fork/tree/remove-small-and-irrelevant-coords</a><br/>
><br/>
><br/>
> Comments, suggestions, corrections and reviews are very welcome :-)<br/>
><br/>
> Of course, I'd also be happy to try to support with further explainations, tests and doc pages.<br/>
><br/>
> Sam<br/>
><br/>
><br/>
> ---<br/>
><br/>
> Some usage examples (requires a table containing colums id, name and the_geom):<br/>
><br/>
> Example:<br/>
> SELECT id,name,st_npoints(st_remove_irrelevant_points_for_view(the_geom, st_makeenvelope(5,30,20,60,4326))) FROM mytable<br/>
> WHERE the_geom && st_makeenvelope(5,30,20,60,4326)<br/>
> ORDER BY id,name<br/>
><br/>
> SELECT id,name,st_nrings(st_remove_small_geometries(the_geom,0.01)) FROM mytable<br/>
> WHERE the_geom && st_makeenvelope(5,30,20,60,4326)<br/>
> ORDER BY id,name<br/>
><br/>
<br/>
--<br/>
<br/>
Libre GIS consultant/developer<br/>
<a href="https://strk.kbt.io/services.html" target="_blank">https://strk.kbt.io/services.html</a></div>
</div>
</div>
</div></div></body></html>