<div dir="ltr">This would be an improvement but still non efficient.<div><br></div><div>you have 2 possibilities, supposing that what you want is points 20 meter spaced in all road_buf : </div><div><br></div><div>either you compute for each road_buffer the points inside, one road at a time ( figuratively ).<br>
</div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>This means you write a function which generates points 20-meter spaced in the bounding box of the road you are working on, and keep those in the real road buffer, and group result points as a multipoints (for cosmetic purpose)</div>
<div>.</div><div><br></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div>You would then use it like this : </div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px">
<div>SELECT road_id, points_insidea_road(the_geom) AS my_points_inside_the_road</div><div>FROM <span style="font-size:12.800000190734863px;font-family:arial,sans-serif">road_polygons_</span><span style="font-size:12.800000190734863px;font-family:arial,sans-serif">table</span></div>
<div><span style="font-size:12.800000190734863px;font-family:arial,sans-serif"><br></span></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div><span style="font-size:12.800000190734863px;font-family:arial,sans-serif">You would have as output a line per road with a multipoint containing all the point 20 meter spaced inside the road.</span></div>
<div><span style="font-size:12.800000190734863px;font-family:arial,sans-serif"><br></span></div></blockquote><font face="arial, sans-serif">Or (what you wrote) you generate all points and keep those intersecting one or many road.</font><div>
<font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">The first one is mandatory, because it avoids to manipulate (incredibly) big table of all points spaced by 20 meters for UK (around 500 * 10^6 points ! )</font></div>
<div><font face="arial, sans-serif">Even with indexes it's not a good idea to use such number of rows.</font></div><div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">That's the first point (write a function working for one road, then use it for all road).</font></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">The second point is the way you compute is very inefficient. If your road is going from south-West to NorthEast, you will generate a very big number of points, and very few will be in the road_buffer. This is problematic as for a road of only 20 kms, you may generate as many as 100k points and keep only few of them. If you want to process hundreds of ks or roads it will become very problematic. Also you would have to generate points each time.</font></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">So here is what I suggest you : change your strategy :</font></div><div><font face="arial, sans-serif"> instead of generating all point in bounding box and then keeping only those in road_buffer,</font></div>
<div><font face="arial, sans-serif">generate a line every 20 meters going North south and an line every 20 meters going East-West , then use the function ST_Intersection to keep only part of this lines being inside the road_polygon, then you have the points inside road_polygons as the intersections of these EW lines with the SN lines.</font></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">It will be very efficient because you can create a table with all the lines going East-West and South-North for great britain (about 25k + 50k lines), and build index on it (index on geom and on the column saying if it is SN or EW).</font></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">The trick is the number of lines is around 500km * 50 line/km + 1000</font><span style="font-family:arial,sans-serif">km * 50 line/km , where the number of points is </span><font face="arial, sans-serif"> 500km * 50 line/km <b><font size="4">*</font></b> 1000</font><span style="font-family:arial,sans-serif">km * 50 line/km</span></div>
<div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">Hope it helps,</font></div><div><font face="arial, sans-serif"><br></font></div><div><font face="arial, sans-serif">Cheers,</font></div>
<div><font face="arial, sans-serif">Rémi-C<br></font><div><div><br></div><div><div><br></div><div><br></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/11/13 James David Smith <span dir="ltr"><<a href="mailto:james.david.smith@gmail.com" target="_blank">james.david.smith@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hey Remi,<br>
<br>
Thanks for your reply. So in your mind you think we should have a<br>
database of say 300 polygons, and then we run a command like this<br>
right?<br>
<br>
SELECT<br>
ST_Collect(st_setsrid(ST_POINT(x,y),27700))<br>
FROM<br>
generate_series(53320::int, 667380::int,20) as x,<br>
generate_series(7780::int, 1226580::int,20) as y,<br>
road_polygons_table<br>
WHERE<br>
st_intersects(road_polygons_table.the_geom, st_setsrid(ST_POINT(x,y),27700))<br>
<br>
What do you think?<br>
<br>
Thanks<br>
<span class="HOEnZb"><font color="#888888"><br>
James<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<br>
On 11 November 2013 14:51, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
> Hey,<br>
> the whole point on using a sgbds like postgis is using index.<br>
><br>
> If you have one line you don't use indexes...<br>
><br>
> So in short, don't make one polygon with a buffer of all the road, but a<br>
> table with a line for the buffer for every road, then do you computation to<br>
> create grid of points inside of polygons, then union the result of points!<br>
><br>
> And it s always a bad idea to run a function on big data when you have not<br>
> tested it fully (including scaling behavior) on small data.<br>
><br>
><br>
> Cheers<br>
> Rémi-C<br>
><br>
><br>
> 2013/11/11 James David Smith <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>><br>
>><br>
>> Hi all,<br>
>><br>
>> Would appreciate some advice on the best way to accomplish this please.<br>
>><br>
>> Our situation is that we have a single polygon which has been created<br>
>> by buffering all of the major roads in the UK. Projection is OSGB36<br>
>> (27700). Obviously it's quite a big polygon.<br>
>><br>
>> --> SELECT st_area(geom) FROM roadbufferunion;<br>
>> st_area<br>
>> ------------------<br>
>> 77228753220.8271<br>
>><br>
>> What we now want to do is create a regular grid of 20 metre x 20 metre<br>
>> points instead the polygon area. So we wrote this function (based on<br>
>> some googling, apologies for not being able to recall the exact person<br>
>> who originally wrote it):<br>
>><br>
>> CREATE OR REPLACE FUNCTION makegrid(geometry, integer, integer)<br>
>> RETURNS geometry AS<br>
>> 'SELECT ST_Collect(st_setsrid(ST_POINT(x,y),$3)) FROM<br>
>> generate_series(53320::int, 667380::int,$2) as x<br>
>> ,generate_series(7780::int, 1226580::int,$2) as y<br>
>> where st_intersects($1,st_setsrid(ST_POINT(x,y),$3))'<br>
>> LANGUAGE sql<br>
>><br>
>> and we then run this by doing the following:<br>
>><br>
>> SELECT st_x((ST_Dump(makegrid(geom, 20, 27700))).geom) as x,<br>
>> st_y((ST_Dump(makegrid(geom, 20, 27700))).geom) as y INTO grid_points<br>
>> from roadbufferunion;<br>
>><br>
>> However after over 2 days of the query running on a pretty powerful<br>
>> linux cluster, we still have no result. I'm not sure if it is<br>
>> actually running or not to be honest.<br>
>><br>
>> Does the query look right?<br>
>> Any ideas how we can make it run quicker?<br>
>><br>
>> Thanks<br>
>><br>
>> James<br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>