Re: [postgis-users] Beginning in PostGIS

[Rémi Cura]

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 :

> 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 :
>
> 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
>>  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 :
>>
>>> 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 
  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 :
>
>> tl;dr: Have you tried QGIS

Re: [postgis-users] Beginning in PostGIS

[Luciano]

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 :

> 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
>  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 :
>
>> 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 > >:
>>
>>  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 
>>>  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 :

> 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
>
>>

Re: [postgis-users] Beginning in PostGIS

[Rémi Cura]

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
 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 :

> 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 
> :
>
>  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 
>>  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 :
>>>
 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
 > s

Re: [postgis-users] Beginning in PostGIS

[Luciano]

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 :

>  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 
>  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 :
>>
>>> 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
>>>
>>
>>
>>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> ___
> postgis-users mailing 
> listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailma

Re: [postgis-users] Beginning in PostGIS

[Lee Hachadoorian]

  
  
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  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 :
  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
> - De

Re: [postgis-users] Beginning in PostGIS

[James Keener]

OH, I think I understand: You want to copy and paste from a shapefile
layer into the appropriate postgis layer?

I don't know if you can do that directly.  You may want to write a
script to do it, or import the shapefile into a temporary table and then
using multiple sql statements to build up your normalized tables.

http://stackoverflow.com/questions/1310103/split-table-with-duplicates-into-2-normalized-tables

has a little example.  Basically you import the towns with a insert into
blah select distinct (town code, town description) and then continue
that, but for the foreign keys have your updates or inserts do a
subquery to find the correct foreign row.

Jim

On 04/18/2015 02:01 PM, Luciano wrote:
> 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 fromshape file to postgiswith
> 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  >:
> 
> 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  >:
> 
> 
> 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
> 



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

[Luciano]

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 :

> 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 :
>
>>
>> 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

[Rémi Cura]

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 :

>
> 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

[James Keener]

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  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 :
>
>> 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

[Luciano]

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 :

> 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

[James Keener]

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

[postgis-users] Beginning in PostGIS

[Luciano]

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