[postgis-users] Grid of points inside Polygon

James David Smith james.david.smith at gmail.com
Fri Nov 15 05:48:02 PST 2013


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


More information about the postgis-users mailing list