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

[Worth Lutz]

My first reaction (without thinking too much :-)) is that you will need to
write a PL/pgSQL procedure to do that.

 

Worth

 

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Tyler Frazier
Sent: Friday, March 22, 2013 7:20 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] spatial join and summarize, list all names

 

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

 

 

 

  _____  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.2904 / Virus Database: 2641/6195 - Release Date: 03/21/13

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