<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica">hello,</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">If I have a table structured like this....</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">POINT TABLE</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">the_geom, genus_array</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">my_geom genus1+300||genus2+5000||genus3+1000.......</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">my_geom2 genus1+300||genus2+8000.........</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">how should I do if I want to extract the info for genus2 (5000 and 8000) and cross its geometry to make a point-in-polygon operation?</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">I explain you what I was doing till now in PHP with the <b>old point table</b> structure:</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">select SUM(p.count_occurrences) as count, poly.gid as code from point_table p INNER JOIN polygon_table poly ON poly.the_geom && p.the_geom and CONTAINS(poly.the_geom,p.the_geom) and taxonomy_level='genus2' group by poly.the_geom,poly.gid</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">Quite simple, as we simply have a structure like...</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">POINT TABLE</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">the_geom,taxonomy_level,count_occurrences</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">the_geom2,taxonomy_level2,count_occurrences</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">the problem now is that something similar to <b>count_occurrences does not exist</b> in the point table (we have to calculate it, <b>parsing the results</b> coming from a select genus_array where genus_array~'genus2').</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">I think I should create a plpgsql function that returns a <b>setof records</b> with the geometry and the new counts parameter (extracted after parsing array_genus string)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">Then this function can be directly applied to the point-in-polygon operation,something like...</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">select sum(get_counts_by_taxa('my_genus')) as count, poly.gid from point_table p inner join polygon_table ON poly.the_geom && p.the_geom</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">and CONTAINS(poly.the_geom,p.the_geom) and taxonomy_level~'my_genus' group by poly.the_geom,poly.gid</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">Am I on the right track? is this new point-in-polygon operation possible <b>without creating a new temporal point table or view </b>(to cross with polygonal table)?</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">I send you attached the main PHP code I do for parsing array_genus. BUT <b>all this is not useful for point-in-polygon operation</b>, just for extracting data and geometry of a user selected category (taxa like genus2).</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">Thanks in advance! btw, if you have any useful resource to effectively learn plpgsql just tell me. I have done some effort but its becoming quite hard...</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">$query = "select ST_AsGeoJSON(the_geom,2),array_genus from $collection where array_genus ~ '$taxa'";</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">//I get the geom and a string like.... genus1+300||genus2+5000||genus3+1000</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $result = pg_query($connexion,$query);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $geojson = array(</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'type' => 'FeatureCollection',</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'features' => array()</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> );</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $geojson['taxa']=$taxa;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> while($row=pg_fetch_row($result))</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> {</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">//searching where $taxa string is located in array_genus string</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $pos=strpos($row[1],$taxa);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">//cut the string</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $new_string=substr($row[1],$pos);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">//where do we have to cut again...</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $pos=strpos($new_string,'||');</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> if ($pos) //we have more than one taxa on the string</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> {</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">//cut again</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $new_string2=substr($new_string,0,$pos);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $data=explode('+', $new_string2);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $count=$data[1];</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> }</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> else</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> {</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $data=explode('+', $new_string);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> $count=$data[1];</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> }</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">then I echo the results as json_encode, getting a geoJSON I can use in my webapplication. But <b>all this is not useful for point-in-polygon operation</b></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">$feature = array(</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'type' => 'Feature',</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'geometry' => json_decode($row[0], true),</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'properties' => array(</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> 'count' => $count</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> )</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> );</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> array_push($geojson['features'], $feature);</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323"> }</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323; min-height: 15.0px"><br></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; color: #232323">echo json_encode($geojson,JSON_NUMERIC_CHECK );</p>