[postgis-users] spatial join and summarize, list all names

[Stephen Woodbridge]

How about something like:

SELECT p.p_name,
     array_to_string(ARRAY_AGG(Grade.Mark),';') As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

Aggregate into an array and then convert the array to a string. Look at 
the array functions in the manual for more information.

-Steve W

On 3/22/2013 7:20 PM, Tyler Frazier wrote:
> Hello,
>
> I'm pretty new to postgreSQL / postGIS and am running with pgadmin and
> using with QGIS and R.
>
> I am attempting to perform a Spatial Join of 3000 thousand towns
> (points) to 1000 enumeration areas (polygons) where the name of each
> town which shares a common geometry with its enumeration area is
> included as an attribute in a field.  In doing so, I have started with
> the following command.
>
> SELECT towns_r1.name, towns_r1.twn_ea_nos,
>                   ea_r1.gid, ea_r1.ea_nos, ea_r1.the_geom
> INTO TABLE region1_eas
> FROM towns_r1, ea_r1
> WHERE ST_Within(towns_r1.the_geom, ea_r1.the_geom);
>
> which gives me a table of 3000 enumerations areas, with the name of each
> individual town and the geometry for each enumeration area repeated,
> depending on how many towns were located within it.
>
> What I am attempting to achieve, is to summarize or aggregate to 1000
> enumeration areas, where each row has a field (single field the names of
> all towns listed within?), which includes the name of each town located
> within that enumeration area.
>
> any pointers on how to proceed?
>
> Thanks!
> Ty
>
>
> Tyler Frazier, Postdoctoral Fellow
> Santa Fe Institute
> tyler at santafe.edu <mailto:tyler at santafe.edu>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>