[postgis-users] Thematic map and spatial joins

[Morten Sickel]

Rafaelle,

I have done something similar that works fine for me:

First I do the geographical overlay, using st_contains, finding out which
points falls into which polygons:

 SELECT count(op.id) AS n,
    fly.gid,
    op.obstype
   FROM observationpoint op,
    "Valdres_complete_03jun2012_utm32" fly
  WHERE st_contains(fly.the_geom, op.shape_utm) AND op.lokalitet =
'Valdres'::bpchar
  GROUP BY fly.gid, op.obstype;


observationpoint is my point dataset, Valdres_complete... is the polygon
dataset. I want to know how many of different observationtypes that falls
into each polygon, if you just want to summarise per polygon, remove the
references to op.obstype in select and group by.

Then I have a view (observationview) with the fields n - how many, gid -
the id of the polygon (and observationtype) and I can make a new view
connecting this back to the polygoncoverage like

select fly.*. ov.n,ov.obstype
FROM observationview ov, "Valdres_complete_03jun2012_utm32" fly
  WHERE ov.gid=fly.gid

It may be more efficient to put all this into one query, but I have not
investigated that much further.

Morten



Raffaele Morelli skrev:
> Hi,
>
> I am dealing with some thematic map to be rendered with mapserver.
>
> I am actually using mapserver data access connection methods (
> http://mapserver.org/it/input/vector/postgis.html#data-access-connection-method
> )
> to perform joins between geometry and attributes tables using mostly
> aggregate functions.
>
> I would like to use postgis views to put all the logic in the database but
> I am not sure I am doing it in the right way.
>
> I want to count the number of points in table A falling in each polygon in
> table B, thus obtaining a polygon table with an attribute. Here follows my
> view definition I would like to have feedback on:
>
> SELECT subquery.the_geom::geometry(MultiPolygon,32632) AS the_geom,
> count(s.count_att)
> FROM table1 A
> JOIN ( SELECT the_geom, gid FROM table2 B) subquery
> ON A.id = subquery.id
> GROUP BY subquery.the_geom;
>
> Regards
> /raffaele
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>