[postgis-users] Saving a split polygon

[Alexandre Neto]

Hello Peter,

I'm not a database expert whatsoever, and there are better people in this
list to clarify this. I just stated my experience with it.

As far as I know, in order to respect Postgres Primary keys, QGIS uses the
columns default values.

If during the table creation your primary keys were created with something
like:

CREATE TABLE yourtable
(
gid serial NOT NULL,
...
)

Then, a sequence 'yourtable_gid_seq' is created and the default value for
your gid column is set to nextval('yourtable_gid_seq'::regclass)

When a new feature(row) is created if the user does not fill the pk field
(and he is not supposed to), postgres will consider the default value, give
it the next not use value from the sequence and update the sequence.

If you are using QGIS, when you create a new feature or simply split an
existing features, make sure not to type anything in the PK field, or this
will give you a bad time. Just leave it blank, during the save\commit, both
QGIS and Postgres will know what do do with it.

Not sure where all this knowledge came from, but have a look here:
http://www.postgresql.org/docs/9.1/static/functions-sequence.html

Best regards,
Alexandre Neto


On Mon, Oct 7, 2013 at 3:59 PM, Peter Brown <peterbrown.kew at gmail.com>wrote:

> uummmm
> Reading your answer {particularly the bit about  a
> nextval('yourtable_gid_seq'::regclass),} I realise that this bit of code
> is an area of knowledge that I am missing, and clearly if it will solve my
> problem then it would be useful. I wonder if I could ask you to at least
> point me in the right direction.
> I am fairly new to QGIS but generally a power user of databases, so I have
> read the excellent manual
> http://workshops.opengeo.org/postgis-intro/creating_db.html
> and the more fundamental one at
> http://www.postgresql.org/docs/9.2/interactive/sql.html
> However, I do not seem to have come across anything like this. Can you
> advise me?
> Thank you for your help
> Regards
> Peter Brown
>
>
> On 7 October 2013 11:09, Alexandre Neto <senhor.neto at gmail.com> wrote:
>
>> Hello,
>>
>> I'm not sure If I followed your question, but in my experience with QGIS
>> editing Postgres\postgis spatial tables, I add some smal hickups, but never
>> with splitting.
>>
>> If its a normal polygon and you wish to split it with a line, QGIS will
>> assign to all your new polygons a nextval('yourtable_gid_seq'::regclass),
>> and this should solve the PK's problem.
>>
>> If it's a multipart polygon that you want to spit it's parts, you can use
>> the multipartsplit plugin, and it will also take care of your primary keys.
>>
>> Maybe it's a problem with your table, please send the creation SQL
>> command and maybe someone can have a clue of what is happening.
>>
>>
>>
>> On Mon, Oct 7, 2013 at 10:41 AM, Peter Brown <peterbrown.kew at gmail.com>wrote:
>>
>>> Thanks for your prompt response, it is nice to know that it is possible,
>>> but the methods you remember do seem to have problems.
>>> - in QGIS I can split a polygon which is on a layer, and under the
>>> 'layer' option on the menu, I call up the attributes table, but that only
>>> offers me all the fields in the base PostGIS table EXCEPT   (a) the  ID and
>>> (b) the geometry
>>> - I have tried manufacturing another PostGIS table with all the
>>> attributes of the polygon except an ID field with all its 'NO NULLS' etc,
>>> but then when I open a layer in QGIS it draws the polygon but shows it as
>>> not editable
>>> You have obviously managed something clever, but I am not quite sure what
>>> Regards
>>> Peter Brown
>>>
>>>
>>> On 7 October 2013 09:32, Hugues François <hugues.francois at irstea.fr>wrote:
>>>
>>>> **
>>>> Hello,
>>>>
>>>> I previously edited polygons from postgis within qgis and with no
>>>> problem. If I remember well, before saving I also filled attribute data to
>>>> give a new ID. Maybe if you just want to edit polygons without changing
>>>> attributes, you may remove constraints on your postgis table (especially,
>>>> uniques, not null and primary key) before editing (and then put the things
>>>> right back to postgis).
>>>>
>>>> Hugues.
>>>>
>>>>
>>>>
>>>>  ------------------------------
>>>> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
>>>> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Peter Brown
>>>> *Sent:* Monday, October 07, 2013 9:53 AM
>>>> *To:* postgis-users at lists.osgeo.org
>>>> *Subject:* [postgis-users] Saving a split polygon
>>>>
>>>>  In Quantum GIS (QGIS) there is a very useful facility for taking a
>>>> polygon in a PostGIS database linked to QGIS and splitting it. HOWEVER,
>>>> when one then tries to save the two portions, PostGIS puts up an error
>>>> message that it cannot save the new portion because it does not have a new
>>>> ID (it can save the existing portion, because this is only an edit of an
>>>> existing ID).
>>>> There are workarounds for this (e.g. convert to 'Shape' files) but they
>>>> seem incredibly clumsy. Does anyone have a more direct solution?
>>>> Thanks
>>>> Peter Brown
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131007/68cab013/attachment.html>