[postgis-users] Beginning in PostGIS

Sat Apr 18 14:36:46 PDT 2015

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150418/6c1ae44c/attachment.html>