[postgis-users] ST_Intersection very slow

[John Abraham]

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 implementation in Geomedia, Mapinfo, and (I hear) ArcGIS, so any functionality that results in speed improvements would be great.

Mark, I can't wait to figure out why your system was fast!  I was following your (preliminary) tutorial and gridding the data was progressing very slowly.  

I have a provincial boundary file but there seems to be much ambiguity in GIS representations of the provincial boundary, so I won't send you the one I have.  I can try to assemble one from other sources.

--
John Abraham
jea at hbaspecto.com
403-232-1060

On Feb 25, 2015, at 6:28 AM, Mark Wynter <mark at dimensionaledge.com> wrote:

> Hi John
> 
> I’ve just crunched your whole dataset.  The process takes 131 seconds for the vector tiling (using a 16 CPU machine).  Plus another 170 seconds for data prep at the start including making the poly's valid.
> For a 2 CPU machine, it will take circa 15 minutes, or double that using a single CPU.
> 
> Only one small issue outstanding - and that relates to clipping the regular grid prior to tiling.  For clipping I used the union of the abmiw2wlcv_48tiles as supplied with the data - the problem is the abmiw2wlcv_48tiles are rough and ready, which produces voids. The voids using my method unfortunately get the same feature class as lc_class = 32.  You’ll see this clearly on second screenshot.
> The way around this is to clip the regular grid using a high-res shapefile of the Alberta state boundary prior to the tile crunching the lancover_polygons_2010 table.  This is easily done - I just didn’t have the state boundary data.
> 
> I need to get some sleep. John, Remi,  I will share with you the code tomorrow.  For others, I’ll be posting a tutorial that steps through the methods touched upon in this thread..
> 
> John, the only difference between my tutorial and its application to your land cover data was a few tweaks to the data prep stage. Otherwise the same code pattern (no modification at all needed to the worker_function).  It was great to test the code with your data.
> 
> Speak tomorrow.
> Mark
> 
> 
> 
> 
> <Screen Shot 2015-02-25 at 11.29.15 pm.png>
> 
> 
> <Screen Shot 2015-02-25 at 11.39.04 pm.png>
> 
> 
> <Screen Shot 2015-02-25 at 11.41.41 pm.png>
>