Remi, Ok. Cool. I've set the below query running. On Monday I will also attempt to get the original road lines file too. Let's see if we have a result on Monday. ---------------------------------- 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 ; ------------------------------------------ Thanks
James On 15 November 2013 15:37, Rémi Cura <[email protected]> wrote: > > 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 <[email protected]> >> >> 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 <[email protected]> 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 <[email protected]> >> >> >> >> 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 <[email protected]> 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 <[email protected]> >> >> >> >> >> >> 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 <[email protected]> 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 <[email protected]> >> >> >> >> >> >> >> >> 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 <[email protected]> >> >> >> >>> >> >> >> >>> Hey Sandro, >> >> >> >>> >> >> >> >>> Thanks for this, it is at least twice faster =) >> >> >> >>> >> >> >> >>> Cheers, >> >> >> >>> Rémi-C >> >> >> >>> >> >> >> >>> >> >> >> >>> >> >> >> >>> >> >> >> >>> 2013/11/15 James David Smith <[email protected]> >> >> >> >>>> >> >> >> >>>> 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 <[email protected]> >> >> >> >>>> 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 >> >> >> >>>> > [email protected] >> >> >> >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >>>> _______________________________________________ >> >> >> >>>> postgis-users mailing list >> >> >> >>>> [email protected] >> >> >> >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >>> >> >> >> >>> >> >> >> >> >> >> >> > >> >> >> > >> >> >> > _______________________________________________ >> >> >> > postgis-users mailing list >> >> >> > [email protected] >> >> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> _______________________________________________ >> >> >> postgis-users mailing list >> >> >> [email protected] >> >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> > >> >> > >> >> > >> >> > _______________________________________________ >> >> > postgis-users mailing list >> >> > [email protected] >> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> _______________________________________________ >> >> postgis-users mailing list >> >> [email protected] >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > >> > >> > >> > _______________________________________________ >> > postgis-users mailing list >> > [email protected] >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
