2

I've set up a table in PostGIS using OSM data (converted using osm2pgsql). I know this database is good, because I'm able to import it and work with it in TileMill, but whenever I try to interact with any of the layers in QGIS (1.8.0, OSX), the Add button is greyed out (even if I put in a query).

I've tested the connection and when I test my queries it shows that lots of rows were returned, so (again), the data's clearly there—I just can't open it up.

CLARIFICATION: It's also worth noting that QGIS is able to see the database just fine. I can build queries and read out data in the Add PostGIS layer interface and everything comes back correctly. The only thing I can't do in the Add PostGIS Layer interface is add the layer.

CLARIFICATION 2 (from the comments): postgis_version returns 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1. The extensions active on the database are plpgsql (1.0), postgis (2.0.3), and postgis_topology (2.0.3). PostgreSQL and PostGIS were installed using Homebrew.

The table does (now) have a primary key, but QGIS doesn't appear to find it.

7
  • Please elaborate more. How do you try to add the content of the database table? Using the Add PostGIS Layers button? Jul 29, 2013 at 7:14
  • Yes, using the Add PostGIS Layers option. Jul 29, 2013 at 12:58
  • Have you tried the solutions from gis.stackexchange.com/questions/38937/… already?
    – AndreJ
    Jul 29, 2013 at 15:15
  • Yes. postgis_version returns 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1. The extensions active on the database are plpgsql (1.0), postgis (2.0.3), and postgis_topology (2.0.3). FWIW, postgres and postgis were installed using Homebrew. Jul 29, 2013 at 17:56
  • Does your table have a unique primary key field, and are you making sure QGIS is looking at that field for the PK? See: gis.stackexchange.com/questions/25699/…
    – DPierce
    Jul 29, 2013 at 18:19

3 Answers 3

4

The problem, as it turns out, was that I had not named the connection.

2

From Tim Sutton's Blog:

...QGIS’ requirement that the view have a column derived from a primary key or a column with a unique constraint on it, you can cast the row number to int4 like this:

(Emphasis mine)

I believe that the specific int4 requirement is no longer true, but ensure that your field is either explicitly a primary key, or is unique.

Alternatively, you could use Mr. Sutton's query to create a view with the PostGre "ROW_NUMBER()" function, reprinted here (also from Sutton's blog):

create view vw_farms
as
select int4(ROW_NUMBER() over (order by parcel.farm_name)) as id,
parcel.farm_name,
parcel.geometry
from parcel
where parcel.farm_name != '' limit 10 ;
1

Have the tables a definite type (point,layer,polygon)?

If you have not checked "use estimated table data" when establishing a new connection, Postgis searches across the whole tables to get the geometry types every time you want to connect to the database. This will take a lot of time when using larger OSM datasets.


EDIT

This is how my Add postgis layer dialogue looks like:

enter image description here

So no Primary key necessary, but two indices (not shown here). Do you have an osm2pgsql version that is compiled for postgis 2.0? Mine is for postgis 1.5 (on Windows), and it works since years. With Postgis 2.0, there have been many changes in commands.

2
  • They do have definite types (two layers with lines, one with points, and one with polygons). I did not have "use estimated table data" checked, but I've tried it now with that checked, and the add button remains greyed out. I haven't tried just waiting--I'll give that a go. Jul 29, 2013 at 14:54
  • Unfortunately, half an hour later, it still hasn't enabled "Add". It's not an especially big table (~36k rows)... seems like that should've parsed by now. Jul 29, 2013 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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