Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a large table in Postgis2. It has >500 million polygon, line and point features all happily living in there and the table performs brilliantly in other GIS.

I am now trying to just add that table to a project in QGIS but here is the thing, why does it take so long?

When i try to 'Add PostGIS overlay' it says detecting for about 4 hours to tell there are 3 geometry types. OK, i can take that if i only have to do it once...

Once it finishes that i select all three parts click ok.. so far im 2 days into trying to get it to load into the canvas.

What is going on? I am zoomed in so only a few polygons should be rendered on screen.

Is this an error or a 'feature' of QGIS? Is there something i need to specifically do to enhance the performance for QGIS?

Many thanks for your help people.

Update:

Thanks for the help.
I am using MapInfo and Cadcorp SIS both of which love the "everything in one place" model rather than the splitting it up, join it back later with queries model. And thanks @AndrewJoost, the estimated metadata saved about an hour but still it takes a few hours too long and misses one of the geometry types off the list... QGIS not supporting mixed geometries in one overlay is no problem, as long as it supports them in one table.
And the way it does this with a filter is great for small tables but just fails when you have a large one it seems.

I am unable to seperate the table into three parts as this does not make sense for the other systems using the data, and would also break them.

@NathanW How can i get 3 records for a single table into the geometry_columns view now that it is no longer a physical table? any ideas? Sorry about all the newbie questions

Any other ideas to stop QGIS trying to be helpful and scanning a table before allowing me to add it? I dont mind choosing geometry types by hand for every overlay if thats what it takes?

Cheers

share|improve this question

3 Answers 3

up vote 1 down vote accepted

As previously mentioned, mixed Geometries in one layer are not supported by QGIS.

If you have strong use case for keeping all of the data in a single table, another option to creating new tables (as suggested by @Benno) would be to create views referencing each geometry type from the initial table:

CREATE VIEW vwPoints 
SELECT <fields> 
FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_Point'

I am not sure about is the performance implications of going with views vs. individual tables, but that is something you could easily experiment with and decide which route you want to take.

share|improve this answer

To my knowledge, GIS as Arcmap or QGIS can't support layers with several primitives (point, polygon or polyline). What GIS do you use?

You can try to create tables for each type:

Point:

SELECT * INTO newtablepoints FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_Point' ;

polygon :

SELECT * INTO newtablepolygones FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_MultiPolygon' or ST_GeometryType(geom) = 'ST_Polygon' ;

polyline :

SELECT * INTO newtablelines FROM initial_table 
WHERE ST_GeometryType(geom) ='ST_MultiLineString' or ST_GeometryType(geom) = 'ST_LineString';

You can try to add a geometric index :

CREATE INDEX idx_spatial_points ON newtablepoints USING GIST (geom);
CREATE INDEX idx_spatial_polygones ON newtablepolygones USING GIST (geom);
CREATE INDEX idx_spatial_lines ON newtablelines USING GIST (geom);

It will accelerate geographic queries.

Then these three tables will be more easily support by QGIS

share|improve this answer
3  
You can also just edit the geometry_columns table to have three records for the single table with point, line, polygon. QGIS will open them as different layers but write back to the same table. –  Nathan W Aug 3 '13 at 13:54
    
Thanks for the help. I am using MapInfo and Cadcorp SIS both of which love the "everything in one place" model rather than splitting it up. And thanks @AndrewJoost the estimated metadata saved about an hour but still it takes a few hours too long. –  Andy Aug 14 '13 at 13:53
1  
It is also possible to use a view on the table so that you do not need to make a new table. QGIS handles views without an issue. This is a trade-off between duplicating the data and waiting for the query at load time. –  Jay Laura Aug 14 '13 at 14:17

When setting up the Postgis connection, you can check "use estimated table metadata". This will prevent QGIS from looking through all features every time you load the layer.

Unfortunately, you can't change it later on in an easy way.

EDIT

Mixed Geometries in one layer are not supported by QGIS.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.