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

[Tyler Frazier]

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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130322/65f93332/attachment.html>