Hello, I agree Stephen : arrays should do the job.
Note that array functions are here : http://www.postgresql.org/docs/9.1/static/functions-array.html But array_agg is in the aggregate functions part of the doc : http://www.postgresql.org/docs/9.1/static/functions-aggregate.html Hugues -------- Message d'origine-------- De: [email protected] de la part de Stephen Woodbridge Date: sam. 23/03/2013 13:46 À: [email protected] Objet : Re: [postgis-users] spatial join and summarize, list all names 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
<<winmail.dat>>
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
