[postgis-users] problem clipping a feature

Fri Sep 4 14:13:58 PDT 2009

Brilliant! Totally agreed.
Thanks

On Fri, Sep 4, 2009 at 4:50 PM, Kevin Neufeld <kneufeld at refractions.net>wrote:

> 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.
>
> select sum(area(intersection_geom)) from pg_voting_areas ;
>       sum
> ------------------
>  326579103.824914
> (1 row)
>
> select sum(area(the_geom)) from bc_municipality where name = 'PRINCE
> GEORGE';
>       sum
> ------------------
>  326579103.824925
> (1 row)
>
>
> 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.
>
> select sum(area(intersection_geom)) from pg_voting_areas_gridded ;
>       sum
> ------------------
>  326578809.430005
> (1 row)
>
> select sum(area(the_geom)) from bc_municipality_gridded where name =
> 'PRINCE GEORGE';
>       sum
> ------------------
>  326578809.430005
> (1 row)
>
>
> Clearly the walk-through should have said
> "How does that compare to the clipping polygon? (Should be *similiar*.)"
>
> Hope this clarifies things,
> Kevin
>
> Andre Oliveira wrote:
>
>> 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<mailto:
>> 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> <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> <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
>>        <mailto:postgis-users at postgis.refractions.net>
>>        http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>    _______________________________________________
>>    postgis-users mailing list
>>    postgis-users at postgis.refractions.net
>>    <mailto: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
>>
> _______________________________________________
> 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/21f07d9b/attachment.html>