Glad it works ^^ A note for archive : this solution won't be super-efficient. This looks like a textbook case for using the postgres rule system. 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.
Cheers, Rémi-C 2015-04-18 23:36 GMT+02:00 Luciano <[email protected]>: > Very good !! > > based on Remi's advice and some adjustments, it worked! > > Now the table is updating using the SQL Editor or the Qgis. > > Thank you all! > > 2015-04-18 16:19 GMT-03:00 Rémi Cura <[email protected]>: > > Ok, this is totally a postgres question then. >> >> First you create your data model (here it is just for example, you should >> pick better name, don't use capital, no space, etc) >> >> >> CREATE TABLE Cities (...) >> CREATE TABLE Neighborhoods () >> CREATE TABLE Blocks() >> CREATE TABLE Streets () >> >> From here I expect a correct data model, with primary key, foreign key, >> constraints, etc. >> >> You create a postgres table importing_data : >> CREATE TABLE importing_data ( >> iid serial PRIMARY KEY >> ,town_code int >> ,town_description >> >> text >> ,Neighborhood_code int >> ,name_of_the_neighborhood text >> ,block_code int >> ,Street_code int >> ,street_name text) ; >> >> Now you define a trigger >> <http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html> on >> this table >> >> CREATE TRIGGER name AFTER INSERT >> ON importing_data >> FOR EACH ROW >> EXECUTE PROCEDURE filling_data_model() >> >> This trigger says that every time you insert a line in 'importing_data', >> the function 'filling_data_model()' gets called. >> >> Now you define this function so that it does what you want (filling you >> data model) >> >> CREATE OR REPLACE FUNCTION >> filling_data_model( ) >> RETURNS trigger AS >> $BODY$ >> -- >> >> this trigger break an inserted line in table >> >> importing_data >> -- >> >> and put hte informations in the tables >> >> -- >> Cities,Neighborhoods,Blocks,Streets >> DECLARE >> BEGIN >> --the inserted line >> in 'importing_data' >> is in the variable 'NEW' >> >> >> --insert into city >> INSERT INTO Cities VALUES (NEW[1], NEW[2]) ; >> --insert into Neighborhoods >> INSERT INTO ... >> .... >> return NEW; >> END ; >> $BODY$ LANGUAGE plpgsql VOLATILE; >> >> Based on the information you gave, you probably don't want to do an >> insert, but rather an upsert (see here for instance: >> http://stackoverflow.com/a/8702291/330315) >> >> Now it is super easy, simply import your shapefile into the >> 'importing_data' table, and it should be good >> >> cheers >> Rémi-C >> >> >> 2015-04-18 20:42 GMT+02:00 Luciano <[email protected]>: >> >>> Ok Remi, and Jim, thanks... >>> >>> Lee, >>> >>> Thats it, >>> >>> the problem is that I get/receive a shape file with the following >>> structure and need to use it to update my database. >>> >>> But my database does not have the same file's structure. >>> >>> As mentioned above... >>> >>> tia >>> >>> 2015-04-18 15:14 GMT-03:00 Lee Hachadoorian < >>> [email protected]>: >>> >>> Luciano, >>>> >>>> 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.? >>>> >>>> Best, >>>> --Lee >>>> >>>> >>>> On 04/18/2015 12:22 PM, James Keener wrote: >>>> >>>> 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. >>>> >>>> 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. >>>> >>>> Can you give a more complete example of the issue you're facing? >>>> >>>> Jim >>>> >>>> Jim >>>> >>>> On April 18, 2015 12:11:38 PM EDT, Luciano <[email protected]> >>>> <[email protected]> wrote: >>>>> >>>>> Yes, I'm using QGIS. I agree, if I make a table in the database with >>>>> the same structure the shape file is simple. The copy / paste works >>>>> perfectly. >>>>> But my question is how to update for example the blocks table, using >>>>> the copy / paste, since the database structure is different. >>>>> For example, if I copy a polygon layer shape, and try to stick to the >>>>> database layer, the fields of the new polygon will be void. >>>>> Note that my database blocks table does not have the same structure of >>>>> the shape file because it is normalized (or should be), so the fields >>>>> of two data sources do not match. >>>>> In this case, what is the best practice? >>>>> >>>>> tia >>>>> >>>>> 2015-04-18 12:44 GMT-03:00 James Keener <[email protected]>: >>>>> >>>>>> tl;dr: Have you tried QGIS? >>>>>> >>>>>> What were you using to copy/paste before? I didn't think straight >>>>>> editing of the DBaseIII files directly was a sane thing to do, as >>>>>> they're linked up with the shape and shape-index files. >>>>>> >>>>>> PostGIS is just a PostgreSQL database, so any editor that can allow >>>>>> you >>>>>> to edit/duplicate PostgreSQL tables could work. As for mutating >>>>>> geometries, maybe QGIS? That would also allow you to edit geometries, >>>>>> attributes, as well as duplicate features. >>>>>> >>>>>> Hope that helps, >>>>>> Jim >>>>>> >>>>>> On 04/18/2015 11:39 AM, Luciano wrote: >>>>>> > >>>>>> > Hi, >>>>>> > I wonder how can I update a postgresql postgis database before the >>>>>> > following scenario: >>>>>> > Always worked with shape files and update them used copy / paste >>>>>> between >>>>>> > files. >>>>>> > Now, think about creating a database in PostgreSQL and would like to >>>>>> > continue using copy / paste to update polygons, but in my database >>>>>> > structure is different from the shape file. For example: >>>>>> > Imagine that the shapefile have all the fields in one table, >>>>>> already in >>>>>> > the database, by reason of standardization, have these columns in >>>>>> tables >>>>>> > distinct. Below is an example of a register of towns. >>>>>> > >>>>>> > File shape, columns: >>>>>> > town code; >>>>>> > town description; >>>>>> > Neighborhood code; >>>>>> > name of the neighborhood; >>>>>> > block code; >>>>>> > Street code; >>>>>> > street name; >>>>>> > >>>>>> > In Postgres / Gis could look like this: >>>>>> > >>>>>> > Cities table (data): >>>>>> > - Town id >>>>>> > - Description of town >>>>>> > >>>>>> > Neighborhoods table (data): >>>>>> > - Id of the neighborhood >>>>>> > - Description of the neighborhood >>>>>> > - Id of town (foreign key) >>>>>> > >>>>>> > Blocks table: >>>>>> > - Id of the court >>>>>> > - Block of code >>>>>> > - Town id (foreign key) >>>>>> > - Geometry, polygon >>>>>> > >>>>>> > Streets table: >>>>>> > - Street id >>>>>> > - Street name >>>>>> > - Town id (foreign key) >>>>>> > - Geometry, line >>>>>> > >>>>>> > How could update (insert) a block in postgresql table using copy / >>>>>> paste >>>>>> > the shape file? >>>>>> > Would have to create a trigger/procedure (instead of) to automate >>>>>> the >>>>>> > process? >>>>>> > Fields of shape file should be equal to the fields of database >>>>>> table? >>>>>> > Some practical example as a reference? >>>>>> > >>>>>> > tia >>>>>> > -- >>>>>> > Luciano >>>>>> > >>>>>> > >>>>>> > >>>>>> > _______________________________________________ >>>>>> > postgis-users mailing list >>>>>> > [email protected] >>>>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> > >>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> postgis-users mailing list >>>>>> [email protected] >>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> >>>>> >>>>> >>>>> >>>> -- >>>> Sent from my Android device with K-9 Mail. Please excuse my brevity. >>>> >>>> _______________________________________________ >>>> postgis-users mailing >>>> [email protected]http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>>> >>>> -- >>>> Lee Hachadoorian >>>> Assistant Professor in Geography, Dartmouth >>>> Collegehttp://geospatial.commons.gc.cuny.eduhttp://freecity.commons.gc.cuny.edu >>>> >>>> >>> >>> >>> -- >>> Luciano >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > > -- > Luciano > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
