Re: [postgis-users] Improvement suggestion

[Komяpa]

Hi,

These are all hacks with different meanings. ST_CollectionExtract removes
sliver parts that became lines, and ST_Multi just transforms a
GEOMETRYCOLLECTION into MULTI*. Basically ST_Multi is most of time used
with badly-written software that does not expect GEOMETRYCOLLECTION at all,
or wants everything to be of the same type (so, a MULTIPOLYGON).
ST_CollectionExtract performs one kind of clean up. For other clean ups,
like dissolving overlapping polygons, you may need different hacks - maybe
ST_UnaryUnion. If your data does not contain that kind of problems or your
further processing is robust to them, you can just omit it.

On Thu, Dec 13, 2018 at 6:38 PM David Haynes  wrote:

> Question about the solution that was posted below. I have always used
> ST_CollectionExtract( ST_MakeValid(geom), 3 )  and in the post it uses
> st_multi(st_makevalid(geom))
>
> Which is preferred. On the website for ST_CollectionExtract() there is a
> new warning.
> When specifying 3 == POLYGON a multipolygon is returned even when the
> edges are shared. This results in an invalid multipolygon for many cases
> such as applying this function on an ST_Split
>  result.
>
> Try the solution outlined here:
>
> https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
>
>
> On Sun, Dec 2, 2018 at 3:40 PM Paul Ramsey 
> wrote:
>
>> Try the solution outlined here:
>>
>>
>> https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
>>
>>
>> On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
>> paul.doskabou...@gmail.com> wrote:
>>
>>> As I am working with large polygons, I'm always struggling with
>>> performance, and trying to find ways to improve them.
>>> F.e. I have lots of queries like:
>>> SELECT ST_Intersection(table1.geom,table2.geom)
>>> FROM table1
>>> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>>>
>>> In case of large polygons this is sometimes a bottleneck, and I have the
>>> following suggestion:
>>> Create a function which returns the relation between 2 polygons (within,
>>> intersects or disjunct) so that I can do the following:
>>>
>>> SELECT
>>>   CASE
>>>  WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
>>> ST_Intersection(table1.geom,table2.geom)
>>>  ELSE table1.geom
>>>   END
>>> FROM table1
>>> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)
>>>
>>> or (because ST_Relate is calculated twice in previous query):
>>>
>>> SELECT
>>>   CASE
>>>  WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>>>  ELSE t1geom
>>>   END
>>> FROM (
>>>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
>>> t1geom,table2.geom AS t2geom FROM table1
>>>   JOIN table2 on table1.geom && table2.geom
>>> ) AS allpolies
>>> WHERE relate IN (intersects,within)
>>>
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[David Haynes]

Question about the solution that was posted below. I have always used
ST_CollectionExtract( ST_MakeValid(geom), 3 )  and in the post it uses
st_multi(st_makevalid(geom))

Which is preferred. On the website for ST_CollectionExtract() there is a
new warning.
When specifying 3 == POLYGON a multipolygon is returned even when the edges
are shared. This results in an invalid multipolygon for many cases such as
applying this function on an ST_Split
 result.

Try the solution outlined here:
https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis


On Sun, Dec 2, 2018 at 3:40 PM Paul Ramsey 
wrote:

> Try the solution outlined here:
>
>
> https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
>
>
> On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
> paul.doskabou...@gmail.com> wrote:
>
>> As I am working with large polygons, I'm always struggling with
>> performance, and trying to find ways to improve them.
>> F.e. I have lots of queries like:
>> SELECT ST_Intersection(table1.geom,table2.geom)
>> FROM table1
>> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>>
>> In case of large polygons this is sometimes a bottleneck, and I have the
>> following suggestion:
>> Create a function which returns the relation between 2 polygons (within,
>> intersects or disjunct) so that I can do the following:
>>
>> SELECT
>>   CASE
>>  WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
>> ST_Intersection(table1.geom,table2.geom)
>>  ELSE table1.geom
>>   END
>> FROM table1
>> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)
>>
>> or (because ST_Relate is calculated twice in previous query):
>>
>> SELECT
>>   CASE
>>  WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>>  ELSE t1geom
>>   END
>> FROM (
>>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
>> t1geom,table2.geom AS t2geom FROM table1
>>   JOIN table2 on table1.geom && table2.geom
>> ) AS allpolies
>> WHERE relate IN (intersects,within)
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul van der Linden]

