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> <br><br><div class="gmail_quote">On Wed, Sep 2, 2009 at 5:55 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;">Hi Andre,<br>
<br>
>From your query, it looks like your resultant table, pg_voting_areas, is going to have two geometry columns: 'intersection_geom' (an alias provided after ST_Intersection) and 'the_geom' (obtained from v.*, bc_voting_areas.the_geom).<br>
<br>
You mentioned that you tried to add 'the_geom' to the geometry_columns table to help out QuantumGIS. You may want to do the same for 'intersection_geom' so you can visualize the clipped data, not just the voting areas.<br>
<br>
I'm not very familiar with QuantumGIS, but it sounds like it gets confused with tables that have more than one geometry column - you mentioned Quantum recognized this table as having two geometry columns with the same name ... as I pointed out, this is not the case. Try selecting the 'intersection_geom' from pg_voting_areas into a separate table and try to visualize that.<br>
<br>
Hope this helps,<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">
Hi Everybody,<br>
<br>
I was trying to use the ST_Intersection function to overlay (clip) a poygon by another, with the following code from the 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>
<a href="http://m.name" target="_blank">m.name</a> <<a href="http://m.name" target="_blank">http://m.name</a>><div class="im"><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>> = 'PRINCE GEORGE';<div class="im"><br>
After this operation I restarted pgAdminIII, generated an index for pg_voting_areas, run vacuum analyze, and defined GID as the primary key for pg_voting_areas.<br>
<br>
But when I try to connect with QuantumGIS to visualize the data, two datasets where recognized with the same name, one bearing the expected result, but the other a display with more objects than expected for a clip.<br>
<br>
Then I tried to inserted the dataset's line into table geometry_columns: <br>
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, 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 disappeared, but it was the undesired one that survived. Also, I noticed that some of the measurements in the workshop are not totally equal to the ones I obtain (for instance, when I query the clipped poygon's area and the clipping municipality's area, they are not exactly equal - 326579103.825073 versus 326579103.825928). I am using the last versions of postgre, postgis and quantumgis.<br>
When I installled postgre I chose locale by default and created the postgis database manually, bearing UTF8 encoding.<br>
<br>
I would really appreciate a hand here.<br>
Thanks<br>
<br>
<br></div>
------------------------------------------------------------------------<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>
</blockquote>
_______________________________________________<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>
</blockquote></div><br>