[postgis-users] Grid of points inside Polygon

[Rémi Cura]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131115/e69fb482/attachment.html>