Brilliant! Totally agreed. Thanks On Fri, Sep 4, 2009 at 4:50 PM, Kevin Neufeld <[email protected]>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 >> <[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 >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
