<div dir="ltr">Outch,<div>you have only 8 roads in GB? =)</div><div><br></div><div>This is not going to go fast till you don't have some dozens k's of roads.</div><div><br></div><div>I'm guessing you are not at will to send those roads?</div>
<div><br></div><div><br></div><div><div><font face="arial, sans-serif">The next step will be (when you'll be sure everything is OK)</font></div><span style="font-size:12.800000190734863px;font-family:arial,sans-serif"><div>
<br></div><div><br></div>CREATE TABLE lines_for_each_road AS</span><br style="font-size:12.800000190734863px;font-family:arial,sans-serif"><span style="font-size:12.800000190734863px;font-family:arial,sans-serif">WITH all_lines AS (</span><br style="font-size:12.800000190734863px;font-family:arial,sans-serif">
<span style="font-size:12.800000190734863px;font-family:arial,sans-serif">SELECT *</span><br style="font-size:12.800000190734863px;font-family:arial,sans-serif"><span style="font-size:12.800000190734863px;font-family:arial,sans-serif">FROM all_lines</span><br style="font-size:12.800000190734863px;font-family:arial,sans-serif">
<span style="font-size:12.800000190734863px;font-family:arial,sans-serif">),</span><div>cutted_lines AS ( --we cut the line to keep only part of lines inside road_buffer</div><div><div class="im" style="font-family:arial,sans-serif;font-size:12.800000190734863px">
SELECT ST_Intersection(<span style="color:rgb(34,34,34)">all_lines.the_geom,</span><span style="color:rgb(34,34,34)">ukmajrdbuffer.</span><span style="color:rgb(34,34,34)">geom) as lines_cutted, direction</span><br>FROM ukmajrdbuffer, all_lines<br>
</div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">WHERE ST_Intersects(ukmajrdbuffer.</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">geom, all_lines.the_geom)=TRUE </span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">),</span></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">cutted_lines_SN AS ( --this is the cutted lines which going from South to North</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">SELECT *</span></div><div>FROM cutted_lines </div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">WHERE direction = </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">'SN'</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">),</span></div><div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">cutted_lines_EW AS ( --this is the cutted lines going from East toWest</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">SELECT *</span></div><div>FROM cutted_lines </div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">WHERE direction = </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">'EW'</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">),</span></div></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">points AS ( -- we take the intersection of EW  lines with SN lines , that is the points on the grid.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">SELECT ST_Intersection(clSN.</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">lines_cutted</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">, </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">clEW.</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">lines_cutted</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">) AS point</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">FROM </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">cutted_lines_SN</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"> as clSN, </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">cutted_lines_EW</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"> AS clEW</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">WHERE ST_Intersects(</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">clSN.</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">lines_cutted</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">, </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">clEW.</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">lines_cutted</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">)=TRUE --no point ot compute an intersection if lines don't intersect</span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">)</span></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">SELECT </span><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:12.800000190734863px">row_number() over() AS id  , point </span></div>
<div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">FROM </span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px">points</span><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"> ;</span></div>
</div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"> </span></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"><br>
</span></div><div><span style="font-family:arial,sans-serif;font-size:12.800000190734863px"><br></span></div><div><br></div><div>Cheers,</div><div><br></div><div>Rémi-C</div><div><br></div></div><div class="gmail_extra"><br>
<br><div class="gmail_quote">2013/11/15 James David Smith <span dir="ltr"><<a href="mailto:james.david.smith@gmail.com" target="_blank">james.david.smith@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hey Remi,<br>
<br>
I'll do a few checks and get back to you. Maybe I did something wrong<br>
because I set this query going on my local PostgreSQL installation but<br>
also on our Linux Cluster machine which is much more powerful. And it<br>
finished on my local installation BEFORE the cluster. So maybe I did<br>
something wrong on the local query.<br>
<br>
The query that has finished took about 1 hour.<br>
The query on our cluster is still running.<br>
<br>
select count(*) from ukmajrdbuffer = 8<br>
This is because before I was given the data the roads buffer had<br>
already been dissolved unfortunately.<br>
<span class="HOEnZb"><font color="#888888"><br>
James<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<br>
On 15 November 2013 13:43, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
> Good !<br>
><br>
> Something is strange with the result,<br>
> you get only 190k lines intersecting road buffer,<br>
> it is very few , I expected at least 10times this!<br>
> How many time took this computing by the way?<br>
><br>
> How many road buffer geom do you have ? (select count(*) from ukmajrdbuffer<br>
> ).<br>
><br>
> Cheers,<br>
> Rémi-C<br>
><br>
><br>
> 2013/11/15 James David Smith <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>><br>
>><br>
>> Hey.<br>
>><br>
>> Yes, it's done. Was just getting some lunch! :-)<br>
>><br>
>> select count(*) from lines_for_each_road<br>
>> Result = 187033<br>
>><br>
>> I have also just ran 'VACUUM ANALYZE' on the tables<br>
>> 'lines_for_each_road' as well as the table 'all_lines'<br>
>><br>
>> I also can confirm that I have ran the following commands:<br>
>><br>
>> CREATE INDEX all_lines_index ON all_lines USING GIST ( the_geom )<br>
>> CREATE INDEX ukmajrdbuffer_index ON ukmajrdbuffer USING GIST (geom);<br>
>><br>
>> Should we now uncomment this line from the previous query?<br>
>><br>
>> "  SELECT row_number() over() AS id--,*  "<br>
>><br>
>> Thanks again Remi,<br>
>><br>
>> James<br>
>><br>
>><br>
>><br>
>> On 15 November 2013 13:14, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
>> > Also if you do have indexes,<br>
>> > can you run a "VACUUM ANALYZE", so that the indexes will be used?<br>
>> ><br>
>> > Cheers,<br>
>> ><br>
>> > Rémi-C<br>
>> ><br>
>> ><br>
>> > 2013/11/15 Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>><br>
>> >><br>
>> >> It should be finished by now,<br>
>> >> can you check you have geom indexes on :<br>
>> >> "ukmajrdbuffer.geom" and  "all_lines.the_geom"<br>
>> >><br>
>> >><br>
>> >> How many geoms do you have in "ukmajrdbuffer"?<br>
>> >><br>
>> >> Cheers,<br>
>> >> Rémi-C<br>
>> >><br>
>> >><br>
>> >> 2013/11/15 Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>><br>
>> >>><br>
>> >>> Hey Sandro,<br>
>> >>><br>
>> >>> Thanks for this, it is at least twice faster =)<br>
>> >>><br>
>> >>> Cheers,<br>
>> >>> Rémi-C<br>
>> >>><br>
>> >>><br>
>> >>><br>
>> >>><br>
>> >>> 2013/11/15 James David Smith <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>><br>
>> >>>><br>
>> >>>> Thanks both. Geometries now fixed.<br>
>> >>>><br>
>> >>>> The query 'CREATE TABLE lines_for_each_road....' has now been set<br>
>> >>>> running. Will report back when it's done. I suspect it may take a<br>
>> >>>> while!<br>
>> >>>><br>
>> >>>> James<br>
>> >>>><br>
>> >>>> On 15 November 2013 11:03, Sandro Santilli <<a href="mailto:strk@keybit.net">strk@keybit.net</a>> wrote:<br>
>> >>>> > On Fri, Nov 15, 2013 at 11:50:42AM +0100, Rémi Cura wrote:<br>
>> >>>> >> Yep, maybe something like<br>
>> >>>> >><br>
>> >>>> >> UPDATE ukmajrdbuffer SET the_geom = ST_MakeValid(the_geom)<br>
>> >>>> >> WHERE ST_IsValid(the_geom) = FALSE<br>
>> >>>> ><br>
>> >>>> > ST_MakeValid internally checks for ST_IsValid, so no need<br>
>> >>>> > to add the condition (which would run the test twice).<br>
>> >>>> ><br>
>> >>>> > --strk;<br>
>> >>>> > _______________________________________________<br>
>> >>>> > postgis-users mailing list<br>
>> >>>> > <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> >>>> > <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>> >>>> _______________________________________________<br>
>> >>>> postgis-users mailing list<br>
>> >>>> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> >>>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>> >>><br>
>> >>><br>
>> >><br>
>> ><br>
>> ><br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> > <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>