Example 1 - a shapefile of 3 new trash bins

!Note. Each insertion set of data has to have unique ids compared to other datasets. This means that the gid and the dataid in the Asset -table have to be unique.
This is because we might get 3 separate datasets that all have ids between 1 and 500.
The steps to insert the data are:

1. Inserting the geometry and ids into Asset -table
2. Inserting the data into EquipmentChild -table and connecting it to the Asset table

Say we had inserted a set with dataids between 1 and 500. Then we'd start inserting a second set that also had dataids between 1 and 500. The 1st step would be fine. In the 2nd phase we'd get an error message. This is because the Equipment -table wouldn't know to which 1,2,3,4,5...,500 dataid to connect to since we'd have 2 of each.

1. The Data

Table trashdatatable

id	model	brand	installed	material	geom
1	L & T	City 30	2017-07-15	wood	0101000020270F00004260E576244D7841C74B37E119785941
2	L & T	City 100	2017-07-15	steel	0101000020270F00007368915B0B4E78418D976E4A0B835941
3	L & T	City 60	2017-07-15	plastic	0101000020270F00003BDF4FD50A4E78417B14AE5718835941

2. Extracting x,y,z and creating a temporary table

2.1. If you are lucky enough to have a simple POINT the following works:

CREATE TABLE temp_trash_table AS     
    SELECT id,     
           ST_X(trashdatatable.geom) AS X1, --point x  
           ST_Y(trashdatatable.geom) AS Y1, -- point y
           ST_Z(trashdatatable.geom) AS Z1 -- returns null if not available
    FROM trashdatatable;     

2.2. Otherwise we need some extra steps:

ST_AsText returns MULTIPOINT(first_coord second_coord)

SELECT ST_AsText(geom) FROM trashdatatable;

You'd get a listing like this

MULTIPOINT(25477715.584 6680765.681)
MULTIPOINT(25477676.761 6680732.478)
...

We want the position of first bracket '(' which in this case is always 11

SELECT position('(' in ST_AsText(geom)) FROM trashdatatable;   

We also want to know where there's a space btw the coords

SELECT position(' 6' in ST_AsText(geom)) FROM trashdatatable;

This char index varies as some coordinates have decimals and others don't
So we cut the string from index 12 since it is the next character after '(' that was 11

SELECT substring(ST_AsText(geom) from 12 ) FROM trashdatatable;   

This would return us

25477715.584 6680765.681)   
25477676.761 6680732.478)   
25477676 6680730.847)   

Not ok though, we want to extract both x and y separately
We have to make our code a bit more dynamic
SELECT substring(ST_AsText(geom) from position('(' in ST_AsText(geom))+1 ) FROM trashdatatable;

And add the space location
The substring function works as follows: substring(the_string FROM index_to_start FOR length_of_substring)
length_of_substring = index of ' 6' - index of '(' e.g. 24-11

SELECT substring(ST_AsText(geom) from position('(' in ST_AsText(geom) )+1   
                 FOR position(' 6' in ST_AsText(geom))-position('(' in ST_AsText(geom))  
                 ) 
FROM trashdatatable;   

We want the first function to be our X but Y is also needed. We already know that Y is between ' ' and ')'

SELECT substring(ST_AsText(geom) from position('(' in ST_AsText(geom))+1 
                 FOR position(' 6' in ST_AsText(geom))-position('(' in ST_AsText(geom))-1  
                ) as x,
        substring(ST_AsText(geom) from position(' 6' in ST_AsText(geom))+1 
                 FOR position(')' in ST_AsText(geom))-position(' 6' in ST_AsText(geom))-1  
                ) as y                
FROM trashdatatable;   

If this works it returns something like

x	y
25477715.584	6680765.681
25477676.761	6680732.478
...	...

You are ready to build the temporary table to help us insert the right kind of geometry into Asset table

 DROP TABLE temp_trash_table;
 CREATE TABLE temp_trash_table AS
 SELECT id,
        substring(ST_AsText(geom) from position('(' in ST_AsText(geom))+1 
                 FOR position(' 6' in ST_AsText(geom))-position('(' in ST_AsText(geom))-1  
                ) as x,
        substring(ST_AsText(geom) from position(' 6' in ST_AsText(geom))+1 
                 FOR position(')' in ST_AsText(geom))-position(' 6' in ST_AsText(geom))-1  
                ) as y,
                0 as z
FROM trashdatatable;   

3. Creating a new multipoint geometry from x, y and z columns and INSERTing into asm.Asset -table.

We are using a function ST_MakePoint to make a 3D point, ST_SetSRID to set the coordinate system and
finally ST_Collect to make the point MULTI*
Remember that the extracted x, y and z are all text so we need a cast to make them double (number type with decimals)

INSERT INTO asm."Asset" (dataid , geom_point)   
            SELECT id,   
                   ST_Collect(ST_SetSRID(ST_MakePoint(cast(x as double precision),   
                                                      cast(y as double precision),   
                                                      cast(z as double precision),0.0),3879))     
	    FROM temp_trash_table   
        GROUP BY id;

4. Inserting into TrashEquipment table from Trashdatatable and asm.Asset

! Note that installer is a restricted value and must exist in "Actors"."Maintainer" table

    INSERT INTO "Equipments"."TrashEquipment" ( model , brand , installDate , material, "gid_Asset")
                SELECT t.model, t.brand, t.installed, t.material, a.gid
                FROM trashdatatable t
                JOIN asm."Asset" a  ON (a.dataid = t.id);    

Notice that all the data was written into both TrashEquipment and Equipment tables. This is because TrashEquipment inherits Equipment table.

Next tutorial is Linking an Equipment to InfraPart -table