[Qgis-user] Question on PostGIS Table and QGIS

[Alexandre Neto]

Hi Randal,

I believe there are a few things going on at the same time here.

When you did

select *, (st_dump(geom)).geom as the_geom from trgtgis.boundary;

you ended up with two geometry columns since trgtgis.boundary.* already
have a geom column(the one with the multipolygons), (can't find a reason to
it's name in qgis are both the_geom tho).

So, instead of using *, you should pick the columns one by one, leaving the
original geometry column out.

Besides that, after using st_dump(), try cast it as the right geometry and
CRS, otherwise QGIS might have problems in guessing. Something like this:

(st_dump(geom)).geom::geometry('POLYGON',26916)

Finally, in order to be able open the layer in QGIS, you need a unique
values primary key, and you can't use the original boundary primary key,
because it has now duplicated values. You can wrap your query and had
a ROW_NUMBER()
OVER() as gid. Something like this:


WITH r as
(
  SELECT
    field1,
    field2,
    field3,--<...>
    (ST_Dump(geom)).geom::geometry('POLYGON',26916) as the_geom
  FROM
    table_1
)
SELECT
    ROW_NUMBER() OVER() as id,
    r.*
FROM r;


Finally, if you want to skip all this, you can just select all your
features in QGIS, start editing and use the (very handy cof cof) multipart
split plugin <http://plugins.qgis.org/plugins/splitmultipart/>, it will
split all your multipart polygons into single polygons in the original
layer.

Hope it helps,

Alexandre

On Mon, Jun 8, 2015 at 4:06 PM, Randal Hale <rjhale at northrivergeographic.com
> wrote:

> I've had one other suggestion - I'll add this to the list and see what
> happens. Luckily this isn't vital at the moment - so I have some time to
> play a bit and see what I can do better.
>
> On 06/08/2015 11:03 AM, Jeff McKenna wrote:
>
>> On 2015-06-07 11:50 AM, Randal Hale wrote:
>>
>>> I'm breaking new ground and I'm pretty sure this might be a mistake on
>>> my end. I just need to understand why.
>>>
>>> I've set up a PostGIS database. I'm importing data through DB manager
>>> into my schema.
>>>
>>> I imported some parcel records and discovered that I had multipolygons.
>>> So I decided to break them up into single polygons through the psql
>>> interface:
>>> create table trgtgis.parcels as select *, (st_dump(geom)).geom as
>>> the_geom from trgtgis.boundary;
>>>
>>> When I go back to QGIS I have two tables called parcels (only one listed
>>> if I look through postgresql). One table is what I suspect to see with
>>> column called the_geom, data Type as Geometry, Spatial type of Polygon,
>>> SRID of 26916. The second parcel table is greyed out and doesn't have a
>>> spatial type or a SRID. Neither lists a primary key (I need to go back
>>> and add one) but I don't think this is the problem...I think.
>>>
>>> Screenshot:
>>>
>>> https://drive.google.com/file/d/0B8WLtz606XDdcFIxaG5fNExpMlU/view?usp=sharing
>>>
>>>
>>>
>> Hi Randy,
>>
>> I've seen this issue before (QGIS listing multiple layers for one PostGIS
>> table), but I did solve it, I just can't remember how I did that ha :)
>>
>> I believe one of my issues was that QGIS (and MapServer in fact) need a
>> unique ID field, to display the data from PostGIS, so I always make sure to
>> specify a unique ID field when creating tables for use in both QGIS and
>> MapServer.
>>
>> -jeff
>>
>>
>>
>>
>>
>>
> --
> -----------------
> Randal Hale
> North River Geographic Systems, Inc
> http://www.northrivergeographic.com
> 423.653.3611 rjhale at northrivergeographic.com
> twitter:rjhale     http://about.me/rjhale
> http://www.northrivergeographic.com/introduction-to-quantum-gis
> Southeast OSGEO: http://wiki.osgeo.org/wiki/Southeast_US
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20150609/7a57a351/attachment.html>