Re: [Qgis-user] Question on PostGIS Table and QGIS
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 rjh...@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 rjh...@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Question on PostGIS Table and QGIS
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 rjh...@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Question on PostGIS Table and QGIS
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 -- Jeff McKenna MapServer Consulting and Training Services http://www.gatewaygeomatics.com/ ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
