<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Glad it works ^^</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">A note for archive : this solution won't be super-efficient.</div><div class="gmail_default" style="font-family:monospace,monospace">This looks like a textbook case for using the postgres rule system.<br>A simpler solution would be to switch to statement trigger (as opposed ot row trigger) if you care about it, but it may be trickier to write.</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,</div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C</div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-18 23:36 GMT+02:00 Luciano <span dir="ltr"><<a href="mailto:br.analistagis@gmail.com" target="_blank">br.analistagis@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><span lang="en"><span>Very good !!</span><br><br> <span>based on</span> Remi's advice<span> and</span><span> some</span> <span>adjustments, </span><span>it worked!</span><span></span><br><br> <span>Now the</span> <span>table</span> <span>is updating</span> using the <span>SQL</span> <span>Editor</span> or<span> the</span> <span>Qgis</span><span>.</span><br><br> <span>Thank you all!<br></span></span></div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-18 16:19 GMT-03:00 Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span>:<div><div class="h5"><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Ok, this is totally a postgres question then.</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">First you create your data model (here it is just for example, you should pick better name, don't use capital, no space, etc)</div><font face="monospace, monospace"><div class="gmail_default" style="font-family:monospace,monospace;display:inline"></div><br>CREATE TABLE Cities (...)<br>CREATE TABLE Neighborhoods ()<br>CREATE TABLE Blocks()<br>CREATE TABLE Streets ()</font><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">From here I expect a correct data model, with primary key, foreign key, constraints, etc.</div><div class="gmail_default"><font face="monospace, monospace"><br></font></div><div class="gmail_default"><font face="monospace, monospace">You create a postgres table importing_data :</font></div><div class="gmail_default"><font face="monospace, monospace">CREATE TABLE importing_data (</font></div><div class="gmail_default"><font face="monospace, monospace">iid serial PRIMARY KEY</font></div><div class="gmail_default"><font face="monospace, monospace">,town_code int<br></font></div><font face="monospace, monospace">,town_description<div class="gmail_default" style="display:inline"> </div>text<br></font><div class="gmail_default"><font face="monospace, monospace">,Neighborhood_code int<br>,name_of_the_neighborhood text<br>,block_code int<br>,Street_code int<br>,street_name text) ;</font></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default"><font face="monospace, monospace">Now you define</font><a href="http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html" style="font-family:monospace,monospace" target="_blank"> a trigger</a><font face="monospace, monospace"> on this table</font></div><div class="gmail_default"><font face="monospace, monospace"><br></font></div><div class="gmail_default"><font face="monospace, monospace">CREATE TRIGGER name AFTER INSERT<br> ON importing_data<br> FOR EACH ROW <br> EXECUTE PROCEDURE filling_data_model()<br><br>This trigger says that every time you insert a line in 'importing_data', the function 'filling_data_model()' gets called.<br><br>Now you define this function so that it does what you want (filling you data model)</font><br></div><font face="monospace, monospace"><br>CREATE OR REPLACE FUNCTION<br>filling_data_model( )<br>RETURNS trigger AS <br>$BODY$ <br>--<div class="gmail_default" style="font-family:monospace,monospace;display:inline"> </div>this trigger break an inserted line in table<div class="gmail_default" style="font-family:monospace,monospace;display:inline"> </div>importing_data <br>--<div class="gmail_default" style="font-family:monospace,monospace;display:inline"> </div>and put hte informations in the tables<div class="gmail_default" style="font-family:monospace,monospace;display:inline"> </div></font><div><font face="monospace, monospace"><div class="gmail_default" style="font-family:monospace,monospace;display:inline">-- </div>Cities,Neighborhoods,Blocks,Streets <br>DECLARE <br>BEGIN <br> --the inserted line<div class="gmail_default" style="font-family:monospace,monospace;display:inline"> in 'importing_data'</div> is in the variable 'NEW'<br> </font></div><div><font face="monospace, monospace"><div class="gmail_default" style="font-family:monospace,monospace;display:inline"> </div>--insert into city<br> INSERT INTO Cities VALUES (NEW[1], NEW[2]) ;<br> --insert into Neighborhoods<br> INSERT INTO ...<br> ....<br> return NEW;<br>END ;<br>$BODY$ LANGUAGE plpgsql VOLATILE;</font><br><div><br></div><div><div class="gmail_default" style="font-family:monospace,monospace">Based on the information you gave, you probably don't want to do an insert, but rather an upsert (see here for instance: <a href="http://stackoverflow.com/a/8702291/330315" target="_blank">http://stackoverflow.com/a/8702291/330315</a>)</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Now it is super easy, simply import your shapefile into the 'importing_data' table, and it should be good</div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">cheers</div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C</div><br></div></div></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-18 20:42 GMT+02:00 Luciano <span dir="ltr"><<a href="mailto:br.analistagis@gmail.com" target="_blank">br.analistagis@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div>Ok Remi, and Jim, thanks...<br><br></div>Lee,<br><br><span lang="en"><span>Thats it,<br><br> the problem is</span> <span>that I get/receive a</span> <span>shape file</span> <span>with the following structure</span> <span>and</span> <span>need to</span> <span>use it</span> <span>to update</span> <span>my database</span><span>.</span><br><br> <span>But</span> <span>my database</span> <span>does not have the</span> <span>same</span> <span>file's structure</span><span>.</span></span><br><br><span lang="en"><span>As mentioned above...<br><br></span></span></div><span lang="en"><span>tia<br></span></span></div><div class="gmail_extra"><br><div class="gmail_quote">2015-04-18 15:14 GMT-03:00 Lee Hachadoorian <span dir="ltr"><<a href="mailto:Lee.Hachadoorian+L@gmail.com" target="_blank">Lee.Hachadoorian+L@gmail.com</a>></span>:<div><div><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Luciano,<br>
<br>
I think I'm not understanding your goal. Do you have a shapefile
that contains a mix of towns, neighborhoods, blocks and streets? Are
you trying to load the shapefile but break the features up so that
towns get inserted in a PostGIS towns table, neighborhoods get
inserted in a PostGIS neighborhoods table, etc.? <br>
<br>
Best,<br>
--Lee<div><div><br>
<br>
<div>On 04/18/2015 12:22 PM, James Keener
wrote:<br>
</div>
<blockquote type="cite">I guess I'm still not fully understanding the problem.
I don't understand what problem the normalization is causing you.
You shouldn't need to duplicate the rows in different tables when
you duplicate one in another table.<br>
<br>
To edit fields in QGIS you need to enable editing on the layer and
then you can get end editable form for each feature or you can
edit directly in the attribute table. Copy and pasting features in
QGIS copied all of the attributes as well.<br>
<br>
Can you give a more complete example of the issue you're facing?<br>
<br>
Jim<br>
<br>
Jim<br>
<br>
<div class="gmail_quote">On April 18, 2015 12:11:38 PM EDT,
Luciano <a href="mailto:br.analistagis@gmail.com" target="_blank"><br.analistagis@gmail.com></a> wrote:
<blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">
<div><span lang="en"><span>Yes</span><span>, I'm using</span> <span>QGIS</span><span>.</span>
<span>I agree</span><span>,</span> <span>if I make a</span> <span>table</span>
<span>in the database</span> <span>with
the same structure</span> <span>the shape
file</span> <span>is simple.</span> <span>The</span> <span>copy / paste</span>
<span>works perfectly</span><span>.</span><br>
<span>But</span> <span>my question</span>
<span>is</span> <span>how to update</span>
<span>for example</span> <span>the</span>
<span>blocks</span> <span>table</span><span>,</span>
<span>using the</span> <span>copy /
paste,</span> <span>since</span> <span>the database</span> <span>structure
is</span> <span>different.</span><br>
<span>For example</span><span>,</span> <span>if I copy</span> <span>a polygon</span>
<span>layer</span> <span>shape</span><span>,</span>
<span>and try to</span> <span>stick
to the</span> <span>database</span> <span>layer</span><span>, the fields</span> <span>of the new</span> <span>polygon</span>
<span>will be</span> <span>void.</span><br>
<span>Note that</span> <span>my</span>
<span>database</span> <span>blocks</span>
<span>table does not have</span> <span>the same structure</span> <span>of
the</span> <span>shape file</span> <span>because it</span> <span>is</span> <span>normalized</span> <span>(or should
be</span><span>), so</span> <span>the fields</span>
<span>of</span> <span>two data
sources</span> <span>do not
match.</span><br>
<span>In this case</span><span>, what is the</span>
<span>best</span> <span>practice?<br>
<br>
</span></span></div>
<span lang="en"><span>tia<br>
</span></span></div>
<div class="gmail_extra"><br>
<div class="gmail_quote">2015-04-18 12:44 GMT-03:00 James
Keener <span dir="ltr"><<a href="mailto:jim@jimkeener.com" target="_blank">jim@jimkeener.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">tl;dr:
Have you tried QGIS?<br>
<br>
What were you using to copy/paste before? I didn't
think straight<br>
editing of the DBaseIII files directly was a sane thing
to do, as<br>
they're linked up with the shape and shape-index files.<br>
<br>
PostGIS is just a PostgreSQL database, so any editor
that can allow you<br>
to edit/duplicate PostgreSQL tables could work. As for
mutating<br>
geometries, maybe QGIS? That would also allow you to
edit geometries,<br>
attributes, as well as duplicate features.<br>
<br>
Hope that helps,<br>
Jim<br>
<div>
<div><br>
On 04/18/2015 11:39 AM, Luciano wrote:<br>
><br>
> Hi,<br>
> I wonder how can I update a postgresql postgis
database before the<br>
> following scenario:<br>
> Always worked with shape files and update them
used copy / paste between<br>
> files.<br>
> Now, think about creating a database in
PostgreSQL and would like to<br>
> continue using copy / paste to update polygons,
but in my database<br>
> structure is different from the shape file. For
example:<br>
> Imagine that the shapefile have all the fields
in one table, already in<br>
> the database, by reason of standardization,
have these columns in tables<br>
> distinct. Below is an example of a register of
towns.<br>
><br>
> File shape, columns:<br>
> town code;<br>
> town description;<br>
> Neighborhood code;<br>
> name of the neighborhood;<br>
> block code;<br>
> Street code;<br>
> street name;<br>
><br>
> In Postgres / Gis could look like this:<br>
><br>
> Cities table (data):<br>
> - Town id<br>
> - Description of town<br>
><br>
> Neighborhoods table (data):<br>
> - Id of the neighborhood<br>
> - Description of the neighborhood<br>
> - Id of town (foreign key)<br>
><br>
> Blocks table:<br>
> - Id of the court<br>
> - Block of code<br>
> - Town id (foreign key)<br>
> - Geometry, polygon<br>
><br>
> Streets table:<br>
> - Street id<br>
> - Street name<br>
> - Town id (foreign key)<br>
> - Geometry, line<br>
><br>
> How could update (insert) a block in postgresql
table using copy / paste<br>
> the shape file?<br>
> Would have to create a trigger/procedure
(instead of) to automate the<br>
> process?<br>
> Fields of shape file should be equal to the
fields of database table?<br>
> Some practical example as a reference?<br>
><br>
> tia<br>
> --<br>
> Luciano<br>
><br>
><br>
><br>
</div>
</div>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
<br clear="all">
</div>
</blockquote>
</div>
<br>
-- <br>
Sent from my Android device with K-9 Mail. Please excuse my
brevity.
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div></div><span><font color="#888888"><pre cols="72">--
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
<a href="http://geospatial.commons.gc.cuny.edu" target="_blank">http://geospatial.commons.gc.cuny.edu</a>
<a href="http://freecity.commons.gc.cuny.edu" target="_blank">http://freecity.commons.gc.cuny.edu</a>
</pre>
</font></span></div>
</blockquote></div></div></div><span><font color="#888888"><br><br clear="all"><br>-- <br><div><div dir="ltr"><div><div dir="ltr"><div>Luciano<br></div><br></div></div></div></div>
</font></span></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div></div></div><span class="HOEnZb"><font color="#888888"><br><br clear="all"><br>-- <br><div><div dir="ltr"><div><div dir="ltr"><div>Luciano<br></div><br></div></div></div></div>
</font></span></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>