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