<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;background-color:#FFFFFF;font-family:Calibri,Arial,Helvetica,sans-serif;">
<p style="margin-bottom: 0in">Hi again</p>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-bottom: 0in">I did a simple test with <font color="#000000"><font face="Monaco, monospace"><font size="2" style="font-size: 11pt">ST_SnapToGrid(geo, 0.001) on a real dataset that based on farm property border in Norway and intersection of this
dataset <a href="http://www.skogoglandskap.no/en/subjects/ar5_land_resource_map/subject_view" title="http://www.skogoglandskap.no/en/subjects/ar5_land_resource_map/subject_view
Cmd+ Klikk eller trykk for å følge koblingen">
http://www.skogoglandskap.no/en/subjects/ar5_land_resource_map/subject_view</a> .
</font></font></font></p>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-bottom: 0in"><font color="#000000"><font face="Monaco, monospace"><font size="2" style="font-size: 11pt">The result was almost 100.000 invalid polygons of 2.6 millions polygons.</font></font></font></p>
<p style="margin-bottom: 0in"><font color="#000000"><font face="Monaco, monospace"><font size="2" style="font-size: 11pt">I have added SQL below so you can check the way I have used the postgis functions.</font></font></font></p>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-bottom: 0in"><font color="#000000"><font face="Monaco, monospace"><font size="2" style="font-size: 11pt">The server I tested on has this configuration
</font></font></font></p>
<p style="margin-bottom: 0in"><font size="2" style="font-size: 11pt"><font color="#000000"><font face="Menlo-Regular">postgis_full_version | POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08"
LIBXML="2.7.6" LIBJSON="0.11" TOPOLOGY RASTER</font></font></font></p>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-bottom: 0in"><font color="#000000"><font face="Menlo-Regular"><font size="3" style="font-size: 13pt"><font face="Monaco, monospace"><font size="2" style="font-size: 11pt">First i convert the data to meter and check that it was valid then I
did a st_snaptogrid and then I count who is valid who is not.</font></font></font></font></font></p>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> <span style="color: #011993">count</span>(*)
<span style="color: #931a68">as</span> valid_rows <span style="color: #931a68">from</span> (</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_isValid(org_geo), ST_area(org_geo), ST_isValid(snap_geo), ST_area(snap_geo) </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">from</span> (Select geo <span style="color: #931a68">
as</span> org_geo , ST_SnapToGrid(geo, 0.001) <span style="color: #931a68">as</span> snap_geo
<span style="color: #931a68">from</span> (</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_transform(geo,25835) <span style="color: #931a68">
as</span> geo <span style="color: #931a68">from</span> gaard_ar5_7kl_16_12_2012_flate</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t1</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t2 <span style="color: #931a68">where</span> st_isvalid( org_geo)
<span style="color: #931a68">and</span> st_isvalid( snap_geo) </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t3;</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
valid_rows </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; color: rgb(79, 145, 146);">
------------</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
2648499</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
(1 row)</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> <span style="color: #011993">count</span>(*)
<span style="color: #931a68">as</span> invalid_rows <span style="color: #931a68">
from</span> (</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_isValid(org_geo), ST_area(org_geo), ST_isValid(snap_geo), ST_area(snap_geo) </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">from</span> ( Select geo <span style="color: #931a68">
as</span> org_geo , ST_SnapToGrid(geo, 0.001) <span style="color: #931a68">as</span> snap_geo
<span style="color: #931a68">from</span> (</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_transform(geo,25835) <span style="color: #931a68">
as</span> geo <span style="color: #931a68">from</span> gaard_ar5_7kl_16_12_2012_flate</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t1</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t2 <span style="color: #931a68">where</span> st_isvalid( org_geo)
<span style="color: #931a68">and</span> st_isvalid( snap_geo) = <span style="color: #5329d2">
false</span></p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t3;</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
invalid_rows </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; color: rgb(79, 145, 146);">
--------------</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
94308</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
(1 row)</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; min-height: 15px;">
<br>
</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_isValid(org_geo), ST_area(org_geo), ST_isValid(snap_geo), ST_area(snap_geo), ST_isSimple(snap_geo) </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">from</span> ( Select geo <span style="color: #931a68">
as</span> org_geo , ST_SnapToGrid(geo, 0.001) <span style="color: #931a68">as</span> snap_geo
<span style="color: #931a68">from</span> (</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
<span style="color: #931a68">select</span> ST_transform(geo,25835) <span style="color: #931a68">
as</span> geo <span style="color: #931a68">from</span> gaard_ar5_7kl_16_12_2012_flate</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t1</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
) <span style="color: #931a68">as</span> t2 <span style="color: #931a68">where</span> st_isvalid( org_geo)
<span style="color: #931a68">and</span> st_isvalid( snap_geo) = <span style="color: #5329d2">
false</span></p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
limit 5;</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -712380.76390491705 6752484.312060223</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -712380.76390491705 6752484.312060223</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -693336.00845633878 6760997.9791273763</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -693336.00845633878 6760997.9791273763</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -685803.05900000001 6761659.9010000005</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -685803.05900000001 6761659.9010000005</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -686975.27643165435 6761818.4071217496</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -686975.27643165435 6761818.4071217496</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -685793.5384688942 6761692.5602394082</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
NOTICE: Self-intersection at <span style="color: #931a68">or</span> near point -685793.5384688942 6761692.5602394082</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
st_isvalid | st_area | st_isvalid | st_area | st_issimple </p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco; color: rgb(79, 145, 146);">
------------+------------------+------------+------------------+-------------</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
t | 12071.0927293049 | f | 12071.0915145167 | t</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
t | 1249.63589967907 | f | 1249.62388100091 | t</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
t | 2438.3765259538 | f | 2438.41668248867 | t</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
t | 8.73835355134684 | f | 8.72929450303413 | t</p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
t | 8.1236959106824 | f | 8.12728899896324 | t</p>
<p style="margin-bottom: 0in"></p>
<p style="margin-right: 0px; margin-left: 0px; font-size: 11px; line-height: normal; font-family: Monaco;">
(5 <span style="color: #931a68">rows</span>)</p>
<div><br>
</div>
<p style="margin-bottom: 0in"><br>
</p>
<p style="margin-bottom: 0in"><font color="#232323"><font face="CourierNewPSMT, monospace"><font size="2" style="font-size: 11pt">Lars</font></font></font></p>
<p></p>
<p style="margin-bottom: 0in"><br>
</p>
<br>
<br>
<div style="color: rgb(0, 0, 0);">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>Fra:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> på vegne av Willy-Bas Loos <willybas@gmail.com><br>
<b>Sendt:</b> 14. september 2016 12:52<br>
<b>Til:</b> PostGIS Users Discussion<br>
<b>Emne:</b> Re: [postgis-users] fuzzy tolerance</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div class="gmail_quote">On Wed, Sep 14, 2016 at 11:27 AM, Stefan Keller <span dir="ltr">
<<a target="_blank" href="mailto:sfkeller@gmail.com">sfkeller@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204,204,204); padding-left:1ex">
There is at least SnapToGrid:<br>
# select ST_SnapToGrid((ST_DumpPoints(<wbr>mypoly.geom)).geom, 0.1) from mypoly;<br>
<br>
:Stefan<br clear="all">
</blockquote>
</div>
<br>
Hm, i considered that before, but now i can't say why i discarded the thought.<br>
</div>
One thing is that the unit for snaptogrid is degrees for WGS84, so for world data that would pose a problem: you're not using a uniform grid to snap to.<br>
</div>
But in other coordinate systems, and if the algorithm is fast enough, one could use this in a trigger and always store geometries snapped to a 10cm grid. That would be precise enough for our data.<br>
</div>
It is easy to understand what is happening too, so that is an advantage.<br>
</div>
<div>BTW i tested and saw that it's not necessary to dump the points, you can snap the whole polygon.<br>
</div>
<br>
Any words of warning about using a trigger and storing the data on a 10 cm grid like i suggest?<br>
<br>
</div>
Cheers,<br>
<br>
<br>
<div class="gmail_extra">-- <br>
<div class="gmail_signature">
<div dir="ltr">Willy-Bas Loos<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>