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 );
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
