[postgis-users] extracting attributes from non-standard text field to cross with polygon

[perikut]

hello,


If I have a table structured like this....


POINT TABLE

the_geom, genus_array


my_geom   genus1+300||genus2+5000||genus3+1000.......


my_geom2   genus1+300||genus2+8000.........



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?


I explain you what I was doing till now in PHP with the *old point table*structure:


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


Quite simple, as we simply have a structure like...


POINT TABLE

the_geom,taxonomy_level,count_occurrences

the_geom2,taxonomy_level2,count_occurrences


the problem now is that something similar to *count_occurrences does not 
exist* in the point table (we have to calculate it, *parsing the results*coming from a select genus_array where genus_array~'genus2').



I think I should create a plpgsql function that returns a *setof records*with the geometry and the new counts parameter (extracted after parsing 
array_genus string)



Then this function can be directly applied to the 
point-in-polygon operation,something like...


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

and CONTAINS(poly.the_geom,p.the_geom) and taxonomy_level~'my_genus' group 
by poly.the_geom,poly.gid



Am I on the right track? is this new point-in-polygon operation possible *without 
creating a new temporal point table or view *(to cross with polygonal 
table)?


I send you attached the main PHP code I do for parsing array_genus. BUT *all 
this is not useful for point-in-polygon operation*, just for extracting 
data and geometry of a user selected category (taxa like genus2).


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...



$query = "select ST_AsGeoJSON(the_geom,2),array_genus from 
$collection where array_genus ~ '$taxa'";


//I get the geom and a string like....  genus1+300||genus2+5000||genus3+1000


  $result = pg_query($connexion,$query);


 $geojson = array(

      'type'      => 'FeatureCollection',

      'features'  => array()

   );


     $geojson['taxa']=$taxa;


    while($row=pg_fetch_row($result))


    {

//searching where $taxa string is located in array_genus string

   $pos=strpos($row[1],$taxa);


//cut the string

   $new_string=substr($row[1],$pos);


//where do we have to cut again...

   $pos=strpos($new_string,'||');


   if ($pos)  //we have more than one taxa on the string

   {

//cut again

   $new_string2=substr($new_string,0,$pos);

   $data=explode('+', $new_string2);

   $count=$data[1];

   }

   else

   {

   $data=explode('+', $new_string);

   $count=$data[1];

   }



then I echo the results as json_encode, getting a geoJSON I can use in my 
webapplication. But *all this is not useful for point-in-polygon operation*


$feature = array(

         'type' => 'Feature',

         'geometry' => json_decode($row[0], true),

          'properties' => array(

          'count' => $count

          )

     );

      array_push($geojson['features'], $feature);

      }



echo json_encode($geojson,JSON_NUMERIC_CHECK );
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120606/7707a770/attachment.html>