Well, to my surprise this already exists... and it even has the same name
as I proposed...
https://postgis.net/docs/ST_Relate.html
Guess it's time to do some experimenting with that...
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul van der Linden]

Thanks for your elaborate answer,, I will have a good think about it.

Just a random thought I had:
As I was doing some experiments with st_intersection, I noticed some
strange thing though.
On our prod. machine (windows, pg11, postgis 2.5.0 (not 100% sure), geos
3.7.0), it takes about a minute to calculate the st_intersection, on my
machine (windows only using libgeos) it takes 11 seconds after my
optimization (15seconds without) and according to Martin Davis at his end
(java?) it takes about 4 seconds.
I don't have any macros defined (I saw f.e. a USE_RECTANGLE_INTERSECTION),
could that be the cause of the speed-differences?
And can it be verified that all code/compiler optimizations are enabled for
the windows builds of postgis/geos?

P.S. can you include me in the cc?
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul Ramsey]

Forgot to include my reference [1]
https://commitfest.postgresql.org/21/1868/

On Wed, Dec 5, 2018 at 11:19 AM Paul Ramsey 
wrote:

>
>
> On Tue, Dec 4, 2018 at 2:27 AM Darafei "Komяpa" Praliaskouski <
> m...@komzpa.net> wrote:
>
>> If you are managing large geometries, splitting them off into "geometry
>> table" with non-uniquie ID and subdivided parts sometimes helps.
>> You want to build a tree on top of your geometry internals to make it all
>> fast, one way to do that is to make sure the internal parts of geometry are
>> available for indexing in GiST, via ST_Subdivide.
>>
>> Other beautiful way would be to hide this all behind the scenes in
>> PostGIS itself and making ST_Intersection and ST_Intersects optimize the
>> cases you mentioned internally, caching some kind of tree internally,
>> probably in the geometry itself.
>>
>
> The efficient handling of ultra-large geometries, even in a caching case,
> is going to involve some core changes to how PostGIS deals with PostgreSQL.
> First, we need to make checking the relevance of the cache cheaper. Right
> now, checking the cache involves comparing the entire contents of the
> current geometry (gserialized form) with the entire contents of the cached
> geometry (gserialized form), using a memcmp. The memcmp itself isn't so
> expensive, but fully reading in the current geometry (gserialized) *is*
> expensive, as the whole thing has to be de-toasted. This gets into the
> decompression issues for toasted values, which we can address both by
> patching PgSQL [1] and by changing up our own serialization to use
> uncompressed storage and compressing the things we want to compress
> ourselves (leaving an uncompressed header, for example). Once we are able
> to read back only a part of a large geometry, it becomes possible to use a
> hashcode in the header to test whether the current cache is still valid,
> and that aspect of ultra-large geometry reading gets better.
>
> One aspect that doesn't get better is the selectivity of the bbox of the
> ultralarge geometry. This is something that subdividing neatly fixes, at
> the same time as it dodges the toasting problem. If you aren't subdividing
> then your ultralarge geometry will probably have an extremely
> over-determined bounds, and so you will be testing more inputs against the
> full geometry than you really want to. Even with a nice, efficient, cached
> tree to do the testing with, the penalty of all those unnecessary tests
> builds up.
>
> The way around that is to start looking at using inverted indexes and
> multi-key coverages of polygons, which looks a lot like the way people with
> key/value stores do spatial indexing. There are some implementation issues
> there, particularly with geometry, that doesn't have a nice, implicit
> coordinate bounds for any given collection of features (as opposed to
> geography, which always lives inside (-180,-90,180,90). There's also some
> limitations with respect to the current PgSQL implementation which we might
> want to address, most notably the use of 32 bit keys in the GIN index. If
> we take a bit away for indicating key containment vs overlaps, we're left
> with only 31 bits, which in geography space is a about metre resolution (if
> memory serves). Not survey grade, and maybe not suitable for all purposes.
>
> Note that many of these issues can be worked around very easily (amazingly
> easily, in my opinion) by modelling with a subdivided query table, given
> orders of magnitude better performance without any infrastructural changes
> to PostGIS *or* PgSQL. The power of homogeneous inputs is hard to
> overstate, and the difficulty of dealing efficiently with the full
> heterogeneous range of spatial data inputs is hard to understate.
>
> P
>
>
>
>>
>> On Mon, Dec 3, 2018 at 11:43 PM Paul van der Linden <
>> paul.doskabou...@gmail.com> wrote:
>>
>>> No, didn't do that.
>>> Don't think it's going to improve readability of the query to be honest
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>>
>> --
>> Darafei Praliaskouski
>> Support me: http://patreon.com/komzpa
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul Ramsey]

