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
