[postgis-users] Beginning in PostGIS
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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users signature.asc Description: OpenPGP digital signature ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
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 j...@jimkeener.com: 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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Luciano ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
Ok, I have a shape file with the following structure. Shape file, columns: town code; town description; Neighborhood code; name of the neighborhood; block code; Street code; street name; Using the How to Copy / paste a geometry from shape file to postgis with follow structure: 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 2015-04-18 13:36 GMT-03:00 Rémi Cura remi.c...@gmail.com: maintaining your data model is easy to do with triggers. About copy / past. Sorry this just doesn't make much sense to me. You can import shapefile into postgres if it is the question. You can also use trigger on the import table to fill you rmodel with the imported data. Cheers, Rémi-C 2015-04-18 17:39 GMT+02:00 Luciano br.analista...@gmail.com: 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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Luciano ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
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 br.analista...@gmail.com 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 j...@jimkeener.com: 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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users -- Luciano ___ postgis-users mailing list postgis-users@lists.osgeo.org 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 list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
maintaining your data model is easy to do with triggers. About copy / past. Sorry this just doesn't make much sense to me. You can import shapefile into postgres if it is the question. You can also use trigger on the import table to fill you rmodel with the imported data. Cheers, Rémi-C 2015-04-18 17:39 GMT+02:00 Luciano br.analista...@gmail.com: 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 postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] Beginning in PostGIS
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 remi.c...@gmail.com: 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 br.analista...@gmail.com: 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 lee.hachadooria...@gmail.com : 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 br.analista...@gmail.com br.analista...@gmail.com 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 j...@jimkeener.com: 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
Re: [postgis-users] Beginning in PostGIS
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 br.analista...@gmail.com: 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 remi.c...@gmail.com: 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 br.analista...@gmail.com: 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 lee.hachadooria...@gmail.com: 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 br.analista...@gmail.com br.analista...@gmail.com 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 j...@jimkeener.com: 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