On Tue, Dec 4, 2018 at 2:27 AM Darafei "Komяpa" Praliaskouski 
wrote:

> If you are managing large geometries, splitting them off into "geometry
> table" with non-uniquie ID and subdivided parts sometimes helps.
> You want to build a tree on top of your geometry internals to make it all
> fast, one way to do that is to make sure the internal parts of geometry are
> available for indexing in GiST, via ST_Subdivide.
>
> Other beautiful way would be to hide this all behind the scenes in PostGIS
> itself and making ST_Intersection and ST_Intersects optimize the cases you
> mentioned internally, caching some kind of tree internally, probably in the
> geometry itself.
>

The efficient handling of ultra-large geometries, even in a caching case,
is going to involve some core changes to how PostGIS deals with PostgreSQL.
First, we need to make checking the relevance of the cache cheaper. Right
now, checking the cache involves comparing the entire contents of the
current geometry (gserialized form) with the entire contents of the cached
geometry (gserialized form), using a memcmp. The memcmp itself isn't so
expensive, but fully reading in the current geometry (gserialized) *is*
expensive, as the whole thing has to be de-toasted. This gets into the
decompression issues for toasted values, which we can address both by
patching PgSQL [1] and by changing up our own serialization to use
uncompressed storage and compressing the things we want to compress
ourselves (leaving an uncompressed header, for example). Once we are able
to read back only a part of a large geometry, it becomes possible to use a
hashcode in the header to test whether the current cache is still valid,
and that aspect of ultra-large geometry reading gets better.

One aspect that doesn't get better is the selectivity of the bbox of the
ultralarge geometry. This is something that subdividing neatly fixes, at
the same time as it dodges the toasting problem. If you aren't subdividing
then your ultralarge geometry will probably have an extremely
over-determined bounds, and so you will be testing more inputs against the
full geometry than you really want to. Even with a nice, efficient, cached
tree to do the testing with, the penalty of all those unnecessary tests
builds up.

The way around that is to start looking at using inverted indexes and
multi-key coverages of polygons, which looks a lot like the way people with
key/value stores do spatial indexing. There are some implementation issues
there, particularly with geometry, that doesn't have a nice, implicit
coordinate bounds for any given collection of features (as opposed to
geography, which always lives inside (-180,-90,180,90). There's also some
limitations with respect to the current PgSQL implementation which we might
want to address, most notably the use of 32 bit keys in the GIN index. If
we take a bit away for indicating key containment vs overlaps, we're left
with only 31 bits, which in geography space is a about metre resolution (if
memory serves). Not survey grade, and maybe not suitable for all purposes.

Note that many of these issues can be worked around very easily (amazingly
easily, in my opinion) by modelling with a subdivided query table, given
orders of magnitude better performance without any infrastructural changes
to PostGIS *or* PgSQL. The power of homogeneous inputs is hard to
overstate, and the difficulty of dealing efficiently with the full
heterogeneous range of spatial data inputs is hard to understate.

P



>
> On Mon, Dec 3, 2018 at 11:43 PM Paul van der Linden <
> paul.doskabou...@gmail.com> wrote:
>
>> No, didn't do that.
>> Don't think it's going to improve readability of the query to be honest
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> --
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Komяpa]

