[postgis-users] Beginning in PostGIS

Sat Apr 18 20:22:00 PDT 2015

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.analistagis at 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.cura at 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.analistagis at 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.Hachadoorian+L at 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.analistagis at gmail.com>
>>>> <br.analistagis at 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 <jim at 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 at lists.osgeo.org
>>>>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>> >
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at 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 at lists.osgeo.orghttp://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
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Luciano
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150419/972a1717/attachment.html>