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 <[email protected]
<mailto:[email protected]>> 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
[email protected]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