If you are managing large geometries, splitting them off into "geometry
table" with non-uniquie ID and subdivided parts sometimes helps.
You want to build a tree on top of your geometry internals to make it all
fast, one way to do that is to make sure the internal parts of geometry are
available for indexing in GiST, via ST_Subdivide.

Other beautiful way would be to hide this all behind the scenes in PostGIS
itself and making ST_Intersection and ST_Intersects optimize the cases you
mentioned internally, caching some kind of tree internally, probably in the
geometry itself.

On Mon, Dec 3, 2018 at 11:43 PM Paul van der Linden <
paul.doskabou...@gmail.com> wrote:

> No, didn't do that.
> Don't think it's going to improve readability of the query to be honest
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Jonathan McCormack]

In our use of PostGIS on nationwide datasets, we too struggled with similar 
performance issues.  In the end, we largely fell back upon the tricks mentioned 
by Paul (along with some pre-processing using the ArcGIS Dice tool to limit the 
number of vertices for any polygon).

Perhaps there's a really obvious explanation, but is there a reason that 
functions like ST_Intersection are not optimized on the backend to, e.g., test 
the relation and return geometry A without further processing when geometry A 
falls entirely within geometry B?  It seems like it would be pretty easy and 
would tend to speed up most real-world queries.

Jonathan McCormack
Attorney Advisor & Information Systems Specialist
Auctions and Spectrum Access Division, WTB
Federal Communications Commission

From: postgis-users  on behalf of 
Nicolas Ribot 
Reply-To: PostGIS Users Discussion 
Date: Monday, December 3, 2018 at 2:51 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] Improvement suggestion

Hi,

Did you cut your big polygons with st_subdivide before intersecting them ?
It usually speeds up queries by several orders of magnitude.

Nicolas

On Mon, 3 Dec 2018 at 18:41, Paul van der Linden 
mailto:paul.doskabou...@gmail.com>> wrote:
I indeed know that trick to eliminate an st_intersection, but that comes at the 
cost of a st_within.
In the test-case I'm examing now, the st_intersects and st_within both take 
about 40 seconds and the st_intersection takes about a minute.
So to eliminate one of the 40 seconds (and in this case even the intersection 
because st_within is true), a function returning the relation could speed up 
things
___
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users<https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers=DwMFaQ=y0h0omCe0jAUGr4gAQ02Fw=ttSzwKHPIaN3Ue2Op6mH8FkPyFpiuuXAybXGNkPA8Zw=1FyqHI-zvQdeWurRnP6ebNq1GOYr3W03RbRg9a1tAxc=5Odg0h7eiL4iybklq5xzLe801SZYbxMgl62YD8Ph0_8=>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul van der Linden]

No, didn't do that.
Don't think it's going to improve readability of the query to be honest
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Nicolas Ribot]

Hi,

Did you cut your big polygons with st_subdivide before intersecting them ?
It usually speeds up queries by several orders of magnitude.

Nicolas

On Mon, 3 Dec 2018 at 18:41, Paul van der Linden 
wrote:

> I indeed know that trick to eliminate an st_intersection, but that comes
> at the cost of a st_within.
> In the test-case I'm examing now, the st_intersects and st_within both
> take about 40 seconds and the st_intersection takes about a minute.
> So to eliminate one of the 40 seconds (and in this case even the
> intersection because st_within is true), a function returning the relation
> could speed up things
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul van der Linden]

