Brilliant! Totally agreed.<br>Thanks<br><br><div class="gmail_quote">On Fri, Sep 4, 2009 at 4:50 PM, Kevin Neufeld <span dir="ltr"><<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">This is likely due to accumulative rounding differences of the ordinates and area computation. The answer will also more than likely change from varying system architectures. You'll notice that when I run the same query (on which I believe is the same dataset), I too get differences in precision.<br>
<br>
select sum(area(intersection_geom)) from pg_voting_areas ;<br>
sum<br>
------------------<br>
326579103.824914<br>
(1 row)<br>
<br>
select sum(area(the_geom)) from bc_municipality where name = 'PRINCE GEORGE';<br>
sum<br>
------------------<br>
326579103.824925<br>
(1 row)<br>
<br>
<br>
However, if I reduce the precision of the geometries from bc_voting_areas and bc_municipalities (using ST_SnapToGrid with a parameter of 0.1), the areas add up to the same amount.<br>
<br>
select sum(area(intersection_geom)) from pg_voting_areas_gridded ;<br>
sum<br>
------------------<br>
326578809.430005<br>
(1 row)<br>
<br>
select sum(area(the_geom)) from bc_municipality_gridded where name = 'PRINCE GEORGE';<br>
sum<br>
------------------<br>
326578809.430005<br>
(1 row)<br>
<br>
<br>
Clearly the walk-through should have said<br>
"How does that compare to the clipping polygon? (Should be *similiar*.)"<br>
<br>
Hope this clarifies things,<br>
Kevin<br>
<br>
Andre Oliveira wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">
Hello Kevin,<br>
<br>
Well, that surely worked out the problem and improved my knowledge of postgis! :)<br>
I have a final question: in foss4g2007' tutorial the result of querying the clipped poygon's area and the clipping municipality's area is exactly equal, but when I do it:<br>
<br>
-- What is the area of the clipped features?<br>
select sum(ST_Area(intersection_geom)) from pg_voting_areas;<br>
<br>
-- How does that compare to the clipping polygon? (Should be the same.)<br>
select ST_Area(the_geom) from bc_municipality where name = 'PRINCE GEORGE';<br>
<br>
the results are not exactly equal - 326579103.825073 versus 326579103.825928).<br>
<br>
Thank you very much <br></div><div><div></div><div class="h5">
On Wed, Sep 2, 2009 at 5:55 PM, Kevin Neufeld <<a href="mailto:kneufeld@refractions.net" target="_blank">kneufeld@refractions.net</a> <mailto:<a href="mailto:kneufeld@refractions.net" target="_blank">kneufeld@refractions.net</a>>> wrote:<br>
<br>
Hi Andre,<br>
<br>
From your query, it looks like your resultant table,<br>
pg_voting_areas, is going to have two geometry columns:<br>
'intersection_geom' (an alias provided after ST_Intersection) and<br>
'the_geom' (obtained from v.*, bc_voting_areas.the_geom).<br>
<br>
You mentioned that you tried to add 'the_geom' to the<br>
geometry_columns table to help out QuantumGIS. You may want to do<br>
the same for 'intersection_geom' so you can visualize the clipped<br>
data, not just the voting areas.<br>
<br>
I'm not very familiar with QuantumGIS, but it sounds like it gets<br>
confused with tables that have more than one geometry column - you<br>
mentioned Quantum recognized this table as having two geometry<br>
columns with the same name ... as I pointed out, this is not the<br>
case. Try selecting the 'intersection_geom' from pg_voting_areas<br>
into a separate table and try to visualize that.<br>
<br>
Hope this helps,<br>
Kevin<br>
<br>
Andre Oliveira wrote:<br>
<br>
Hi Everybody,<br>
<br>
I was trying to use the ST_Intersection function to overlay<br>
(clip) a poygon by another, with the following code from the<br>
workshop at foss4g2007:<br>
<br>
CREATE TABLE pg_voting_areas AS<br>
SELECT<br>
ST_Intersection(v.the_geom, m.the_geom) AS intersection_geom,<br>
ST_Area(v.the_geom) AS va_area,<br>
v.*,<br></div></div>
<a href="http://m.name" target="_blank">m.name</a> <<a href="http://m.name" target="_blank">http://m.name</a>> <<a href="http://m.name" target="_blank">http://m.name</a>><div class="im"><br>
<br>
FROM<br>
bc_voting_areas v,<br>
bc_municipality m<br>
WHERE<br>
ST_Intersects(v.the_geom, m.the_geom) AND<br></div>
<a href="http://m.name" target="_blank">m.name</a> <<a href="http://m.name" target="_blank">http://m.name</a>> <<a href="http://m.name" target="_blank">http://m.name</a>> = 'PRINCE GEORGE';<div>
<div></div><div class="h5"><br>
<br>
After this operation I restarted pgAdminIII, generated an index<br>
for pg_voting_areas, run vacuum analyze, and defined GID as the<br>
primary key for pg_voting_areas.<br>
<br>
But when I try to connect with QuantumGIS to visualize the data,<br>
two datasets where recognized with the same name, one bearing<br>
the expected result, but the other a display with more objects<br>
than expected for a clip.<br>
<br>
Then I tried to inserted the dataset's line into table<br>
geometry_columns:<br>
INSERT INTO geometry_columns (f_table_catalog, f_table_schema,<br>
f_table_name,f_geometry_column, coord_dimension, srid, type)<br>
<br>
SELECT '', 'public', 'pg_voting_areas', 'the_geom',<br>
ST_CoordDim(the_geom),<br>
ST_SRID(the_geom),<br>
GeometryType(the_geom)<br>
<br>
FROM public.pg_voting_areas LIMIT 1;<br>
<br>
After I did this, one of the datasets recognized by QuantumGIS<br>
disappeared, but it was the undesired one that survived. Also, I<br>
noticed that some of the measurements in the workshop are not<br>
totally equal to the ones I obtain (for instance, when I query<br>
the clipped poygon's area and the clipping municipality's area,<br>
they are not exactly equal - 326579103.825073 versus<br>
326579103.825928). I am using the last versions of postgre,<br>
postgis and quantumgis.<br>
When I installled postgre I chose locale by default and created<br>
the postgis database manually, bearing UTF8 encoding.<br>
<br>
I would really appreciate a hand here.<br>
Thanks<br>
<br>
<br>
------------------------------------------------------------------------<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div></div>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="im"><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="im"><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
------------------------------------------------------------------------<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></blockquote><div><div></div><div class="h5">
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br>