'[postgis-users] =?iso-8859-1?q?R=E9p=3A_Re=3A_R=E9p=3A_Re=3A_Grou?= =?iso-8859-1?q?ping_by_geom_with'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] =?iso-8859-1?q?R=E9p=3A_Re=3A_R=E9p=3A_Re=3A_Grou?= =?iso-8859-1?q?ping_by_geom_with
From:       Hugues François <hugues.francois () irstea ! fr>
Date:       2014-04-24 15:44:07
Message-ID: 094f01cf5fdd$3f062139$5568ddc3 () cemagref ! fr
[Download RAW message or body]

Instead of a.the_geom != b.the_geom you should write not \
st_equals(a.the_geom,b.the_geom). 

Hug

James David Smith <james.david.smith@gmail.com> a écrit :

No problem. I'm not quite sure how to work that into the query though please?

On 24 April 2014 16:27, Hugues François <hugues.francois@irstea.fr> wrote:
> Excuse me for the mistake. Let's try st_equals.
> 
> Hug
> 
> James David Smith <james.david.smith@gmail.com> a écrit :
> 
> 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@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@lists.osgeo.org \
> > [mailto:postgis-users-bounces@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@lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 