I indeed know that trick to eliminate an st_intersection, but that comes at
the cost of a st_within.
In the test-case I'm examing now, the st_intersects and st_within both take
about 40 seconds and the st_intersection takes about a minute.
So to eliminate one of the 40 seconds (and in this case even the
intersection because st_within is true), a function returning the relation
could speed up things
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Simon (SPDBA) Greener]

Did anyone else spot that the person created his target table, created an index 
on that (empty) table, then inserted all the data in it using St_intersection? 
He should create the index afterwards.


⁣Sent from BlueMail ​

On 3 Dec. 2018, 08:41, at 08:41, Paul Ramsey  wrote:
>Try the solution outlined here:
>
>https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
>
>
>On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
>paul.doskabou...@gmail.com> wrote:
>
>> As I am working with large polygons, I'm always struggling with
>> performance, and trying to find ways to improve them.
>> F.e. I have lots of queries like:
>> SELECT ST_Intersection(table1.geom,table2.geom)
>> FROM table1
>> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>>
>> In case of large polygons this is sometimes a bottleneck, and I have
>the
>> following suggestion:
>> Create a function which returns the relation between 2 polygons
>(within,
>> intersects or disjunct) so that I can do the following:
>>
>> SELECT
>>   CASE
>>  WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
>> ST_Intersection(table1.geom,table2.geom)
>>  ELSE table1.geom
>>   END
>> FROM table1
>> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN
>(intersects,within)
>>
>> or (because ST_Relate is calculated twice in previous query):
>>
>> SELECT
>>   CASE
>>  WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>>  ELSE t1geom
>>   END
>> FROM (
>>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
>> t1geom,table2.geom AS t2geom FROM table1
>>   JOIN table2 on table1.geom && table2.geom
>> ) AS allpolies
>> WHERE relate IN (intersects,within)
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
>
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

[Paul Ramsey]

Try the solution outlined here:

https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis


On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
paul.doskabou...@gmail.com> wrote:

> As I am working with large polygons, I'm always struggling with
> performance, and trying to find ways to improve them.
> F.e. I have lots of queries like:
> SELECT ST_Intersection(table1.geom,table2.geom)
> FROM table1
> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>
> In case of large polygons this is sometimes a bottleneck, and I have the
> following suggestion:
> Create a function which returns the relation between 2 polygons (within,
> intersects or disjunct) so that I can do the following:
>
> SELECT
>   CASE
>  WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
> ST_Intersection(table1.geom,table2.geom)
>  ELSE table1.geom
>   END
> FROM table1
> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)
>
> or (because ST_Relate is calculated twice in previous query):
>
> SELECT
>   CASE
>  WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>  ELSE t1geom
>   END
> FROM (
>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
> t1geom,table2.geom AS t2geom FROM table1
>   JOIN table2 on table1.geom && table2.geom
> ) AS allpolies
> WHERE relate IN (intersects,within)
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] Improvement suggestion

[Paul van der Linden]

As I am working with large polygons, I'm always struggling with
performance, and trying to find ways to improve them.
F.e. I have lots of queries like:
SELECT ST_Intersection(table1.geom,table2.geom)
FROM table1
JOIN table2 on ST_Intersects(table1.geom,table2.geom)

In case of large polygons this is sometimes a bottleneck, and I have the
following suggestion:
Create a function which returns the relation between 2 polygons (within,
intersects or disjunct) so that I can do the following:

SELECT
  CASE
 WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
ST_Intersection(table1.geom,table2.geom)
 ELSE table1.geom
  END
FROM table1
JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)

or (because ST_Relate is calculated twice in previous query):

SELECT
  CASE
 WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
 ELSE t1geom
  END
FROM (
  SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
t1geom,table2.geom AS t2geom FROM table1
  JOIN table2 on table1.geom && table2.geom
) AS allpolies
WHERE relate IN (intersects,within)
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users