0

I have a table called geos which has two columns. geoName which is a string description of a physical area which is outlined by the spatial Polygon in the geoPoly column. Each record is either a country or a city which may be in one of those countries.

For each geoName, I want to get a list of the names of all intersecting polygons into a single column.

So if geos looks like this:

geoName geoPoly
Mumbai Polygon1
Chennai Polygon2
New York Polygon3
United States Polygon4
India Polygon5
Paris Polygon6
Chicago Polygon7
Los Angeles Polygon7

Using the query below, I'm able to get a table that returns just one result in the intersectingGeo column.

SELECT geos1.geoName AS targetGeo, geos2.geoName AS intersectingGeo
FROM geos geos1, geos geos2
WHERE ST_INTERSECTS(geos1.geoPoly, geos2.geoPoly)
AND geos1.geoName <> geos2.geoName
GROUP BY targetGeo

Which outputs:

geoName intersectingGeo
Mumbai India
Chennai India
New York United States
India Mumbai
Chicago United States
Chicago Los Angeles

But is it possible to get a concatenated list of the geoNames of all intersecting Polygons, like this?

geoName geoPoly
Mumbai India
Chennai India
New York United States
United States New York, Chicago, Los Angeles
India Chennai, Mumbai
Chicago United States

All of the CONCAT examples I can find have just one

AndrewG
  • 1
  • 3

1 Answers1

0

Just found the answer 30 seconds after posting. Obviously.

GROUP_CONCAT()

SELECT geos1.geoName AS targetGeo, GROUP_CONCAT(geos2.geoName SEPARATOR ', ') AS intersectingGeo
FROM geos geos1, geos geos2
WHERE ST_INTERSECTS(geos1.geoPoly, geos2.geoPoly)
AND geos1.geoName <> geos2.geoName
GROUP BY targetGeo

Works perfectly.

Can I concatenate multiple MySQL rows into one field?

AndrewG
  • 1
  • 3