[postgis-users] problem clipping a feature

[Andre Oliveira]

Hello Kevin,

Well, that surely worked out the problem and improved my knowledge of
postgis!   :)
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:

-- What is the area of the clipped features?
select sum(ST_Area(intersection_geom)) from pg_voting_areas;

-- How does that compare to the clipping polygon? (Should be the same.)
select ST_Area(the_geom) from bc_municipality where name = 'PRINCE GEORGE';

the results are not exactly equal - 326579103.825073 versus
326579103.825928).

Thank you very much


On Wed, Sep 2, 2009 at 5:55 PM, Kevin Neufeld <kneufeld at refractions.net>wrote:

> Hi Andre,
>
> 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).
>
> 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.
>
> 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.
>
> Hope this helps,
> Kevin
>
> Andre Oliveira wrote:
>
>> Hi Everybody,
>>
>> 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:
>>
>> CREATE TABLE pg_voting_areas AS
>> SELECT
>>  ST_Intersection(v.the_geom, m.the_geom) AS intersection_geom,
>>  ST_Area(v.the_geom) AS va_area,
>>  v.*,
>>  m.name <http://m.name>
>> FROM
>>  bc_voting_areas v,
>>  bc_municipality m
>> WHERE
>>  ST_Intersects(v.the_geom, m.the_geom) AND
>>  m.name <http://m.name> = 'PRINCE GEORGE';
>>  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.
>>
>> 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.
>>
>> Then I tried to inserted the dataset's line into table geometry_columns:
>> INSERT INTO geometry_columns (f_table_catalog, f_table_schema,
>> f_table_name,f_geometry_column, coord_dimension, srid, type)
>>
>> SELECT '', 'public', 'pg_voting_areas', 'the_geom',
>> ST_CoordDim(the_geom),
>> ST_SRID(the_geom),
>> GeometryType(the_geom)
>>
>> FROM public.pg_voting_areas LIMIT 1;
>>
>> 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.
>> When I installled postgre I chose locale by default and created the
>> postgis database manually, bearing UTF8 encoding.
>>
>> I would really appreciate a hand here.
>> Thanks
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090904/6cd60900/attachment.html>