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:
Spatial Knowledge, my personal blog.
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.
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.
UPDATE (SELECT [Companies], [Company].[Company]
FROM [Circle], [Company] WHERE Contains([Circle].[ID], [Company].[ID]))
SET [Companies] = [Companies] & CHR(10) & [Company]
A recursive subquery! Of course!
You're a genius! :)
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.)
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.
Thx - but it's no real recursion. Call it a self-made aggregat query. Like Count(*) would be replaced by ... SET [Counter] = [Counter] + 1.
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...
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 :-)