[postgis-users] Grid of points inside Polygon
James David Smith
james.david.smith at gmail.com
Fri Nov 15 08:26:53 PST 2013
Hey Remi,
I've actually managed to get the file 'ukmajorroads' already and have
loaded it into my database. There are 395356 rows in the database. There
is a field called 'geom' and I have built an index on it as below;
CREATE INDEX ukrds_index ON ukrds USING GIST (geom);
So should I now run the same query, but do the buffering of the roads 'on
the fly' maybe? For example as below?
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,st_buffer(ukmajrdbuffer.geom,
1000)) as lines_cutted, direction
FROM ukmajrdbuffer, all_lines
WHERE ST_Intersects(st_buffer(ukmajrdbuffer.geom, 1000),
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 ;
On 15 November 2013 15:42, Rémi Cura <remi.cura at gmail.com> wrote:
> 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
>>
>
>
> _______________________________________________
> 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/bd6e84dc/attachment.html>
More information about the postgis-users
mailing list