[postgis-users] Grouping by geom with count?
Rémi Cura
remi.cura at gmail.com
Mon Apr 28 03:13:22 PDT 2014
Oh I forgot :
you may also need the "snaptogrid" function to regularly snap you data to
your data precision (eg 1 meter).
Again the idea is to limit numerical issues.
Cheers,
Rémi-C
2014-04-28 12:12 GMT+02:00 Rémi Cura <remi.cura at gmail.com>:
> It is a guess,
> but it could solve a numerical-precision problem (
> http://en.wikipedia.org/wiki/Rounding_error).
>
> Basically when computing with classical computer, you have a limited
> number of digits to express a number.
> For instance the number 10/3 would be represented as
> 0.3333334 as a float, or 0.333333333334 as a double (conceptually).
> So if you do operations on big numbers you might get unexpected result
> (conceptually : the two points
> (-530000,005 ,-178000,005) and (-530000,006 ,-178000,006) may be seen
> identical by some functions and different by others.).
>
> So it may be worth to give a try to
> http://postgis.net/docs/ST_Translate.html
> just use it before doing any actual computation (ie after spatial
> filtering like dwithin or like).
> This way when computing your points will have less digits.
> After computing just translate it back.
>
> Cheers,
> Rémi-C
>
>
> 2014-04-28 11:40 GMT+02:00 James David Smith <james.david.smith at gmail.com>
> :
>
> Hey Remi,
>>
>> I don't understand what you mean? Why do I need to translate my data?
>>
>> Thanks
>>
>> James
>>
>> On 28 April 2014 10:25, Rémi Cura <remi.cura at gmail.com> wrote:
>> > I'm obsessed with precision,
>> > so I would say translate your data t = ( -530000 ,-178000)
>> > Cheers,
>> > Rémi-C
>> >
>> >
>> > 2014-04-28 10:54 GMT+02:00 James David Smith <
>> james.david.smith at gmail.com>:
>> >
>> >> Hey all,
>> >>
>> >> I decided to push ahead with trying to make a table for this
>> >> mini-project, rather than rely on QGIS styles. I can explain why if
>> >> anyone is interested. However when I run the query that Hugues hepled
>> >> with, I get the error:
>> >>
>> >> ERROR: GEOSUnaryUnion: TopologyException: found non-noded
>> >> intersection between LINESTRING (530395 178004, 530396 178004) and
>> >> LINESTRING (530396 178004, 530396 178004) at 530395.54888857342
>> >> 178004.12613484744
>> >> ********** Error **********
>> >>
>> >> The query I am using is below. Any ideas?
>> >>
>> >> CREATE TABLE lambeth_unique AS (WITH inter AS (
>> >> SELECT DISTINCT a.ssid,
>> >> ST_CollectionExtract(st_intersection(a.the_geom, b.the_geom),2)
>> >> the_geom
>> >> FROM stage a, stage b
>> >> WHERE a.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13
>> >> AND bad_flag IS NULL)
>> >> AND b.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13 AND
>> >> bad_flag IS NULL)
>> >> ),
>> >> inter_line AS (
>> >> SELECT ssid,
>> >> ST_UNION (the_geom) the_geom
>> >> FROM inter
>> >> GROUP BY ssid),
>> >> diff_line AS (
>> >> SELECT a.ssid,
>> >> st_union(st_collectionextract(st_difference(a.the_geom, b.the_geom),2))
>> >> the_geom
>> >> FROM stage a, inter_line b
>> >> WHERE a.ssid=b.ssid AND NOT st_equals(a.the_geom, b.the_geom)d
>> >> GROUP BY a.ssid
>> >> ),
>> >> all_lines AS (
>> >> SELECT *
>> >> FROM inter_line
>> >> UNION ALL
>> >> SELECT * FROM diff_line
>> >> )
>> >> SELECT the_geom,
>> >> count(*)
>> >> FROM all_lines
>> >> GROUP BY the_geom)
>> >>
>> >> Best wishes
>> >>
>> >> James
>> >>
>> >>
>> >>
>> >> On 25 April 2014 08:56, James David Smith <james.david.smith at gmail.com
>> >
>> >> wrote:
>> >> > Thanks guys, I'll give that a crack later.
>> >> >
>> >> > //JDS
>> >> >
>> >> > On 25 Apr 2014 08:33, "Rémi Cura" <remi.cura at gmail.com> wrote:
>> >> >>
>> >> >> Hey,
>> >> >> If you just want visualisation it seem sa waste to do a lot of
>> >> >> computing,
>> >> >> (i.e if you will do no quantitative use )
>> >> >> simply load the linestring in QGIS (version >=2),
>> >> >> set the right width for the line in style and use the transparency
>> >> >> options
>> >> >> selecting the "darkening" rule for object vs object transparency.
>> >> >>
>> >> >> Cheers,
>> >> >> Rémi-C
>> >> >>
>> >> >>
>> >> >> 2014-04-24 23:50 GMT+02:00 Åsmund Tokheim <asmundto at gmail.com>:
>> >> >>>
>> >> >>> Hi
>> >> >>>
>> >> >>> Try changing "a.the_geom != b.the_geom" to "not
>> st_equals(a.the_geom,
>> >> >>> b.the_geom)". By the way, if all that you want is darker colours in
>> >> >>> QGIS,
>> >> >>> you could perhaps just play around with the opacity settings for
>> the
>> >> >>> lines.
>> >> >>>
>> >> >>> Åsmund
>> >> >>>
>> >> >>>
>> >> >>> On Thu, Apr 24, 2014 at 5:22 PM, James David Smith
>> >> >>> <james.david.smith at gmail.com> wrote:
>> >> >>>>
>> >> >>>> Thanks for the reply Hugues. Much appreciated. I've replaced the
>> >> >>>> various bit of the query with my own table names etc, but get an
>> >> >>>> error. Any thoughts/ideas guys?
>> >> >>>>
>> >> >>>> ERROR: operator is not unique: geometry <> geometry
>> >> >>>> LINE 15: WHERE a.ssid=b.ssid and a.the_geom != b.the_geom
>> >> >>>>
>> >> >>>> WITH inter AS (
>> >> >>>> SELECT DISTINCT a.ssid,
>> >> >>>> ST_CollectionExtract(st_intersection(a.the_geom, b.the_geom),2)
>> >> >>>> the_geom
>> >> >>>> FROM stage a, stage b
>> >> >>>> WHERE st_intersects(a.the_geom, b.the_geom)
>> >> >>>> ),
>> >> >>>> inter_line AS (
>> >> >>>> SELECT ssid, ST_UNION (the_geom) the_geom
>> >> >>>> FROM inter
>> >> >>>> GROUP BY ssid),
>> >> >>>> diff_line AS (
>> >> >>>> SELECT a.ssid,
>> >> >>>> st_union(st_collectionextract(st_difference(a.the_geom,
>> >> >>>> b.the_geom),2)) the_geom
>> >> >>>> FROM stage a, inter_line b
>> >> >>>> WHERE a.ssid=b.ssid and a.the_geom != b.the_geom
>> >> >>>> GROUP BY a.ssid
>> >> >>>> ),
>> >> >>>> all_lines AS (
>> >> >>>> SELECT * FROM inter_line
>> >> >>>>
>> >> >>>> UNION ALL
>> >> >>>>
>> >> >>>> SELECT * FROM diff_line
>> >> >>>> )
>> >> >>>>
>> >> >>>> SELECT the_geom, count(*) FROM all_lines
>> >> >>>> GROUP BY the_geom
>> >> >>>>
>> >> >>>>
>> >> >>>>
>> >> >>>>
>> >> >>>> On 24 April 2014 11:37, Hugues François <
>> hugues.francois at irstea.fr>
>> >> >>>> wrote:
>> >> >>>> > Hello,
>> >> >>>> >
>> >> >>>> > I think the use of st_intersection / st_difference may help you
>> to
>> >> >>>> > achieve that you will have to take care of duplicates. I think
>> the
>> >> >>>> > query
>> >> >>>> > could be something like the first draft below but could be
>> >> >>>> > improved. The
>> >> >>>> > principle is to find intersections from geometry from a self
>> join
>> >> >>>> > of your
>> >> >>>> > original table and use this output to get the difference with
>> your
>> >> >>>> > original
>> >> >>>> > table. Maybe someone else will have a better idea !
>> >> >>>> >
>> >> >>>> > HTH
>> >> >>>> >
>> >> >>>> > Hug
>> >> >>>> >
>> >> >>>> > WITH inter AS (
>> >> >>>> > SELECT DISTINCT a.gid,
>> >> >>>> > ST_CollectionExtract(st_intersection(a.geom, b.geom),2) geom
>> >> >>>> > FROM yourtable a, yourtable b
>> >> >>>> > WHERE st_intersects(a.geom, b.geom)
>> >> >>>> > ),
>> >> >>>> >
>> >> >>>> > inter_line AS (
>> >> >>>> > SELECT gid, ST_UNION (geom) geom
>> >> >>>> > FROM line_inter
>> >> >>>> > GROUP BY gid),
>> >> >>>> >
>> >> >>>> > diff_line AS (
>> >> >>>> > SELECT a.gid,
>> >> >>>> > st_union(st_collectionextract(st_difference(a.geom, b.geom),2))
>> >> >>>> > geom
>> >> >>>> > FROM yourtable a, inter_line b
>> >> >>>> > WHERE a.gid=b.gid and a.geom != b.geom
>> >> >>>> > GROUP BY a.gid
>> >> >>>> > ),
>> >> >>>> >
>> >> >>>> > all_lines AS (
>> >> >>>> > SELECT * FROM inter_line
>> >> >>>> >
>> >> >>>> > UNION ALL
>> >> >>>> >
>> >> >>>> > SELECT * FROM diff_line
>> >> >>>> > )
>> >> >>>> >
>> >> >>>> > SELECT geom, count(*) FROM all_lines
>> >> >>>> > GROUP BY geom
>> >> >>>> >
>> >> >>>> >
>> >> >>>> >
>> >> >>>> >
>> >> >>>> > -----Message d'origine-----
>> >> >>>> > De : postgis-users-bounces at lists.osgeo.org
>> >> >>>> > [mailto:postgis-users-bounces at lists.osgeo.org] De la part de
>> James
>> >> >>>> > David
>> >> >>>> > Smith
>> >> >>>> > Envoyé : jeudi 24 avril 2014 11:11
>> >> >>>> > À : PostGIS Users Discussion
>> >> >>>> > Objet : [postgis-users] Grouping by geom with count?
>> >> >>>> >
>> >> >>>> > Hi all,
>> >> >>>> >
>> >> >>>> > A bit of advice please. I have a table of about 250,000
>> linestring.
>> >> >>>> > They represent peoples routes on roads around London. I would
>> like
>> >> >>>> > to
>> >> >>>> > use them in QGIS now to show the most used roads by making them
>> a
>> >> >>>> > darker
>> >> >>>> > colour. So to do this I feel I need to do some sort of grouping
>> of
>> >> >>>> > the
>> >> >>>> > geometries with a count column too - so that I can use that
>> count
>> >> >>>> > column to
>> >> >>>> > define the darkness of the line on my map.
>> >> >>>> >
>> >> >>>> > How could I go about doing this please?
>> >> >>>> >
>> >> >>>> > The problem I can see in my head is that let's say I have one
>> >> >>>> > linestring which goes from A to B. Then another linestring that
>> >> >>>> > goes from A
>> >> >>>> > to B to C. When I group the geometries, these won't group as
>> they
>> >> >>>> > aren't the
>> >> >>>> > same. However I would want the result to be that linestring A
>> to B
>> >> >>>> > is given
>> >> >>>> > a value of 2 and the bit of the line from B to C would be given
>> a
>> >> >>>> > value of
>> >> >>>> > 1.
>> >> >>>> >
>> >> >>>> > I think I'm perhaps overcomplicating this...
>> >> >>>> >
>> >> >>>> > Thanks
>> >> >>>> >
>> >> >>>> > James
>> >> >>>> > _______________________________________________
>> >> >>>> > 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/20140428/7ccb2173/attachment.html>
More information about the postgis-users
mailing list