Hi
Thanks , this was fast code update .
Tested the code now but with geos 3.7 and the attached file and I still have
the same problem .
I assume there is something related to that I tested with geos 3.7.
I also had to test om another server with this cpu (Intel(R) Xeon(R) CPU
E3-1270 V2 @ 3.50GHz (fam: 06, model: 3a, stepping: 09)) where I was running
centos 7.
But I don't think this should make so much difference
Here is the test after the update on this server.
How I installed and compiled is further down the mail.
SELECT PostGIS_full_version();
postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.5.3dev r17378" [EXTENSION] PGSQL="110" GEOS="3.7.2dev-CAPI-1.11.2
995c7c4" SFCGAL="1.2.2" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.5,
released 2016/07/01" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER
EXPLAIN ANALYZE
select (ST_dump(st_union(geo))).geom as geo
from sde_markslag.markslag_myrikilden_temp
where gid < 10000
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=62634.10..62899.37 rows=1000 width=32) (actual
time=23445.338..23544.121 rows=9205 loops=1)
-> ProjectSet (cost=62634.10..62639.37 rows=1000 width=32) (actual
time=23445.333..23542.636 rows=9205 loops=1)
-> Aggregate (cost=62634.10..62634.11 rows=1 width=32) (actual
time=23442.220..23442.220 rows=1 loops=1)
-> Seq Scan on markslag_myrikilden_temp (cost=0.00..62609.51
rows=9835 width=1600) (actual time=0.015..178.305 rows=9999 loops=1)
Filter: (gid < 10000)
Rows Removed by Filter: 557242
Planning Time: 0.189 ms
Execution Time: 23544.779 ms
(8 rows)
Can I use 3.7 or do I have to compile the master branch together postgis 2.5 ?
I was not sure about what to merge from master into 3.7. So I just picked the
attached file from master and nothing else..
Then I removed postgis, gets and gdal installed by yum by using yum remove.
Then I Downloaded and compiled : The size was 521488 for
./src/operation/union/CascadedPolygonUnion.o
gdal branch 1.1 (Why do we use this old gdal ?)
geos branch 3.7 with a copy of CascadedPolygonUnion.cpp from master
postgis brach 2.5
./configure --with-geosconfig=/home/lop/postgres_code/geos/tools/geos-config
--with-pgconfig=/usr/pgsql-11/bin/pg_config
Then I got this error
./configure: line 13475: /home/lop/postgres_code/geos/tools/geos-config:
Permission denied
I added execute on that file then it compiled with ok and installed
PostGIS is now configured for x86_64-unknown-linux-gnu
-------------- Compiler Info -------------
C compiler: gcc -g -O2
SQL preprocessor: /usr/bin/cpp -traditional-cpp -w -P
-------------- Additional Info -------------
Interrupt Tests: DISABLED use: --with-interrupt-tests to enable
-------------- Dependencies --------------
GEOS config: /home/lop/postgres_code/geos/tools/geos-config
GEOS version: 3.7.2dev
GDAL config: /usr/local/bin/gdal-config
GDAL version: 1.11.5
SFCGAL config: /usr/bin/sfcgal-config
SFCGAL version: 1.2.2
PostgreSQL config: /usr/pgsql-11/bin/pg_config
PostgreSQL version: PostgreSQL 11.2
PROJ4 version: 48
Libxml2 config: /usr/bin/xml2-config
Libxml2 version: 2.9.1
JSON-C support: yes
protobuf-c support: no
PCRE support: no
Perl: /usr/bin/perl
--------------- Extensions ---------------
PostGIS Raster: enabled
PostGIS Topology: enabled
SFCGAL support: enabled
Address Standardizer support: disabled
-------- Documentation Generation --------
xsltproc: /usr/bin/xsltproc
xsl style sheets:
dblatex:
convert:
mathml2.dtd: http://www.w3.org/Math/DTD/mathml2/mathml2.dtd
To get work postgres I had to add this file
cat /etc/ld.so.conf.d/geos37-pgdg-libs.conf
/usr/local/lib/
And in /usr/local/lib I have these files
[root@vroomtest geos]# ls /usr/local/lib
gdalplugins libgdal.la libgdal.so.1 libgeos-3.7.2.so libgeos_c.a
libgeos_c.so libgeos_c.so.1.11.2 libgeos.so liblwgeom-2.5.so.0.0.0
liblwgeom.la pkgconfig
libgdal.a libgdal.so libgdal.so.1.18.5 libgeos.a libgeos_c.la
libgeos_c.so.1 libgeos.la liblwgeom-2.5.so.0 liblwgeom.a
liblwgeom.so
Then I started postgres and created a new database and adde the postgis
extension and added the testdata.
Thanks .
Lars
________________________________
From: postgis-users <[email protected]> on behalf of Martin
Davis <[email protected]>
Sent: Friday, April 12, 2019 11:36 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] diffrent execution plan on Postgres 9.5 and
Postgres 11 for ST_union and performance problem Postgres 11
I've figured out a fix for the performance reqression and it's now in GEOS
master. My testing using the sample data provided above indicates it's at
least 10x faster, so essentially eliminating the performance regression in this
case (and it should provide that for most situations)
If anyone can test further to confirm it is indeed faster and most importantly
correct that would be great.
On Thu, Apr 11, 2019 at 1:03 PM Martin Davis
<[email protected]<mailto:[email protected]>> wrote:
Well, we have figured out what the problem is - it's a known regression in the
GEOS UnaryUnion code, reported in this ticket [1]
Unfortunately there's no fix for this yet, but we'll try and escalate this.
[1] https://trac.osgeo.org/geos/ticket/867
/**********************************************************************
*
* GEOS - Geometry Engine Open Source
* http://geos.osgeo.org
*
* Copyright (C) 2011 Sandro Santilli <[email protected]>
* Copyright (C) 2006 Refractions Research Inc.
*
* This is free software; you can redistribute and/or modify it under
* the terms of the GNU Lesser General Public Licence as published
* by the Free Software Foundation.
* See the COPYING file for more information.
*
**********************************************************************
*
* Last port: operation/union/CascadedPolygonUnion.java r487 (JTS-1.12+)
* Includes custom code to deal with https://trac.osgeo.org/geos/ticket/837
*
**********************************************************************/
#include <geos/operation/union/CascadedPolygonUnion.h>
#include <geos/geom/Geometry.h>
#include <geos/geom/GeometryFactory.h>
#include <geos/geom/Polygon.h>
#include <geos/geom/MultiPolygon.h>
#include <geos/geom/util/GeometryCombiner.h>
#include <geos/geom/util/PolygonExtracter.h>
#include <geos/index/strtree/STRtree.h>
// std
#include <cassert>
#include <cstddef>
#include <memory>
#include <vector>
#include <sstream>
#include <geos/operation/valid/IsValidOp.h>
#include <geos/operation/IsSimpleOp.h>
#include <geos/algorithm/BoundaryNodeRule.h>
#include <geos/util/TopologyException.h>
#include <string>
#include <iomanip>
//#define GEOS_DEBUG_CASCADED_UNION 1
//#define GEOS_DEBUG_CASCADED_UNION_PRINT_INVALID 1
namespace {
inline bool
check_valid(const geos::geom::Geometry& g, const std::string& label, bool
doThrow = false, bool validOnly = false)
{
using namespace geos;
if(dynamic_cast<const geos::geom::Lineal*>(&g)) {
if(! validOnly) {
operation::IsSimpleOp sop(g,
algorithm::BoundaryNodeRule::getBoundaryEndPoint());
if(! sop.isSimple()) {
if(doThrow) {
throw geos::util::TopologyException(
label + " is not simple");
}
return false;
}
}
}
else {
operation::valid::IsValidOp ivo(&g);
if(! ivo.isValid()) {
using operation::valid::TopologyValidationError;
TopologyValidationError* err = ivo.getValidationError();
#ifdef GEOS_DEBUG_CASCADED_UNION
std::cerr << label << " is INVALID: "
<< err->toString()
<< " (" << std::setprecision(20)
<< err->getCoordinate() << ")"
<< std::endl
#ifdef GEOS_DEBUG_CASCADED_UNION_PRINT_INVALID
<< "<a>" << std::endl
<< g.toString() << std::endl
<< "</a>" << std::endl
#endif
;
#endif // GEOS_DEBUG_CASCADED_UNION
if(doThrow) {
throw geos::util::TopologyException(
label + " is invalid: " + err->toString(),
err->getCoordinate());
}
return false;
}
}
return true;
}
} // anonymous namespace
namespace geos {
namespace operation { // geos.operation
namespace geounion { // geos.operation.geounion
///////////////////////////////////////////////////////////////////////////////
void
GeometryListHolder::deleteItem(geom::Geometry* item)
{
delete item;
}
///////////////////////////////////////////////////////////////////////////////
geom::Geometry*
CascadedPolygonUnion::Union(std::vector<geom::Polygon*>* polys)
{
CascadedPolygonUnion op(polys);
return op.Union();
}
geom::Geometry*
CascadedPolygonUnion::Union(const geom::MultiPolygon* multipoly)
{
std::vector<geom::Polygon*> polys;
typedef geom::MultiPolygon::const_iterator iterator;
iterator end = multipoly->end();
for(iterator i = multipoly->begin(); i != end; ++i) {
polys.push_back(dynamic_cast<geom::Polygon*>(*i));
}
CascadedPolygonUnion op(&polys);
return op.Union();
}
geom::Geometry*
CascadedPolygonUnion::Union()
{
if(inputPolys->empty()) {
return nullptr;
}
geomFactory = inputPolys->front()->getFactory();
/**
* A spatial index to organize the collection
* into groups of close geometries.
* This makes unioning more efficient, since vertices are more likely
* to be eliminated on each round.
*/
index::strtree::STRtree index(STRTREE_NODE_CAPACITY);
typedef std::vector<geom::Polygon*>::iterator iterator_type;
iterator_type end = inputPolys->end();
for(iterator_type i = inputPolys->begin(); i != end; ++i) {
geom::Geometry* g = dynamic_cast<geom::Geometry*>(*i);
index.insert(g->getEnvelopeInternal(), g);
}
std::unique_ptr<index::strtree::ItemsList> itemTree(index.itemsTree());
return unionTree(itemTree.get());
}
geom::Geometry*
CascadedPolygonUnion::unionTree(
index::strtree::ItemsList* geomTree)
{
/**
* Recursively unions all subtrees in the list into single geometries.
* The result is a list of Geometry's only
*/
std::unique_ptr<GeometryListHolder> geoms(reduceToGeometries(geomTree));
return binaryUnion(geoms.get());
}
geom::Geometry*
CascadedPolygonUnion::binaryUnion(GeometryListHolder* geoms)
{
return binaryUnion(geoms, 0, geoms->size());
}
geom::Geometry*
CascadedPolygonUnion::binaryUnion(GeometryListHolder* geoms,
std::size_t start, std::size_t end)
{
if(end - start <= 1) {
return unionSafe(geoms->getGeometry(start), nullptr);
}
else if(end - start == 2) {
return unionSafe(geoms->getGeometry(start), geoms->getGeometry(start +
1));
}
else {
// recurse on both halves of the list
std::size_t mid = (end + start) / 2;
std::unique_ptr<geom::Geometry> g0(binaryUnion(geoms, start, mid));
std::unique_ptr<geom::Geometry> g1(binaryUnion(geoms, mid, end));
return unionSafe(g0.get(), g1.get());
}
}
GeometryListHolder*
CascadedPolygonUnion::reduceToGeometries(index::strtree::ItemsList* geomTree)
{
std::unique_ptr<GeometryListHolder> geoms(new GeometryListHolder());
typedef index::strtree::ItemsList::iterator iterator_type;
iterator_type end = geomTree->end();
for(iterator_type i = geomTree->begin(); i != end; ++i) {
if((*i).get_type() == index::strtree::ItemsListItem::item_is_list) {
std::unique_ptr<geom::Geometry>
geom(unionTree((*i).get_itemslist()));
geoms->push_back_owned(geom.get());
geom.release();
}
else if((*i).get_type() ==
index::strtree::ItemsListItem::item_is_geometry) {
geoms->push_back(reinterpret_cast<geom::Geometry*>((*i).get_geometry()));
}
else {
assert(!static_cast<bool>("should never be reached"));
}
}
return geoms.release();
}
geom::Geometry*
CascadedPolygonUnion::unionSafe(geom::Geometry* g0, geom::Geometry* g1)
{
if(g0 == nullptr && g1 == nullptr) {
return nullptr;
}
if(g0 == nullptr) {
return g1->clone();
}
if(g1 == nullptr) {
return g0->clone();
}
return unionOptimized(g0, g1);
}
geom::Geometry*
CascadedPolygonUnion::unionOptimized(geom::Geometry* g0, geom::Geometry* g1)
{
geom::Envelope const* g0Env = g0->getEnvelopeInternal();
geom::Envelope const* g1Env = g1->getEnvelopeInternal();
if(!g0Env->intersects(g1Env)) {
return geom::util::GeometryCombiner::combine(g0, g1);
}
if(g0->getNumGeometries() <= 1 && g1->getNumGeometries() <= 1) {
return unionActual(g0, g1);
}
geom::Envelope commonEnv;
g0Env->intersection(*g1Env, commonEnv);
return unionUsingEnvelopeIntersection(g0, g1, commonEnv);
}
/* private */
geom::Geometry*
CascadedPolygonUnion::unionUsingEnvelopeIntersection(geom::Geometry* g0,
geom::Geometry* g1, geom::Envelope const& common)
{
std::vector<geom::Geometry*> disjointPolys;
#if GEOS_DEBUG_CASCADED_UNION
check_valid(*g0, "unionUsingEnvelopeIntersection g0");
check_valid(*g1, "unionUsingEnvelopeIntersection g1");
#endif
std::unique_ptr<geom::Geometry> g0Int(extractByEnvelope(common, g0,
disjointPolys));
std::unique_ptr<geom::Geometry> g1Int(extractByEnvelope(common, g1,
disjointPolys));
#if GEOS_DEBUG_CASCADED_UNION
check_valid(*g0Int, "unionUsingEnvelopeIntersection g0Int");
check_valid(*g1Int, "unionUsingEnvelopeIntersection g1Int");
#endif
std::unique_ptr<geom::Geometry> u(unionActual(g0Int.get(), g1Int.get()));
#if GEOS_DEBUG_CASCADED_UNION
if(! check_valid(*u, "unionUsingEnvelopeIntersection unionActual return")) {
#if GEOS_DEBUG_CASCADED_UNION_PRINT_INVALID
std::cerr << " union between the following is invalid"
<< "<a>" << std::endl
<< *g0Int << std::endl
<< "</a>" << std::endl
<< "<b>" << std::endl
<< *g1Int << std::endl
<< "</b>" << std::endl
;
#endif
}
#endif
if(disjointPolys.empty()) {
return u.release();
}
#if GEOS_DEBUG_CASCADED_UNION
for(size_t i = 0; i < disjointPolys.size(); ++i) {
std::ostringstream os;
os << "dp" << i;
check_valid(*(disjointPolys[i]), os.str());
}
#endif
// TODO: find, in disjointPolys, those which now have their
// environment intersect the environment of the union "u"
// and collect them in another vector to be unioned
std::vector<geom::Geometry*> polysOn;
std::vector<geom::Geometry*> polysOff;
geom::Envelope const* uEnv = u->getEnvelopeInternal(); // TODO: check for
EMPTY ?
extractByEnvelope(*uEnv, disjointPolys, polysOn, polysOff);
#if GEOS_DEBUG_CASCADED_UNION
std::cerr << "unionUsingEnvelopeIntersection: " << polysOn.size() << "/" <<
disjointPolys.size() <<
" polys intersect union of final thing" << std::endl;
#endif
std::unique_ptr<geom::Geometry> ret;
if(polysOn.empty()) {
disjointPolys.push_back(u.get());
ret.reset(geom::util::GeometryCombiner::combine(disjointPolys));
}
else {
// TODO: could be further tweaked to only union with polysOn
// and combine with polysOff, but then it'll need again to
// recurse in the check for disjoint/intersecting
ret.reset(geom::util::GeometryCombiner::combine(disjointPolys));
ret.reset(unionActual(ret.get(), u.get()));
}
#if GEOS_DEBUG_CASCADED_UNION
check_valid(*ret, "unionUsingEnvelopeIntersection returned geom");
#endif
return ret.release();
}
/* private */
void
CascadedPolygonUnion::extractByEnvelope(geom::Envelope const& env,
std::vector<geom::Geometry*>&
sourceGeoms,
std::vector<geom::Geometry*>&
intersectingGeoms,
std::vector<geom::Geometry*>&
disjointGeoms)
{
for(std::vector<geom::Geometry*>::iterator i = sourceGeoms.begin(),
e = sourceGeoms.end(); i != e; ++i) {
geom::Geometry* elem = *i;
if(elem->getEnvelopeInternal()->intersects(env)) {
intersectingGeoms.push_back(elem);
}
else {
disjointGeoms.push_back(elem);
}
}
}
/* private */
void
CascadedPolygonUnion::extractByEnvelope(geom::Envelope const& env,
geom::Geometry* geom,
std::vector<geom::Geometry*>&
intersectingGeoms,
std::vector<geom::Geometry*>&
disjointGeoms)
{
for(std::size_t i = 0; i < geom->getNumGeometries(); i++) {
geom::Geometry* elem =
const_cast<geom::Geometry*>(geom->getGeometryN(i));
if(elem->getEnvelopeInternal()->intersects(env)) {
intersectingGeoms.push_back(elem);
}
else {
disjointGeoms.push_back(elem);
}
}
}
/* private */
geom::Geometry*
CascadedPolygonUnion::extractByEnvelope(geom::Envelope const& env,
geom::Geometry* geom,
std::vector<geom::Geometry*>& disjointGeoms)
{
std::vector<geom::Geometry*> intersectingGeoms;
extractByEnvelope(env, geom, intersectingGeoms, disjointGeoms);
return geomFactory->buildGeometry(intersectingGeoms);
}
geom::Geometry*
CascadedPolygonUnion::unionActual(geom::Geometry* g0, geom::Geometry* g1)
{
return
restrictToPolygons(std::unique_ptr<geom::Geometry>(g0->Union(g1))).release();
}
std::unique_ptr<geom::Geometry>
CascadedPolygonUnion::restrictToPolygons(std::unique_ptr<geom::Geometry> g)
{
using namespace geom;
using namespace std;
if(dynamic_cast<Polygonal*>(g.get())) {
return g;
}
Polygon::ConstVect polygons;
geom::util::PolygonExtracter::getPolygons(*g, polygons);
if(polygons.size() == 1) {
return std::unique_ptr<Geometry>(polygons[0]->clone());
}
typedef vector<Geometry*> GeomVect;
Polygon::ConstVect::size_type n = polygons.size();
GeomVect* newpolys = new GeomVect(n);
for(Polygon::ConstVect::size_type i = 0; i < n; ++i) {
(*newpolys)[i] = polygons[i]->clone();
}
return unique_ptr<Geometry>(
g->getFactory()->createMultiPolygon(newpolys)
);
}
} // namespace geos.operation.union
} // namespace geos.operation
} // namespace geos
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
