georeference.org
Subscribe to this thread
Home - General / All posts - Aggregating Labels using Spatial SQL
petzlux

1,002 post(s)
#22-Mar-07 09:49

Hi

I have a set of points (Company locations which have their name as a variable) that are clustered and I have drawn in a different layer a circle which encloses all of these points. Now I want to merge these points into one record, and concatenate the company names. This would allow me to aggregate labels and display one legible label which contains the company names instead of 10 point labels which overlap and are not clear.

Can I write a Spatial SQL query that will put out a new record with the concatenated company names?

I have attached a image to make my example hopefully clearer:

Attachments:
Clipboard01.jpg


Spatial Knowledge, my personal blog.

mechalas

806 post(s)
#22-Mar-07 10:30

I don't believe so. While doing the spatial query to select the points is trivial, SQL in Manifold does not have a function to concatenate the values from a column (you need something akin to the GROUP_CONCAT function that's in MySQL), and I don't believe it supports cursors, either. The only way that I can think of to do this, then, would be to run the query, then write a script to read the resulting table and build the string that way.

KlausDE

5,615 post(s)
#22-Mar-07 13:15

You don't believe in SQL? That calls for objection!

Asuming you have a Drawing [Company] with a column [Company] and a Drawing [Circle] with a column [Companies] - the one you want to use for the sum of labels, empty before you run this query.

--SQL

UPDATE (SELECT [Companies][Company].[Company] 

        FROM [Circle][Company] WHERE Contains([Circle].[ID][Company].[ID]))

SET [Companies] = [Companies] & CHR(10) & [Company]

mechalas

806 post(s)
#22-Mar-07 13:29

A recursive subquery! Of course!

You're a genius! :)

tjhb

4,712 post(s)
online
#22-Mar-07 14:31

Seconded. (I was goaded into having a go too, but failed completely. Didn't think of anything in the same galaxy as Klaus's code.)

petzlux

1,002 post(s)
#23-Mar-07 03:43

Wow I am amazed! This worked flawless, exactly what I needed.

The supreme mastery of SQL in Manifold really gives one almost limitless powers :-)

We should introduce black belts in Manifold SQL, to be awarded to people like KlausDE.

Many thanks


Spatial Knowledge, my personal blog.

KlausDE

5,615 post(s)
#23-Mar-07 06:48

Thx - but it's no real recursion. Call it a self-made aggregat query. Like Count(*) would be replaced by ... SET [Counter] = [Counter] + 1.

pcardoso


1,658 post(s)
#23-Mar-07 06:55

Very interesting thread.

May I ask you how you define the circles, any automatic way to buffer zones? This is feasible manually for a few "clusters" but for entire London...

petzlux

1,002 post(s)
#23-Mar-07 09:00

pcardoso

Purely visual as I need a neat layout for some marketing material i need to produce. "Clustering" basically is anything we can think to get away with :-)


Spatial Knowledge, my personal blog.

Manifold User Community Use Agreement Copyright (C) 2007-2011 Manifold Software Limited. All rights reserved.