[postgis-users] Grid of points inside Polygon

[Rémi Cura]

Still is a shame :
with proper data we should have some result with about one hour i guess.

Cheers,
Rémi C


2013/11/15 James David Smith <james.david.smith at gmail.com>

> 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 <remi.cura at gmail.com> 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 <james.david.smith at 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.cura at 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.smith at 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.cura at 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.smith at 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.cura at 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.cura at 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.cura at 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.smith at 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 <strk at 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 at lists.osgeo.org
> >> >> >> >>>> >
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >> >> >>>> _______________________________________________
> >> >> >> >>>> postgis-users mailing list
> >> >> >> >>>> postgis-users at lists.osgeo.org
> >> >> >> >>>>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >> >> >>>
> >> >> >> >>>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >> > _______________________________________________
> >> >> >> > postgis-users mailing list
> >> >> >> > postgis-users at lists.osgeo.org
> >> >> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >> >> _______________________________________________
> >> >> >> postgis-users mailing list
> >> >> >> postgis-users at lists.osgeo.org
> >> >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >> >
> >> >> >
> >> >> >
> >> >> > _______________________________________________
> >> >> > postgis-users mailing list
> >> >> > postgis-users at lists.osgeo.org
> >> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >> _______________________________________________
> >> >> postgis-users mailing list
> >> >> postgis-users at lists.osgeo.org
> >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >
> >> >
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at lists.osgeo.org
> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131115/f3ce9364/attachment.html>