So I've was running this query for 866000 s (10 days) before I decided to kill
it:
create table taz_and_lancover_10_fast_2 as
SELECT p.lc_class, n.taz
, CASE
WHEN ST_CoveredBy(p.geom, n.the_geom)
THEN p.geom
ELSE
ST_Multi(
ST_Intersection(p.geom,n.geom)
) END AS geom
> So I've was running this query for 866000 s (10 days) before I decided to
> kill it:
> One potential thing I've realized is that a few of the geometries in tazjan2
> are multipolygons, not single polygons. But it's only a few. There are a
> few very large and complex polygons in lancover_
Hey,
you could try to not use CASE (so separate the spatial join from the
processing, which is easy to parallelize (assuming you have more than one
core )).
First generate the table with
CREATE TABLE psatial_mapping_between_lancover_and_taz AS
SELECT row_number() over() as row_id, p.your_prim
Thanks for the hint, Rémi. I'll give it a try, to see if the st_intersection
is slow (as compared to just st_intersects).
Here's my EXPLAIN ANALYZE for my 5 zone test:
"Nested Loop (cost=1.56..80.91 rows=8 width=560) (actual
time=3929258.738..19756123.479 rows=29 loops=1)"
" Join Filter: (_s
As previously,
you need to separate the processing from the spatial filtering if you want
to know where the slowness comes from (run the exact same querry, without
the CASE).
What you numbers tall you is that your index is relatively useless : it
reduce the data set, but there are still way too mu
*"I will write up a tutorial explaining the benefits of vector tiling in
PostGIS, with examples and parallelisation code patterns. If not today,
hopefully over the next week. "*
Would be nice if you have the time!
Best,
Andre
On Fri, Feb 20, 2015 at 2:52 AM, Mark Wynter
wrote:
>
> So I've w
Thanks Mark. I will indeed do the st_dump, but I don't think it will help much
(very few multis). I think the tiling will help a lot. What I wonder, though,
is how long it will take to tile? Afterall, it's still an st_intersection
operation to tile each geometry against each tile.
Is there
> Thanks Mark. I will indeed do the st_dump, but I don't think it will help
> much (very few multis). I think the tiling will help a lot. What I wonder,
> though, is how long it will take to tile? Afterall, it's still an
> st_intersection operation to tile each geometry against each tile.
Hey,
I highjack this interesting discussion, sorry =)
If you have one single massive Polygon (no multi).
You can generate a point table (keeping path of course) with ST_DumpPoints.
Then you construct an index on this table.
Then you perform your Intersection with indexed points, which will be ver
Following advice from Mark and Remi, I am now tiling the troublesome layer.
It's not going quickly. I'm trying an intersection of the layer with only 5 of
my 10kmX10km grids and it has been running for 30 min. I have 8680 grids,
which is more features than the layer I was originally working w
The land cover data you’re working with - does it come as a raster? We need a
strategy for busting up that single, monolithic poly with lots of holes.
If its a raster, then I think that tiling on import would be of huge benefit.
I’m just under the pump the next few days.
Damn I'm publishing a QGIS plugin for POstGIS,
I can't write you the fast intersection by casting polygon to points,
intersection then casting back to polygon.
I'll try to squeeze it somehow.
Cheers,
Rémi-C
2015-02-25 1:44 GMT+01:00 Mark Wynter :
> The land cover data you’re working with - does i
I’ll also try and take a look - I’ve just downloaded the dataset in question -
I’m keen to see it. Will revert.
On 25 Feb 2015, at 6:41 pm, Rémi Cura wrote:
> Damn I'm publishing a QGIS plugin for POstGIS,
> I can't write you the fast intersection by casting polygon to points,
> intersection
Where is the data set?
Cheers,
Rémi-C
2015-02-25 9:18 GMT+01:00 Mark Wynter :
> I’ll also try and take a look - I’ve just downloaded the dataset in
> question - I’m keen to see it. Will revert.
>
> On 25 Feb 2015, at 6:41 pm, Rémi Cura wrote:
>
> Damn I'm publishing a QGIS plugin for POstGIS,
>
Wow guys. 131 seconds is a lot faster than 10 days.
Remi, if you can work out a fast intersection method that uses points and lines
as a preprocessor to dealing with the polygons, I think it would be a great
addition to PostGIS. ST_Intersection in PostGIS is often quite a bit slower
than the
John, I've got a variation in mind that works solely using lc_class32 - should
take same time as what I sent through earlier. I'm guessing 2 minutes!!! A
different SQL statement in the data prep stage - it is now becoming very clear
to me as to how to tile lc_class32 in the purest sense. Which
This would be awesome "
I'm keen to experiment with the dumped points, using my recursive quadgrid
function, so the vector grids subdivide to a maximum depth or number of
points per cell. So this will be applied at the point level to give an
optimized number of grid cells.
"
If I understand you cor
Yes Remi - you have understood correctly how the quadgrid function works - I
will have a play with the quadgrid tomorrow, then let's share notes.
> If I understand you correctly, you would subdivide like you do currently, but
> the depth of subdivision would be a function of number of points rem
I don't think it will be fastest to compute the subdivide, but it would be
way fastest to use it for processing afterward !
Cheers,
Rémi-C
2015-03-05 13:00 GMT+01:00 Mark Wynter :
> Yes Remi - you have understood correctly how the quadgrid function works -
> I will have a play with the quadgrid t
19 matches
Mail list logo