[postgis-users] st_union produces different result from st_memunion

[Martin Davis]

Good that it's working for you too.

Glad you asked about scripting.  I was just going to post about a good
option for doing this in a scripted way.  This uses the new JTS CLI [1][2].
(Note you will need to pull the latest overly-sr branch and rebuild, since
the -explode option is newly added as of yesterday)

Here's a JtsOp command to run OverlayNG on the full dataset from a WKB
file, saving the result to a file:

<JTS HOME>/bin/jtsop.sh -a dbca_dump_116_35_117_34.wkb -b "POLYGON (EMPTY)"
-f wkb -explode OverlayNG.union 100000000 > out.wkb

Alternatively the input could be sourced from a psql command-line query
extracting the WKB from the DB.  That can be piped to the JtsOP command,
using the option "-a stdin".

And it may be possible to pipe the JtsOp WKB output back into the DB,
perhaps by wrapping it with a SQL header and footer containing a COPY
command?

If you get this working it would be nice to see the toolchain.

As for getting this into PostGIS, that is definitely the plan, and is being
worked on right now.  Hopefully to ship by Q4 2020.

[1] https://github.com/locationtech/jts/blob/master/doc/JTSOp.md
[2] http://lin-ear-th-inking.blogspot.com/2019/08/a-cli-for-jts.html

On Thu, Jul 9, 2020 at 1:15 AM Jonathan Schultz <jonathan at schultz.la> wrote:

> It seems to work OK for me too. I managed to export the result using the
> 'Save as XML' function, which is a bit painful but does the job. Can you
> suggest how difficult it would be to script the procedure?
>
> Please let me know if there's anything can do to help move this along. It
> will be great to have this functionality in PostGIS.
>
> Cheers,
> Jonathan
>
> On 9/7/20 12:09 am, Martin Davis wrote:
> > Good news - overlaying the full dataset works using JTS OverlayNG in
> Snap-Rounding mode.  It completes in 195 s using a grid size of 1e-8 (which
> is quite fine).
> >
> > Interestingly it didn't complete when using OverlayNG with Snapping
> noding (which is  heuristic-based noding strategy intended to preserve more
> precision).  I'll have to look into that further.
> >
> > Here's some instructions on how to run this in JTS:
> >
> > - Clone the branch https://github.com/dr-jts/jts/tree/overlay-sr
> > - Build using maven clean install
> > - Run the TestBuilder (
> https://github.com/locationtech/jts/blob/master/doc/JTSTestBuilder.md)
> > - Convert the SQL file to a WKB file by removing the SQL header and
> footer, leaving only the WKB text
> > - Drop the WKB file onto the TestBuilder A input box to load it
> > - Run the geometry function OverlayNG/union with Grid Scale = 100000000
> >
> >
> > On Tue, Jul 7, 2020 at 10:49 PM Jonathan Schultz <jonathan at schultz.la
> <mailto:jonathan at schultz.la>> wrote:
> >
> >     Oh sorry about that. Here is a link to the file on my own VPS:
> http://barraqda.org/dbca_dump_116_35_117_34.zip
> >
> >     I've been playing with JTS but it's a bit of a steep learning curve.
> Getting stuck on Java NoClassDefFoundError messages, I presume related to
> incompatible versions and such-like. I'd very much appreciate advice on
> running OverlayNG.
> >
> >     Cheers,
> >     Jonathan
> >
> >     On 8/7/20 1:41 pm, Martin Davis wrote:
> >      > That's interesting that ST_SnapToGrid doesn't help for the full
> dataset - but not totally surprising, since it's a pretty blunt instrument.
> >      >
> >      > I'd like to try out the larger dataset.  That free download
> website seems to have a bunch of unpleasant strings attached though - is
> there any other way you can make the data available?
> >      >
> >      > If you are able to run Java, by all means try OverlayNG.  Happy
> to provide advice on how to run it if needed.
> >      >
> >      > cheers - Martin
> >      >
> >      > On Tue, Jul 7, 2020 at 8:10 PM Jonathan Schultz <
> jonathan at schultz.la <mailto:jonathan at schultz.la> <mailto:
> jonathan at schultz.la <mailto:jonathan at schultz.la>>> wrote:
> >      >
> >      >     Thank you for the prompt feedback.
> >      >
> >      >     As you suggested I tried using ST_SnapToGrid and that worked
> very well even with a very fine grid -
> st_snaptogrid(geometry,0.000000000001). However when it came to a
> collection of lines more useful for real work (13540 records compared with
> 6 in the minimal dataset) I found that snapping even to a grid so coarse as
> to render the data useless - st_snaptogrid(geometry,0.01) - still produced
> an incorrect result.
> >      >
> >      >     Here is the dump of my larger dataset:
> https://anonfiles.com/rdYcG1Edo2/dbca_dump_116_35_117_34_sql
> >      >
> >      >     I'll have a go myself with OverlayNG.
> >      >
> >      >     And I will file a PostGIS issue depending on the conclusions
> from this discussion.
> >      >
> >      >
> >      >
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200709/eaa64ee2/attachment.html>