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]>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> >> FROM >> bc_voting_areas v, >> bc_municipality m >> WHERE >> ST_Intersects(v.the_geom, m.the_geom) AND >> 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] >> 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
