[postgis-users] problem clipping a feature
Andre Oliveira
andreolivei at gmail.com
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>
More information about the postgis-users
mailing list