[postgis-users] Beginning in PostGIS

Rémi Cura remi.cura at gmail.com
Sat Apr 18 12:19:28 PDT 2015 

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

More information about the postgis-users mailing list