You should try to get the original road file, because i will be very easy to reapply some buffer and filtering to keep only major road. Yet, why not let it run during the week end?
Cheers, Rémi-C 2013/11/15 James David Smith <james.david.sm...@gmail.com> > Hey Remi, > > Do you think before I try running the big query you have just sent me, > that I should go back and try to get the original file of uk roads? I > mean the very original file that has not had any buffers applied or > any merging done. > > Or shall we just go for it and see if it has finished when I come into > work on Monday?! haha. > > Thanks > > James > > On 15 November 2013 14:01, Rémi Cura <remi.c...@gmail.com> wrote: > > Outch, > > you have only 8 roads in GB? =) > > > > This is not going to go fast till you don't have some dozens k's of > roads. > > > > I'm guessing you are not at will to send those roads? > > > > > > The next step will be (when you'll be sure everything is OK) > > > > > > CREATE TABLE lines_for_each_road AS > > WITH all_lines AS ( > > SELECT * > > FROM all_lines > > ), > > cutted_lines AS ( --we cut the line to keep only part of lines inside > > road_buffer > > SELECT ST_Intersection(all_lines.the_geom,ukmajrdbuffer.geom) as > > lines_cutted, direction > > FROM ukmajrdbuffer, all_lines > > WHERE ST_Intersects(ukmajrdbuffer.geom, all_lines.the_geom)=TRUE > > ), > > cutted_lines_SN AS ( --this is the cutted lines which going from South to > > North > > SELECT * > > FROM cutted_lines > > WHERE direction = 'SN' > > ), > > cutted_lines_EW AS ( --this is the cutted lines going from East toWest > > SELECT * > > FROM cutted_lines > > WHERE direction = 'EW' > > ), > > points AS ( -- we take the intersection of EW lines with SN lines , > that is > > the points on the grid. > > SELECT ST_Intersection(clSN.lines_cutted, clEW.lines_cutted) AS point > > FROM cutted_lines_SN as clSN, cutted_lines_EW AS clEW > > WHERE ST_Intersects(clSN.lines_cutted, clEW.lines_cutted)=TRUE --no > point ot > > compute an intersection if lines don't intersect > > ) > > SELECT row_number() over() AS id , point > > FROM points ; > > > > > > > > > > > > Cheers, > > > > Rémi-C > > > > > > > > 2013/11/15 James David Smith <james.david.sm...@gmail.com> > >> > >> Hey Remi, > >> > >> I'll do a few checks and get back to you. Maybe I did something wrong > >> because I set this query going on my local PostgreSQL installation but > >> also on our Linux Cluster machine which is much more powerful. And it > >> finished on my local installation BEFORE the cluster. So maybe I did > >> something wrong on the local query. > >> > >> The query that has finished took about 1 hour. > >> The query on our cluster is still running. > >> > >> select count(*) from ukmajrdbuffer = 8 > >> This is because before I was given the data the roads buffer had > >> already been dissolved unfortunately. > >> > >> James > >> > >> > >> > >> > >> On 15 November 2013 13:43, Rémi Cura <remi.c...@gmail.com> wrote: > >> > Good ! > >> > > >> > Something is strange with the result, > >> > you get only 190k lines intersecting road buffer, > >> > it is very few , I expected at least 10times this! > >> > How many time took this computing by the way? > >> > > >> > How many road buffer geom do you have ? (select count(*) from > >> > ukmajrdbuffer > >> > ). > >> > > >> > Cheers, > >> > Rémi-C > >> > > >> > > >> > 2013/11/15 James David Smith <james.david.sm...@gmail.com> > >> >> > >> >> Hey. > >> >> > >> >> Yes, it's done. Was just getting some lunch! :-) > >> >> > >> >> select count(*) from lines_for_each_road > >> >> Result = 187033 > >> >> > >> >> I have also just ran 'VACUUM ANALYZE' on the tables > >> >> 'lines_for_each_road' as well as the table 'all_lines' > >> >> > >> >> I also can confirm that I have ran the following commands: > >> >> > >> >> CREATE INDEX all_lines_index ON all_lines USING GIST ( the_geom ) > >> >> CREATE INDEX ukmajrdbuffer_index ON ukmajrdbuffer USING GIST (geom); > >> >> > >> >> Should we now uncomment this line from the previous query? > >> >> > >> >> " SELECT row_number() over() AS id--,* " > >> >> > >> >> Thanks again Remi, > >> >> > >> >> James > >> >> > >> >> > >> >> > >> >> On 15 November 2013 13:14, Rémi Cura <remi.c...@gmail.com> wrote: > >> >> > Also if you do have indexes, > >> >> > can you run a "VACUUM ANALYZE", so that the indexes will be used? > >> >> > > >> >> > Cheers, > >> >> > > >> >> > Rémi-C > >> >> > > >> >> > > >> >> > 2013/11/15 Rémi Cura <remi.c...@gmail.com> > >> >> >> > >> >> >> It should be finished by now, > >> >> >> can you check you have geom indexes on : > >> >> >> "ukmajrdbuffer.geom" and "all_lines.the_geom" > >> >> >> > >> >> >> > >> >> >> How many geoms do you have in "ukmajrdbuffer"? > >> >> >> > >> >> >> Cheers, > >> >> >> Rémi-C > >> >> >> > >> >> >> > >> >> >> 2013/11/15 Rémi Cura <remi.c...@gmail.com> > >> >> >>> > >> >> >>> Hey Sandro, > >> >> >>> > >> >> >>> Thanks for this, it is at least twice faster =) > >> >> >>> > >> >> >>> Cheers, > >> >> >>> Rémi-C > >> >> >>> > >> >> >>> > >> >> >>> > >> >> >>> > >> >> >>> 2013/11/15 James David Smith <james.david.sm...@gmail.com> > >> >> >>>> > >> >> >>>> Thanks both. Geometries now fixed. > >> >> >>>> > >> >> >>>> The query 'CREATE TABLE lines_for_each_road....' has now been > set > >> >> >>>> running. Will report back when it's done. I suspect it may take > a > >> >> >>>> while! > >> >> >>>> > >> >> >>>> James > >> >> >>>> > >> >> >>>> On 15 November 2013 11:03, Sandro Santilli <s...@keybit.net> > >> >> >>>> wrote: > >> >> >>>> > On Fri, Nov 15, 2013 at 11:50:42AM +0100, Rémi Cura wrote: > >> >> >>>> >> Yep, maybe something like > >> >> >>>> >> > >> >> >>>> >> UPDATE ukmajrdbuffer SET the_geom = ST_MakeValid(the_geom) > >> >> >>>> >> WHERE ST_IsValid(the_geom) = FALSE > >> >> >>>> > > >> >> >>>> > ST_MakeValid internally checks for ST_IsValid, so no need > >> >> >>>> > to add the condition (which would run the test twice). > >> >> >>>> > > >> >> >>>> > --strk; > >> >> >>>> > _______________________________________________ > >> >> >>>> > postgis-users mailing list > >> >> >>>> > postgis-users@lists.osgeo.org > >> >> >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> >> >>>> _______________________________________________ > >> >> >>>> postgis-users mailing list > >> >> >>>> postgis-users@lists.osgeo.org > >> >> >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> >> >>> > >> >> >>> > >> >> >> > >> >> > > >> >> > > >> >> > _______________________________________________ > >> >> > postgis-users mailing list > >> >> > postgis-users@lists.osgeo.org > >> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> >> _______________________________________________ > >> >> postgis-users mailing list > >> >> postgis-users@lists.osgeo.org > >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > > >> > > >> > > >> > _______________________________________________ > >> > postgis-users mailing list > >> > postgis-users@lists.osgeo.org > >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> _______________________________________________ > >> postgis-users mailing list > >> postgis-users@lists.osgeo.org > >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > > > > _______________________________________________ > > postgis-users mailing list > > postgis-users@lists.osgeo.org > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users