[postgis-users] Grouping by geom with count?
James David Smith
james.david.smith at gmail.com
Mon Apr 28 01:54:27 PDT 2014
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
More information about the postgis-users
mailing list