[postgis-users] Grouping by geom with count?
Rémi Cura
remi.cura at gmail.com
Mon Apr 28 02:25:14 PDT 2014
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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140428/76cb8d5f/attachment.html>
More information about the postgis-users
mailing list