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