[postgis-users] Grouping by geom with count?
Rémi Cura
remi.cura at gmail.com
Mon Apr 28 03:12:04 PDT 2014
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/db79ed25/attachment.html>
More information about the postgis-users
mailing list