[postgis-users] problem clipping a feature

Kevin Neufeld kneufeld at refractions.net
Fri Sep 4 08:50:14 PDT 2009


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



More information about the postgis-users mailing list