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
[email protected] <mailto:[email protected]>
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
