<div dir="ltr">Still is a shame : <div>with proper data we should have some result with about one hour i guess.</div><div><br></div><div>Cheers,</div><div>Rémi C</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">
Remi,<br>
<br>
Ok. Cool. I've set the below query running. On Monday I will also<br>
attempt to get the original road lines file too. Let's see if we have<br>
a result on Monday.<br>
----------------------------------<br>
<div><div class="h5">CREATE TABLE lines_for_each_road AS<br>
WITH all_lines AS (<br>
SELECT *<br>
FROM all_lines<br>
),<br>
cutted_lines AS ( --we cut the line to keep only part of lines inside<br>
road_buffer<br>
SELECT ST_Intersection(all_lines.the_geom,ukmajrdbuffer.geom) as<br>
lines_cutted, direction<br>
FROM ukmajrdbuffer, all_lines<br>
WHERE ST_Intersects(ukmajrdbuffer.geom, all_lines.the_geom)=TRUE<br>
),<br>
cutted_lines_SN AS ( --this is the cutted lines which going from South to North<br>
SELECT *<br>
FROM cutted_lines<br>
WHERE direction = 'SN'<br>
),<br>
cutted_lines_EW AS ( --this is the cutted lines going from East toWest<br>
SELECT *<br>
FROM cutted_lines<br>
WHERE direction = 'EW'<br>
),<br>
points AS ( -- we take the intersection of EW lines with SN lines ,<br>
that is the points on the grid.<br>
SELECT ST_Intersection(clSN.lines_cutted, clEW.lines_cutted) AS point<br>
FROM cutted_lines_SN as clSN, cutted_lines_EW AS clEW<br>
WHERE ST_Intersects(clSN.lines_cutted, clEW.lines_cutted)=TRUE --no<br>
point ot compute an intersection if lines don't intersect<br>
)<br>
SELECT row_number() over() AS id , point<br>
FROM points ;<br>
</div></div>------------------------------------------<br>
Thanks<br>
<span class="HOEnZb"><font color="#888888"><br>
James<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
On 15 November 2013 15:37, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
><br>
> You should try to get the original road file, because i will be very easy to<br>
> reapply some buffer and filtering to keep only major road.<br>
> Yet, why not let it run during the week end?<br>
><br>
> Cheers,<br>
><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 Remi,<br>
>><br>
>> Do you think before I try running the big query you have just sent me,<br>
>> that I should go back and try to get the original file of uk roads? I<br>
>> mean the very original file that has not had any buffers applied or<br>
>> any merging done.<br>
>><br>
>> Or shall we just go for it and see if it has finished when I come into<br>
>> work on Monday?! haha.<br>
>><br>
>> Thanks<br>
>><br>
>> James<br>
>><br>
>> On 15 November 2013 14:01, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
>> > Outch,<br>
>> > you have only 8 roads in GB? =)<br>
>> ><br>
>> > This is not going to go fast till you don't have some dozens k's of<br>
>> > roads.<br>
>> ><br>
>> > I'm guessing you are not at will to send those roads?<br>
>> ><br>
>> ><br>
>> > The next step will be (when you'll be sure everything is OK)<br>
>> ><br>
>> ><br>
>> > CREATE TABLE lines_for_each_road AS<br>
>> > WITH all_lines AS (<br>
>> > SELECT *<br>
>> > FROM all_lines<br>
>> > ),<br>
>> > cutted_lines AS ( --we cut the line to keep only part of lines inside<br>
>> > road_buffer<br>
>> > SELECT ST_Intersection(all_lines.the_geom,ukmajrdbuffer.geom) as<br>
>> > lines_cutted, direction<br>
>> > FROM ukmajrdbuffer, all_lines<br>
>> > WHERE ST_Intersects(ukmajrdbuffer.geom, all_lines.the_geom)=TRUE<br>
>> > ),<br>
>> > cutted_lines_SN AS ( --this is the cutted lines which going from South<br>
>> > to<br>
>> > North<br>
>> > SELECT *<br>
>> > FROM cutted_lines<br>
>> > WHERE direction = 'SN'<br>
>> > ),<br>
>> > cutted_lines_EW AS ( --this is the cutted lines going from East toWest<br>
>> > SELECT *<br>
>> > FROM cutted_lines<br>
>> > WHERE direction = 'EW'<br>
>> > ),<br>
>> > points AS ( -- we take the intersection of EW lines with SN lines ,<br>
>> > that is<br>
>> > the points on the grid.<br>
>> > SELECT ST_Intersection(clSN.lines_cutted, clEW.lines_cutted) AS point<br>
>> > FROM cutted_lines_SN as clSN, cutted_lines_EW AS clEW<br>
>> > WHERE ST_Intersects(clSN.lines_cutted, clEW.lines_cutted)=TRUE --no<br>
>> > point ot<br>
>> > compute an intersection if lines don't intersect<br>
>> > )<br>
>> > SELECT row_number() over() AS id , point<br>
>> > FROM points ;<br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> > Cheers,<br>
>> ><br>
>> > Rémi-C<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>
>> >> 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>
>> >><br>
>> >> James<br>
>> >><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<br>
>> >> > 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<br>
>> >> >> >>>> set<br>
>> >> >> >>>> running. Will report back when it's done. I suspect it may take<br>
>> >> >> >>>> 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>><br>
>> >> >> >>>> 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>
>> ><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>